VBA has the capability to use functions that are stored in Dynamic Link Libraries (DLLs). The examples in this section use common Windows API calls to DLLs.
In Windows, many file types are associated with a particular application. This association makes it possible to double-click the file to load it into its associated application.
The following function, named GetExecutable , uses a Windows API call to get the full path to the application associated with a particular file. For example, your system has many files with a .txt extension - one named Readme.txt is probably in your Windows directory right now. You can use the GetExecutable function to determine the full path of the application that opens when the file is double-clicked.
Note | Windows API declarations must appear at the top of your VBA module. |
Private Declare Function FindExecutableA Lib "shell32.dll" _ (ByVal lpFile As String, ByVal lpDirectory As String, _ ByVal lpResult As String) As Long Function GetExecutable(strFile As String) As String Dim strPath As String Dim intLen As Integer strPath = Space(255) intLen = FindExecutableA(strFile, "\", strPath) GetExecutable = Trim(strPath) End Function
Figure 11-18 shows the result of calling the GetExecutable function, with an argument of the filename for an MP3 audio file. The function returns the full path of the application that's associated with the file.
CD-ROM | This example is available on the companion CD-ROM. The filename is file association.xlsm . |
VBA doesn't have a way to directly get information about disk drives . But with the assistance of three API functions, you can get just about all of the information you need.
Figure 11-19 shows the output from a VBA procedure that identifies all connected drives, determines the drive type, and calculates total space, used space, and free space. In the example shown, the system has six drives connected.
The code is rather lengthy, so I don't list it here, but the interested reader should be able to figure it out by examining the code on the CD-ROM.
CD-ROM | This example is available on the companion CD-ROM in a file named drive information.xlsm . |
The example in this section uses a Windows API function to return information about the active printer. The information is contained in a single text string. The example parses the string and displays the information in a more readable format.
Private Declare Function GetProfileStringA Lib "kernel32" _ (ByVal lpAppName As String, ByVal lpKeyName As String, _ ByVal lpDefault As String, ByVal lpReturnedString As _ String, ByVal nSize As Long) As Long Sub DefaultPrinterInfo() Dim strLPT As String * 255 Dim Result As String Call GetProfileStringA _ ("Windows", "Device", "", strLPT, 254) Result = Application.Trim(strLPT) ResultLength = Len(Result) Comma1 = InStr(1, Result, ",", 1) Comma2 = InStr(Comma1 + 1, Result, ",", 1) ' Gets printer's name Printer = Left(Result, Comma1 - 1) ' Gets driver Driver = Mid(Result, Comma1 + 1, Comma2 - Comma1 - 1) ' Gets last part of device line Port = Right(Result, ResultLength - Comma2) ' Build message Msg = "Printer:" & Chr(9) & Printer & Chr(13) Msg = Msg & "Driver:" & Chr(9) & Driver & Chr(13) Msg = Msg & "Port:" & Chr(9) & Port ' Display message MsgBox Msg, vbInformation, "Default Printer Information" End Sub
Note | The ActivePrinter property of the Application object returns the name of the active printer (and lets you change it), but there's no direct way to determine what printer driver or port is being used. That's why this function may be useful. |
Figure 11-20 shows a sample message box returned by this procedure.
CD-ROM | This example is available on the companion CD-ROM. The filename is printer info .xlsm . |
The example in this section uses Windows API calls to determine a system's current video mode for the primary display monitor. If your application needs to display a certain amount of information on one screen, knowing the display size helps you scale the text accordingly . In addition, the code determines the number of monitors . If more than one monitor is installed, the procedure reports the virtual screen size.
'32-bit API declaration Declare Function GetSystemMetrics Lib "user32" _ (ByVal nIndex As Long) As Long Public Const SM_CMONITORS = 80 Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Public Const SM_CXVIRTUALSCREEN = 78 Public Const SM_CYVIRTUALSCREEN = 79 Sub DisplayVideoInfo() Dim numMonitors As Long Dim vidWidth As Long, vidHeight As Long Dim virtWidth As Long, virtHeight As Long Dim Msg As String numMonitors = GetSystemMetrics(SM_CMONITORS) vidWidth = GetSystemMetrics(SM_CXSCREEN) vidHeight = GetSystemMetrics(SM_CYSCREEN) virtWidth = GetSystemMetrics(SM_CXVIRTUALSCREEN) virtHeight = GetSystemMetrics(SM_CYVIRTUALSCREEN) If numMonitors > 1 Then Msg = numMonitors & " display monitors" & vbCrLf Msg = Msg & "Virtual screen: " & virtWidth & " " Msg = Msg & virtHeight & vbCrLf & vbCrLf Msg = Msg & "The video mode on the primary display is: " Msg = Msg & vidWidth & " " & vidHeight Else Msg = Msg & "The video display mode: " Msg = Msg & vidWidth & " " & vidHeight End If MsgBox Msg End Sub
Figure 11-21 shows the message box returned by this procedure when running on a dualmonitor system.
CD-ROM | This example is available on the companion CD-ROM. The filename is video mode.xlsm . |
The example in this section adds some sound capability to Excel. Specifically, it enables your application to play WAV or MIDI files. For example, you might like to play a short sound clip when a dialog box is displayed. Or maybe not. In any case, if you want Excel to play WAV or MIDI files, this section has what you need.
CD-ROM | The examples in this section are available on the companion CD-ROM in a file named sound.xlsm . |
The following example contains the API function declaration plus a simple procedure to play a sound file called sound . wav , which is presumed to be in the same directory as the workbook:
Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Const SND_SYNC = &H0 Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 Sub PlayWAV() WAVFile = "sound.wav" WAVFile = ThisWorkbook.Path & "\" & WAVFile Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) End Sub
In the preceding example, the WAV file is played asynchronously. This means that execution continues while the sound is playing. To stop code execution while the sound is playing, use this statement instead:
Call PlaySound(WAVFile, 0&, SND_SYNC Or SND_FILENAME)
If the sound file is a MIDI file, you'll need to use a different API call. The PlayMIDI procedure starts playing a MIDI file. Executing the StopMIDI procedure stops playing the MIDI file. This example uses a file named xfiles . mid .
Private Declare Function mciExecute Lib "winmm.dll" _ (ByVal lpstrCommand As String) As Long Sub PlayMIDI() MIDIFile = "xfiles.mid" MIDIFile = ThisWorkbook.Path & "\" & MIDIFile mciExecute ("play " & MIDIFile) End Sub Sub StopMIDI() MIDIFile = "xfiles.mid" MIDIFile = ThisWorkbook.Path & "\" & MIDIFile mciExecute ("stop " & MIDIFile) End Sub
The Alarm function, which follows , is designed to be used in a worksheet formula. It uses a Windows API function to play a sound file when a cell meets a certain condition.
Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Function Alarm(Cell, Condition) Dim WAVFile As String Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 If Evaluate(Cell.Value & Condition) Then WAVFile = ThisWorkbook.Path & "\sound.wav" Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) Alarm = True Else Alarm = False End If End Function
The Alarm function accepts two arguments: a cell reference and a condition ( expressed as a string). The following formula, for example, uses the Alarm function to play a WAV file when the value in cell B13 is greater than or equal to 1000.
=ALARM(B13,">=1000")
The function uses VBA's Evaluate function to determine whether the cell's value matches the specified criterion. When the criterion is met (and the alarm has sounded), the function returns True ; otherwise , it returns False .
CROSS-REFERENCE | The SayIt function, presented earlier in this chapter, is a much simpler way to use sound in a function. |
Most Windows applications use the Windows Registry database to store settings. (See Chapter 4 for some additional information about the Registry.) Your VBA procedures can read values from the Registry and write new values to the Registry. Doing so requires the following Windows API declarations:
Private Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long) As Long Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" _ (ByVal hKey As Long) As Long Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sValueName As String, _ ByVal dwReserved As Long, ByVal dwType As Long, _ ByVal sValue As String, ByVal dwSize As Long) As Long Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long) As Long Private Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sValueName As String, _ ByVal dwReserved As Long, ByRef lValueType As Long, _ ByVal sValue As String, ByRef lResultLen As Long) As Long
CD-ROM | I developed two wrapper functions that simplify the task of working with the Registry: GetRegistry and WriteRegistry . These functions are available on the companion CD-ROM in a file named windows registry.xlsm . This workbook includes a procedure that demonstrates reading from the Registry and writing to the Registry. |
The GetRegistry function returns a setting from the specified location in the Registry. It takes three arguments:
RootKey : A string that represents the branch of the Registry to address. This string can be one of the following:
HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS
HKEY_CURRENT_CONFIG
HKEY_DYN_DATA
Path : The full path of the Registry category being addressed.
RegEntry : The name of the setting to retrieve.
Here's an example. If you'd like to find which graphic file, if any, is being used for the Desktop wallpaper, you can call GetRegistry as follows. (Notice that the arguments are not case-sensitive.)
RootKey = "hkey_current_user" Path = "Control Panel\Desktop" RegEntry = "Wallpaper" MsgBox GetRegistry(RootKey, Path, RegEntry), _ vbInformation, Path & "\RegEntry"
The message box will display the path and filename of the graphic file (or an empty string if wallpaper is not used).
The WriteRegistry function writes a value to the Registry at a specified location. If the operation is successful, the function returns True ; otherwise, it returns False . WriteRegistry takes the following arguments (all of which are strings):
RootKey : A string that represents the branch of the Registry to address. This string may be one of the following:
HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS
HKEY_CURRENT_CONFIG
HKEY_DYN_DATA
Path : The full path in the Registry. If the path doesn't exist, it is created.
RegEntry : The name of the Registry category to which the value will be written. If it doesn't exist, it is added.
RegVal : The value that you are writing.
Here's an example that writes a value representing the time and date Excel was started to the Registry. The information is written in the area that stores Excel's settings.
Sub Auto_Open() RootKey = "hkey_current_user" Path = "software\microsoft\office.0\excel\LastStarted" RegEntry = "DateTime" RegVal = Now() If WriteRegistry(RootKey, Path, RegEntry, RegVal) Then msg = RegVal & " has been stored in the registry." Else msg = "An error occurred" End If MsgBox msg End Sub
If you store this routine in your personal macro workbook, the setting is automatically updated whenever you start Excel.
If you want to use the Windows Registry to store and retrieve settings for your Excel applications, you don't have to bother with the Windows API calls. Rather, you can use VBA's GetSetting and SaveSetting functions.
These two functions are described in the online Help, so I won't cover the details here. However, it's important to understand that these functions work only with the following key name:
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
In other words, you can't use these functions to access any key in the Registry. Rather, these functions are most useful for storing information about your Excel application that you need to maintain between sessions.