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.
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 🙂