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.

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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

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.