PDA

View Full Version : Play Sound in Excel



mgrech
10-17-2012, 10:19 AM
I found a nice piece of code that plays a sound when the cell value changes. It works well if the number in the inequality is an integer but it does not work if it is a decimal as per below. How do I fix it so that it works also for decimals?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("F2:F250")
For Each Cell In CheckRange
If Cell.Value < -0.05 Then
PlaySound = True
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\ding.wav", 1)
End If

End Sub

p45cal
10-17-2012, 03:38 PM
It works here, as you have it.
Realise, you have to put a value in less than negative 0.05, such as -1.
.004 will not trigger it.

corfuen
10-18-2012, 02:20 AM
yes like the reply set you can deal it

mgrech
10-18-2012, 09:10 AM
guys, i understand what you are saying but it is not working for me. problem is that it plays the sound every time there is a change, no matter what the new value is.

I have my criterion set to play sound if cell value < -0.05. If cell value is +3, it plays sound, if cell value is -1 it plays sound, if cell value is -0.04 it plays sound.

so as i said it is not working.

Kenneth Hobs
10-18-2012, 12:34 PM
Are you "sure" that no value in your range meets the criterion?

Most people code it for just the cell(s) in a certain range. The way you do that is to use Intersect(). Dinging because I changed cell F2 when it is F3 that meets the criterion might lead one to think that F2 meet the criterion.


Private Const SND_APPLICATION = &H80 ' look for application specific association
Private Const SND_ALIAS = &H10000 ' name is a WIN.INI [sounds] entry
Private Const SND_ALIAS_ID = &H110000 ' name is a WIN.INI [sounds] entry identifier
Private Const SND_ASYNC = &H1 ' play asynchronously
Private Const SND_FILENAME = &H20000 ' name is a file name
Private Const SND_LOOP = &H8 ' loop the sound until next sndPlaySound
Private Const SND_MEMORY = &H4 ' lpszSoundName points to a memory file
Private Const SND_NODEFAULT = &H2 ' silence not default, if sound not found
Private Const SND_NOSTOP = &H10 ' don't stop any currently playing sound
Private Const SND_NOWAIT = &H2000 ' don't wait if the driver is busy
Private Const SND_PURGE = &H40 ' purge non-static events for task
Private Const SND_RESOURCE = &H40004 ' name is a resource name or atom
Private Const SND_SYNC = &H0 ' play synchronously (default)

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 Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Dim bPlaySound As Boolean

Set CheckRange = Range("F2:F250")
For Each Cell In CheckRange
If Cell.Value < -0.05 Then
bPlaySound = True
End If
Next Cell

If bPlaySound Then
'sndPlaySound32 "C:\windows\media\ding.wav", 1
PlaySound "C:\windows\media\ding.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
End If
End Sub

Kenneth Hobs
10-18-2012, 01:16 PM
Of course does the same.

Private Const SND_ASYNC = &H1 ' play asynchronously
Private Const SND_FILENAME = &H20000 ' name is a file name

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 Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Range("F2:F250"), "< -0.05") > 0 Then _
PlaySound "C:\windows\media\ding.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
End Sub

p45cal
10-18-2012, 02:20 PM
<snip> it plays the sound every time there is a change, no matter what the new value is.

I have my criterion set to play sound if cell value < -0.05.
No you don't, it will play a sound if ANY cell in F2:F250 is less than -0.05, regardless of the cell you've just changed.

see if this behaves the way you want:Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CellsJustChanged As Range, CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("F2:F250")
Set CellsJustChanged = Intersect(CheckRange, Target)
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then
PlaySound = True
Exit For
End If
Next
End If
If PlaySound Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
End Sub
or a bit shorter:Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, CellsJustChanged As Range, CheckRange As Range
Dim PlaySound As Boolean

Set CellsJustChanged = Intersect(Range("F2:F250"), Target)
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
Exit For
Next
End If
End Sub

mgrech
10-19-2012, 06:59 AM
guys, thank you for your suggestions. i will try them.

that said, why does the original code work well when the inequality has an integer but it does not work when it has a decimal? I thought it has to do with defining the type properly ( I have fortran programming expereince but I am new to VBA)

Kenneth Hobs
10-19-2012, 07:54 AM
Try making a simple example. Of course an integer versus a long or double type could cause a problem. What can trip you up is if your Excel data is double but you convert it to integer in vba and then compare that to a double. Understand too the differences between a vba Round and a worksheet function round. They do not work the same.

p45cal
10-19-2012, 08:25 AM
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1)
Exit For
Next
I've just noticed an error in the above, it should be:
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then Call sndPlaySound32("C:\windows\media\ding.wav", 1):Exit For
Next

mgrech
10-19-2012, 08:54 AM
attached is an example. you can see the numbers i have in column f.

p45cal
10-19-2012, 09:22 AM
so try the attached

mgrech
10-19-2012, 11:30 AM
thank you for sending me this. it is an improvement but i still have an issue.

