Forced recalculation of Macro function

[murray]

I have a workbook with multiple sheets. Each sheet is a price build of
a product where different components are from different countries
priced in different currencies.
Current exchange rates are entered on the first sheet in the workbook
(a 'Master data' sheet).
I have created a Macro that calculates the local currency equivalent
for each component based on the unit of currency purchase, and the
current exchange rate.
My problem is that when I enter a new exchange rate there is no
automatic update of the values on the worksheets that call the Macro.
I have tried the Command + = keystroke to force a recalculation for
the workbook, and a SHIFT Command = + keystroke to force recalculation
on just one worksheet, but neither has any visible effect.
If I copy the buy price and paste it back on itself, that generates an
updated value though.

Is there anyway in Excel 2004 Mac to force Macros to recalculate?

Murray

 

[Bob Greenblatt]

I have a workbook with multiple sheets. Each sheet is a price build of
a product where different components are from different countries
priced in different currencies.
Current exchange rates are entered on the first sheet in the workbook
(a 'Master data' sheet).
I have created a Macro that calculates the local currency equivalent
for each component based on the unit of currency purchase, and the
current exchange rate.
My problem is that when I enter a new exchange rate there is no
automatic update of the values on the worksheets that call the Macro.
I have tried the Command + = keystroke to force a recalculation for
the workbook, and a SHIFT Command = + keystroke to force recalculation
on just one worksheet, but neither has any visible effect.
If I copy the buy price and paste it back on itself, that generates an
updated value though.

Is there anyway in Excel 2004 Mac to force Macros to recalculate?

Murray

Does your macro function start with Option Volatile? If not, try that. Are
you sure Calculation is set to Automatic?

 

[murray]

Bob,

Sorry about the long hiatus in replying. Firstly, Yes Calculation is
set to automatic.

Secondly, I should have said I have written a Function, not a Macro
(if that makes any difference).

The function is as follows:
Function Currency_Convert(amount, unit)
Select Case unit
Case "J"
Currency_Convert = amount / Range("Master_information!JPY")
Case "U"
Currency_Convert = amount / Range("Master_information!
USD")
Case "A"
Currency_Convert = amount / Range("Master_information!
AUD")
Case "P"
Currency_Convert = amount / Range("Master_information!
GBP")
Case "N"
Currency_Convert = amount / Range("Master_information!
NZD")
End Select
End Function

What I wanted was to enter the cuurent exchange rate on the
"Master_information" worksheet and have the whole workbook
recalculate, but that does not occur. If I re-enter the currency type
(ie re-enter "J" in the currency cell, the new value will recalculate,
but for that row only.

Any further suggestions?

Murray

 

[JE McGimpsey]

What I wanted was to enter the cuurent exchange rate on the
"Master_information" worksheet and have the whole workbook
recalculate, but that does not occur. If I re-enter the currency type
(ie re-enter "J" in the currency cell, the new value will recalculate,
but for that row only.

XL tries to be efficient by not recalculating only those cells for which
the arguments of the functions have changed. For instance, if the
formula in B1 were

=SUM(A1:A10)

there's no sense recalculating B1 unless a cell in A1:A10 changes. So it
looks at the arguments of a function in determining whether to
recalculate.

Since you don't reference the rate table in the function's arguments, XL
doesn't know to update cells calling the function when the rate table
changes. One way would be to rewrite your function to take the rate
table as an argument, for instance, something like:

Public Function Currency_Convert(byVal amount As Double, _
byVal unit As String, byRef ratetable As Range) As Variant
Select Case UCase(unit)
Case "J"
sLookup = "JPY"
Case "U"
sLookup = "USD"
Case Else
sLookup = "Not Found"
End Select
If sLookup = "Not Found" Then
Currency_Convert = CVErr(xlErrRef)
Else
dConvert = Application.VLookup(sLookup, ratetable, 2, False)
Currency_Convert = amount / dConvert
End If
End Function

Call as =Currency_Convert(A1, B1, Master_information!A1:B10)

Obviously the exact implementation depends on your rate table structure.

An alternative is to put

Application.Volatile Volatile:=True

as your first line of code. That will force recalculation of the
function every time a calculation occurs in the worksheet. If you don't
have a lot of calls to the function, this won't be a big performance
hit, but if you have hundreds or thousands of calls, it could slow
things down a bit.