Global VBA variables getting reset

Topic

New Reply

Am wondering if anybody has any pointers regarding the following. I have an Excel application that WAS working fine. When it’s first opened, the user is authenticated (via an external file) and I store various permission type attributes in global VBA variables. The external file is then closed.
I had to make some enhancements and something that I did has caused the global variables to get reset when one particular command button is pressed. The odd thing is that if I add the line “Debug.Print gbAuthorized” immediately before the final Exit Sub, then I find that it IS still set to True. However, once control is returned to the user and I check the variables, all the values have been lost (so gbAuthorized is set False).
Any help much appreciated.

Reply | Quote

Replies

Any chance of posting a stripped-down version of the file – with all confidential details altered|removed?

Reply | Quote

… has caused the global variables to get reset … Any help much appreciated.

So file this under “Any“:
I’d recommend you get rid of Global Variables, always in every program, any language.
This may draw some comments, but I will maintain my stand that good programming practices recommend passing arguments to functions and getting results.
And yes, I do have one or two Global Variables in one or two applications, but I’m not proud of it.

In terms of VBA, the user interface is a macro (“the user invokes a macro”) which by definition draws on characteristics pre-established by the user – the ActiveSheet, the Selection etc.
With the possible exception of session/environment values, everything else must -by definition – be local to the code inside the macro, which passes data, by arguments, to slave functions, and so on down the line.

To the question about functions with millions of arguments, I recommend using VBA’s TYPE structures, so (as an example) all the variable data concerning the user environment can be passed as a single argument in a structure (“typEnvrionment”)

In your particular case, it seems axiomatic that if you replace all the global variables with data passed as arguments, you cannot then have a problem with Global Variables, and I’m not trying to be facetious; as you change from Global to local variables, you will uncover design and coding flaws along the way, and solve many more problems as you go.

I really DO hope this helps!

Reply | Quote

Chris,

Thanks. I understand and agree with you about (at the very least) minimising the use of global variables. I really only use them as a last resort.

This is slightly different, though. I need to store the user’s permissions somewhere – without leaving open the external file where I obtained them from. The “gatekeeper” type variable is gbAuthorized – set True if the user is authorized to use the application. And if it’s set True, then the more specific permission variables are also populated (e.g., SQL filter clauses).
What I have now – and what used to work fine – is that whenever the user presses any command button within the application, the first line of code verifies that gbAuthorized is set to True.
One alternative would be to store the permission info in a [very hidden] worksheet and then access it there. However, an enterprising user could easily unhide all sheets in the workbook and then modify their permissions. Hence the idea of using global variables.

Reply | Quote

I need to store the user’s permissions somewhere – without leaving open the external file where I obtained them from.

OK, so you really have two problems:
Problem 1: 11,000 lines of code poorly written (we agree on that since we agree that global variables Baaaaad), and chances are high that there are other bad programming practices in there;
Problem 2: Your immediate problem of resolving the issue before 5pm today.

Problem 2: I would recommend against the use of anything (Hidden sheets) that an enterprising user can get to. In Word I use Document.variables which are reasonably secure. Excel 2000 doesn’t have them. Excel2003 may have them. Excel 2000 Custom Document Properties can be set by the user but only interrogated by the programmer, right? What Built-In properties might you hijack to use as a toggle for your gblAuth switch? Failing that …
Problem 2a: Load a GUI form, but don’t show it, and set a variable value in that. Doesn’t even have to be a form the user ever sees (frmMyLittleSecret.cmdOK.Tag=”Y”)
Problem 2b: Make use of an registry or INI file value somewhere, again using the MyLittleSecret convention (a.k.a. “3rejwqvmyc”)

Problem 1:I have often been faced with an orphaned program of 000s lines; I tell the client up front that large parts will have to be re-written (before I can understand what the code does), and I believe that it is faster and cheaper to wade through the code, replacing common chunks with calls to utility procedures, and get it done once than spend the rest of my life applying band-aid patches.
From your response, I suspect you will be inclined to agree with me, but it can be tough going to the client/boss to explain that what will turn out to be a 5-minute coding (Codeine?!) patch-and-test will take four weeks to get set up.

The bottom line is that you are about to spend 4 weeks trying to track down this bug, and until you do the application lies moribund anyway.
If it helps, tell your boss that you have been chatting with a grizzled old veteran who programmed the 4th DEC-6 machine in the world ….

Reply | Quote

I was liking your 2a – I hadn’t thought of that – but then I realised that whatever resetting is taking place also removes any userforms from memory. So unless I’m missing something I don’t think that will solve it.
Using the registry I had considered (I already store user preferences there), but that would still technically allow the enterprising (and daring!) user to edit their registry and change their settings. Also, three of the global variables are variant arrays (formerly worksheet ranges), so that would complicate it.

