Playing A Sound In VBA
This page describes play a sound in VBA.
You may want to play a sound in your application, such as to indicate an error condition
or indicate the completion of a long running procedure. VBA gives us only
the Beep statement to play the default notification sound. However,
Windows provides about 50 different sound files (wav format) in the
C:\Windows\Media folder, and you may have custom sound files of
your own. This page describes how to play these sound files.
To play a sound, you use the sndPlaySound32 Windows API function, located
in the winmm.dll file. Thus, you need to use a
Declare statement to reference that function. At the top of your code module (before any procedure
declarations but after any Option statements), insert the following code:
Public Declare Function sndPlaySound32 _
Lib "winmm.dll" _
Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
This allows you to call the sndPlaySound32 function. If you are putting the
Declare statement in an object module (a class module, a userform's code module,
the ThisWorkbook module, or one of the Sheet modules, change Public to
Private). You pass to this function the file name you want to play.
For example,
sndPlaySound32 "C:\Windows\Media\Chimes.wav", 0&
We can use a nice wrapper function for this code that accepts either a fully qualified file name
or the file name only of a file in C:\Windows\Media, making it simpler
to play a sound. This function, called PlayTheSound is shown below.
Sub PlayTheSound(ByVal WhatSound As String)
If Dir(WhatSound, vbNormal) = "" Then
WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
If InStr(1, WhatSound, ".") = 0 Then
WhatSound = WhatSound & ".wav"
End If
If Dir(WhatSound, vbNormal) = vbNullString Then
Beep
Exit Sub
End If
Else
' WhatSound is a file. Use it.
End If
sndPlaySound32 WhatSound, 0&
End Sub
This function tests if WhatSound is a file name. If so, that file is played by
sndPlaySound32. If WhatSound is not a file name,
the code looks in the C:\Windows\Media folder to find a wav file with that name.
Because the function allows you to omit the .wav extension for ease of use,
the code will add the .wav extension if it was not provided. Then, if the
file cannot be found, the code issues a standard VBA Beep. If the file is
found, it is played by sndPlaySound32.
For example, the following code plays the Windows "Chord" sound.
PlayTheSound "chord"
This is functionally equivalent to:
PlayTheSound "C:\Windows\Media\Chord.wav"
The code will automatically find the file name in C:\Windows\Media and
append the .wav extension.
If you are not sure what sound you want to play, you can use the following VBA code to list the
files in the C:\Windows\Media folder and play each one. First,
run the following code that will create a list of media files:
Sub ListWavFiles()
Dim N As Long
Dim FSO As Object
Dim FF As Object
Dim F As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FF = FSO.GetFolder(Environ("SystemRoot") & "\Media")
For Each F In FF.Files
N = N + 1
Cells(N, 1) = F.Name
Cells(N, 2) = F.Path
Next F
ActiveSheet.Columns(1).AutoFit
End Sub
This code will list in column A all the wav files in
C:\Windows\Media and put the fully qualified file name
in column B. To play one of these sounds, select the cell in either column A or B and
execute the following code:
Sub PlayActiveCellSound()
PlayTheSound ActiveCell.Text
End Sub
This will allow you to select the appropriate sound for your requirements.
 |
This page last updated: 28-Oct-2008. |