Vba Continue Macro While Sound Plays

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&        

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                                       Const SND_ASYNC = &H1                                      Const SND_NODEFAULT = &H2                                  Const SND_MEMORY = &H4                                     Const SND_LOOP = &H8                                       Const SND_NOSTOP = &H10                                          

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 = 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, 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.

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: 18-May-2011.

sandersyetion.blogspot.com

Source: http://www.cpearson.com/excel/PlaySound.aspx

Share this post