Microsoft Office Tutorials and References
In Depth Information
Windows API Calls
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
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.
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 .
The examples in this section are available on the companion CD-ROM in a file named
sound.xlsm .
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
Private Declare PtrSafe Function RegOpenKeyA Lib “ADVAPI32.DLL” _
(ByVal hKey As Long, ByVal sSubKey As String, _
ByRef hkeyResult As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib “ADVAPI32.DLL” _
Search JabSto ::

Custom Search