VBA switching between formulas and values

[NickvdB]

Hello,

In excel 2010 we have a large spreadsheet with multiple thousands formulas. In order to optimize the loading of this file I'm investigating the option to have a vba code to switch between values and formulas and back to values.

I'm not happy with the option to alter automatic calculations to manual sincs this affects other open files and causes irritations and flaws. Therefore I would like to know if it would be possible to change cell values from e.g. =b1+c1 resulting in 5 to the value 5 in that cell and when running the macro having the formula =b1+c1 resulting in value 6 after an update of cells b1 and c1.
Is this advisable and workable? I want to prevent hardcoding the formulas in the vba since they can and will change during time and I want to have an easy change of the running macro.

Any advice???

 

[BiocideJ]

I have really never tried to do something like this before and I believe that changing the Application.Calculation option would be FAR less disruptive. I make no guarantees that this process is going to be without errors and caution you to remember that there is no UNDO feature when a macro runs so if something causes the macro to error out, you will be left with a sheet full of values with no way of getting the formulas back.

All that being said, something along the lines of the following WILL do what you are asking.

Sub ToggleFormulas()
Dim mySheet as Worksheet
Dim formulaRange As Range
Dim arrFormula As Variant
    Set mySheet = ActiveSheet
    Set formulaRange = mySheet.Range( _
        mySheet.Range("A1"), _
        mySheet.Cells.SpecialCells(xlCellTypeLastCell))
    arrFormula = formulaRange.Formula
    
    formulaRange.Formula = formulaRange.Value
    
    
    'some functions
    
    
    formulaRange.Formula = arrFormula

End Sub

 

[NickvdB]

Thanks for the advice BiocideJ. I've found out it also works very quick in the VBA to have only 1 column filled with formulas and copy this column to the right.