By itself, Excel doesn't have much to offer in the area of sound —VBA's Beep command is about as good as it gets. However, with a few simple API calls, your application can play WAV or MIDI files.
The text-to-speech feature, which debuted in Excel 2002, extends the sound capability quite a bit. In fact, Excel 2002 and later can "speak" text by using the Speak method of the Speech object.The examples in this section focus on playing sound files, not speech.
Not all systems support sound. To determine whether a system supports sound, use the CanPlaySounds method. Here's an example:
If Not Application.CanPlaySounds Then
MsgBox "Sorry, sound is not supported on your system." Exit Sub End If
The following example contains the API function declaration plus a simple procedure to play a sound file called dogbark.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
WAVFile = "dogbark.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
MIDIFile = "xfiles.mid"
MIDIFile = ThisWorkbook.Path & "\" & MIDIFile mciExecute ("play " & MIDIFile) End Sub
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
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.
Was this article helpful?