VBA module code not working after save or cell edit - strange behavior

[jpowell1080]

I'm very new to VBA so excuse my ignorance and know that any help is much appreciated.

I am trying to create a function (in insert>module) to sum based on cell color and then separately (in the sheet1 code) use "Sheets(1).Calculate" to force recalculation of the function on selection change event. What I thought was going to be a simple tutorial video of about 10 minutes has now turned into 6 hours of hell. The code seems to work at first, but after saving the sheet or making a change to any cell the code stops working and the cell using SumColor does not update unless I double click the cell and press enter.

Video I was following was "How to SUM Cells by Color in Excel (Get it to update automatically)" by Leila Gharani on YouTube.

First off, here's what I have:


(Insert>Module):

Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range) As Double

    Dim cell As Range
    Dim myColor As Long
    myColor = MatchColor.Cells(1, 1).Interior.Color
    For Each cell In sumRange
        If cell.Interior.Color = myColor Then
            SumColor = SumColor + cell.Value
        End If
    Next cell
    MsgBox "module"
End Function

and in (Sheet 1>View Code):

Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets(1).Calculate
Msgbox "sheet"
End Sub

Is there anything that would cause this to break upon saving the file (as .xlsm) or upon entering/deleting data from ANY cell in the sheet? As you can see in the code, i put some msgbox's in to try and diagnose where it breaks. The msgbox for "sheet" always pops up on selection change and the "module" message does initially, but after saving/modifying cells the "module" msgbox fails to appear. At this point, i try going to Formulas Tab>Calculation>calculate sheet and this does not force recalculation of the cell calling the SumColor function. Should "calculate sheet" do this or is there another way to do it for functions?

Once my sheet is in this state the only way to make it work again is to go back to the module or sheet code windows and make literally any edit. Add a space, or press enter once or even press space then backspace and its all good until I hit save, etc...

I just cannot wrap my head around this. Please help.

[#2]

@jpowell1080 

Not so long ago, I created a small User Defined Function achieving a similar result.

Function SumColor(rngCol As Range)

Application.Volatile

Dim C As Long
Dim Total As Variant

C = Application.ThisCell.Interior.Color

Total = 0

For Each Cell In rngCol

    If Cell.Interior.Color = C Then
        Total = Total + Cell.Value
    End If
Next
       
SumColor = Total

End Function

Had not seen the one you refer to , but it's not rocket science. Just colour the cell with the formula in it to the colour you want to sum and it will work every time something changes in your sheet. 

[#3]

@Riny_van_Eekelen 

Thanks for this! I'm not currently at the computer having the problem so I will try this out in a few hours, but this makes me wonder (again, as someone new to VBA, and who really hasn't done much coding in the several years) what makes your function run every time something in the sheet changes? Is this default behavior for a function? Do I still need the "ActiveSheet.Calculate" or similar in the sheet code to force this? If not then why would your function not require it but the one I posted would? 

Again, sorry for my lack of understanding but one has to start somewhere.

[#4 · Accepted solution]

@jpowell1080 No rush! It's almost diner time in my part of the world and after that I'm away. The "Application.Volatile" makes the function calculate every time data gets entered and "Return/Enter" pressed. It may slow your system down if you have massive amounts of these formula in your sheet, but I haven't really noticed a problem with it.

[#5]

@Riny_van_Eekelen Thanks for the help. It turns out Application.Volatile was the missing piece of my puzzle. I copy/pasted your code to give it a try but couldn't get a good output in the cell. So I stuck Application.Volatile into the code I had and viola`.

Hope your dinner was good :)

[#6]

@jpowell1080 Fair enough. Attached a working example, just to demonstrate how my code works.