Hi All
So I have only played with this for 20 mins or so - but hoping someone may have been down this path:
I'd like to write a UDF that reports the current calculation mode to a cell - so what I thought would work would be:
However, this seems to behave quite "strangely" in manual calc mode. If I edit the formula (F2 etc) then it returns manual as expected. However, if I F9/SHIFT+F9 (or even CTRL+ALT+SHIFT+F9) then the UDF returns "AUTO" - which is not what I was expecting. However - the UDF is being called - I've checked with a break-point/debug.print.
TBH its not the end of the world if I can't get this working via UDF (there are plenty of alternative ways without using a UDF - they're just not as generic) - I am just intrigued/baffled and wondering if anyone has an explanation.
Thanks
Ben
So I have only played with this for 20 mins or so - but hoping someone may have been down this path:
I'd like to write a UDF that reports the current calculation mode to a cell - so what I thought would work would be:
VBA Code:
Public Function GetCalcMode()
Application.Volatile True
If Application.Calculation = xlAutomatic Then
GetCalcMode = "AUTO"
ElseIf Application.Calculation = xlManual Then
GetCalcMode = "MANUAL"
Else
GetCalcMode = "OTHER"
End If
End Function
However, this seems to behave quite "strangely" in manual calc mode. If I edit the formula (F2 etc) then it returns manual as expected. However, if I F9/SHIFT+F9 (or even CTRL+ALT+SHIFT+F9) then the UDF returns "AUTO" - which is not what I was expecting. However - the UDF is being called - I've checked with a break-point/debug.print.
TBH its not the end of the world if I can't get this working via UDF (there are plenty of alternative ways without using a UDF - they're just not as generic) - I am just intrigued/baffled and wondering if anyone has an explanation.
Thanks
Ben