can I use IF statement to call a VBA function ?

[acwai]

I have used the code in here to create a function called Alarm

http://tinyurl.com/c59tec9

I can get it to play a sound by using =alarm(a1,">2") to test whethe
a1 is >2

Can I use an IF statement to trigger the function alarm ?

Thanks

 

[merjet]

Yes, but I wonder why you would want that when the function has an IF statement within already.

 

[joeu2004]

I have used the code in here to create a function
called Alarm http://tinyurl.com/c59tec9
I can get it to play a sound by using =alarm(a1,">2")
to test whether a1 is >2

What a silly thing to do! Not you; the design of the function.

Can I use an IF statement to trigger the function alarm ?

Redesign function Alarm as follows:

'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 Alarm(Optional retnval = "")
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
ErrHandler:
Alarm = retnval
End Function

Then you can call it with the following equivalent IF expression:

=IF(A1>2,Alarm(),"")

And of course, this permits you to use Alarm with much more complicated
conditional expressions, for example:

=IF(AND(A1>2,B1<3,C1=4),Alarm(),"")

By default, Alarm returns the null string (""). The optional parameter
allows you to use Alarm in arithmetic expressions, to wit:

=IF(A1>2,2+Alarm(0),"")

or more simply:

=IF(A1>2,Alarm(2),"")