Dynamic Link Libraries (DLLs)

team lib

Even if you have not done any VBA or C coding before, you may well have come across DLLs. Just have a look in the System or System32 subdirectories of the Windows directory on your computer and you will find tens, if not hundreds or even thousands of them. But what exactly are DLLs?

Well, clearly they are files. In fact, they are code files, similar to the modules that you get in Access. DLL stands for dynamic link library and if we look at what the name means, we begin to understand a little better how they work. They are called libraries because they contain a number of procedures (functions or subprocedures) that can be read and used. Similar to a real library where people can access the library, this form of library is a collection of procedures.

Other programs can access (or link to) the library to use a procedure whenever they want. This means that the other programs don't need to contain their own copy of the procedure, saving the programmer's expense and the user 's disk space.

The dynamic part of the name comes from the fact that the DLL itself isn't loaded into memory until an application needs it. It's the same as saying that a book is only borrowed from a real library when someone wants to read it. There is no real limit on the number of applications that can link to a DLL at the same time. Windows keeps track of when the DLL needs to be loaded into memory, and when applications have finished with the DLL it can be unloaded from memory.

Some DLLs contain functions with fairly limited appeal . Others, such as Kernel32.dll , User32.dll , and Gdi32.dll , are used by every Windows application to provide their basic operations. User32.dll provides the code that Windows provides for all aspects of interaction with the user interface; Gdi32.dll provides the code that is used for graphics rendering and interaction with other types of display device, such as printers; and Kernel32.dll provides a host of routines for low-level system activities, such as memory management and file input and output.

As a Windows application, our VBA code can also use the functions in these DLLs to achieve things that aren't otherwise supported in Access. There are literally hundreds of functions in these three DLLs, so we have plenty of scope! Of course, we can make use of functions in other DLLs as well and, if none provide the function we need, we can even build our own DLL if we have tools like Visual Basic or Visual C++. They really do provide a useful way of extending the functionality of VBA without too much hard work. It is also possible to purchase new DLLs.

So let's move on and see how we use DLLs in VBA.

Declaring a DLL in Code

Before we can use a procedure in a DLL, we must tell VBA where it is - both the name of the DLL itself, and which procedure (function or subprocedure) in that DLL we want to use. In our book and library analogy this is like telling a borrower (VBA), where the library (DLL) is located, and what book (Procedure) to borrow (Declare). VBA can then dynamically link to that routine, as required, while the application is running. VBA doesn't automatically check that the procedure exists, either when you compile or start the application. If it can't be accessed, you will only find this out when an error message appears as you make the call to the function at run time.

One very useful DLL function we can use is timeGetTime which can be found in Winmm.dll . This function returns the number of milliseconds that have elapsed since Windows started. It is useful because we can execute this function twice, once before and once after executing a portion of code and, by subtracting one from another, we can determine how long the code took to execute. This is a typical example of using a procedure in a DLL to enhance standard Access or VBA functionality - the Timer function in Access is only accurate to a whole second. Another function, GetTickCount() in Kernel32.dll can also be used to return the number of milliseconds that have elapsed since Windows started, but the accuracy of GetTickCount() varies between different operating systems and processors.

The process of calling a procedure in a DLL is also referred to as 'making an API call'. API stands for application programming interface - the published set of procedures that a particular application or operating system provides for the programmer to use.

In order to declare the timeGetTime() function, we place the following statement in the Declarations section of a module:

 Declare Function timeGetTime Lib "WINMM" () As Long 

This indicates that the function is called timeGetTime() and that it returns a Long type value. It is in the WINMM library ( Winmm.dll ) and it takes no parameters, hence the empty brackets towards the end of the function declaration.


Make sure that you type the function declaration exactly as it appears above. The names of functions and subprocedures in DLLs in 32-bit versions of Windows (for instance, Windows NT, 2000, and XP) are case-sensitive. If you do not capitalize the name of the function or subprocedure correctly Access will generate an error message and the code will not execute correctly.

Once a function has been declared in this manner, all we have to do is execute it. If you type the above declaration in a module, and then enter the following in the Immediate window:


The number of milliseconds since Windows was started will be returned. We will return to this function later on when we look at optimizing VBA in Chapter 17.

Did you know that there is also an API call (that is, DLL procedure) that can tell you whether or not the user has swapped the left and right mouse buttons around? To declare it, we place the following statement in the Declarations section of a module:

 Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long 

The function is called GetSystemMetrics() and is in the User32.dll library. We haven't specified a path in front of the library name, so the following directories are searched in turn to find the DLL.

  • The current directory

  • The Windows system directory

  • The Windows directory

  • The directories listed in the PATH environment variable

    If you want to use a DLL that resides anywhere else but these four locations, you need to qualify it with a full path.

