Temporarily Prevent a Worksheet’s Formulas from Updating in Excel
by noahlh on April 30, 2010
This is the first in a series of extremely specific, nerdy posts I’ll make – not necessarily because I think a lot of people will be interested – but rather because I’m sure there will be a few specific people who will be interested and will find this via the Googles. Basically when I run into a weird problem and have to spend more than 10 seconds researching it, I think it’s only fair that I write up the solution in the hopes of saving some other folks out there a few minutes of time.
So for this case, I created a big multi-page spreadsheet and need to (temporarily) prevent one of the pages from allowing the formulas to update (while allowing other pages to update). In this case there are a ton of formulas on the page, so changing the formulas themselves isn’t an option
The solution I found is in a very simple two-part macro. Thanks to user ‘shg’ from excelforum.com for posting this back in November 2008 (Original link):
Sub CommentFormulas() ' comments out all but array formulas Dim cell As Excel.Range With Application .ScreenUpdating = False .Calculation = xlManual For Each cell In ActiveSheet.UsedRange With cell If .HasFormula And Not .HasArray Then .Formula = "'" & .Formula End If End With Next .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Sub UncommentFormulas() ' restores commented formulas Dim cell As Excel.Range With Application .ScreenUpdating = False .Calculation = xlManual For Each cell In ActiveSheet.UsedRange With cell ' the left(cstr(cell)) below accommodates cells having errors If Left(CStr(.Value), 1) = "=" And Not .HasArray Then .Formula = Trim(.Text) End If End With Next .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub
I made one small change in the second macro by allowing Excel to ‘trim’ the converted text. For some reason in my version (Excel 2003) running the UncommentFormulas macro.
What this does is convert all the formulas on a page to raw text with one macro (so you can change around other stuff without any of the formulas being affected — it essentially ‘freezes’ the page), and then when you’re ready to turn the page “live” again, you use the second macro to turn everything back to normal.
Took a few minutes to get working, but it does work perfectly. Enjoy.