Forum Discussion
VBA module code not working after save or cell edit - strange behavior
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.
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.
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.
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.
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 🙂