The GetSystemMetrics() function takes an argument nIndex of type Long and returns a Long . The function actually returns information about the layout of the user interface (for example, the height of window title bars, the width of scroll bars) as well as other juicy tidbits, such as whether the user has swapped the left and right mouse buttons (via the Control Panel). The argument nIndex is used to specify a constant indicating the type of information we want. In our case, we want to know whether the mouse buttons have been swapped. So we need to declare the following constant and pass it as an argument to the function:

 Public Const SM_SWAPBUTTON = 23 

In fact, you can try it out for yourself!

Try It Out-Using a Function in a DLL

  1. Open the Chapter 11 Code module.

  2. In the Declarations section, type the following function declaration:

       Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long   
  3. Now declare the following constant, again in the Declarations section:

       Public Const SM_SWAPBUTTON = 23   
  4. Create the ShowHands() procedure by typing the following code into the module:

       Sub ShowHands()     If GetSystemMetrics(SM_SWAPBUTTON) = False Then     MsgBox "Your mouse is right-handed!"     Else     MsgBox "Your mouse is left-handed!"     End If     End Sub   
  5. Run the ShowHands() procedure and a dialog box should appear which indicates whether your mouse is configured for right- or left-handed use.

  6. Now swap the left and right mouse buttons by altering the button configuration. You can do this by opening the Control Panel , selecting Mouse, and changing the Button configuration option on the Buttons tab of the dialog box that appears.

    click to expand
  7. Hit the OK button to close the Mouse Properties dialog box and go back to the module that you have just created. You will probably notice at this stage that the mouse buttons have been swapped around.

  8. Run the ShowHands() procedure again. This time, the message box that appears should indicate that the mouse buttons have been swapped. Click the OK button to close the message box:

  9. If you're still sitting there wondering why you can't seem to click the OK button, remember that you have swapped the mouse buttons round and you should be clicking the other button!

  10. Finish up by changing the mouse buttons back to their original settings. (And then get even more annoyed on the way because you keep bringing up context-sensitive menus when you just want to click things!).

How It Works

As we explained just before the example, the GetSystemMetrics function returns information about the screen layout. The actual information that is returned depends on the argument we pass to the function. We passed it the SM_SWAPBUTTON constant. But how did we know that was the constant to use? And how did we know that this was the declaration for the constant?

 Public Const SM_SWAPBUTTON = 23 

Come to think of it, how did we even know about the GetSystemMetrics function?

Well, in the real world, the most popular libraries have good indexes, and it's the same in the programming world. For OLE libraries and Access library databases, such an index is provided by the Object Browser. However, for DLLs, we rely on the vendor's documentation for information about the functions within the DLL - what arguments they take, what values they return and so on.

Because User32.dll is such a vital and frequently used component of Windows, it is very well documented. The authoritative source of such information is the Microsoft Platform SDK (where SDK stands for Software Development Kit). This information is also available online from http://msdn.microsoft.com/ . You may find it easier to find the information by searching the Microsoft Developer Network or Microsoft TechNet CDs if you have a subscription. Alternatively, if you only want the function declaration and any constant or type declarations, you can use the Win32 API Viewer, which comes with Microsoft Office 2000 Developer Edition.

If you do look up the documentation, you will see that when GetSystemMetrics is called with the SM_SWAPBUTTON constant, it returns a non-zero value if the buttons have been swapped and False (that is, zero) if they have not. It's as simple as that!


One cool feature of VBA is the ability to create enumerated constants or Enums . An Enum is simply a list of constants that you can use to represent allowable integer values that can be passed to certain procedures. In the previous example, we noted that 23 was an allowable value to pass as the nIndex argument to GetSystemMetrics and we declared a constant SM_SWAPBUTTON to represent this value. Let's see how we can make things even easier for the programmer by creating an Enum .

Try It Out-Creating an Enum
  1. Open the Chapter 11 Code module.

  2. In the Declarations section, delete the following line:

       Public Const SM_SWAPBUTTON = 23   
  3. Now add the following lines to the Declarations section:

       Public Enum SystemMetrics     SM_MOUSEPRESENT = 19     SM_SWAPBUTTON = 23     SM_MOUSEWHEELPRESENT = 75     End Enum   
  4. Next , modify the GetSystemMetrics declaration so that it looks like this:

       Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As SystemMetrics) As Long   
  5. Now, go to the Immediate window and type in the following line:


    Note how, as you get to the part of the line where you should enter the argument to the GetSystemMetrics function, the Auto List Members feature of VBA presents you with a list of the three valid selections (There are three of them; one is hidden behind the tooltip!).

    click to expand
  6. Select SM_SWAPBUTTON , close the parentheses and hit Enter . The result displayed should indicate whether you have configured your computer for left-handed use.

How It Works

This example should be fairly self-explanatory. First, we provide the definition of the SystemMetrics Enum :

 Public Enum SystemMetrics   SM_MOUSEPRESENT = 19   SM_SWAPBUTTON = 23   SM_MOUSEWHEELPRESENT = 75 End Enum 

As well as the value indicating whether the mouse buttons are swapped, we have also supplied another two values which, when passed to the GetSystemMetrics function, indicate whether a mouse is present or whether a mouse wheel is present. These both return a non-zero value to indicate the presence of the relevant item and zero to indicate its absence.

Once we have specified the constituents of the Enum , we can use it as a way of limiting the values that can be supplied as an argument to certain functions.

 Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As SystemMetrics) _ As Long 

The great thing about using an Enum is that when we are writing our code, we are prompted with the acceptable values for a particular argument. It is one thing having the constant SM_SWAPBUTTON in place of the difficult to remember value 23 . But by creating an Enum and binding it to the argument nIndex , we can ensure that the programmer is prompted with that - and any other acceptable values - when using that function in code.

Note that you will only be prompted with the contents of the Enum if you the Auto List Members option on the Editor tab of the Tools/Options dialog is checked in the VBA IDE.


Once you have declared the function within the DLL you want to use, you can call it by name from within VBA, as if it were a native VBA function. However, there may be occasions when you want to change the name of the DLL function. For example, VBA has a SetFocus method that applies to forms and controls. Now there is also a SetFocus API call (that is, there is a SetFocus function in the User32.dll library). If you were to declare it in the normal manner, it might cause confusion:

 Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long 

Now you would have both a SetFocus method and a SetFocus API call. To make things clearer, you could create an Alias for the API call. In other words, you could rename it. So, for example, you can declare the SetFocus API call, but rename it as Win32SetFocus :

 Declare Function Win32SetFocus Lib "user32" Alias "SetFocus" _   (ByVal hwnd As Long) As Long 

The Alias keyword is also useful if the name of the DLL function isn't legal in VBA or Visual Basic (or is too long to keep typing!). The API functions _lopen , _lread , and _lwrite can't be declared directly because of the use of an underscore at the beginning of a function name is illegal in VBA. However, they can be declared if they are aliased to a legal name such as LOpen .

You do not have to use aliases for your functions, but it is often a good idea, if only to avoid confusion and prevent the inconvenience of encountering name conflicts between the API library and VBA. For example, you might want to give functions aliases which begin with the prefix api_ , such as api_SetFocus . This makes it easier for people reading your code to see when you are using native Access or VBA functions and when you are calling functions in other DLLs and this, in turn, can make the code easier to debug.

Using the ByVal Keyword

We saw earlier that, by default, VBA passes arguments to a procedure by reference. In other words, Access passes a pointer to the memory address of the variable that is being passed, rather than the actual value of the variable. However, many functions in DLLs expect to receive the value of the variable, rather than a pointer to its memory address. If this is the case, you need to pass the argument by value by placing the ByVal keyword in front of the argument when you declare the function:

 Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long 

Passing Strings

The functions in most DLLs expect any strings which are passed to them to be null- terminated strings. A null-terminated string uses a null character (ASCII value 0) to indicate the end of the string. VBA, however, doesn't use null-terminated strings. Therefore, if you pass a string from VBA to a function in a DLL, you will need to convert it. This can also be done with the ByVal keyword.

When the ByVal keyword is used with a string argument, VBA converts the string variable into a null-terminated string and passes to the DLL function a pointer to the memory address of the null-terminated string (that is, it passes the null-terminated string by reference ). Yes, this is the opposite of what we said earlier about using the ByVal keyword - but it only applies with VBA and VB strings.

Because the string is passed to the DLL function by reference, the DLL can modify it. This presents a problem. If the DLL attempts to modify the value of a null-terminated string and the new value is longer than the original one, the function doesn't increase the length of the string. Instead, it simply carries on writing the remainder of the string into the memory location adjacent to that of the null-terminated string. This is not good! In fact, if it happens, your application will probably crash.

To prevent this, you should make sure that the string you pass to the function is large enough to accept any value that the function may place in it. You do this by passing a fixed length string of a suitably large size :

 Dim strFilename As String * 255 

You should consult the documentation for the DLL function to determine the maximum size, but 255 characters is usually sufficient.

Passing Arrays to a DLL

To pass an array of numeric values to a procedure in a DLL, you simply pass the first element of the array. You can do this because all the elements of a numeric array are laid out sequentially in contiguous memory space. After you have passed the first element of the array, the function is then able to retrieve the remaining elements by itself. However, you can't pass string arrays this way - attempting to do so may cause your application to crash!

Type Conversion

Because most DLLs are written in C or C++, the data types used by the arguments to the procedures within the DLL aren't identical to the data types used by VBA. However, it's not difficult to map the C data types to the Visual Basic data types if you consult the following table:

C Data type


VBA Equivalent



ByVal b As Boolean


