ThreeWave Playing A Sound In VBA

This page describes play a sound in VBA.
ShortFadeBar

Playing A Sound

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&

If the file cannot be found, Windows will play the default sound, unless this is disabled by using the value SND_NODEFAULT in the uFlags parameter (see below).

The uFlags parameter specifies how the sound is to be played. You can combine multiple options by adding their respective values together and passing the sum in the uFlags parameter.

Const SND_SYNC = &H0        ' (Default) Play the sound synchronously. Code execution
                            ' pauses until sound is complete.

Const SND_ASYNC = &H1       ' Play the sound asynchronously. Code execution
                            ' does not wait for sound to complete.

Const SND_NODEFAULT = &H2   ' If the specified sound is not found, do not play
                            ' the default sound (no sound is played).

Const SND_MEMORY = &H4      ' lpszSoundName is a memory file of the sound.
                            ' Not used in VBA/VB6.

Const SND_LOOP = &H8        ' Continue playing sound in a loop until the next
                            ' call to sndPlaySound.

Const SND_NOSTOP = &H10     ' Do not stop playing the current sound before playing
                            ' the specified sound.

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, Optional Flags As Long = 0)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            ' Can't find the file. Do a simple Beep.
            Beep
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If
    ' Finally, play the sound.
    sndPlaySound32 WhatSound, Flags
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", SND_SYNC

This is functionally equivalent to:

PlayTheSound "C:\Windows\Media\Chord.wav", SND_SYNC

The code will automatically find the file name in C:\Windows\Media and append the .wav extension.

SectionBreak

Previewing System Sounds

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.

ShortFadeBar
LastUpdate This page last updated: 18-May-2011.

-->