Function (using VBA macro) Runs Too Frequently

[BenW71]

I have a function in VBA that calculates a field based on other fields in a spreadsheet. It is not a straightforward computation and has to use a range in another workbook. Basically it's like =f_CalcPct (A1, D1, B1, G1, F1, H1, J1).


What i'm seeing is that this brings the spreadsheet to a crawl (recalculating with 12 threads, freezes, etc) VERY often, and it seems to recalculate the ENTIRE sheet (1500 rows, so not huge, but runs SLOW) more than needed based on the actual cells being changed. It even recalculates the entire sheet if you change values that aren't associated with any of the fields or the xlookup ranges... i have a debug.print statement in there and some times it runs the functions IF I RESIZE A COLUMN. Another example, if i clear the entire column of calculations, then put it in A1, it will run for A1. If i then put that function into A2, it runs on A1 and A2,

I don't want to change it to remove the function and have a button recalculate the whole sheet, because that would mean any changes to any columns (ie add/remove) in the sheet would need changes to the code. I use that function in multiple workbooks and the columns are different locations........

I also considered putting a checkbox on the spreadsheet and having the first line of the function be ("if not checked then exit function") but this ends up of course blanking out the value (can you send the current value prior to the change to the function)? For example, if f_CalcPct was in cell Z1, could i send the value currently in Z1 as an additional (optional?) argument? I was going to send the Address for the cell using the function and then in the function set the value to the current value, but that gives a circular error)...

I also don't want to turn off automatic recalculation. I only want it to stop running this one column out of control.

So my question is how can i have an equation in a cell on a worksheet and NOT have it recalculate unless i want it to...

 

[Cubist]

First I would see if the code is optimal first. Can you post the code?

 

[BenW71]

Well i can see it running when none of the arguments to that function change via:

Public Function f_CapPct(strPortfolio As String, strPnL As String, strBU As String, strProjectType As String, strAccountingType As String, Optional theCellRef As String, Optional strDebugUse As String = "") As Double
On Error GoTo ErrorHandler
Dim R As Range, WS As Worksheet
Dim strTableAccountingType As String, strTablePortfolio As String, strTablePnL As String, strTableBU As String, strTableProjectType As String
Dim theCellR As Range, TempR As Range
   
    f_CapPct = 1
   
    Set theCellR = ActiveSheet.Range(theCellRef)
    If strDebugUse = "" Then
        f_CapPct = theCellR.Value
        Debug.Print "EXIT FUNCTION on " & theCellRef & " with " & f_CapPct
        Exit Function
    End If

This is just a test to see when it's running and i remove that when i want it calculating for real, but shows how i know when it's running...

There are many times i see it run when i didn't change any of the arguments listed in the function or anything that those arguments refer to.

What i do do get around it is i copy and paste the values into the column (instead of the calculation) and then just move the formula back when i need to recalculate it...

 

[Cubist]

Is this the entire code? You can add a timer to see how long it's running.

Public Function f_CapPct(strPortfolio As String, strPnL As String, strBU As String, strProjectType As String, strAccountingType As String, Optional theCellRef As String, Optional strDebugUse As String = "") As Double

Dim t as double: t = Timer

'your code here....

Debug.Print Timer - t

 

[Alex Blakenburg]

Are any of the parameters of the function volatile ? ie Now(), Offset, Indirect etc
Is the function being used in a conditional formatting formula ?
Do you have circular reference iterations enabled ?