Link to home
Start Free TrialLog in
Avatar of KenHadley
KenHadleyFlag for United States of America

asked on

Error in code causes global variables to unset?

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.
Avatar of Norie
Norie

Why not avoid the error?

Or even use On Error Resume Next?
Avatar of KenHadley

ASKER

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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.