January 20, 2022

Play Sound in Excel Automatically with VBA

How to play a sound in Excel with a macro or VBA code to warn us, for example, of a wrong data entry. Watch the video below:

Play Sound in Excel with a Macro

We may want to play a sound in our application, for example, to indicate an error. The default sound is the beep. However, with VBA we can play more than 80 sound files (wav format) from the C:\Windows\Media folder. We can also use our own recorded sound files.

The term “API” is an acronym for “Application Programming Interface”. The term “Windows API” is used to describe a collection of Functions that MS Windows provides for developers to exploit. The Windows API can be visualized as a set of developer’s libraries. In fact, Windows offers innumerable functions which can be used to do everything from setting the clock, sending email, printing, playing WAV and MP3 files, to drawing graphical windows on the screen, etc. winmm.dll is a module for the Windows Multimedia API, which contains low-level audio and joystick functions.

To play a sound, we can use the sndPlaySound32 Windows API function, located in the winmm.dll file. We have to use a Declare statement to reference that function. At the top of our code module after any Option statement, we insert the following code:

Private Declare PtrSafe Function sndPlaySound32 Lib “winmm.dll” _
Alias “sndPlaySoundA” (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

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 playingthe specified sound.

The following macro example plays the ‘windows error’ sound if you enter a value greater than the threshold value of 28.

Option Explicit

Private Declare PtrSafe Function sndPlaySound32 Lib “winmm.dll” _
Alias “sndPlaySoundA” (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim highestGST As Long

highestGST = 28

If Range(“B2”).Value > highestGST Then

Call sndPlaySound32("C:\windows\media\Windows Error.wav", 1)

End If

End Sub

The macro below loops through all the data in your worksheet and plays the ‘windows exclamation’ sound file whenever it encounters a value of Goods & Services Tax greater than 28:

Option Explicit

Private Declare PtrSafe Function sndPlaySound32 Lib “winmm.dll” _
Alias “sndPlaySoundA” (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Sub PlaySound()
Dim lastrow As Long, i As Long

lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“B:B”))

MsgBox lastrow

For i = 2 To lastrow
If Cells(i, 2) > 28 Then Call sndPlaySound32(“C:\Windows\Media\Windows Exclamation.wav”, &H8)
Next i

End Sub

Play sound in Excel using Macro
Play sound in Excel Using VBA

Further Reading:

Playing A Sound In VBA