24. Windows API

What Is the Windows API?

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.

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.

Understanding an API Declaration

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.

• 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.

Using an API Declaration

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.

Figure 24.1. The GetUserName API function can be used to get a user’s Windows login name—which is more difficult to edit than the Excel username.

API Examples

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.

Retrieve the Computer Name

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.

Figure 24.2. Use the computer name to verify that an application has the rights to run on the installed computer.

Check Whether an Excel File Is Open on a Network

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.

Retrieve Display-Resolution Information

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.

Custom About Dialog

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.

Figure 24.3. You can customize the About dialog used by Windows for your own program.

Disable the X for Closing a Userform

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.

Figure 24.4. Disable the X button on a userform, forcing users to use the Close button to shut down the form properly and rendering them unable to bypass any code attached to the Close button.

Running Timer

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.

Playing Sounds

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:

Retrieving a File Path

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

Figure 24.5. Create a custom browse window to return the file path of a user-selected file. This can be used to ensure the user does not select the wrong file for import.

Finding More API Declarations

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.

Next Steps

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.