Passing User-Defined TypesThis 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 you to 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 have defined according to the function's requirements.
You can figure out when you must pass a user-defined type and which type definition you must include in your code by looking at the Declare statement for the function. An argument requiring a data structure always is 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 is 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 is 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 Property Get 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 Property Let 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 all values will be current, ' then set hour portion of local time. GetLocalTime sysLocalTime sysLocalTime.wHour = intHour SetLocalTime sysLocalTime End Property
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.