CODEWITHSUNDEEP
excelvbafunctionuser-defined-functionsvolatile
xlLogan
xlLogan

Reputation: 21

How to Force a Function (UDF) Update in MS Excel VBA

This is my first post because in several years of learning VBA, I always been able to find the answers to all my question because some one else beat me to it and because stackexchange rocks. However, I have finally ran into something that I cannot find any answers for...

BACKGROUND: I have a very complicated Excel workbook that is very VBA intensive (lots of loops and automatic subs). I'm very cautious about referencing cells from VBA because if someone ever shifts a cell without updating the VBA code, problems will ensue. However, I have a couple calculations that need to be done that are far too complex for an in-cell formula, so I've created functions for those things. That avoids any tampering issues and gives me the power I need.

PROBLEM: But the problem is, since the workbook is so complicated, if I make the UDFs volatile, they will lock up the workbook because it's just looping through way to much data. But if I don't make them volatile, the function values will not update in the cells.

HOPES AND DREAMS: I would like to know how to force my UDF to run without being volatile.

NOTE: I've tried everything I can find with no success, but I am open to ideas. ".calculate" does not work (unless I'm using it wrong). ".calculate = ...automatic" doesn't work either. Creating a "volatile" variable and I toggle externally also doesn't work.

Another solution would be to temporarily turn on function volatility from a different module/sub, but I don't know of a way to do that.

Any help would be greatly appreciated.

Thanks!

Upvotes: 2

Views: 5025

Answers (1)

Andreas Covidiot
Andreas Covidiot

Reputation: 4745

(As K.Davis said and xlLogan approved in the comments above) Application.Calculate or (if not working) Application.CalculateFull can help in many cases.

But sometimes it may be necessary to (additionally) mark some UDF(s) as volatile (which xlLogan explicitely did/could not do), especially (like in another case I had) when the dependencies are bi-directional between sheet formulas and VBA code (and like in my case not dependent on some sheet change, but e.g. on the excel filename, that may have been changed during two openings and is recognized and processed during the Workbook_Open() event):

Sub Foo()
    Application.Volatile  '=> so this sub/function is not considered entirely dependent (deterministic)
                          '   on some cells only, but on other "background" conditions as well
    ...
End Sub

Also explicitely disabling and enabling events during certain (potentially expensive) recalculation triggering operations/changes (e.g. also possbile via user dialogs/buttons) could help to manage performance issues, e.g. using (some often applicable functionality):

'used with EventsEnable()
Sub EventsDisable()
    With Application: .EnableEvents = False:  .ScreenUpdating = False:  .Calculation = xlCalculationManual:  End With
End Sub


'used with EventsDisable()
Sub EventsEnable()
    With Application:  .EnableEvents = True:  .ScreenUpdating = True:  .Calculation = xlCalculationAutomatic:  End With
End Sub

Upvotes: 4

Related Questions