Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Windows API Calls

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.

Determining file associations

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.

Figure 11-18: Determining the path and name of the application associated with a particular file.

CD-ROM  

This example is available on the companion CD-ROM. The filename is  file association.xlsm .

Determining disk drive information

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.

Figure 11-19: Using Windows API functions to get disk drive information.

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 .

Determining default printer information

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.

Figure 11-20: Getting information about the active printer by using a Windows API call.

CD-ROM  

This example is available on the companion CD-ROM. The filename is  printer info .xlsm .

Determining video display information

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.

Figure 11-21: Using a Windows API call to determine the video display mode.

CD-ROM  

This example is available on the companion CD-ROM. The filename is  video mode.xlsm .

Adding sound to your applications

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 .

PLAYING A WAV FILE

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)

PLAYING A MIDI FILE

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

PLAYING SOUND FROM A WORKSHEET FUNCTION

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.

Reading from and writing to the Registry

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.

READING FROM THE REGISTRY

The GetRegistry function returns a setting from the specified location in the Registry. It takes three arguments:

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

WRITING TO THE REGISTRY

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

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.

An Easier Way to Access the Registry

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.

 

Related

Категории