Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Any chance of posting a stripped-down version of the file - with all confidential details altered|removed?
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ColinBurrows View Post
    ... 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!

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    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

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ColinBurrows View Post
    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 ....

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    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...

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ColinBurrows View Post
    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.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 www.appspro.com.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    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.

    Quote Originally Posted by pieterse View Post
    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?

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by chrisgreaves View Post
    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.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by pieterse View Post
    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...[attachment=87554:Test.xls]
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by ColinBurrows View Post
    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...

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ColinBurrows View Post
    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.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ColinBurrows View Post
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •