UDF: How make volatile

  • Thread starter Thread starter Lyle
  • Start date Start date
L

Lyle

I have a UDF named exchangeRate (below):

In a cell I have:
=exchangeRate()

At first entry this updates, refreshes or whatever properly, for
example, to 1.5966.

But on subsequent loads, automatic recalculation, or F9 it does not
update; it remains at 1.5966.

Why not? How can I make it responsive to F9?

(If I change the function to
Public Function exchangeRate@(ByVal vDummy, Optional ByVal vDefault@ =
1.6)
and reference a cell on the spreadsheet with vDummy then updating
seems to take place on each calculation.
I'm hoping there is a less kludgy way.)


**** begin code ****
Public Function exchangeRate@(Optional ByVal vDefault@ = 1.6)
Application.Volatile True

On Error GoTo exchangeRateErr

Dim matches As Object
Dim rawHTML$
Dim regularExpression As Object
Dim xmlHttpRequest As Object

Set regularExpression = CreateObject("VBScript.RegExp")
regularExpression.Pattern = "Euro \(EUR\)<\/td><td
align=""center"" class=""regulartext"">(\d\.\d{0,4})<\/td><td
align=""center"" class=""regulartext"">(\d\.\d{0,4})<\/td>"

Set xmlHttpRequest = CreateObject("Microsoft.XMLHTTP")
xmlHttpRequest.Open "GET", "http://www.rbcroyalbank.com/
RBC:Rc@5fY71A8UAAqnCsxI/rates/cashrates.html", False
xmlHttpRequest.send
rawHTML = xmlHttpRequest.responseText

Set matches = regularExpression.Execute(rawHTML)
exchangeRate = CCur(matches(0).SubMatches(1))

exchangeRateExit:
Exit Function

exchangeRateErr:
With Err
MsgBox .Description _
& vbNewLine & vbNewLine _
& "Exchange Rate defaulted to " & CStr(vDefault), _
vbInformation, _
"Error :" & Err.Number
End With
exchangeRate = vDefault
Resume exchangeRateExit:

End Function
**** end code ****
Note: News clients and/or servers may insert line breaks into code,
resulting in compile errors. These line breaks must be removed.
 
You may have to make the XML call asynchronous and operate as a
separate process, then, have the function simply pull the value from
the other call on demand.
 
You may have to make the XML call asynchronous and operate as a
separate process, then, have the function simply pull the value from
the other call on demand.
Click to expand...

This function always returns the exchange rate, the number that I want
it to return. It is used in slightly different formats in other
applications. It works perfectly. In Excel it is not being called when
F9 is pressed. But it is called when I include a dummy parameter
pointing to a cell on the spread sheet. And it works perfectly then
too. But, IMO, this is a kludge. I am hoping that someone can tell me
how to encourage Excel to call the function when F9 is pressed, or
when automatic calculation is done because a value has changed.
 
Try adding

Application.Volatile

at the head of the code

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top