VBA switching between formulas and values

NickvdB

Board Regular
Joined
Apr 30, 2014
Messages
68
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???
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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.

Code:
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

Board Regular
Joined
Apr 30, 2014
Messages
68
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top