Programming Excel with VBA and .NET : Variables (part 4) - User-Defined Types & Objects

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.