With all the wonderful things you can do in Excel VBA, there are some things that are out of VBA’s reach or are just too difficult to do, such as finding out what the user’s screen resolution setting is. This is where the Windows application programming interface (API) can help.
If you look in the folder WinntSystem32 (Windows NT systems), you will see many files with the extension .dll. These files, which are dynamic link libraries (dll), contain various functions and procedures that other programs can access, including VBA. They give the user access to functionality used by the Windows operating system and many other programs.
Keep in mind that Windows API declarations are accessible only on computers running the Microsoft Windows operating system.
This chapter does not teach you how to write API declarations, but it does teach you the basics of interpreting and using them. Several useful examples have also been included, and you are shown how to find more.
The following line is an example of an API function:
Private Declare Function GetUserName _
Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) _
As Long
There are two types of API declarations:
• Functions—Return information
• Procedures—Do something to the system
The declarations are structured similarly.
Basically, what this declaration is saying is
• It is Private
; therefore, it can be used only in the module in which it is declared. Declare it Public
in a standard module if you want to share it among several modules.
API declarations in standard modules can be public or private. API declarations in class modules must be private.
• It will be referred to as GetUserName
in your program. This is the variable name assigned by you.
• The function being used is found in advapi32.dll.
• The alias, GetUserNameA
, is what the function is referred to in the DLL. This name is case-sensitive and cannot be changed; it is specific to the DLL. There are often two versions of each API function. One version uses the ANSI character set and has aliases that end with the letter A. The other version uses the Unicode character set and has aliases that end with the letter W. When specifying the alias, you are telling VBA which version of the function to use.
• There are two parameters: lpBuffer
and nSize
. These are two arguments that the DLL function accepts.
The downside of using APIs is that there may be no errors when your code compiles or runs. This means that an incorrectly configured API call can cause your computer to crash or lock up. For this reason, it is a good idea to save often.
Using an API is no different from calling a function or procedure you created in VBA. The following example uses the GetUserName
declaration in a function to return the UserName
in Excel:
Run the ProgramRights macro, and you will learn whether you are currently signed on as the administrator. The result shown in Figure 24.1 indicates an administrator sign-on.
The following sections provide more examples of useful API declarations you can use in your Excel programs. Each example starts with a short description of what the example can do, followed by the actual declarations, and an example of its use.
The examples in this book are 32-bit API declarations and may not work in 64-bit Excel. For example, if in a 32-bit version we have this declaration:
It will need to be changed to the following to work in the 64-bit version:
But how can you know whether a Long
needs to be changed to a LongPtr
or to Long,Long
? It might not even need to be changed at all! Because of the confusion that has ensued, Jan Karel Pieterse of JKP Application Development Services (www.jkp-ads.com) is working on an ever-growing web page listing the proper syntax for the 64-bit declarations. It can be found at www.jkp-ads.com/articles/apideclarations.asp.
This API function returns the computer name. This is the name of the computer found under MyComputer, Network Identification:
The ComputerCheck macro uses an API call to get the name of the computer. In Figure 24.2, the program refuses to run for any computer except the hard-coded computer name of the owner.
You can check whether you have a file open in Excel by trying to set the workbook to an object. If the object is Nothing
(empty), you know the file is not opened. However, what if you want to see whether someone else on a network has the file open? The following API function returns that information:
Calling the FileIsOpen
function with a particular path and filename as the parameter will tell you whether someone has the file open.
The following API function retrieves the computer’s display size:
The CheckDisplayRes
macro warns the client that the display setting is not optimal for the application.
If you go to Help, About Windows in Windows Explorer, you get a nice little About dialog with information about the Windows Explorer and a few system details. With the following code, you can pop up that window in your own program and customize a few items, as shown in Figure 24.3.
The X button located in the upper-right corner of a userform can be used to shut down the application. The following API declarations work together to disable that X, forcing the user to use the Close button. When the form is initialized, the button is disabled. After the form is closed, the X button is reset to normal:
The DeleteMenu macro in the UserForm_Initialize
procedure causes the X in the corner of the userform to be grayed out, as shown in Figure 24.4. This forces the client to use your programmed Close button.
You can use the NOW
function to get the time, but what if you needed a running timer displaying the exact time as the seconds tick by? The following API declarations work together to provide this functionality. The timer is placed in Cell A1 of Sheet1.
Run the StartTimer macro to have the current date and time constantly updated in cell A1.
Have you ever wanted to play a sound to warn users or congratulate them? You could add a sound object to a sheet and call that sound. However, it would be easier to use the following API declaration and specify the proper path to a sound file:
The following API enables you to create a custom file browser. The program example using the API customizes the function call to create a browser for a specific need. In this case, it will return the file path of a user-selected file:
This is the actual program created to use this information:
Next, create the userform. The following code is attached to the Browse button, as shown in Figure 24.5. Note that the function specifies the starting directory:
Private Sub cmdBrowse_Click()
txtFile = GetFileName("c:")
End Sub
There are many more API declarations than the ones discussed in this chapter. In fact, this chapter barely scratched the surface of the wealth of procedures and functions available. Microsoft has many tools available to help you create your own APIs (search Platform SDK). Many programmers, such as Ivan F. Moala, have also developed declarations to share (http://xcelfiles.homestead.com/APIIndex.html). Ivan has created a site full of examples that include instructions.
In Chapter 25, “Handling Errors,” you learn about error handling. In a perfect world, you want to be able to hand your applications off to a co-worker, leave for vacation, and not have to worry about an unhandled error appearing while you are on the beach. Chapter 25 discusses how to handle obvious and not-so-obvious errors.