The Object Data Type

A chapter on Excel objects would not be complete without a discussion of the object data type. If you find multiple instances of the same object in your program, then you can use an object variable to handle the reference rather than constantly retyping the qualifiers. Also, variables can be assigned meaningful names, making the program easier to interpret. Object variable are similar to other VBA data types in that they must be declared in code. For example,

Dim myObject as Object declares an object variable named myObject; however, assigning a value to an object variable differs from assignments to more common data types. The Set keyword must be used to assign an object reference to a variable.

Set myObject = Range("A1:A15")

This will assign the Range object representing cells A1 through A15 to the variable myObject. Properties of the object can then be initialized in the usual way.

myObject.Font.Bold = True

This sets the values in cells A1 through A15 to be displayed in bold-face type. Declaring variables as above using the general object data type is not recommended because the object will not be bound to the variable until run-time. If VBA has trouble resolving references to various properties and methods when checking them at run-time, it can significantly slow down execution of a program. I recommend that you use object-specific data types whenever possible. Any object type can be used—just consult the Object Browser for a list of available types. Using the Range object, the above example can be rewritten thusly:

Dim myRange as Excel.Range Set myRange=Range("A1:A15") myRange.Font.Bold = True

You may also include the library (Excel) in your declaration to avoid any ambiguity; however, it is the object type (Range) that is important. Now the object will be referenced at compile time and VBA will have no trouble working out references to the properties and methods of the object, as the type of object and the library to which it belongs have been explicitly declared. You will see more examples of object variable types in the next section, in subsequent chapters, and in the Battlecell program.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment