• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA variables not recognized / not synchronized?

fred3

fred3

Member
I've been using a particular Excel Workbook for some time. I've had to update the VBA code within it to catch up with years of Excel changes.
Now, I'm seeing things like this:
In a Module, I declare variables outside of any procedure - so they should be good throughout the Module, right?
e.g. Dim vbaStartDate As Date at the beginning of the Module in a Module Declaration section.
Then, I assign values to vbaStartDate in a Subroutine using a Function.
I set Watches on vbaStartDate in that Function and in the Subroutine and from the Dim declaration all in the same Module. As I step through the code, they don't match.
This is MS365 desktop Excel. Everything is up to date.
I must be missing something basic here.
Any suggestions?
 
According to your guessing challenge just follow the variable value in debug step by step mode …​
​
 
Fred, I don't use 365, but we can noodle on it together. Marc's recommendation is how I'd do it, except I think you made it clear that's exactly what you're doing. Need some details, then; when (exactly) does the discrepancy show up?

Is it the sort of workbook you can post for others to try out?
 
Sorry folks, for some reason this question wasn't being "watched". I've fixed that now but a lot of time has gone by.
I do believe that the context was being noted. But, that's a good suggestion!
 
I'll tell you what's happened when I see this in my code: I've defined a variable as Public in one module, and accidentally forgotten to remove its Dim statement in another. So there's a Public version of the variable, and in one module or procedure a separate version.

Seems to me if you see that vbaStartDate is equal to x in three procedures but to y in a fourth, then the explanation may be that there's a Dim statement in that fourth procedure. You can find it for sure by right-clicking on vbaStartDate in that procedure and selecting the Definition option; it'll go to whatever statement defines the variable.

One other possibility: By any chance is vbaStartDate defined in a class module? If so it becomes a variable in that class, and its value can change from one instance of the object to another. You sound fairly experienced so probably not, but I just thought I'd ask.
 
Thanks Bob, If I'd been asked, I'd have said that there would be at least a warning. So this is good information to have!
 
If you mean a warning from the VBE that you have the same variable name used in multiple contexts, I don't know about other VBA programmers but I consider this a feature not a dangerous practice. I reuse temporary variable names, once they have a standard meaning to me, all the time: "owb" is a workbook, "ows" is a worksheet, "ocs" is ows.Cells, rZ is the final row in a range, cZ is the rightmost column number, ar is a temporary array, lots of stuff like that. In any given procedure I'm likely to use those names with confidence that I don't have to worry about them conflicting with the same variables in different procedures or modules — unless I deliberately define them at the module level or label them Public.

(Sure, if I'm working with multiple worksheets I use Dim and label them owbFm and owbTo, or arNC and arMN, or whatever. And when I do declare a Public variable I give it a longer and more descriptive name. But I like not having to think too carefully about the temporary names I use inside a single procedure.)
 
Yes, I did mean a warning from the VBE that you have the same variable name used in multiple contexts. Somehow I recall seeing that. Does that happen or not? It seems it would be a benefit if an occasional annoyance.
Thanks for the good ideas. I've been pondering which types to use in more a structured context as you describe.
Sorry for my lack of continual experience: what do you mean by "temporary variable names" and how are they established?
 
What you've probably seen—at least I see it occasionally—goes like this: "Compile error: / Duplicate declaration in current scope". It can happen if I declare the same variable name twice in a Dim / Private / Public / Static / whatever statement, but it's most likely to happen when I have a Dim statement somewhere in my program, and then use that same name above the Dim statement. The first use counts as an implicit declaration, you see (it'll define it as Variant, I assume); then somewhere further down it encounters the Dim statement for the same name and complains. At that point I have to decide whether to move the Dim statement up higher, or just use a different variable name where it first occurs.

It's a compile-time error, by the way; the VBE won't spot it when you first write the code, only when it sets out to execute that procedure.
 
Sorry for my lack of continual experience: what do you mean by "temporary variable names" and how are they established?
 
LOL, I don't mean anything official by it. I just meant a variable that I use casually and short-term.

When I define a class and its properties, I probably choose the class name and its property names carefully. For instance, I'm working now on some Excel programs that read my client's Active-Directory database; the first program that deals with the AD data uses a class named ADRaw to handle in incoming raw data, preprocesses it to a more convenient form, and writes it out to a workbook using methods of a class named ADCooked. Subsequent programs that read the cooked-AD workbook use the ADCooked class. It has properties with similarly descriptive names.

But when I'm just making a loop for one small piece of a program, I don't worry too much about what I use for the loop index. All my loop indices are two-letter names and the first letter is 'j'; the second letter can be anything meaningful to me, so a loop might look like this:
Code: 
' Read through all the AD IDs in the array.
For ji = 1 to UBound(ADIDs,1)
  vi = ADIDs(ji)
  If Not Exists(TSSUsers, vi, ous) Then GoTo IterateID 'this user has no mainframe ID

  ' Read through all this user's profiles.
  Set opfls = ous.Profiles
  If opfls Is Nothing then GoTo IterateID 'this user has no profiles
  For jp = 1 To opfls.Count
    Set opfl = opfls(jp)
    ' Do whatever I want to do with each profile of the current user
    Next jp

IterateID:
  Next ji
In that snippet of code, some of the variables—ADIDs, TSSUsers, Profiles—have descriptive names that I thought about before creating. But a lot of them—ji, vi, ous, opfls, jp, opfl—are just throwaway names; I don't need to think very hard about what they are because they're used in that one little bit of code and never again. So I pick a name casually. That's all I meant by "temporary".
 
The practical way to avoid this kind of trouble is to get into the habit of not using the same name for public or module level variables and local variables inside a sub or function. Also, Public variables and module-level variables have their place, but they should be used sparingly.
 
You must log in or register to reply here.
Back
Top