23. The Windows Application Programming Interface (API)
In This Chapter
Understanding an API Declaration
Making 32-Bit- and 64-Bit-Compatible API Declarations
With all the wonderful things you can do in Excel VBA, there are some things that are out of VBA’s reach or that 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 Windows System directory WindowsSystem32 (Windows NT systems), you will see many files with the extension .dll. These files, which are dynamic link libraries (DLLs), contain various functions and procedures that other programs, including VBA, can access. 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 are also included. You can find more online by searching for terms like “Windows API List.”
Understanding an API Declaration
The following is an example of an API function:
Private Declare PtrSafe Function GetUserName _
Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) _
As LongPtr
There are two types of API declarations, which are structured similarly:
Functions—Return information
Procedures—Do something to the system
Basically, this is what you can tell about the API function above:
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.
Caution
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 a program. This is the variable name assigned in the code.
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 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:
Public Function UserName() As String
Dim sName As String * 256
Dim cChars As Long
cChars = 256
If GetUserName(sName, cChars) Then
UserName = Left$(sName, cChars - 1)
End If
End Function
Sub ProgramRights()
Dim NameofUser As String
NameofUser = UserName
Select Case NameofUser
Case Is = "Administrator"
MsgBox "You have full rights to this computer"
Case Else
MsgBox "You have limited rights to this computer"
End Select
End Sub
Run the ProgramRights macro, and you learn whether you are currently signed on as administrator. The result shown in Figure 23.1 indicates that Administrator is the current username.
Figure 23.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. You can then control what rights a user has with your program.
Making 32-Bit- and 64-Bit-Compatible API Declarations
With Excel 2010, Microsoft increased the compatibility between 32-bit and 64-bit API calls by allowing 64-bit calls to work on 32-bit systems but not vice versa. This is not the case with Excel 2007, so if you’re writing code that might be used in Excel 2007, you need to check the bit version and adjust accordingly.
The examples in this chapter are 64-bit API declarations and might not work in older versions of 32-bit Excel. For example, say that in a 64-bit version you have this declaration:
Private Declare PtrSafe Function GetWindowLongptr Lib _
"USER32" Alias _
"GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As _
Long) As LongPtr
It will need to be changed to the following to work in the 32-bit version:
Private Declare Function GetWindowLongptr Lib "USER32" Alias _
"GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As _
Long) As LongPtr
The difference is that PtrSafe needs to be removed from the declaration. You might also notice that there is a new variable type in use: LongPtr. Actually, LongPtr isn’t a true data type; it is LongLong for 64-bit environments and Long in 32-bit environments. This does not mean that you should use it throughout your code; it has a specific use, such as in API calls. But you might find yourself using it in your code for API variables. For example, if you return an API variable of LongPtr to another variable in your code, that variable must also be LongPtr.
If you need to distribute a workbook to Excel 2007 32-bit and 64-bit users, you don’t need to create two workbooks. You can create an If...Then...Else statement in the declarations area and set up the API calls for both versions. So, for the preceding two examples, you could declare them like so:
#If VBA7 Or Win64 Then
Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
As LongPtr
#Else
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
As LongPtr
#End If
The pound sign (#) is used to mark conditional compilation. The code compiles only the line(s) of code that satisfy the logic check. #If VBA7 Or Win64 checks to see whether the current environment is using the new code base (VBA7, in use only since Office 2010) or whether the environment (Excel, not Windows) is 64-bit. If true, the first API declaration is processed; otherwise, the second one is used. For example, if Excel 2007 64-bit or Excel 2010 or newer is running, the first API declaration is processed, but if the environment is 32-bit Excel 2007, the second one is used. Note that in 64-bit environments, the second API declaration will be colored as an error but will compile just fine.
The change to 64-bit API calls is still novel, and there is some confusion as Microsoft continues to make changes. To help make sense of it all, Jan Karel Pieterse of JKP Application Development Services (www.jkp-ads.com) is working on an ever-growing web page that lists the proper syntax for the 64-bit declarations. You can find it at www.jkp-ads.com/articles/apideclarations.asp.
API Function Examples
The following sections provide more examples of helpful API declarations you can use in your Excel programs. Each example starts with a short description of what the function can do, followed by the actual declarations, and an example of its use.
Retrieving the Computer Name
This API function returns the computer name (that is, the name of the computer found under Computer, Computer Name):
Private Declare PtrSafe Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, ByRef nSize As Long) _
As LongPtr
Private Function ComputerName() As String
Dim stBuff As String * 255, lAPIResult As LongPtr
Dim lBuffLen As Long
lBuffLen = 255
lAPIResult = GetComputerName(stBuff, lBuffLen)
If lBuffLen > 0 Then ComputerName = Left(stBuff, lBuffLen)
End Function
Sub ComputerCheck()
Dim CompName As String
CompName = ComputerName
If CompName <> "BillJelenPC" Then
MsgBox _
"This application does not have the right to run on this computer."
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub
The ComputerCheck macro uses an API call to get the name of the computer. In this example, the workbook refuses to open on any computer except the hard-coded computer name of the owner.
Checking 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 that the file is not open. However, what if you want to see whether someone else on a network has the file open? The following API function returns that information:
Private Declare PtrSafe Function lOpen Lib "kernel32" Alias "_lopen" _
(ByVal lpPathName As String, ByVal iReadWrite As Long) As LongPtr
Private Declare PtrSafe Function lClose Lib "kernel32" _
Alias "_lclose" (ByVal hFile As LongPtr) As LongPtr
Private Const OF_SHARE_EXCLUSIVE = &H10
Private Function FileIsOpen(strFullPath_FileName As String) As Boolean
Dim hdlFile As LongPtr
Dim lastErr As Long
hdlFile = -1
hdlFile = lOpen(strFullPath_FileName, OF_SHARE_EXCLUSIVE)
If hdlFile = -1 Then
lastErr = Err.LastDllError
Else
lClose (hdlFile)
End If
FileIsOpen = (hdlFile = -1) And (lastErr = 32)
End Function
Sub CheckFileOpen()
If FileIsOpen("C:XYZ Corp.xlsx") Then
MsgBox "File is open"
Else
MsgBox "File is not open"
End If
End Sub
You can call the FileIsOpen function with a particular path and filename as the parameter to find out whether someone has the file open.
Retrieving Display-Resolution Information
The following API function retrieves the computer’s display size:
Declare PtrSafe Function DisplaySize Lib "user32" Alias _
"GetSystemMetrics" (ByVal nIndex As Long) As LongPtr
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
Function VideoRes() As String
Dim vidWidth
Dim vidHeight
vidWidth = DisplaySize(SM_CXSCREEN)
vidHeight = DisplaySize(SM_CYSCREEN)
Select Case (vidWidth * vidHeight)
Case 307200
VideoRes = "640 x 480"
Case 480000
VideoRes = "800 x 600"
Case 786432
VideoRes = "1024 x 768"
Case Else
VideoRes = "Something else"
End Select
End Function
Sub CheckDisplayRes()
Dim VideoInfo As String
Dim Msg1 As String, Msg2 As String, Msg3 As String
VideoInfo = VideoRes
Msg1 = "Current resolution is set at " & VideoInfo & Chr(10)
Msg2 = "Optimal resolution for this application is 1024 x 768" & Chr(10)
Msg3 = "Please adjust resolution"
Select Case VideoInfo
Case Is = "640 x 480"
MsgBox Msg1 & Msg2 & Msg3
Case Is = "800 x 600"
MsgBox Msg1 & Msg2
Case Is = "1024 x 768"
MsgBox Msg1
Case Else
MsgBox Msg2 & Msg3
End Select
End Sub
The CheckDisplayRes macro warns the client that the display setting is not optimal for the application.
Customizing the About Dialog
If you go to Help, About Windows in File Explorer, you get a nice little About dialog with information about the File Explorer and a few system details. With the following code, you can get that window to pop up in your own program and customize a few items, as shown in Figure 23.2.
Declare PtrSafe Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" _
(ByVal hwnd As LongPtr, ByVal szApp As String, ByVal szOtherStuff As _
String, ByVal hIcon As Long) As LongPtr
Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
Sub AboutMrExcel()
Dim hwnd As LongPtr
On Error Resume Next
hwnd = GetActiveWindow()
ShellAbout hwnd, Nm, "Developed by Tracy Syrstad" + vbCrLf + _
Chr(169) + "" & " MrExcel.com Consulting" + vbCrLf + vbCrLf, 0
On Error GoTo 0
End Sub
Disabling 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 form. You can capture the close event with QueryClose, but to prevent the button from being active and working at all, you need an API call. The following API declarations work together to disable that X and force the user to use the Close button. When the form is initialized, the X button is disabled. After the form is closed, the X button is reset to normal:
Private Declare PtrSafe Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName _
As String) As LongPtr
Private Declare PtrSafe Function GetSystemMenu Lib "user32" _
(ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr
Private Declare PtrSafe Function DeleteMenu Lib "user32" _
(ByVal hMenu As LongPtr, ByVal nPosition As Long, _
ByVal wFlags As Long) As LongPtr
Private Const SC_CLOSE As Long = &HF060
Private Sub UserForm_Initialize()
Dim hWndForm As LongPtr
Dim hMenu As LongPtr
'ThunderDFrame is the class name of all userforms
hWndForm = FindWindow("ThunderDFrame", Me.Caption)
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
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 23.3. The client must therefore use your programmed Close button.
Figure 23.3 Disable the X button on a userform to force users to use the Close button to shut down the form properly and prevent them from bypassing any code attached to the Close button.
Creating a Running Timer
You can use the NOW function to get the time, but what if you need a running timer that displays 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:
Public Declare PtrSafe Function SetTimer Lib "user32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
Public Declare PtrSafe Function KillTimer Lib "user32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long) As LongPtr
Public Declare PtrSafe Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPtr
Private lngTimerID As Long
Private datStartingTime As Date
Public Sub StartTimer()
StopTimer 'stop previous timer
lngTimerID = SetTimer(0, 1, 10, AddressOf RunTimer)
End Sub
Public Sub StopTimer()
Dim lRet As LongPtr, lngTID As Long
If IsEmpty(lngTimerID) Then Exit Sub
lngTID = lngTimerID
lRet = KillTimer(0, lngTID)
lngTimerID = Empty
End Sub
Private Sub RunTimer(ByVal hWnd As Long, _
ByVal uint1 As Long, ByVal nEventId As Long, _
ByVal dwParam As Long)
On Error Resume Next
Sheet1.Range("A1").Value = Format(Now - datStartingTime, "hh:mm:ss")
End Sub
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? To do this, you can add a sound object to a sheet and then call that sound. However, it would be easier to use the following API declaration and specify the proper path to a sound file:
Public Declare PtrSafe Function PlayWavSound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal LpszSoundName As String, _
ByVal uFlags As Long) As LongPtr
Public Sub PlaySound()
Dim SoundName As String
SoundName = "C:WindowsMediaChimes.wav"
PlayWavSound SoundName, 0
End Sub
Next Steps
In Chapter 24, “Handling Errors,” you’ll find out about error handling. In a perfect world, you want to be able to hand off your applications to a co-worker, leave for vacation, and not have to worry about an unhandled error appearing while you are on the beach. Chapter 24 discusses how to handle obvious and not-so-obvious errors.