Declaring and Using Object Variables

The variables that you've learned so far are used to store data. Storing data is the main reason for using "normal" variables in your procedures. In addition to the normal variables that store data, there are special variables that refer to the Visual Basic objects. These variables are called object variables. In Chapter 2, you learned a few things about various objects. Now, you will learn how you can represent an object with the object variable.

Object variables don't store data. They tell where the data is located. For example, with the object variable you can tell Visual Basic that the data is located in cell E10 of the active worksheet. Object variables make it easy to locate data. When writing Visual Basic procedures, you often need to write long instructions, such as:

Worksheets("Sheet1").Range(Cells(1,1), Cells(10, 5).Select

Instead of using long references to the object, you can declare an object variable that will tell Visual Basic where the data is located. Object variables are declared in a similar way to the variables you already know. The only difference is that after the As keyword, you enter the word Object as the data type. For instance:

Dim myRange As Object

The statement above declares the object variable named myRange.

Well, it's not actually enough to declare the object variable. You also have to assign a specific value to the object variable before you can use this variable in your procedure. Assign a value to the object variable by using the Set keyword. The Set keyword is then followed by the equal sign and the value that the variable will refer to. For example:

Set myRange = Worksheets("Sheet1").Range(Cells(1,1), Cells(10, 5))

The above statement assigns a value to the object variable myRange. This value refers to cells A1:E10 in Sheetl. If you omit the word Set, Visual Basic will display an error message—"Run-time error 91: Object variable or With block variable not set."

Again, it's time to see a practical example. The UseObjVariable procedure shown below demonstrates the use of the object variable called myRange:

Sub UseObjVariableO

Dim myRange As Object

Set myRange = Worksheets("Sheet1"). _

Range(Cells(1, 1), Cells(10, 5)) myRange.BorderAround Weight:=xlMedium With myRange.Interior .ColorIndex = 6 .Pattern = xlSolid End With

Set myRange = Worksheets("Sheet1"). _ Range(Cells(12, 5), Cells(12, 10)) myRange.Value = 54 Debug.Print IsObject(myRange) End Sub

Let's examine the code of the UseObjVariable procedure line by line. The procedure begins with the declaration of the object variable myRange. The next statement sets the object variable myRange to Range A1:E10 on Sheet1. From now on, every time you want to reference this range, instead of using the entire object's address, you'll use the shortcut—the name of the object variable. The purpose of this procedure is to create a border around range A1:E10. Instead of writing a long instruction:

Worksheets("Sheet1").Range(Cells(1, 1), _

Cells(10, 5)).BorderAround Weight:=xlMedium you can take a shortcut by using the name of the object variable: myRange.BorderAround Weight:=xlMedium

The next series of statements changes the color of the selected range of cells (A1:E10). Again, you don't need to write the long instruction to reference the object that you want to manipulate. Instead of the full object name, you can use the myRange object variable. The next statement assigns a new reference to the object variable myRange. Visual Basic forgets the old reference, and the next time you use myRange, it refers to another range (E12:J12). After the number 54 is entered in the new range (E12:J12), the procedure shows you how you can make sure a specific variable is of the Object type. The instruction Debug.Print IsObject(myRange) will enter True in the Immediate window if myRange is an object variable. IsObject is a VBA function that indicates whether a specific value represents an object variable.

0 0

Post a comment