Topic: Global VBA variables getting reset @ AskWoody

Global VBA variables getting reset
Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Global VBA variables getting reset
- This topic has 25 replies, 5 voices, and was last updated 15 years, 3 months ago.
Author

Topic
New Reply

WSColinBurrows
AskWoody Lounger

January 6, 2010 at 11:06 am #465414

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

Viewing 7 reply threads
Author

Replies
- WSunkamunka
  AskWoody Lounger
  
  January 6, 2010 at 11:34 am #1195840
  
  Any chance of posting a stripped-down version of the file – with all confidential details altered|removed?
  
  Reply | Quote
- WSchrisgreaves
  AskWoody Lounger
  
  January 6, 2010 at 1:27 pm #1195856
  
  … 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
- WSColinBurrows
  AskWoody Lounger
  
  January 6, 2010 at 1:56 pm #1195861
  
  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
  
  WSchrisgreaves
  AskWoody Lounger
  
  January 6, 2010 at 3:22 pm #1195879
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 6, 2010 at 5:30 pm #1195905
  
  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
  
  WSchrisgreaves
  AskWoody Lounger
  
  January 6, 2010 at 5:56 pm #1195906
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 7, 2010 at 8:02 am #1196006
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 7, 2010 at 8:20 am #1196015
  
  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
  
  WSchrisgreaves
  AskWoody Lounger
  
  January 7, 2010 at 8:40 am #1196017
  
  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
  
  WSchrisgreaves
  AskWoody Lounger
  
  January 7, 2010 at 9:40 am #1196034
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 8, 2010 at 8:33 pm #1196873
  
  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
  
  WSchrisgreaves
  AskWoody Lounger
  
  January 9, 2010 at 4:00 am #1196917
  
  - 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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 9, 2010 at 7:39 am #1196951
  
  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
- WSColinBurrows
  AskWoody Lounger
  
  January 6, 2010 at 1:27 pm #1195857
  
  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
- WSpieterse
  AskWoody Lounger
  
  January 7, 2010 at 12:38 am #1195956
  
  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
- WSchrisgreaves
  AskWoody Lounger
  
  January 7, 2010 at 2:21 am #1195964
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 7, 2010 at 8:14 am #1196011
  
  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
- WSpieterse
  AskWoody Lounger
  
  January 7, 2010 at 11:48 am #1196106
  
  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
- WSColinBurrows
  AskWoody Lounger
  
  January 8, 2010 at 7:35 pm #1196859
  
  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
  
  WSpieterse
  AskWoody Lounger
  
  January 9, 2010 at 12:14 pm #1197000
  
  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
  
  WSColinBurrows
  AskWoody Lounger
  
  January 18, 2010 at 12:09 pm #1205203
  
  ###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
- WSpieterse
  AskWoody Lounger
  
  January 9, 2010 at 12:16 pm #1197004
  
  MZ Tools has a couple of tools to help you remove e.g. unused variables.
  
  Reply | Quote
- WSpieterse
  AskWoody Lounger
  
  January 10, 2010 at 12:15 pm #1197267
  
  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
- WSStuartR
  AskWoody Lounger
  
  January 10, 2010 at 1:24 pm #1197278
  
  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
- WSpieterse
  AskWoody Lounger
  
  January 19, 2010 at 3:39 am #1205322
  
  MZtools has that functionality
  
  Reply | Quote
Viewing 7 reply threads

Reply To: Global VBA variables getting reset
You can use BBCodes to format your content.
Your account can't use all available BBCodes, they will be stripped before saving.

Your information:
Name (required):

Mail (will not be published) (required):

Website:

Cancel

Plus Membership

Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.

AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.

Welcome to our unique respite from the madness.

It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.

Global VBA variables getting reset

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Key Links

Remembering Woody

Global VBA variables getting reset

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Login and Registration

Key Links

Remembering Woody