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- The examples in this section are available on the companion CD-ROM in a file named %

ROM 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 = &H2 000 0 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.

CROSS- The SayIt function, presented earlier in this chapter, is a much simpler way to

REFERENCE use sound in a function.

0 0

Responses

Post a comment