MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Beep (or another sound...)

  Asked By: Patti    Date: Oct 11    Category: MS Office    Views: 3357

I just found a thread from 2006 on how to use a wav file, or perhaps
the Beep command, to sound an alert that a certain cell has reached a
certain number. That's exactly what I want to do.

I'm using 2007 which may be different and, in any event, I didn't
understand what I was reading. Does anyone have a simple reference for
dummies on this one?



4 Answers Found

Answer #1    Answered By: Alyssa Campbell     Answered On: Oct 11

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

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

Answer #2    Answered By: Shelia Wells     Answered On: Oct 11

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

Sub MakeNoise()


End Sub

Answer #3    Answered By: Roop Kapoor     Answered On: Oct 11

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
Alarm = False
End Function

Answer #4    Answered By: Abasi Massri     Answered On: Oct 11

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,

Didn't find what you were looking for? Find more on Beep (or another sound...) Or get search suggestion and latest updates.