Assigning an Object to a Variable

     

As I mentioned at the end of Chapter 3, "Understanding Program Variables ," 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  
graphics/note_icon.gif

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 

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 that I described in detail in Chapter 1 (see "Taking Advantage of IntelliSense").

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 2004 Budget worksheet in the Budget.xls workbook:

 Dim budgetSheet As Worksheet Set budgetSheet = Workbooks("Budget.xls").Worksheets("2004 Budget") 


Absolute Beginner's Guide to VBA
Absolute Beginners Guide to VBA
ISBN: 0789730766
EAN: 2147483647
Year: 2003
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net