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