in reality i have column f as a function of column d and column e where

column f = (column d / column e) - 1

i found out that if i change the value of column f manually then the program works. however if i update column d or column e so that column f updates , then the program does not work.

how can i modify it such that it works all the time?

p45cal
10-19-2012, 11:40 AM
however if i update column d or column e so that column f updatesHow actually are columns D/E being updated? by keyboard input? by link to other cells, perhaps on another sheet? by DDE link or some such.. How?

mgrech
10-19-2012, 07:16 PM
i use a program called xlq which downloads data from yahoo finance so for example i will have a formula =xlq(MSFT,lastprice) and it would get the last trading price for MSFT.

that said, when doing the testing i was populating the values of columns d and e by hand, just by typing a number. i was not using the xlq formula. still the program did not respond well. it only works well if i make the change to column f directly.

Kenneth Hobs
10-19-2012, 08:32 PM
Set CheckRange = Range("F2:F250").Dependents

p45cal
10-20-2012, 02:13 AM
Set CheckRange = Range("F2:F250").Dependents Kenneth, I don't think F2:F250 has any dependents. though it probably has .precedents.
Haven't got time to look at it right now, but I suppose something along the lines of:
set checkrange = intersect(target.dependents,range("F2:F250"))
For each cll in checkrange.cells
if…
and then there'd be the question of whether the way data gets into the sheet will trigger the sheet_change event at all. It could be a case of using the calculate event and keeping a global variable version of the F2:F250 range (or a Static local variable of the same) to check against for changes.

mgrech
10-22-2012, 09:44 AM
this last suggestion works. thanks p45cal

mgrech
10-22-2012, 10:09 AM
one more question, if I may. How can I get the sound to keep playing until the user clicks ok? I tried using
Const SND_LOOP = &H8

but it does not seem to be making any difference?

p45cal
10-22-2012, 01:10 PM
try:Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, CellsJustChanged As Range
Set CellsJustChanged = Intersect(Target.Dependents, Range("F2:F250"))
If Not CellsJustChanged Is Nothing Then
For Each Cell In CellsJustChanged.Cells
If Cell.Value < -0.05 Then
sndPlaySound32 "C:\windows\media\ding.wav", SND_ASYNC + SND_LOOP
Exit For
End If
Next
End If
End Sub
and the following event code for an activeX button I put on the sheet stops the sound.Private Sub CommandButton1_Click()
sndPlaySound32 vbNullString, SND_SYNC
End Sub

mgrech
10-23-2012, 08:43 AM
hi p45cal, thank you very much for the loop info.

it turns out that i spoke too soon.... the code works when i change one of the values in column d or column e by hand. however it does not work when these values are a function of xlq such as =xlq(lowprice) and they update automatically. (xlq refreshes every minute)

any ideas how i can fix this? thanks.

p45cal
10-23-2012, 09:34 AM
hi p45cal, thank you very much for the loop info.

it turns out that i spoke too soon.... the code works when i change one of the values in column d or column e by hand. however it does not work when these values are a function of xlq such as =xlq(lowprice)
which is why, earlier, I said:
and then there'd be the question of whether the way data gets into the sheet will trigger the sheet_change event at all.
Try the following, instead of the Sheet_Change event [it's just a realisation of:
It could be a case of using the calculate event and keeping a global variable version of the F2:F250 range (or a Static local variable of the same) to check against for changes. using a static local variable]:
Private Sub Worksheet_Calculate()
Static OldCheckRangeValues
Set CheckRange = Range("F2:F250")
If Not IsEmpty(OldCheckRangeValues) Then
i = 1
For Each cll In CheckRange.Cells
If cll.Value < -0.05 And OldCheckRangeValues(i, 1) <> cll.Value Then
sndPlaySound32 "C:\windows\media\ding.wav", SND_ASYNC + SND_LOOP
Exit For
End If
i = i + 1
Next cll
End If
OldCheckRangeValues = CheckRange.Value
End Sub
This, I'd hope, would work in the same way, playing a sound when a value changes in the range and that value is less than -0.05. This means that if a value is already less than -0.05, and it changes to a different value, but still less than -0.05, it will play a sound.
It might be that you don't want it to play a sound if the value changing is already less than -0.05 and remains so. In that case replace the If cell.value line with:If cll.Value < -0.05 And OldCheckRangeValues(i, 1) > -0.05 Then

Of course, you will already have at least the lines which declare the following at the top of the module:
Private Const SND_ASYNC = &H1 ' play asynchronously
Private Const SND_LOOP = &H8 ' loop the sound until next sndPlaySound
Private Const SND_SYNC = &H0 ' play synchronously (default)
or something similar.. because you need them.

mgrech
10-24-2012, 01:25 PM
thanks p45cal. i will see how it goes tomorrow when the market is open.

mgrech
10-25-2012, 08:12 AM
hi p45cal, we have success. program works as hoped. thanks.