Assigning an Object to a Variable
Objects have their own data types. You can declare a variable as an object by using the following form of the Dim statement:
Dim variableName As ObjectType
Here, ObjectType is the data type of the object you want to work with. For example, if you want to work with a Document object, you'd use a Dim statement similar to this:
Dim currentDocument As Document
It's a good idea to use object variables whenever you can because it enables you to use the Visual Basic Editor's handy IntelliSense features. Using the preceding example, if I type currentDocument and then a period (.), VBA displays a list of all the properties and methods associated with that object.
In the list, the items that have the green blocks with motion lines are the methods, whereas the items that have the rectangles with a pointing finger are the properties.
After you've set up your object variable, you can assign an object to it by using the Set statement. Set has the following syntax:
Set variableName = ObjectName
variableName
The name of the variable.
ObjectName
The object you want to assign to the variable.
For example, the following statements declare a variable named budgetSheet to be a Worksheet object and then assign it to the 2008 Budget worksheet in the Budget.xls workbook:
Dim budgetSheet As Worksheet
Set budgetSheet = Workbooks("Budget.xls").Worksheets("2008 Budget")
Note: Object variables take up memory. For optimum code performance, you can reclaim the memory used by unneeded object variables by setting the variable equal to Nothing, like so:
Set budgetSheet = Nothing