Returning Application.Calculation value in UDF

BenMcBen

New Member
Joined
Aug 19, 2014
Messages
34
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:

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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
It would seem that any application- or worksheet-level calculation (as opposed to a specific range calculation) effectively makes the calculation mode automatic temporarily. The same behaviour is observed if you use Application.Calculate in code, but not if you use something like Range("A1").Calculate
 
Upvote 0
It would seem that any application- or worksheet-level calculation (as opposed to a specific range calculation) effectively makes the calculation mode automatic temporarily. The same behaviour is observed if you use Application.Calculate in code, but not if you use something like Range("A1").Calculate
Thanks Rory, I was wondering if it was "just me" / my setup - I have a lot of add-ins like Bloomberg & Reuters (sorry Refinitive) that I thought might be in play...
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top