VBA Alarm function condition incorrectly returns False

[gertbr]

I am trying to configure an alarm based on the value of a cell which is linked to a web query
The formula is
=Alarm(A1;">=1")
When I set the condition based on the value of cel A1 and type manually a number > 1 in cel A1, the alarm functions properly
When I change A1 in the formula to D4 or D5, the formula returns False and the alarm doesn´t sound, even though the values in D4 and D5 exceed 1. What`s wrong here? I really want the alarm to work on D5.

I have attached the excel file. Google doesn`t let me attach the wav file.

Hope someone can solve this mystery....

 

[strongm]

Link doesn't work for me ...

I think you need to show us your Alarm function, since this is not an Excel feature; I'm guessing it is a variant of a tip posted by John Walkenbach on his web site

 

[gertbr]

Correct, the Alarm function is the following
'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(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 & "\ferrari250.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

The Ferrari250.wav file can be replaced with any other wav file. What is strange is that this works when applied on A1 but doesn´t work when applied on D5, where the result of D5 is the result of two web queries divided by eachother. Let me know if any other details are needed.

 

[gertbr]

hope this link works.....

 

[gertbr]

looks like there is a limitation to the lenght of the url, please try this one.....

 

[strongm]

The links just take us to the GoogleDocs login page ...

 

[strongm]

Check the number format of D4 and D5 ...

 

[gertbr]

I posted the related Excel file to MediaFire, hope you can access the links below.

The question is,.. why does the alarm function return True in cel E3 and False in cels E5,6,7&9?

Many thanks for your support!!
Regards,
Gert


http://www.mediafire.com/?wmmmwyyminz

http://www.mediafire.com/file/mjoumknmamv/ferrari250.wav

 

[strongm]

Well - as soon as I do a refresh of the data here all the alarms correctly update themselves to True ...

 

[SkipVought]

Becuase you THINK that it meets the condition, but CLEARLY it does not. The problem is with the DATA referenced in the cell(s) in question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gertbr]

Skip & strongm,

First,... many thanks for your feedback, your support is very well appreciated!!

I am still puzzled!!! How can the alarm formula work on one computer but doesn`t on another? And how can there be a problem with the data in the referenced cels while the alarm formula works on some computers?

Many thanks in advance, hope you can share your thoughts.

FYI, I did send the file to John Walkenbach and was fortunate to get feedback. Unfortunatly, the feedback was the same as strongm`s,..... all alarms function properly in all cels.

Regards,
Gert

 

[gertbr]

Skip & strongm,

Based on your feedback, figured that the problem must be caused by local settings. Changed the decimal indicator from , to . in control panel and guess what,....... all formulas work!!!!!!!!!!!!!!

Took a week to solve this problem but very happy that it works now thanks to your valuable contributions!

Regards,
Gert

 

[PHV]

You may replace this:
If Evaluate(Cell.Value & Condition) Then

with this:
If Evaluate(Replace(Cell.Text, ",", ".") & Condition) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886