Forum Discussion
ClovisBelley
Mar 22, 2023Copper Contributor
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 refresh when I calculate cell. The only way is to select it, edit the formula then click enter. I'd like it to refresh automatically, here is the code for the function.
Function SumColoredCells(CC As Range, RR As Range) As Double
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
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.
I know I don't know anything (Socrates)
- NikolinoDEGold Contributor
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.
I know I don't know anything (Socrates)
- ClovisBelleyCopper ContributorThank 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.