We help IT Professionals succeed at work.

Error in code causes global variables to unset?

KenHadley asked
Last Modified: 2012-02-10
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

Open in new window

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

Open in new window

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.
Watch Question

NorieAnalyst Assistant

Why not avoid the error?

Or even use On Error Resume Next?


The error will be avoided, of course, when the project is finished. During the coding process of this project errors will inevitable occur, though, so I ran into this problem many times.

Since I started having this error I've added a macro I call at the beginning of each Sub to set the global variables should they not be set, so I've at least stopped the error from happening for me.

That doesn't mean I'm not curious to know how this error could've even happened. This question is not a problem, per say, just a curiosity why I was having the error in the first place. I believe simply preventing an error from happening in whatever way doesn't really help much if you never understood why the error was happening in the first place.
Top Expert 2011

This does happen when you're in the development stages of a project - I have taken the same approach as you and used some sort of routine to check and reinstate global variables.  It's not a problem once you have all the errors sorted and move into a production stage. It's good practice to do this anyway, as even in the best regulated code, unexpected errors can occur.

It's most noticeable when you have say a global variable holding a WithEvents Application object to handle application level events - these quite often stop firing when unhandled errors occur.
NorieAnalyst Assistant

I understand what you mean, it would be an idea to find out why this happens.

I've actually looked into this before and never found a definitve answer.

In fact most people kind of encouraged not using global variables in the first place and this sort of behaviour is one of the reasons they give.
Top Expert 2011

I always assumed that following some sorts of errors the whole VBA run time environment restarts itself and so global variables get reset to default values.
Most Valuable Expert 2012
Top Expert 2012
This one is on us!
(Get your first solution completely free - no credit card required)


Thank you all for your great insights!

I think I will continue on developing and assuming my "global" variables have been lost when calling any procedure.

Thank you dlmille for your good answer (and link) and I'm not surprised to find yet another great work by Chip Pearson as an alternate solution to a seemingly common problem.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.