Properly call a cell formular from a macro in excel vba

Question

im trying to work out a little scheduler in excel to manage my life a little better. I use different cell colors for each event (yellow for work, red for university, etc.). I already have a macro CountColor which counts the occurence of a color in a certain range which works fine (i pretty much copy pasted it from an online solution). I now use the macro to calculate the used time into cells like this:

the cells contain =ColorCount(H5;B2:F15) whereas the interior color of the first argument determines the color to count and the seconds parameter is the range to count the color in. This macro works fine. The last number is just the sum of the above three.

I now however face the problem, that changing the interior color of a cell does not trigger the recalculation of formulars. I created a simple button (not the ActiveX one) and assigned a macro to it:

Public Sub CalcButton_onclick()
    Worksheets(1).Range("I13:I16").Calculate
End Sub

but when i click the button (i also tried to recalculate the whole sheet by using Worksheets(1).Calculate) nothing happens. Only when i, for example, change the value int the cell my times get recalculated. My button's macro is definitely executed i tested that by adding Worksheets(1).Cells(20, 20).Value = "Test" after the Calculate call and it changed the value of the given cell properly.

For the purpose of completion, i also add the code of the CountColor macro:

'counts the occurence of the interior color of rColor in rRange
Public Function ColorCount(ByRef rColor As Range, ByRef rRange As Range) As Integer
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult As Integer
    vResult = 0
    lCol = rColor.Interior.ColorIndex
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
    Next rCell
    ColorCount = vResult
End Function

Im not really sure what the problem is but i believe i may have misinterpreted the Calculate method. I only created the 2 macros above. I appriciate any help!

By the way something meta: is this a proper use of a picture in a question? I could not think of a better way to show what i want my output to look like.

One suggested approach is here https://stackoverflow.com/questions/37619240/recalculate-on-changing-cell-background — SJR, May 02 '19 at 17:09
Try `Worksheets(1).Range("I13:I16").CalculateFull` https://stackoverflow.com/questions/37459903/what-is-the-difference-between-application-calculate-and-application-calculatefu — Tim Williams, May 02 '19 at 17:09
@SJR solution is best, you're catching an event trigger on the sheet. I like using `Private Sub Worksheet_Change(ByVal Target As Range)` personally as it'll update even without selections. https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change — Riley Carney, May 02 '19 at 17:12
@TimWilliams i tried that but it gave me the error that the object does not support the property or method. — Yastanub, May 02 '19 at 17:12
@SJR hey thanks for the help but the problem is, that `CountColor` will not be axecuted by calling `Calculate` on it. So even if this catches my event, my macro is not used — Yastanub, May 02 '19 at 17:20
@RileyCarney i now added `Application.Volatile` to my function but it still is not called — Yastanub, May 02 '19 at 17:27
You'll need to add `application.volatile` at the top of your function. — SJR, May 02 '19 at 17:27
You have to select another cell after the format change to trigger the event code. I'm surprised there isn't an easier way. — SJR, May 02 '19 at 17:28
The moral of the story is perhaps not to rely on colours, or use the underlying criteria for the colours if they exist. — SJR, May 02 '19 at 17:36
@SJR haha maybe but it is the most convenient way to display a schedule. The thing is, it is not really my goal to automate the recalculatio because the button is well fullfilling this purpose. I only wonder why, even if i add `Application.Volatile`, it does not Calculate the cell when i call `Calculate` on the range. And yes i triple checked if the range is correct. — Yastanub, May 02 '19 at 17:48
It's not actually a good idea to use colour as data. Have you placed the event code in the correct sheet module? — SJR, May 02 '19 at 17:55
Sorry it's `Application.CalculateFull` I guess it's not available on Range. Or you could try `Worksheets(1).Range("I13:I16").Formula = Worksheets(1).Range("I13:I16").Formula` — Tim Williams, May 02 '19 at 17:56
@sjr my way you don't need to select a cell, it detects when the sheet itself changes, that's why I'd recommend over selectionchange like you had here. — Riley Carney, May 02 '19 at 20:03
@SJR i right-clicked `ThisWorksheet under` "Microsoft Excel Objects" and selected `Show Code` and then inserted it in there. — Yastanub, May 03 '19 at 13:02
@RileyCarney - i may be missing something but the cell contents would have to be changed and am not sure that is necessarily the case. — SJR, May 03 '19 at 13:04
@Yastanub - not sure that's right. Delete existing code, right-click the relevant sheet tab, View Code, and paste the code there. — SJR, May 03 '19 at 13:07
@SJR i tried and inserted the code under the sheet tab where the `Me.Range` actually makes sense :) Anyways it is not working, I guess a color change is just not registered as a sheet change. I just wanna try to get this button to work and do not understand why even with `Application.Volatile` it is not calculated when calling `Worksheets(1).Range("I13:I16").Calculate`. I guess it is not worth getting it to work anyways its just a little "feature" i wanted to implement since the idea came to me and i thought it would be cool. I can see my schedule even without hour calculation :) — Yastanub, May 03 '19 at 13:21
@SJR found the problem myself and added an answer. Apparently it caused issues that some of the cells were "without filling" instead of explicitely filled white. — Yastanub, May 03 '19 at 13:43

score 3 · Answer 1 · answered May 02 '19 at 17:10

3

Making your colorcount UDF volatile would help (add Application.Volatile) but as you have discovered changing the color or formatting of a cell does not trigger a recalculation so even a volatile UDF will not recalc just on a color change.

If you make your UDF volatile then Range.Calculate should trigger a recalc in Automatic calc mode.

answered May 02 '19 at 17:10

Charles Williams

23,121
5
38
38

Where should i add `Application.Volatile`? – Yastanub May 02 '19 at 17:15
Okay i figured i have to add it to the beginning of the function but it still does not work. – Yastanub May 02 '19 at 17:23

score 1 · Accepted Answer · answered May 03 '19 at 13:42

I found the problem myself.

At first i want to thank everyone for their hints and tips. I would propably have needed them after fixing my initial problem and so i had them fixed in advance :)

My problem actually was apparently, that i wanted to declare my spare time as white color. But actually i had several cells which had "no fill color" after coloring every free cell explicitely white it now works with the button. The solution with Worksheet_Change() method in the sheet code did not work unfortunately because a color change is not evaluated as a change in the sheet. Worksheet_SelectionChange() however did the trick with updating when you click on another cell so i do not need the button anymore.

My bad, totally thought that `Worksheet_Change()` would work with color change. Thought I knew most everything to know about VBA in Excel but I guess you learn something new every day! — Riley Carney, May 06 '19 at 19:48

Properly call a cell formular from a macro in excel vba

2 Answers2