Return to VBA Code Examples

VBA Set Object Variables – Workbooks, Worksheets, & More

This tutorial will teach you how to define object variables using the Set statement in VBA.

Defining Object Variables

Objects are the cornerstone of Microsoft Office – without objects, we cannot achieve anything.  In Excel, objects include the Workbook, Worksheet or Range Objects. In Microsoft Word, examples are the Document or Table object. Each object has a variety of Properties and Methods that can be programmed to control the behavior of that object.

Declaring the Object Variable

Before we can reference the object in code, and therefore control the object, we need to declare the object. We can do this using the Dim Statement.

This Dim declaration can occur inside a procedure:

vba objects 1

or outside a procedure at the module-level:

vba objects 2

If the variable is declared at the module-level (outside the procedure), the variable can be used throughout the module.

If the object variable is declared with the Public statement then the variable can be used throughout the VBA Project:

vba objects 3

Set Value

Once you have declared the object, you need to assign a value to the object. This must be done using the Set statement and can only be done within a Procedure.

Note: This is different than assigning values to non-object variables. You MUST use the Set statement to assign the object to the variable. If you do not, you’ll receive an error:

vba objects error

Once you have assigned a value to the object, you can then write code to control the behavior or manipulate the object.

VBA Programming | Code Generator does work for you!

Object Examples in Excel

Workbook Object

Once you have declared a workbook variable, you can then assign a workbook to that object and use the Properties and Methods available to manipulate that object. In the example below we are going to save a workbook.

Worksheet Object

Similarly you can manipulate a worksheet or worksheets once you have declared the worksheet as a variable. In the example below, we rename Sheet1 and Sheet2.

Tired of Searching for VBA Code Examples? Try AutoMacro!

Range Object

The Range object is one of the most  useful objects to manipulate in Excel. In the example below, we bold Range A1 to E1 and format it with a bottom border.

Shape Object

You can also use object variables to work with shapes.