23. 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 Windows System directory WindowsSystem32 (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. You can find more online by searching for terms like “Windows API List.”

Understanding an API Declaration

The following line 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:

• Functions—Return information

• Procedures—Do something to the system

The declarations are structured similarly.

Basically, what this declaration is saying is this:

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

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 will 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

The examples in this book are 64-bit API declarations and might not work in 32-bit Excel. For example, if in a 64-bit version you have the 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 becomes 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 your workbook to 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, setting up the API calls for both versions. So, for the preceding two examples, you could declare them like so:

#If VBA7 And 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 only compiles the line(s) of code that satisfy the logic check. #If VBA7 And Win64 checks to see whether the current environment is using the new code base (in use only since Office 2010) and whether the environment (Excel, not Windows) is 64-bit. If both are true, the first API declaration is processed. Else, the second one is used. For example, if Excel 2013 64-bit is running, the first API declaration is processed, but if the environment is Excel 2013 32-bit or Excel 2007, the second one is used. Note that in the 64-bit environment, the second API declaration will be colored as an error, but will compile just fine.

The change to 64-bit API calls is still new 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 listing the proper syntax for the 64-bit declarations. You can find it at www.jkp-ads.com/articles/apideclarations.asp.

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.

Retrieving the Computer Name

This API function returns the computer name. This 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 the preceding 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

Calling the FileIsOpen function with a particular path and filename as the parameter will tell you 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, vbCrLf + Chr(169) + "" & " MrExcel.com Consulting" _
    + vbCrLf, 0
On Error GoTo 0
End Sub

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

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 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, forcing 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. This forces the client to use your programmed Close button.

Figure 23.3. 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 need 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.

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? 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:

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 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 24 discusses how to handle obvious and not-so-obvious errors.