minor problem with vba code

[to75ne]

I have a problem with a bit of vba code. Any advise/help or a reason why would be appreciated. I did not write the code just adapted it. I know very little vba.

The code for the function:-

'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm2(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 & "\getout" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_FILENAME)
Alarm2 = True
Exit Function
End If
ErrHandler:
Alarm2 = False
End Function

When market goes into play the function is called from a cell in the main ba sheet. It plays a wave file stating market is inplay. It lasts about 12 seconds.

But it keeps looping. If I wish it to play only once then stop, what do I add to the code?

If I wish it to play 3 time then stop, what do I add to the code?

I have an identical function called alarm as opposed to alarm 2.
It fires when the sheet auto greens me out. The wavefile is different and of a similar length. Its called in an identical way (different cell) but it only plays once. The code and method of calling in both are identical. Why does one loop and the other does not?

Also the total matched, last updated, tx counter and countdown timer, all freeze up, when one of these wavefiles play. They then update once they have stopped or in the case of of alarm2, between the ending and restarting of the wavefile.

None of the above are that important that I need to change them, just would be nice so to speak if I could. They both work well, and do not slowdown or interfere with ba’s functionality in any way.

Sorry about the length of post, best I could do to explain things. The important thing is I have this glitch thing inside my head that I need to know why things do what they do, if I don’t know why, they end up sending me round the bend.

thanks (in advance)

[Zapata]

It may be nothing to do with the code but the condition being repeatedly met causing the function to be called repeatedly.

For example if the function call is based solely around "G1" being "In-play" the function would be repeatedly called.

Could you possibly post the line(s) triggering the function.

[to75ne]

thanks zapata.

i will try to explain.

In cell p3 I have the following formula =IF(ISBLANK(O6),"",10).

In cell p4 I have the following formula =Alarm(P3,">1")

In cell L6 is my global greenup command. When it fires its status cell o6 states ok. Cell p3 will then display 10.

Cell p4 will now change from false to true and calls wave file alarm. Which plays once.


In cell q3 I have the formula =IF(ISBLANK(G1),"",10)

In cell q4 I have the formula =Alarm2(Q3,">1")

When the cell g1 states inplay cell q3 displays 10, cell q4 changes from false to true and calls wave file alarm2 . which then plays continually until the market changes (g1 no longer displays inplay).

Since my original post I have swapped the triggers over ie call alarm2 from Alarm2(p3,">1") so when it auto greens and states ok in o6. alarm2 plays only once.

When I swap the other one over, alarm play continuosly until market is changed.

It would seem that something in “o6” allows it to do what it is supposed to do. And all other cells cause it to repeat.

As I said previously, the sheet works well its just that my brain is glitched. I have this need to know why things do what they do. Its doing my head in.

If I really needed/wanted to play a wave file once the market as gone in play, its far easier to swap the sound file that plays peter webb saying market is in play, to one that I wanted instead.

Im now going for out for drink (shortly) and hopefully forget about this non problem.
Its just I need to know, I don’t know why I just do. So any plausible solution or explanation would be of great help to me.

hope above makes some sense. once again thanks

[Zapata]

Not surprised you went out for a drink.

[to75ne]

Not surprised you went out for a drink.

im glad i did. the need to know why is still with me but no where near as strong as yesterday.

i was really just mucking about to see if i could get a wave file to play. once i got it to play, i could not resist pushing it a bit further, knowing full well that i would probably find a problem (in reality a non problem), that would play with the glitch in my mind.

as i said the urge is no way near as strong as yesterday and i would still like to know why. it will gradually recede, so i will keep this post up for a few days in the hope someone will be able to explain. if not i will delete it, so i can then safely forget it.

thanks zapata