Programming Excel with VBA and .NET

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.