This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

2.3 Object Variables

Beginning VBA

Beginning VBA

Previous Part: 2.2 Loops (part 1)

 

What are object variables?

In Section 2.1 Using Variables, objects were listed as a type of variable.  Whilst this is a perfectly acceptable statement, in this part, we will explore this in a bit more detail.

If you remember back to Section 1.4 Understanding the Code, objects come in various types, worksheets are objects, cells are objects, charts are objects.  In VBA, it is possible to declare a bland ‘object’ as a variable.

'Declare a worksheet as an object
Dim wkSheet as Object

It is also possible to be more specific by declaring an object variable.  For example, the code below creates a worksheet object variable called wkSheet.

'Declare a worksheet as a worksheet
Dim wkSheet as Worksheet

Advertisement:

Both pieces of code are acceptable and will run without error.  A variable declared as an object can hold all types of objects.  By declaring a variable as a type of object, it can only hold that type of object.  For example, a worksheet object variable can only hold worksheets.

 

Objects are always Set

After declaring an object variable, something can be put in it.  Normally to assign values to variables an equals symbol is used

'Assign an object to an Object variable (won't work)
wkSheet = ActiveWorksheet

But this doesn’t work for objects, it will result in an error message.

Error when object variable not set

Objects must be ‘Set’.  The word Set appears before the name of the variable.

'Assign an object to an Object variable 
Set wkSheet = ActiveWorksheet

Advertisement:

It is now possible to refer to the ActiveWorksheet with just wkSheet.  This does not seem like much of a saving, but when working with longer sections of code it can be a big time-saving.  For example, when consistently referring to a specific cell, the following code would take a long time to write each time.

Workbooks("ReferenceBook.xlsx").Sheets("TaxCodes").Range("B3").Value

Instead, a range object variable could be created.

Dim SalesTax as Range
Set SalesTax = Workbooks("ReferenceBook.xlsx").Sheets("TaxCodes").Range("B3")

Now, each time the cell value is required the code could be simplified down as follows.

SalesTax.Value

 

Common object variables

Any object can be used as a type of object variable, but the most common types for any beginner VBA programmer are:


Advertisement:

Workbook – any Excel workbook which is already open:

'Set a specific workbook to a variable
Dim wbk As Workbook
Set wbk = Workbooks("NameOfWorkbook.xlsx")

'Set the Active Workbook to a variable 
Dim wbk As Workbook 
Set wbk = ActiveWorkbook

'Set the 3rd opened workbook to a variable 
Dim wbk As Workbook 
Set wbk = Workbooks(3)

'Set the Workbook containing the VBA code to a variable 
Dim wbk As Workbook 
Set wbk = ThisWorkbook

'Open a workbook and set to a variable
Dim wbk As Workbook
Set wb = Workbooks.Open("C:\Users\marks\Documents\openThisWorkbook.xlsx")

'Create new workbook and set to a variable
Dim wbk As Workbook
Set wbk = Workbooks.Add

Worksheets – any Worksheet within a workbook

'Set a specific worksheet to a variable 
Dim ws As Worksheet 
Set ws = Sheets("Name of Worksheet")

'Set the Active Sheet to a variable 
Dim ws As Worksheet 
Set ws = ActiveSheet

'Set the 3rd worksheet to a variable
Dim ws As Worksheet
Set ws = Sheets(3)

'Create a new worksheet and set to a variable
Dim ws As Worksheet
Set ws = Worksheets.Add

Range – any range of cells on a worksheet

'Set a range of cells to a variable
Dim rng As Range
Set rng = Workbooks(1).Sheets(2).Range("B2")

 

Previous Part: 2.2 Loops (part 1)

Next Part: 2.4 Loops (part 2)

Save