Microsoft Office Tutorials and References
In Depth Information
an array. Often the value of the variable isn’t known until the procedure is executing. For example, if
the variable x contains a number, you can define the array’s size by using this statement:
ReDim MyArray (1 to x)
You can use the ReDim statement any number of times, changing the array’s size as often as you
need to. When you change an array’s dimensions the existing values are destroyed. If you want
to preserve the existing values, use ReDim Preserve . For example:
ReDim Preserve MyArray (1 to y)
Arrays crop up later in this chapter when I discuss looping (“Looping blocks of instructions”).
An object variable is a variable that represents an entire object, such as a range or a worksheet.
Object variables are important for two reasons:
h They can simplify your code significantly.
h They can make your code execute more quickly.
Object variables, like normal variables, are declared with the Dim or Public statement. For
example, the following statement declares InputArea as a Range object variable:
Dim InputArea As Range
Use the Set keyword to assign an object to the variable. For example:
Set InputArea = Range(“C16:E16”)
To see how object variables simplify your code, examine the following procedure, which doesn’t
use an object variable:
Worksheets(“Sheet1”).Range(“A1”).Value = 124
Worksheets(“Sheet1”).Range(“A1”).Font.Bold = True
Worksheets(“Sheet1”).Range(“A1”).Font.Italic = True
Worksheets(“Sheet1”).Range(“A1”).Font.Size = 14
Worksheets(“Sheet1”).Range(“A1”).Font.Name = “Cambria”