Very strange error in my program

[duluter]

Hi, all.

Here's a snippet from my Word VBA program. This code is from a subroutine in a regular VBA module.


If GlobalVar1 > 0 Then
MyVar = GlobalVar1
Else
MyVar = GetANumberThatIsntZero
GlobalVar1 = MyVar
End If
I am using a global variable to store a user setting between calls of the subroutine. The first time the subroutine is run, GlobalVar1 = 0. The above code checks if GlobalVar1 is zero or not. If it isn't, then we do something (unimportant). If it is zero, then we set it to something that isn't zero. Basically, GlobalVar1 will be zero the first time around, but never again thereafter.

Except that the value of GlobalVar1 isn't changing--no matter how many times I run the subroutine. Which is freaking me out. What's freaking me out more is that if I add a messagebox after the above code, it says that GlobalVar1 has changed to a non-zero value. Thus, my code only works when I put a completely superfluous messagebox after it. What the heck?!

Just for kicks, I also modified the code so that instead of using a global variable, I used a property on a form that is open. Exact same result. The value of the property never changes, except when I add a messagebox after the code.

What is going on?


EDIT:
OK. I just did a little investigatory. In my original scenario, a form is open the whole time, and it's a button on the form that calls the subroutine. I just wrote a little program to call the subroutine without having the form open in the background, and it worked perfectly--the first time the subroutine is called, GlobalVar1 = 0, but each time the subroutine is called after that, GlobalVar1 <> 0. Thus, having the form open is what is causing this to act weird. Is there a known issue in VBA with global variable assignments when a form calls a public subroutine in a standard VBA module? How can I get around this? I have to have the form open.


Duluter

[TonyJollans]

It would be more helpful if you gave a little more detail rather than just saying "then we do something (unimportant)". It is important because the code as you have posted it won't change GlobalVar1 unless it is zero (or negative).

Again, with your statement about the message box, you are not giving helpful information. Your MsgBox shows that GlobalVar1 has changed to a non-zero value. Yes, it has. It does that first time round, when it starts as zero, it will then be changed, just, maybe, never again.

Although I don't know exactly what you want, or what you're seeing, I am certain that your problem is to do with the lifetime of variables. Form Properties live (and die) with the Form. Global variables live and die with the Project.

[duluter]

TonyJollans:

I see what you are saying, so I am posting the real code below.

This is the version that does not work:

When my form activates, it runs this code to initialize the global variable to zero:
Sycamore2.AAOTOComNameCol = 0
When the user double clicks an entry in a listbox on the form, the following subroutine is called in the Sycamore2 Module.

'Global variable declaration
Public AAOTOComNameCol as Integer

Public Sub MyRoutine()

Dim ComCol as Integer
Dim objTable as Table
...
...
...
If Sycamore2.AAOTOComNameCol > 0 Then

ComCol = Sycamore2.AAOTOComNameCol

Else

ComCol = GetHeaderColumnNumber("common name", objTable, "Common Name")
Sycamore2.AAOTOComNameCol = ComCol

End If

End Sub

After the routine ends, the user can then make additional selections in the listbox on the form. The above subroutine will get called after each user selection. The GlobalVariable should "remember" that the user has already gone through this process at least once because it is zero only before the first time the procedure is run. Afterwards, AAOTOComNameCol will always be a number larger than zero because of these lines:

ComCol = GetHeaderColumnNumber("common name", objTable, "Common Name")
Sycamore2.AAOTOComNameCol = ComCol

The GetHeaderColumnNumber function always returns a value greater than zero.

The problem is that the value of my GlobalVariable seems to keep reverting back to zero in between calls to the subroutine. However, if I place a messagebox after the code above (after the close of the IF statement), then the code works perfectly. The messagebox seems like it should be irrelevant, but it makes the difference for some reason. Note that the messagebox can display any message--the important thing is that the messagebox is shown, not what it is saying to the user.

Does that clear things up? I feel convinced that this is some sort of VBA bug. I did an experiment and created a new project with a much simplified form and module but with basically the same functionality and it worked fine. So I thought maybe my form is corrupt in some way. I created a new form and made it identical to my old form, deleted the old form, and it still does not work. My only lame idea is that somehow the complexity of my form is overwhelming the system. But it's not a super-complicated form (one textbox, one listbox, four frames that each hold 3 or 4 checkboxes).


Duluter

[TonyJollans]

Reading between the lines a little ... you say:


When my form activates, it runs this code to initialize the global variable to zero

Does that mean you have that code in the UserForm Activate routine?

You imply that you have a modal UserForm and, if so, maybe the user (you?) is switching in and out of the userform, constantly deactivating and re-activating it, in which case the Activate routine will keep on running.

All a bit of guesswork but try putting the initialisation code in the Initialize routine instaed of the Activate one.

[duluter]

Tony:

That did it. I moved the initialization out of the Activate routine and into Initialize. I see why that would be important, but it's not clear to me why putting a messagebox into the original code would change the way it runs. Anyway, thank you for the help.


In between posts, I can up with a different solution that seems to work well. Basically what this code is doing is trying to guess which columns are which in a Word table based on the text in the cells in the first row of the table (the header row). Essentially, if the header for a column is "Scientific Name" then the program can figure out what this column is for. But if the header is, say, "Name", the program can't figure out if the column is for the scientific name or the common name. In this case, it asks the user to identify which column is which. In my original scheme, I used global variables to keep the column assignments between calls to the routine (which means that the user only has to identify the columns the first time the routine is run, which is good). In my newer version of the code (which I wrote when I was having trouble with the old version), the column identifiers are stored directly in the Word file in each header cell's Cell.ID property. We never use this property for anything, so I co-opted it for my purposes. Here's my question: From a good coding perspective, is it better to store this information in global variables, embedded in the document itself (Cell.ID), or another route entirely? I'd prefer not to store the information in properties on the form because then the subroutine must be opened by that particular form, which I may not want in the future. Any suggestions on which method you would use would be appreciated.


Thanks,

Duluter

[TonyJollans]

Here's my question: From a good coding perspective, is it better to store this information in global variables, embedded in the document itself (Cell.ID), or another route entirely? I'd prefer not to store the information in properties on the form because then the subroutine must be opened by that particular form, which I may not want in the future. Any suggestions on which method you would use would be appreciated.

There are two, related, factors to consider: scope and lifetime. Scope is who or what can see the data, and lifetime is, well, how long it lives. In general, you should use as narrow a scope, and as short a lifetime, as possible. This reduces resource usage, as well as the chance for error.

Again, in general, if you want to keep information with the document, then use the document (this would be particulary true where you had asked the user for anything, even if only confirmation). If you're going to (or can, easily) determine it every time your code runs, then keep it in code; where in the code depends on where and when you need it. It rather sounds like, in this case, you want a global variable (in a module)

[fumei]

"but it's not clear to me why putting a messagebox into the original code would change the way it runs. "

Why? Because just as Tony mentioned, you had the code in Activate, not Initialize.

When you added the messagebox instruction, focus is passed to the messagebox, and then (when the messagebox is closed) focus is passed back to the userform...and Activate is fired.

I would agree with Tony (and you), that you need a Public variable. There is absolutely nothing wrong with using one. You simply have to understand the two things Tony also mentioned. Scope and lifetime.

They are critical is using VBA (and most other programming languages).

I have never understood why so many people use Activate to...ahem...Initialize variables on a userform.

Another possibility for your case - although I still do not think we have full enough details - is to store your global variable instead as a document variable. Depending on the situation (not fully described), if the global is only going to be 0 once, then making it a DOCUMENTVARIABLE would means you test it once. If 0, make it not-zero. Dada. Done. You can get the value of that DOCUMENTVARIABLE anytime you want (as it is in Scope in ALL modules). You never have to initialize it, or declare it.

Well that is not true. You have to declare and set it in the first place. But that is done ONCE.

Just a thought.

[duluter]

I wrote: "but it's not clear to me why putting a messagebox into the original code would change the way it runs. "

fumei, you wrote: "Why? Because just as Tony mentioned, you had the code in Activate, not Initialize."

I should have written that that's why I don't understand why it made the code work when I added the messagebox. I would think that my form would lose focus when the messagebox pops up. When the messagebox is cleared, the form regains focus and the Activate routine is run. That should have caused my global variables to re-initialize. Right? Except that the only time the code worked was when I added the messagebox. There's something here that I'm still not understanding.

Thanks for the tip about document variables. I have not used them before. I'll read up on them.


Duluter