Hi Programmers,

I'm making a worksheet for students to enter answers in. To make this
more exciting, I've collected numerous sounds, such as crowds booing
and cheering, and used the following:

in a worksheet cell:

=IF(ISBLANK(I23), "E",
IF(I23<"M",QCcrowdboo()&"D",IF(I23="M",QAcrowdche er()&"C")))

(note that the "E", "D", and "C" in this only pertain to displaying
different Wingdings hands.)

in VBA:

Private Const SND_ASYNC = &H1
Private Const SND_FILENAME = &H20000

Private Declare Function PlaySound Lib _
"winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
-------------------------------
Private Sub PlaySoundFile(rsPath As String)
PlaySound rsPath, 0, SND_ASYNC Or SND_FILENAME
End Sub
-------------------------------
Public Function QAcrowdcheer() As String
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
Cheer = vbNullString
End Function
-------------------------------
Public Function QCcrowdboo() As String
PlaySoundFile "C:\Windows\Media\QCcrowdboo.wav"
Boo = vbNullString
End Function
-------------------------------

However, I've collected many more sounds, such as different crowds
clapping, booing, laughing, etc., and added more public functions, like
QAcrowdcheer2, QCcrowdboo2....and want to randomize the calling of
these sounds.

So, for a correct answer typed in a cell of the worksheet, call any
function that begins with QA, and for a wrong answer, call any function
that begins with QC...making it random each time.

Thanks much

An easier way to do this would be as follows (similarly for the boo sounds):

Public Function QAcrowdcheer() As String
Dim RandCheer as Integer

Randomize
RandCheer = Rnd() ' gives random decimal number between 0-1
Select Case RandCheer
Case 0 to 0.1
PlaySoundFile "C:\Windows\Media\QAcrowdcheer1.wav"
Case 0.1 to 0.2
PlaySoundFile "C:\Windows\Media\QAcrowdcheer2.wav"
....
End Select
Cheer = vbNullString
End Function

Adjust the Select Case as needed depending on the number of .wav files you
have and the "probability" of each being selected (for example, if there is a
favorite you might play that half the time, dividing up the other choices
equally).

--
- K Dales


" wrote:

Hi Programmers,

I'm making a worksheet for students to enter answers in. To make this
more exciting, I've collected numerous sounds, such as crowds booing
and cheering, and used the following:

in a worksheet cell:

=IF(ISBLANK(I23), "E",
IF(I23<"M",QCcrowdboo()&"D",IF(I23="M",QAcrowdche er()&"C")))

(note that the "E", "D", and "C" in this only pertain to displaying
different Wingdings hands.)

in VBA:

Private Const SND_ASYNC = &H1
Private Const SND_FILENAME = &H20000

Private Declare Function PlaySound Lib _
"winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long
-------------------------------
Private Sub PlaySoundFile(rsPath As String)
PlaySound rsPath, 0, SND_ASYNC Or SND_FILENAME
End Sub
-------------------------------
Public Function QAcrowdcheer() As String
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
Cheer = vbNullString
End Function
-------------------------------
Public Function QCcrowdboo() As String
PlaySoundFile "C:\Windows\Media\QCcrowdboo.wav"
Boo = vbNullString
End Function
-------------------------------

However, I've collected many more sounds, such as different crowds
clapping, booing, laughing, etc., and added more public functions, like
QAcrowdcheer2, QCcrowdboo2....and want to randomize the calling of
these sounds.

So, for a correct answer typed in a cell of the worksheet, call any
function that begins with QA, and for a wrong answer, call any function
that begins with QC...making it random each time.

Thanks much

Thanks KD! I input the code but it appears that when an answer is
input into the cell, only 1 of two sounds are being played,
"C:\Windows\Media\QAcrowdcheer.wav" and "C:\Windows\Media\QAaaaah.wav"
Do you know why this is? Did I type this correctly?

Public Function QAcheer() As String
Dim RandCheer As Integer

Randomize
RandCheer = Rnd() 'gives the decimal number between 0-1
Select Case RandCheer
Case 0 To 0.1
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
Case 0.1 To 0.2
PlaySoundFile "C:\Windows\Media\QAkidsapplause.wav"
Case 0.2 To 0.3
PlaySoundFile "C:\Windows\Media\QAgoteam.wav"
Case 0.3 To 0.4
PlaySoundFile "C:\Windows\Media\QAcrowdclap2.wav"
Case 0.4 To 0.5
PlaySoundFile "C:\Windows\Media\QAcrowdclap.wav"
Case 0.5 To 0.6
PlaySoundFile "C:\Windows\Media\QAaaaah.wav"
Case 0.6 To 0.7
PlaySoundFile "C:\Windows\Media\QAcrowdcheer2.wav"
Case 0.7 To 0.8
PlaySoundFile "C:\Windows\Media\QAwow.wav"
Case 0.8 To 0.9
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
End Select
QAcheer = vbNullString

End Function

If you declare RandCheer as Integer, it will always be Zero or 1. You
haven't accounted for .9 to 1, so there should be deafening silence half the
time and the rest of the time, QAcrowdcheer.wav. Anyway, declare it as
single.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Thanks KD! I input the code but it appears that when an answer is
input into the cell, only 1 of two sounds are being played,
"C:\Windows\Media\QAcrowdcheer.wav" and "C:\Windows\Media\QAaaaah.wav"
Do you know why this is? Did I type this correctly?

Public Function QAcheer() As String
Dim RandCheer As Integer

Randomize
RandCheer = Rnd() 'gives the decimal number between 0-1
Select Case RandCheer
Case 0 To 0.1
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
Case 0.1 To 0.2
PlaySoundFile "C:\Windows\Media\QAkidsapplause.wav"
Case 0.2 To 0.3
PlaySoundFile "C:\Windows\Media\QAgoteam.wav"
Case 0.3 To 0.4
PlaySoundFile "C:\Windows\Media\QAcrowdclap2.wav"
Case 0.4 To 0.5
PlaySoundFile "C:\Windows\Media\QAcrowdclap.wav"
Case 0.5 To 0.6
PlaySoundFile "C:\Windows\Media\QAaaaah.wav"
Case 0.6 To 0.7
PlaySoundFile "C:\Windows\Media\QAcrowdcheer2.wav"
Case 0.7 To 0.8
PlaySoundFile "C:\Windows\Media\QAwow.wav"
Case 0.8 To 0.9
PlaySoundFile "C:\Windows\Media\QAcrowdcheer.wav"
End Select
QAcheer = vbNullString

End Function