I've got some code in my spreadsheet where I use global variables (declared at top of Module with Public keyword - this is same as using Global keyword, right?).
I reference these variables in some way during any Worksheet_SelectionChange event.
I've ran into the problem several times where if my code was interrupted because of some bug in my code, it would cause the global variables to become unset (or set to null, if that's a better way to say it). Why is this happening? How can an error unrelated to the variables which causes my code to stop running cause global variables to reset?
I've made some very simplistic code to demonstrate the behavior I'm talking about. In a blank workbook I put this in Module1:
Public a As Integer
Public Sub InitializeGlobalVariables()
a = 1
Public Sub TriggerError()
Dim b As Integer
b = "error"
In sheet1 I have:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = a
First run InitializeGlobalVariables(
) to set the global variable a to 1. Then just move the cursor around to populate some of the cells with 1. Then run TriggerError and make the error happen. Then try to move the cursor around again... the cells are now being filled with 0 instead of 1.
Can anyone explain this behavior to me?
Thanks in advance.