VBA Check or un check a Library reference and your global variables get emptied?
HelloI spent many hours trying to figure out why seemingly erratically my Global variable were being emptied.
I spent some more time preparing a Forum Post to ask for help … in the process of preparing the question, I found the cause and an initial workaround. :)
But as I had not found any help after a lot of internet searching, I thought I would briefly mention the issue out of passing interest and as it might help somebody else who is searching who has a similar problem in the future.
_.__
Background to the issue
So I have a large file with a lot of messy and complicated coding built up on and off over a year or so.
It is sometimes convenient to use a global variable declaration at the top of a code module so that I can use the value in it from different codes at different times.
A lot goes on when the file is open. ..What goes on is usually a bit different every day.
It gets passed on and opened and closed in different Excel versions and different computers..
( It is a pretty amazing and special file actually, .. corrupted with multiple ThisWorkbook code modules and other unique things like Worksheet code modules with no worksheets :) )
The current problem ( was)
From time to time a code failed as a global variable no longer seemed to have in it what it should have..
The causes
_ 1) Manually / Womanually uncheck or check any reference
If you manually or womanually check or uncheck any reference ( From VB Editor -- Tools -- References – scroll about and check or uncheck a small box https://imgur.com/hbsEg3Z" onclick="window.open(this.href);return false; ) , then all global variable in a file are emptied. It doesn’t matter where the global variable is or where the code is that you try to use the global variable.
So simple conclusion here: make any reference change manually or womanually and your Globals are buggered
_2) Programmatically uncheck or check any reference
The situation here is slightly more complicated.
As long as the code in which the reference has changed has not ended , then in that code or any called procedures , the global variable maintain their values
As soon as the code in which the reference is changed ends, then all global variables are emptied.
The workarounds.
Strangely it doesn’t help to refill your Globies after changing a reference check.- at least not in the code that does the reference change. ( They still work anyway in that procedure before it is closed )
One workaround is to do all your Globy filling in a procedure which you schedule to run just after any code which does any reference check changes.
As a “Belt and Braces” approach, I also call the Globy filling procedure, or fill a globy or two, in a few other codes, whether those codes need the globies or not. – Does no harm to refresh them when you have the time….. as the saying goes… “….top up your globy from time to time so its filled and ready for action ….”
_._
Demo Examples
I have uploaded 2 files, ( a drastically reduced version of my main file, and a fresh made file ) with a few demo codes for anyone is interested at looking at the problem:
The Files have some ThisWorkbook code modules, normal code modules and worksheet code modules, and a few others.. 4 Code modules are of relevance to the issue
In these 3 modules_....
_.. DiesesArbeitsmappe1 - the German version of This Workbook code module 1 ( DiesesArbeitsmappe in fresh uncorrupted file)
_.. Modul1 – normal code module
_.. Tabelle7 ( “Sheet1” ) – conventional worksheet code module for an existing worksheet.
_...I have simple codes like this
Code: Select all
' In Lis Workbook code module
Option Explicit
Public LisWkBkProWb As Workbook
Public LisWkBkGloby As Long
Sub FillLisWkBkGloby()
Set LisWkBkProWb = ThisWorkbook
Let LisWkBkGloby = 42
End Sub
' Run the code above, then the code below and all is well.
' Check or uncheck any reference and run code again below....
Sub HowLongsYourGloby()
MsgBox LisWkBkGloby & vbCrLf & LisWkBkProWb.Name
End Sub
If you then change any reference manually, and just run the second code then it will error as the Globies have apparently been emptied as an unwanted by-product of changing any reference
In a second normal code module, DoItProgramatically, are some more complicated codes:
Sub ScrewAboutWithReferencesInRunningCodeIsOK()
This checks and un checks a few references programmatically. It demos that in this code and any called procedure there is no problem with using the Globies, ( which are filled at the start of the code ).
Sub TakeOffAReferenceScrewUpYourGlobies()
This demos the problem..
It initially fills the global variables. It then un checks a reference ( for Office ).
Just before this code, Sub TakeOffAReferenceScrewUpYourGlobies() , finishes, it schedules the code, Sub WhatsInMyGlobies( ), to start after a couple of seconds.
The code Sub WhatsInMyGlobies( ) errors because the global variables have been emptied as an unwanted by product of the un checking of the Office reference
The next code should then be run a few seconds after the last code
Sub WorkaroundFillYaGlobsAfterARefChange
This code does something similar to the last. This time it initially fills the globies again and re checks the Office reference.
It also schedules the code to Sub WhatsInMyGlobies( ) to start a few seconds after this code , Sub WorkaroundFillYaGlobsAfterARefChange , finishes. But it schedules a code Sub FillMeGlobsUpMate() to start a few seconds before the code Sub WhatsInMyGlobies( ) is scheduled to start. As a result of this, code Sub WhatsInMyGlobies( )does not error.
Finally….
Sub JustToCheck()
This code schedules 4 codes to start one after the other. These code do:-
1 Fills the Globies
2 Un checks a reference ( for Office )
3 tries to use the globies, and errors
( 4 – not part of the main demo, but just for tidiness re checks the reference to Office, as I suspect it is sensible to do that. )
Two uploaded demo Files:
_ A stripped down version of the File that first had all the problems : https://app.box.com/s/hqr8eytl0576o1kwvhv2g1ajpfetzle4" onclick="window.open(this.href);return false;
_ A fresh made file.: https://app.box.com/s/5ypovakgtaglqe8ztkh6q8az2tmsiurb" onclick="window.open(this.href);return false; ( Also attached )
Note: If you are using a version of Excel other than 2003, 2007 or 2010, then you will need to tweak for the correct Library GUIDs ( If you do that you might like to pass on the GUIDs to me – Here is a simple code to get those:
https://tinyurl.com/yb7ay2r9" onclick="window.open(this.href);return false;
I checked all in XL 2003, 2007 and 2010, so did my wife. Results r generally the same
Alan
( see you next Winter maybe :) )
