In your VB Editor in Excel, place the following code:

Sub MakeNoise()

MsgBox Chr(7), vbOKOnly, "Beep Tester"

End Sub

Press F5 and you'll get a small surprise.

The problem with that bit of code is obvious: it raises a dialog in the
front of the user that stops all other code execution just to make a noise.
Just as a reference for the future, Character Code 7 is reserved and known
as the "System Beep Code". Issuing it results in that piercing Beep sound.

VBA, however, doesn't make using this little system jingle too easy since it
doesn't have a method available to send the code to the system. In VBScript,
for example, I'd simply write a line of code like this:

Wscript.echo Chr(7)

...and my job would be done. Not so in VBA.

As you chase this little problem across the web, you discover that other
people have burned hours and hours trying to come up with an elegant
workaround. The two best I've seen are from Joel Spolsky (dive into the
system API, come up for air, dive back in, etc, and learn to love confusion)
and another guy who just threw up his hands and asked the Windows Sound
player to play a WAV file that is installed on all Windows systems to date.
That's the thread you encountered. If I dissect his code, I read it like
this:

Option Explicit '- Convention to make sure you declare all
'your variables before attempting to use them.

'API Class to take care of playing the file -
'API = Application Programming Library, basically,
'it's code someone else wrote, is on your system and
'you can call it too
Public Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
'Took 3 lines of code to make that call to the API!!


Sub VBASound() ' This is how you define the beginning of a subroutine
'Call Api to play LoadIt.wav witch is in the same folder as
'the active document! Of course, LoadIt.WAV had better be in
' that folder or this code will throw an error. Ooops.
Call sndPlaySound32(ActiveWorkbook.Path & "\LoadIt.WAV", 0)
End Sub ' End of the code

In Excel's VBA (and Word) the following also works



Sub MakeNoise()



Beep



End Sub

From John Walkenbach Power Programming with VBA: Examples CD:
Function that plays a sound when the total reaches or exceeds 1,000:

Option Explicit

Private 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 = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

Getting a beep sound on a pc is not as easy as it sounds. :-) The beep command
may work on some, but not on many PCs, depending on which version operating
system is running.

I have never attempted it from VBA, but I did write some VB code a few years ago
that will work on every PC I have tested that has an internal speaker. I posted
it on Planet Source Code,
www.pscode.com/.../ShowCode.asp