Entrepremasochism

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.
						
			

				

					
Comments Off • Tagged as: excel, formula, lock, prevent, updating
					
Share on Twitter, Facebook, Delicious, Digg, Reddit