Long Integer

ByVal l As Long



ByVal l As Long



ByVal l As Long


Pointer to a long integer

l As Long


Pointer to an integer

l As Long


Pointer to a string

ByVal s As String

LPRECT (for example)

Pointer to a type

See below



See below



Use a subprocedure

User-defined Data Types

Often, procedures in DLLs use structures as their arguments. A structure is the C-language equivalent of a user-defined type in VBA, that is, a type that you have defined yourself. If a procedure expects a structure, we can pass it a user-defined type so long as we pass it by reference .

User-defined types allow you to place several different data types together in one type, allowing you to group together related variables . For example, in an Accounting system you might wish to create a type for a Customer which includes the name, account number, credit limit, and so on.

One of the more frequently used structures in DLL functions is the RECT structure. This is a representation of a rectangular area and is composed of four elements representing the left, top, bottom, and right coordinates of the rectangle.

The RECT structure can be represented by the following user-defined type in VBA:

 Type RECT   Left As Long   Top As Long   Right As Long   Bottom As Long End Type 

It's typically used to represent a rectangular area on the screen. You can see from the diagram below that the coordinates for the four elements are measured from the top left corner of the screen:

click to expand

An example of a DLL procedure which uses this structure is the ClipCursor function. When this is passed a RECT structure, it confines the mouse pointer to a rectangular area on the screen defined by the coordinates of the structure.

   Type RECT     Left As Long     Top As Long     Right As Long     Bottom As Long     End Type     Declare Function ClipCursor Lib "user32" (lpRect As RECT) As Long     Sub Foo()     Dim rectClipArea As RECT     Dim lngRetVal As Long     With rectClipArea     .Top = 200     .Left = 100     .Bottom = 420     .Right = 280     End With     lngRetVal = ClipCursor(rectClipArea)     End Sub   

Null Pointers

Sometimes a procedure in a DLL expects to be passed a null pointer . If you have confined the mouse pointer with the ClipCursor function, you can free it by passing a null pointer to the ClipCursor function. In VBA, the equivalent to a null pointer is just the value zero, usually written as:

 ByVal 0& 

The & is a type declaration character which indicates that the pointer is a long (that is, 32-bit pointer). Note that the null pointer must be passed by value. If the ByVal had been omitted, we would have found ourselves passing a pointer to & rather than a null pointer.

It is also possible to use the friendlier VBA module constants vbNull or vbNullChar instead of the ByVal & syntax.

However, we told VBA in our function declaration that the argument is of type RECT , so it will generate its own error if we try and pass anything else - like a null pointer - to the function. The answer is to declare the argument with a type of Any :

 Declare Function ClipCursor Lib "user32" (lpRect As Any) As Long 

This turns off VBA's type checking and allows any data type to be passed to the function. So the call to free the mouse pointer would look like this:

 lngRetVal = ClipCursor(ByVal 0&) 

The Dangers of Using DLLs

Bear in mind when you use DLL functions that, as soon as execution passes into the DLL, you lose all the cozy protection that VBA offers. Although Windows API functions themselves , and all good third-party DLLs, are designed to trap their own errors and exit gracefully, they will not always do this if you supply the wrong parameter types or values.

Each time your code calls any procedure - either another VBA routine or a DLL - the data type of each argument is checked against those declared in the procedure. If you try to pass a wrong data type to any function or subroutine you get a Type Mismatch error. When you declare a DLL procedure in VBA, you can take advantage of the built-in type checking that occurs. That way, if you pass a wrong data type, you'll get a friendly VBA error message rather than a system crash.

You could, of course, declare all the arguments as type Any , and VBA would allow you to pass any data type you wanted. Almost without exception, your next step would then be Ctrl-Alt-Del because the format of the arguments doesn't match those required by the DLL. It's not that the DLL has caused the error directly, but simply that it can't make head nor tail of what you've sent it!

So, to minimize errors, you should always place as tight a definition on your DLL data types as possible when you declare them. Of course, there are times that you can declare a function in two different ways. In this situation, one step you can take to make your declarations safer is to use an Alias to rename one or more of them:

 Declare Function ClipCursorOn Lib "user32" Alias "ClipCursor" _                                             (lpRect As RECT) As Long Declare Function ClipCursorOff Lib "user32" Alias "ClipCursor" _                                              (lpRect As Any) As Long 

Both can coexist in your code together, and you can call the correct forms of the function as you need them.


When you use DLL or API functions you should always save your work regularly and back up any databases before you modify them. If you are using Windows 2000 or XP, you should also ensure that no other applications (outside Access) have unsaved data in case you freeze Windows completely.

Well, that's three of our topics down and one to go. This final topic, like the one we have just covered, is also concerned with extending the functionality of Access. It's all about extending Data Access Objects by adding user-defined or custom properties.

team lib

Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net