Forum Discussion

ClovisBelley's avatar
ClovisBelley
Copper Contributor
Mar 22, 2023

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

 

 

  • 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's avatar
    NikolinoDE
    Gold Contributor

    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's avatar
      ClovisBelley
      Copper Contributor
      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.

Resources