Adding sound to your applications

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

PLAYING A WAV FILE

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

Sub PlayWAV()

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)

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.

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.

+1 0

Post a comment