VBA - UDF to turn calculation of cells on/off

[bunni]

Hi,

I have a small UDF which I would like to use to turn on / off the calculation for specific cells in a large spreadsheet.

it looks like the following

Public Function CalcSwitch(Switch_Target As Range, Switch_If As Variant, Func As Variant)


If Switch_Target <> Switch_If Then
result = Application.Caller.Text
result = Val(result)
Else:
result = Func
End If


CalcSwitch= result


End Function


an example use case for the UDF would be the following:

=CalcSwitch(B2,"Please Calculate",SUM(D2:E2))

If the content of B2 cell is "Please Calculate", then the calculation of the SUM(D2:E2) function is done.
If the content of B2 cell is NOT "Please Calculate", then the function keeps the original / initial value of the cell - meaning don't re-calculates the cell value. Even if the values in the D2:E2 range have changed, the cell values don't updates.

The reason I created this formula, because I have a large workbook, with very calculation heavy functions. recalculating the whole workbook takes several minutes, however, I often only need to recalculate part of the spreadsheet. I thought that this way I can avoid recalculation of calculation heavy functions while keeping the original value of the cell unchanged.

Issue:
The function keeps the original value unchanged (works only with numeric values, but this is fine), but the calculation time does not change. It seems that the function runs the calculation of the "Func" even if the Switch_Target is not equal to the Switch_If parameter.


background:
sometimes when I work on large spreadsheets and I want to turn off calculations of a specific part of the sheet, I do the following.
=IF(B2="Please Calculate",SUM(D2:E2),"")

This is very similar to what I have done above, and it works great. The drawback is that I lose the cell value in case the IF condition is FALSE. This is what I wanted to overcome with the UDF above.


If you have any ideas how I could solve the issue, or overcome the problem in any other way please let me know!

Thanks,
Laszlo

 

[Yongle]

A different approach

Use VBA to calculate a "BIG" formula and place value only in the cell
- recalc would then only occur when VBA triggered

a simple example
1. Select cell B1 \ click on data validation \ list \ enter only one value in source: Please Calculate

2. Paste code below into sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
    Application.EnableEvents = False
        Target.ClearContents
        Call MyOtherMacro
    Application.EnableEvents = True
End If
End Sub

Sub MyOtherMacro()
    Range("A1") = Range("A1") + 5
End Sub

3. Use dropdown in B1 and select Please Calculate

4. Whenever dropdown in B1 selected "formula" for A1 is recalculated

5. User need some warning that the formula calculation is "pending" to prevent unnecessary annoyance

 

[mikerickson]

I'm assuming that the SUM(D2:E2) is for illustration purposes only and that your real functions are much more complicated.
And I'm also assuming that you are aware of which functions are volatile (calculate on every cell change) or not (calculate only when precedent cells are changed.)

Would setting calculation to Automatic Except for Data Tables help your situation?

 

[bunni]

A different approach

Use VBA to calculate a "BIG" formula and place value only in the cell
- recalc would then only occur when VBA triggered

a simple example
1. Select cell B1 \ click on data validation \ list \ enter only one value in source: Please Calculate

2. Paste code below into sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
    Application.EnableEvents = False
        Target.ClearContents
        Call MyOtherMacro
    Application.EnableEvents = True
End If
End Sub

Sub MyOtherMacro()
    Range("A1") = Range("A1") + 5
End Sub

3. Use dropdown in B1 and select Please Calculate

4.Whenever dropdown in B1 selected "formula" for A1 is recalculated

5.User need some warning that the formula calculation is "pending" to prevent unnecessary annoyance

This is a nice solution, thanks will try it out. However I would prefer solving it with a UDF since I can re-use it easily any time in other spreadsheets as well (especially if I save it into personal.xlsb), also I can configure and modify "calculation schemes" from my dashboards... e.g.: for this report, refresh this group of cells, for that report that group of cells. and it is easy to set up and modify these schemes by controlling the Swtich_Target parameters of groups of cells with if functions etc.

 

[bunni]

I'm assuming that the SUM(D2:E2) is for illustration purposes only and that your real functions are much more complicated.
And I'm also assuming that you are aware of which functions are volatile (calculate on every cell change) or not (calculate only when precedent cells are changed.)

Would setting calculation to Automatic Except for Data Tables help your situation?

