User Defined Functions NOT recalculating

excel vba user-defined-functions xlsx xlsm
tpascale picture tpascale · Apr 19, 2011 · Viewed 30.3k times · Source

I recently took a large, stable XLSM file, and split it apart into an XLAM and XLSX. Thousands of cells in the XLSX call (udfs) functions in the XLAM, and each such udf begins with the statement "Application.Volatile" (overkill, to force recalc).

The XLSX will NOT recalc with F9 thru Ctrl-Alt-Shift F9, nor with Cell.Calculate thru Application.CalculateFull. The XLSX cells are simply "dead" ... but ... I can reawaken them one by one if I hit F2 to edit the formula and then hit ENTER. Cells reawakened this way seem to stay awake, and recalc normally thereafter.

Has anyone encountered this strange behavior and are there any additional ways to force Excel to reconstruct the calc graph from scratch that I should try ?

One additional note in case it matters: I opened the XLAM and the XLSX via File Open, and have not installed the XLAM using the File ... Options ... Addins route - because in the past when I have done so, the minute you "uncheck" and installed XLAM then all the UDF references get replaced by full pathname links - pretty ugly. Alternatively if someone can outline a workaround for installing XLAM addins that doesn't create broken links everywhere I'll go with that.

Answer

Greg Glynn picture Greg Glynn · Nov 4, 2015

This works:

Sub Force_Recalc()
    Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Related questions

Refresh Excel VBA Function Results

How can I get a user-defined function to re-evaluate itself based on changed data in the spreadsheet? I tried F9 and Shift+F9. The only thing that seems to work is editing the cell with the function call and then …

Read More
Create a custom worksheet function in Excel VBA

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula): =MyCustomFunction(A3) Can this be done? EDIT: This is my VBA function signature: Public Function MyCustomFunction(str …

Read More
Function Overloading and UDF in Excel VBA

I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions. As VBA doesn't seem to support this, could anyone …

Read More