The application still works, btw. It’s coded throughout so that whenever I find that gbAuthorized is set False, I reopen the external file I mentioned and populate the global variables anew. The issue is therefore really one of speed/iritation. The app is slow because it has to repeatedly authenticate the user.

The 11,000 number may be misleading. That is spread across 15 modules and 1 class module. It also includes numerous comment lines and white space. There may only be about 7,000 lines of actual code. Still very large for a single Excel workbook, though…

Reply | Quote

So unless I’m missing something I don’t think that will solve it.

Have you tried it? (evil grin!)

I didn’t want to send you down this path, but you seem to be a happy-go-lucky glutton for punishment so …

Problem 2c: Many years ago I wrote an authentication scheme in Word, should be adaptable to Excel.
I wanted to store a number (I used an 8-digit date) in the document where no user would think to look, but where it would be changed if the user played with the text.
Imagine a Word document, and consider the first 8 space characters (asc-032).
Suppose the basic font is 10-pt.
And you want to store the 14th April 2008 (“20080414”) in the document.

Add the eight digits to the base font size to yield 12, 10, 10, 18, 10, 14, 11, 14 (if I got that right) and then format those first 8 space characters to be those font sizes.
To read out the number, subtract the base font size from the first 8 space characters font size to yield 20080414.

There was more to it than that, but you will get the idea.

Reply | Quote

Have you tried it? (evil grin!)

I didn’t want to send you down this path, but you seem to be a happy-go-lucky glutton for punishment so …

I did try it – and any loaded userforms are wiped out. I didn’t say, but I checked out your other proposal (2 without a suffix). Again it looked promising – there IS a Workbook.CustomDocumentProperties() collection – but then I realised that any data stored there could be accessed (and changed) through the front.

Thanks for the “private key decoder type” suggestion. For now at least, I’m not quite that much of a glutton for punishment.

I *am* making progress though. See my response to Hans in a moment.

Reply | Quote

I *am* making progress though. See my response to Hans in a moment.

Correction: I said Hans but meant Jan Karel. I never was very good with names…

Reply | Quote

I said Hans but meant Jan Karel. I never was very good with names…

Fer Sure! Those Global Names will cause you grief every time (hah hah hah!)

… any code that impacts the control causes the project to reset …

I suspect that this might by classified as “a change to the source code”; you probably know already that changing a DIM statement can sometimes, and changing a REDIM statement will always bring up “This will reset your project?”, but of course an ActiveX being a ‘3rd-party” device won’t conform to Microsoft standards, even.

… deleting a sheet that contains an ActiveX control is enough to trigger the reset.

And I suspect that deleting a sheet comes pretty close to deleting large gobs of code in terms of ability to reset the interpreter. This would be true if a sheet contained code, but I don’t see why it should reset unless the module contained code currently being executed. I have many projects that add and then delete sheets through each loop of the application with no deleterious effects.

Reply | Quote

The 11,000 number may be misleading. That is spread across 15 modules …

So here I would consider a process of extracting common or utility code and implementing a utility library, “Utils.XLA” or similar.
Once the small/trivial/simple library is set up (and referenced from your application), everything is in place each time you stumble across stable utility code.
Cut it out of the application logic, paste it into the library, and day-by-day chip away at the 11,000 lines/15 modules until the application houses just the logic and application-specific code, and the library is a repository of useful code.
You possibly know this already, but I have found the chipping process to be of great value for clients who can’t/won’t tolerate a cleanup exercise, but are happy to bury the cost in incremental edits over a year or more. I can point you to a typical library if you want.

Reply | Quote

So here I would consider a process of extracting common or utility code and implementing a utility library, “Utils.XLA” or similar…

Chris, thanks for this. Your approach certainly sounds like the way to go – in the applicable circumstances. However…

In this particular case I am the sole author/creator/developer of the application – so I am very familiar with all the code. Out of curiosity I looked and there are actually a total of 11,500 lines of code – 7500 in the 15 modules I mentioned, and another 4000 behind 14 userforms. The equivalent of the utility library you mention are modules such as MStandardCode and MCommonCode. Much of the application uses techniques/code from the very excellent book “Professional Excel Development” by Bullen, Bovey and Green. I would guess you’re familiar with it, but if not you should check it out.

The reason for the large number of lines of code is that it’s a complete Financial Reporting application. It’s used by some 250 people worldwide – including about 100 in Bangalore(!). At one time I wondered about a separate “Utils.xla” file, but that would complicate the distribution. Particularly given the high turnover in Bangalore. Now I just have to make a single workbook available to everybody. As it is I had to write my own calendar control. I couldn’t rely on mscal.ocx being available on each user’s machine.

Anyway, thanks again for all your responses.

Reply | Quote

- so I am very familiar with all the code.