indeed it is for illustration purposes just to keep the example easy to read. The real formulas are much more complex, combined / array functions running over a couple thousand data points. so they are really slow.
I'm not an expert for data tables, but the benefit of the UDF would be that I could separately turn on / off calculation for specific parts of my spreadsheet/model by changing the values of several "Switch_Target" cells. Hope my explanation is clear.
would I be able to do it using data tables? (updating some data tables, but not all)

so the ideal solution to my problem would be a UDF. I could easily embed it into my reporting dashboards, and setting it up to control different parts of my models is also very convenient.


Thank you!

 

[bunni]

okay, so I was able to make it work with a little trick.

The UDF remained the same. However, I've changed how I entered the function in the spreadsheet.

Instead of:
=CalcSwitch($B$2,"Yes",SUM(D2:E2))

I've entered it this way:
=CalcSwitch($B$2,"Please Calculate",IF($B$2="Please Calculate",SUM(D2:E2),""))

It seems like that if embed the SUM function into an IF function, and I set the IF condition to basically the same as the condition of the CalcSwitch UDF, it works.
- If B2 = "Please Calculate", then the content of my cell is recalculated. And since it is actually not a SUM function, but a computational heavy complex function, it takes a while it to finish calculation.

 

[bunni]

okay, so I was able to make it work with a little trick.

The UDF remained the same. However, I've changed how I entered the function in the spreadsheet.

Instead of:
=CalcSwitch($B$2,"Yes",SUM(D2:E2))

I've entered it this way:
=CalcSwitch($B$2,"Please Calculate",IF($B$2="Please Calculate",SUM(D2:E2),""))

It seems like that if embed the SUM function into an IF function, and I set the IF condition to basically the same as the condition of the CalcSwitch UDF, it works.
- If B2 = "Please Calculate", then the content of my cell is recalculated. And since it is actually not a SUM function, but a computational heavy complex function, it takes a while it to finish calculation.

- if B2 = anything else. Than cell value does not change, and calculation time reduces to a fragment of a second.

Do you have any idea what property the IF function has that causes this behavior?
Can I mimic this within my UDF to avoid adding an extra IF to my function?

(p.s.: sorry mistakenly hit the post button before I finished writing the post, and seems like I can't edit my previous post. Admin, I would appreciate if you could edit the above text to my previous post, and delete this one, thanks)

 

[mikerickson]

Many array functions can be replaced by helper columns of non-array functions and sped up considerably that way.

The core problem remains that the approach that you are using requires Excel to calculate the function value before passing it as an argument to the UDF.
You could speed that up considerably by passing the range as a range and doing the calculation inside the UDF. I suspect that you have only a few formulas (repeatedly used in many cells, but the basicaly the same formula)

Perhaps something like this

Function ToggleSum(CalculationToggle As Boolean, RangeToSum As Range) As Variant

    If CalculationToggle Then

        ToggleSum = Evaluate("Sum(" & RangeToSum.Address(, , , True) & ")")

    Else

        ToggleSum = Application.Caller.Text

        If IsDate(ToggleSum) Then
            ToggleSum = DateValue(ToggleSum)
        ElseIf IsNumeric(ToggleSum) Then
            ToggleSum = Val(ToggleSum)
        End If

    End If
End Function

With a worksheet formula like =ToggleSum($E$1=1, A1:C1) which will calculate the sum of A1:C1 whenever E1=1, but the cell will remain unchanged when E1<>1

 

[bunni]

Thanks everyone for the responses! I've ended up using a very simple, but effective solution.

Public Function Origval()

Origval = Application.Caller.Text


End Function

=IF(B2="Please Calculate",SUM(D2:E2),Origval())

and again, the SUM(D2:E2) formula is just there for illustration purposes, the real formula here is more complex, computationally heavy function.
If I enter "Please Calculate" in B2, then the IF function returns the result of the SUM formula, otherwise it keeps the original value without recalculating the SUM function.
embedding my complex functions this way, I can switch calculation on / off for different parts of my worksheet from a dashboard, and save a lot computing power/calculation time.

 

[Yongle]

I like simple solutions
Feels like this technique could be applied to a few things ( need to think this through )
Using If to act as a "brake" until the 2nd condition is satisfied could have many potential uses
Thanks for feeding that back