Using Dynamic Link Libraries (DLLs) | Beginning Access 2007 VBA

Dynamic Link Libraries (DLLs) are simply compiled code libraries. You already used a DLL when adding a reference to the ADO library (Chapter 5) so that you could work with the ADO object model and the properties, methods, and events for the objects it contains. In this section, you will look at another example of how to use a standard DLL by just adding a reference in your project. You will also look at how to manually declare an external DLL and use it within your code.

Standard DLLs

You can reference libraries such as DLLs by selecting Tools References from the Visual Basic Editor and then selecting the particular library that you want to work with. After a reference has been added, you can then manipulate the functionality contained in the library from within your VBA code.

Try It Out-Using the Microsoft Office Object Library to Create a Custom Command Bar

Let’s look at an example of creating custom command bars for your application using the Microsoft Office 12.0 Object Library. The CommandBars property of the Application object allows you to access the CommandBars collection. The CommandBars collection enables you to manage and create toolbars, menu bars, and shortcut menus.

Create a new database. Select the Office Button New. From the list of available templates, select "Blank Database." Name the database Ch11CodeExamples and click the Create button.
Set a reference to Microsoft Office 12.0 Object Library in the References dialog box, if one is not already set. From the Visual Basic Editor, select Tools References and scroll down the list. Select Microsoft Office 12.0 Object Library, as shown in Figure 11-1. Click the OK button to add the reference.

Figure 11-1
Add a new module to your database.

Add the following procedures to your module:

  Sub CreateCommandBar() Dim cbReport As CommandBar Dim btnButton As CommandBarButton 'delete the command bar and continue if it does not exist On Error Resume Next CommandBars("Wrox Report").Delete On Error GoTo 0 'add the new command bar to the collection Set cbReport = CommandBars.Add("Wrox Report", msoBarFloating) 'add Zoom button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandBars("Print Preview") _     .Controls("Zoom").Id) 'add 2 pages button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandB   ars("Print Preview") _     .Controls("Two Pages").Id) 'add copy button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandBars("Database") _     .Controls("Copy").Id) 'add a custom button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton) btnButton.Caption = "Test Button" btnButton.Style = msoButtonCaption 'list the name of the procedure to call when the button is clicked btnButton.OnAction = "DisplayMessage" 'display the new command bar by setting visible cbReport.Visible = True End Sub Sub DisplayMessage() MsgBox "The new button was clicked." End Sub

From the Immediate Window, execute the CreateCommandBar function by typing CreateCommandBar and pressing Enter. A new toolbar (as shown in Figure 11-2) is added to the Add-Ins tab on the Custom Toolbars ribbon.

Figure 11-2
Click Test Button on the toolbar. You should see a screen similar to the one shown in Figure 11-3.

Figure 11-3

How It Works

After adding a reference to the Microsoft Office 12.0 Object Library, you created two new procedures to make use of the CommandBar and CommandBarButton objects that are contained in the library. The CreateCommandBar procedure creates a new section in the Add-Ins tab on the Custom Toolbars ribbon.

 Sub CreateCommandBar() Dim cbReport As CommandBar Dim btnButton As CommandBarButton 'delete the command bar and continue if it does not exist On Error Resume Next CommandBars("Wrox Report").Delete On Error GoTo 0 'add the new command bar to the collection Set cbReport = CommandBars.Add("Wrox Report", msoBarFloating)

After creating the new command bar, you can add various buttons with predefined functionality to it, including Zoom, 2page, and Copy buttons:

 'add Zoom button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandBars("Print Preview") _     .Controls("Zoom").Id) 'add 2 pages button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandBars("Print Preview") _     .Controls("Two Pages").Id)  'add copy button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton, _     CommandBars("Database") _     .Controls("Copy").Id)

A custom button called "Test Button" is then added, which calls the DisplayMessage procedure when clicked:

 'add a custom button to the toolbar Set btnButton = cbReport.Controls.Add(msoControlButton) btnButton.Caption = "Test Button" btnButton.Style = msoButtonCaption 'list the name of the procedure to call when the button is clicked btnButton.OnAction = "DisplayMessage"

The new command bar is displayed by setting the Visible property to True:

  'display the new command bar by setting visible cbReport.Visible = True End Sub

