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
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.
Objects must be ‘Set’. The word Set appears before the name of the variable.
'Assign an object to an Object variable Set wkSheet = ActiveWorksheet
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.
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.
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:
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)