Turning Auto Calcs into Run by Button

[ronjon65]

I have a file that uses VBA functions and equations built into the cells. When you change an input, it auto updates/calculates (default for most users).

The issue is that it takes 20+ seconds to perform the calculations. So lets say you want to enter a new set of inputs (maybe 5). You change one then wait, then change another, then wait.

So the question is, how can I "trip" up the auto calculate and only perform the calculation once a button is pressed? This way, you would enter all of the inputs then press Run. I don't want to turn automatic calculations off though because that sets a user parameter (I don't want to mess with the user settings).

Thanks, I couldn't think of anything off the top of my head to do this...maybe there is something simple?

[jonh]

Clear the formulas and write them back to the cells after the data has been input?



This quick example SaveFormulaInfo (which I haven't tested at all) should go through each sheet and write all formulas to an array (e.g. call it when the workbook opens.)


A better idea might be to run the code only once to write the formulas to a hidden sheet if you ever change the workbook instead of using the array as arrays can be kinda volatile.


Run ClearFormulaInfo to delete the formulas prior to input.
Run LoadFormulaInfo to reset the formulas after input.



Dim fc() As fcell
Private Type fcell
sheet As String
address As String
formula As String
End Type


Sub SaveFormulaInfo()
Dim c As Range, r As Range, s As Worksheet
ReDim fc(0)

For Each s In ActiveWorkbook.Sheets
Set r = GetFormulas(s)
If Not r Is Nothing Then
i = UBound(fc)
ReDim Preserve fc(i + r.Cells.Count - 1)

For Each c In r
fc(i).sheet = c.Worksheet.Name
fc(i).address = c.address
fc(i).formula = c.formula
i = i + 1
Next
End If
Next
End Sub


Private Function GetFormulas(s As Worksheet) As Range
On Error Resume Next
Set GetFormulas = s.Cells.SpecialCells(xlCellTypeFormulas, 23)
End Function


Sub LoadFormulaInfo()
For i = 0 To UBound(fc)
sheets(fc(i).sheet).Range(fc(i).address).formula = fc(i).formula
Next
End Sub


Sub ClearFormulaInfo()
For i = 0 To UBound(fc)
sheets(fc(i).sheet).Range(fc(i).address).formula = ""
Next
End Sub

[snb]

application.calculation=

[ronjon65]

jonh. Thanks, I was thinking something along those lines. I will have to mess with it some.

snb. No go on that since that will affect user settings (as discussed previously). I suppose you can store the existing setting and reset back when closing, but if Excel crashes that can mess them up. I can't allow that.

[snb]

If you think to know better, I'll leave you to that.

[ronjon65]

Hmmm, I don't know what you mean.

I have already tried:

Application.Calculation = xlCalculationAutomatic
and

Application.Calculation = xlCalculationManual

But these are Excel specific settings and not settings for the individual file. If I could make it specific to the file, then it would work. Otherwise, I have to change user settings (which is not allowed in this case). Maybe this works differently in different versions though? You haven't given any information about how this can be done without affecting user settings.

[SamT]

Toggle Calculation off on when that sheet is Activated Deactivated.


Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub ButtonCode()
Application.Calculate
End Sub

[ronjon65]

Toggle Calculation off on when that sheet is Activated Deactivated.

I have already went down that road and did not find it acceptable for the following reasons:

-What if Excel crashes?
-What if the user changes their preferred settings while working the file?
-What if the user settings are manual already? Like I said, you may be able to detect the current setting and restore to that on close. Still faced with previous two issues.

I just don't want to mess with user settings since they are somewhat fragile and some users may not realize that a change has happened. This could confuse them when working on other things.

[SamT]

IF Excel Crashes you have bigger problems

Add CalculationSetting to the Event subs

'CalculationSetting as Module level Variable
CalculationSetting = Application.Calculation
'And
Application.Calculation = CalculationSetting


IMO, it is not Best Practice to allow User Settings on a distributed Macro enabled Workbook. IMO, BP would be to save User Settings when the Workbook is activated, force Common Settings, then restore User Settings when the Book is Deactivated.

[ronjon65]

Well, this would address item 3, but not items 1 and 2. Unfortunately, Excel can and will crash, especially when you consider different Office versions and 32 bit/64 bit, OS versions and 32/64, and system configurations. A fair amount of users would not be aware that automatic calculations is the default. They might possibly think their Excel "broke" after a crash since things are not updated. I could easily see this happening and given enough users/time, it is an eventuality.

That said, I think there would be a way to just put an intermediate "trip" so that the calculations are not performed until you force the do so. This would entirely eliminate the risk of a user setting being inadvertently adjusted. Even if low, I would rather try to a zero risk option. I have a few other ideas and will report back if I have success.

[SamT]

different Office versions and 32 bit/64 bit, OS versions and 32/64, and system configurations...
I would rather try to a zero risk option

With that many users and versions, you need code to prevent any user from using custom settings and start using Compiler Directives in all your code so that you can use one set of code for all versions.
But... You know best.

[ronjon65]

I think this will work and its pretty simple.

- For the cells that are the inputs to the formulas, just have an intermediate position for them. Then that intermediate cell is updated via VBA. That way, when inputs are changed nothing happens (because the formulas don't see a change). When the VBA is run, the input cells are copied to the intermediate position and Excel recognizes that an update is required.