troubleshooting Question

Error in code causes global variables to unset?

Avatar of KenHadley
KenHadleyFlag for United States of America asked on
Microsoft Excel
7 Comments1 Solution339 ViewsLast Modified:
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:
Option Explicit

Public a As Integer

Public Sub InitializeGlobalVariables()
    a = 1
End Sub

Public Sub TriggerError()
    Dim b As Integer
    b = "error"
End Sub

In sheet1 I have:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target = a
End Sub

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.
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros