Search code examples
vbaexcelexcel-udf

Automatic calculation of Excel VBA UDF related to cell properties


I have written an UDF to count cells of certain color and with certain LineStyles, I'm posting the entire function:

Function CountTime(rData As Range, cellRefColor As Range) As Variant    

    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Variant

    Application.Volatile

    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
        If cellCurrent.Borders(xlDiagonalUp).LineStyle <> xlNone Then
            cntRes = cntRes + 0.5
        End If
    Next cellCurrent

    CountTime = cntRes
End Function

Now, the problem I have is that the formula does not automatically calculate when one of the cells in rData has it's color or line properties changed. I have added Application.Volatile, and I also tried to trigger the calculation by a Worksheet_Change sub, however that does not work as Excel does not seem to consider changing color a change to the cell/worksheet.

Is there any way to make the cell calculate and update automatically when a user changes the color or line properties of a cell in rData?

EDIT -- SOLVED Thanks very much ignotus, the ChangeSelection workaround is good enough for my purposes. Didn't think of that. And the background info is also handy, thanks a lot.


Solution

  • I don't think it is possible, but a quite fair solution would be to recalculate when you leave a cell or change it:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Me.Calculate
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Me.Calculate
    End Sub
    

    Or as an alternative, place a button on the sheet that calls Me.Calculate

    FYI: What is Me? Source

    Me refers to the parent object from which the code is "sitting" in. If you are writing in a Sheet module, the Me will refer to that specific sheet.

    Using Me is handy because we don't have to worry about the sheet name changing, and it also makes it a little easy for future code readers as they don't have to remember that "Main UserForm" is the UserForm we are currently working on. You can apply the same methods to the Me that you would be able to apply to the object if you gave the full name.

    Within the Sheet1 module, the following lines are identical in purpose:

    Worksheets("Sheet1").Range("A1").Select
    Me.Range("A1").Select