The DisplayMessage procedure was also created and gets called when the new Test Button is clicked because it is assigned to the OnAction property of the Test Button:

 Sub DisplayMessage() MsgBox "The new button was clicked." End Sub

When you run the DisplayMessage procedure from the Immediate Window, the custom command bar is displayed. When you click the Test button, a message box is displayed to indicate that the new button was clicked. You could, for example, assign this command bar to display when a report opens by setting the Visible property to True in the OnOpen event of the desired report.

This is just one of numerous examples of how you can work with functionality provided by various libraries. You will look at some additional examples of working with other standard libraries later in this chapter in the discussion on automation.

Using External DLLs

In the prior section, you looked at how to add a reference to an existing library and then to use the features provided by the library from VBA code. By adding an explicit reference as you did before, you were able to see the available properties, methods, and events for each object as you typed code in the code window.

At times, however, you need to reference external DLLs from within your code. External DLLs do not provide the help feature that allows you to see what properties, methods, and events are available. You have to know the object and how to work with it. To use an external DLL, you must declare the function in VBA, call the function, and use the result.

Try It Out-Using an External DLL to Get System Information

Let’s walk through an example of referencing an external DLL to get information about your system.

First, declare the external DLL. To do so, add the following code to the General Declarations section of your module:
```
  Private Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As SYSTEM_INFO) 
```

Next, add the SYSTEM_INFO type definition to the General Declarations section of the module:

  Type SYSTEM_INFO    dwOemID As Long    dwPageSize As Long    lpMinimumApplicationAddress As Long    lpMaximumApplicationAddress As Long    dwActiveProcessorMask As Long    dwNumberOfProcessors As Long    dwProcessorType As Long    dwAllocationGranularity As Long    dwReserved As Long End Type

Add the following procedure to the module:

  Sub DisplaySystemInfo() Dim strMessage As String Dim lpSysInfo As SYSTEM_INFO 'call the external function and populate the SYSINFO user defined type 'with the results GetSystemInfo lpSysInfo 'use the results from the function to build a string strMessage = "Number of processors: " & lpSysInfo.dwNumberOfProcessors & vbCrLf strMessage = strMessage & "Processor type: " & lpSysInfo.dwProcessorType 'display the number of processors and processor type to the user MsgBox strMessage End Sub

From the Immediate Window, execute the DisplaySystemInfo procedure by typing DisplaySystemInfo and pressing Enter.
You should see a message similar to the one shown in Figure 11-4.

Figure 11-4

How It Works

First, you added a declaration for the external library to the General Declarations section of the module. The declaration is case-sensitive and must be typed exactly as shown below.

 Private Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As SYSTEM_INFO)

Next, you added the SYSTEM_INFO type declaration to the General Declarations section of the module. This data type contains the parameters in the exact format expected by the GetSystemInfo external library.

 Type SYSTEM_INFO    dwOemID As Long    dwPageSize As Long    lpMinimumApplicationAddress As Long    lpMaximumApplicationAddress As Long    dwActiveProcessorMask As Long    dwNumberOfProcessors As Long    dwProcessorType As Long    dwAllocationGranularity As Long    dwReserved As Long End Type

You then added the DisplaySystemInfo procedure to call the external procedure and use the result:

 Sub DisplaySystemInfo()

The procedure first declared a string variable and a variable to hold the result of the external function call:

 Dim strMessage As String Dim lpSysInfo As SYSTEM_INFO

The external function was then called and passed the local variable to hold the results:

 'call the external function and populate the SYSINFO user defined type 'with the results GetSystemInfo lpSysInfo

Part of the values returned from the function were added to the string variable:

 'use the results from the function to build a string strMessage = "Number of processors: " & lpSysInfo.dwNumberOfProcessors & _       vbCrLf strMessage = strMessage & "Processor type: " & lpSysInfo.dwProcessorType

The msgbox function then displayed the contents of the string variable to the user:

 'display the number of processors and processor type to the user MsgBox strMessage End Sub

Upon executing the DisplaySystemInfo procedure from the Immediate Window, you called the external function, which retrieved information about the current system, and displayed the number of processors and the processor type for the current system in a message box on the screen. As you can see, working with external DLLs is a bit more complicated than adding a reference using the Tools Add References feature. Working with external DLLs requires you to be very precise in your syntax or the function will not operate.