The Problem (Q-score 8, ranked #74th of 95 in the VBA Core archive)
The scenario as originally posted in 2011
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.
Why community consensus is tight on this one
Across 95 VBA Core entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+4)
Verbal answer — walkthrough without a code block
Note: the verified answer is a prose walkthrough. If you need a runnable sample, check VBA Core entries ranked in the top 10 of the same archive.
Figured it out – not sure why Microsoft has this “feature”:
The condition arises when a virgin XLSX that uses an XLAM function is opened / created prior to opening the XLAM. In this case no amount of cajoling will cause the XLSX formulas to bind to and execute those XLAM functions, UNLESS you go into each cell & touch the formula bar & hit ENTER (or, as I discovered, do so en masse via a global replace – in my case all the funcs began w a “k”, so globally replacing “k” with “k” fixed the error). The problem does not occur if the XLAM is opened first.
When to Use It — vintage (14+ years old, pre-2013)
Ranked #74th in its category — specialized fit
This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.
What changed between 2011 and 2026
The answer is 15 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.