Do you use a Procedure Stripper to remove deadwood a.k.a. unreferenced procedures/identifiers?

Reply | Quote

Do you use a Procedure Stripper to remove deadwood a.k.a. unreferenced procedures/identifiers?

No. I’m almost certain I don’t have any unreferenced procedures (with the possible exception of MStandardCode). What do you mean by “identifiers”, though? I would be interested if there’s a utility that identifies unreferenced variables in a project.

In case it’s ever of use to you (or any other lounger reading this), I’ve attached the DIY calendar control that I mentioned. I should say that I didn’t develop it from scratch – it’s very much based on something I got from another lounger (but I forget who).

Reply | Quote

Thanks, but not really practical I’m afraid. The workbook has over 11,000 lines of code(!).

I’ve tried commenting out various sections of code that I added, but to no avail. The fact that the variables are still populated immediately before returning control to the user puzzles me – and makes it hard to troubleshoot. I was hoping to isolate the offending code (I had actually interspersed Debug.Print’s throughout). Presumably something is setting a kind of global switch that effectively resets the project once the code stops running.

I realise it’s a long shot, but I was hoping somebody might have seen similar behaviour and/or be able to point me in the right direction

Reply | Quote

There is either something wrong with your VBA project, or there is a coding problem.

Global variables (and I slightly disagree with chris here: used with prudence they aren’t necessarily bad) are reset in these four cases ONLY:
– When the user presses End after a runtime error
– When you click the stop button on the debugging toolbar
– When you’re in step mode and you close the VBE and click OK to stop debugging
– When the code passes an End statement.

If none of these have happened AND YOU’RE POSITIVE ABOUT IT, then you may have corrupted code.
Code editing debris can be removed by running the Code cleaner by Rob Bovey http://www.appspro.com.

Reply | Quote

Global variables … are reset in these four cases ONLY:

I was struggling to come up with a statement along those lines, that if one is worried about a value in a loaded form being reset, then one ought also to be worried about everything else; that is, any form of globally available value disappears once the program is stopped running.

Code editing debris can be removed by running the Code cleaner by Rob Bovey

But isn’t that part of the morning’s reboot sequence?

Reply | Quote

Global variables (and I slightly disagree with chris here: used with prudence they aren’t necessarily bad) are reset in these four cases ONLY:

I’ve found that there’s a fifth case. See attached workbook. As part of my enhancement I had added an ActiveX control. It seems that any code that impacts the control causes the project to reset once the code finishes running. Even deleting a sheet that contains an ActiveX control is enough to trigger the reset. (The firt thing I thought of was the ActiveX control. I had commented out any code that referenced it, but the problem still occurred. What I hadn’t considered was the deletion of a temporary sheet containing an ActiveX control).

Let me know if you disagree…

Reply | Quote

Yet another good reason to shun ActiveX controls. I try to stay away from those as much as I can. Obviously for good reason.

Reply | Quote

Yet another good reason to shun ActiveX controls. I try to stay away from those as much as I can. Obviously for good reason.

Right. FYI, I tried using a Form Control (rather than ActiveX), and the global variables still get reset. Normal shapes (lines, callouts, etc.) appear to be fine.

For my situation, instead of the control I used Data Validation for the drop down and then a Worksheet_Change event to trigger the requisite code.

Thanks for your help.
(And, on behalf of many, thanks for Name Manager!)

Reply | Quote

Right. FYI, I tried using a Form Control (rather than ActiveX), and the global variables still get reset. Normal shapes (lines, callouts, etc.) appear to be fine.
(And, on behalf of many, thanks for Name Manager!)

Thanks for pointing me at that, indeed an unwanted situation. Good to know.

###EDIT 2009 01 10###
I just tried again to have code remove a forms control, all the global variables retained their value.
### END EDIT###
And you’re welcome…

Reply | Quote

###EDIT 2009 01 10###
I just tried again to have code remove a forms control, all the global variables retained their value.
### END EDIT###

Hmmm. Not sure what I did before, but I agree that forms controls do NOT present the same problem.

And thanks for pointing me towards MZ Tools. I’d never even heard of it, but it IS very useful.

Reply | Quote

MZ Tools has a couple of tools to help you remove e.g. unused variables.

Reply | Quote

Just an additional advice. If I use global variables, I usually include a test variable I set to true after they have been initialised.
I then test at the entrypoint routines whether my test global variable is still true, so I know they have not lost scope. If they do, I simply rerun the init routine.

Reply | Quote

Just an additional advice. If I use global variables, I usually include a test variable I set to true after they have been initialised.
I then test at the entrypoint routines whether my test global variable is still true, so I know they have not lost scope. If they do, I simply rerun the init routine.

That is a VERY neat trick, which I shall be using.

Reply | Quote

MZtools has that functionality

Reply | Quote