Automatic calculation of Excel VBA UDF related to cell properties

Question

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.

I guess the solution is you need to check every cell in range for color change. And on change call your UDF — Paresh J, Jan 20 '15 at 15:57
The short answer (you have several workarounds below) is no. Formatting should be used to *reflect* data, not as data. ;) — Rory, Jan 20 '15 at 17:08
That's what I said. :-) I suggested to have the user type a value into the cell changing the color by conditional formatting. And then it would be much easier for calculations. But no, the people are used to using the colors. Change Management :-) — SimonMoon, Jan 20 '15 at 19:17

score 1 · Accepted Answer · edited Jan 20 '15 at 16:18

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

score 0 · Answer 2 · answered Jan 20 '15 at 16:22

If your function executes very quickly (say, under 50 msec) then you can call it from the event handler Private Sub Worksheet_SelectionChange(ByVal Target As Range); users will not notice it happens. One other problem with this solution would be when the user changes format and leaves the worksheet straight away. Then, if you care for recalc to happen, you can, perhaps, use the Private Sub Worksheet_Deactivate() to run it. There are other possibilities, none bullet-proof or remotely simple.

Automatic calculation of Excel VBA UDF related to cell properties

2 Answers2