8. User-Defined Types
You can create your own composite types out of the existing Visual Basic types. These composite types are called user-defined
types in Visual Basic and they are used
primarily for advanced tasks such as reading and writing binary files or
working with Windows API
functions
.
Use the Type statement to define a user-defined type:
' Code in Variables module
Private Type POINTAPI
x As Long
y As Long
End Type
Private Declare Function GetCursorPos _
Lib "user32" (lp As POINTAPI) As Long
The preceding module-level definition creates a type named POINTAPI that contains two Long types. This definition matches the argument returned by the GetCursorPos
Windows API function, and it enables you to get at the values returned
by that function in code. For example, the following procedure displays
the location of the cursor:
Sub ShowCursorPosition( )
Dim point As POINTAPI
GetCursorPos point
MsgBox point.x & " " & point.y
End Sub
The preceding code declares the point variable using the POINTAPI type defined earlier; then it calls the Windows GetCursorPos function to fill in the value of point. It is common for Windows API functions to return values through user-defined types
in this way (Windows calls user-defined types structures
). Variables with user-defined types use the period to get items from within the type. Thus, point.x gets the value of the x-coordinate in the preceding example.
9. Objects
Object is the general term for an instance of a class. Visual Basic has an Object
type that you can use to create variables that reference any generic
object; however, you usually want to create variables of a specific
class of object. Objects are a special kind of variable because you can
control when they are created. Other types of variables in Visual Basic
are initialized whenever they are declared, but that's not true with
objects
.
The easiest way to create an object variable
is to include the New keyword in the variable declaration. For example, the following line creates a new object variable from the PublicClass class definition:
Dim obj As New PublicClass
Once created, you can use that object's properties and methods to do whatever it is you want to do. New
is an executable statement; if you use it at the module level, the
actual creation of the object is delayed till the first time the object
is referenced within a procedure—a confusing situation that is best to
avoid. If you declare an object variable at the module level, omit New,
then create the object within a procedure explicitly. For example, the
following code creates a global object variable and creates the object
the first time UseObject runs:
' Global object variable
Public g_obj As PublicClass
Sub UseObject( )
' Create global object variable
If g_obj Is "Nothing" Then Set g_obj = New PublicClass
' Show that the object exists
Debug.Print g_obj.CREATED
End Sub
There are a few significant things to point out about the preceding code:
The module-level
declaration uses a specific class type. That makes the class's methods
and properties available to Visual Basic's Auto Complete feature as you
write code.
TypeName(g_obj) = "Nothing" is True if the object has not been created. In that case, the Set statement creates a new instance of the object.
Visual Basic also provides an IsEmpty function to check if an object has been created, but that works only with the generic Object type—it doesn't work with specific classes.
To destroy an object, set the object variable to Nothing:
Set g_obj = Nothing
This is not
necessary when the object is a local variable, since those are
automatically destroyed when the procedure ends. However, global,
module-level, and Static variables exist as long as the workbook is open unless you explicitly destroy them.
Excel provides many
objects that you can use from Visual Basic, but they can be created only
through other Excel objects. For example, the following two statements
are equivalent, and neither one creates a new worksheet!
Dim ws1 As Worksheet
Dim ws2 As New Worksheet
To create an object from Excel, you usually use the Add method of the object's collection class:
Sub CreateExcelObject( )
' Declare a Worksheet object variable
Dim ws As Worksheet
' Create the Worksheet
Set ws = Worksheets.Add
End Sub
Since Excel controls the creation of its objects, it also controls their destruction. Setting ws to Nothing just destroys the object reference; it doesn't remove the worksheet. To destroy an Excel object, you usually use the object's Delete method as shown here:
ws.Delete
Visual Basic includes the keyword With to create blocks of code that work with a specific object. The With
statement creates a shorthand for repeatedly referring to the same
object, and you will frequently see it in recorded code. For example,
the following code creates a new worksheet and sets the object's
properties using With:
Sub UseWith( )
' Create a new worksheet in a With statement
With Worksheets
.Add
.Name = "New Worksheet"
.Range("A1") = "Some new data..."
ActiveWindow.DisplayGridlines = False
End With
End Sub
The Worksheets.Add
method returns a reference to the worksheet object that is then used by
the subsequent properties. Each property begins with a period inside of
the With block. Statements that don't refer to the object, such as ActiveWindow, simply omit the period.
There's nothing wrong with using With, but I prefer to use the variable name explicitly. That's just my style. |
