Passing User-Defined Types

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Many DLL functions require that you pass in a data structure by using a predefined format. When calling a DLL function from VBA, you pass a user-defined type that you've defined according to the function's requirements.

You can figure out when you need to pass a user-defined type and which type definition you need to include in your code by looking at the Declare statement for the function. An argument requiring a data structure is always declared as a long pointer: a 32-bit numeric value that points to the data structure in memory. The conventional prefix for a long pointer argument is "lp". In addition, the data type for the argument will be the name of the data structure.

For example, take a look at the Declare statements for the GetLocalTime and SetLocalTime functions:

Private Declare Sub GetLocalTime Lib "kernel32" (lpSystem As SYSTEMTIME)
Private Declare Function SetLocalTime Lib "kernel32" (lpSystem As SYSTEMTIME) As Long

Both functions take an argument of type SYSTEMTIME, a data structure that contains date and time information. Here's the definition for the SYSTEMTIME type:

Private Type SYSTEMTIME
      wYear          As Integer
      wMonth         As Integer
      wDayOfWeek     As Integer
      wDay           As Integer
      wHour          As Integer
      wMinute        As Integer
      wSecond        As Integer
      wMilliseconds  As Integer
End Type

To pass the data structure to a function, you must declare a variable of type SYSTEMTIME, as in the following example:

Private sysLocalTime As SYSTEMTIME

When calling GetLocalTime, you pass a variable of type SYSTEMTIME to the function, and it fills the data structure with numeric values indicating the current local year, month, day, day of week, hour, minute, second, and millisecond. For example, the following PropertyGet procedure calls GetLocalTime to return a value indicating the current hour:

Public Property Get Hour() As Integer
   ' Retrieve current time, then return hour.

   GetLocalTime sysLocalTime
   Hour = sysLocalTime.wHour
End Property

When calling SetLocalTime, you also pass a variable of type SYSTEMTIME, but you first provide values for one or more of the elements of the data structure. For example, the following PropertyLet procedure sets the hour value for the local system time. First, it calls GetLocalTime to retrieve the most current values for the local time into the data structure, sysSystem. Then it updates the value of the sysLocalTime.wHour element of the data structure with the value of the argument passed to the property procedure. Finally, it calls SetLocalTime, passing in the same data structure, which contains the values retrieved by GetLocalTime plus the new hour value.

Public Property Let Hour(intHour As Integer)
   ' Retrieve current time so that all values will be current,
   ' then set hour portion of local time.
   
   GetLocalTime sysLocalTime
   sysLocalTime.wHour = intHour
   SetLocalTime sysLocalTime
End Property

The Hour property procedures are available in the System class module in System.xls in the ODETools\V9\Samples\OPG\Samples\CH10 subfolder on the Office 2000 Developer CD-ROM.

Note   The GetLocalTime and SetLocalTime functions are similar to the GetSystemTime and SetSystemTime functions. The primary difference is that the GetSystemTime and SetSystemTime functions express time as Greenwich mean time. For example, if your local time is 12:00 midnight, and you live on the West Coast, Greenwich mean time is 8:00 A.M., an eight-hour difference. The GetSystemTime function returns the current time as 8:00 A.M., while GetLocalTime returns 12:00 midnight.

For more information about creating property procedures in class modules, see Chapter 9, "Custom Classes and Objects."