Cell values not refreshing for custom function

I have defined a function that calculates the sum of cells depending on their color. This function works perfectly but refuses to refresh, it doesn't refresh when I click calculate sheet, it doesn't ...

excel

Formulas and Functions

Macros and VBA

NikolinoDE
Mar 22, 2023
ClovisBelley
I want refresh calculation in this VBA code.
you can try adding the following line of code at the beginning of your function:
Application.Volatile
This will make sure that Excel recalculates the function every time there is a change in the worksheet. If this does not work, you can try adding a dummy argument to your function and changing its value every time you want to recalculate the function.
Here’s an example:
Application.Volatile Dim X As Double Dim Y As Long Y = CC.Interior.ColorIndex For Each I In RR If I.Interior.ColorIndex = Y Then X = X + I.Value End If Next I SumColoredCells = X End Function```

Hope I could help you with this information.

NikolinoDE
I know I don't know anything (Socrates)

NikolinoDE

Gold Contributor

Mar 22, 2023

ClovisBelley

I want refresh calculation in this VBA code.

you can try adding the following line of code at the beginning of your function:

Application.Volatile

This will make sure that Excel recalculates the function every time there is a change in the worksheet. If this does not work, you can try adding a dummy argument to your function and changing its value every time you want to recalculate the function.

Here’s an example:

   Application.Volatile
    Dim X As Double
    Dim Y As Long
    Y = CC.Interior.ColorIndex
    For Each I In RR
        If I.Interior.ColorIndex = Y Then
            X = X + I.Value
        End If
    Next I
    SumColoredCells = X
End Function```

Hope I could help you with this information.

NikolinoDE

I know I don't know anything (Socrates)

ClovisBelley

Copper Contributor

Mar 22, 2023

Thank you, this works great but seems to really slow things down, every time I change a value it takes 4 seconds for everything to start working again.

Forum Discussion

Cell values not refreshing for custom function

Resources