Solved: Public Variable Losing Value

[Mavila]

I declare a public string variable in a Module (IWLNm) - where the macro is started. At some point the macro calls a UserForm and the user enters info, clicks "ENTER" and the user's data is captured. The public variable "IWLNm" is assigned a value. During the "Click" event, a PrivateSub is called in this same UserForm module and the public variable "IWLNm" is used. But it loses it's value and becomes "". Then, when the PrivateSub ends and the code returns to the "Click" event, the variable gets it's value back. I use the variable in later subroutines and it's again lost. It seems to only retain its value in the "Click" event even though it is declared in the public module.

I use public variables like this all the time without this problem, including in this project. What am I missing?

Thanks!

[Frosty]

Well, you're missing something, or you wouldn't be having the problem

Unfortunately, your description of the problem isn't enough to tell you the answer definitively. Ideas...

1. Are you sure it's a public variable? You can't put a public variable at the top of a userform module... it becomes a public property of the userform class.
2. Do a search on the actual name of the variable-- do you have an overlapping scope issue (i.e., is it private to a module or procedure)
3. Have you stepped through the code line by line/set a watch on this variable and told your code to stop execution every time the value changes?

Scope and Lifetime are two big concepts that take awhile to wrap your mind around. There are topics in the helpfile to help further understand this... but my suspicion that you "do this all the time" probably means you don't fully understand the concepts. It is considered not terribly good programming practice to make wide-spread use of public variables... mostly because it can be so tricky to track down errors like this.

[Mavila]

Thanks Frosty. My grasp of the terminology (let alone concepts, as you suggest), is a bit sketchy. It's declared in what I believe is called a public module. My understanding and experience is that doing it this way makes the variable's value remain available everywhere in the project macro.

The variable is assigned in a UserForm module when the user clicks enter. The value is a person's name.

When the Enter button is clicked, the variable is assigned in the Click event. The Click event calls a subroutine in the same UserForm module that essentially saves a Word document with the person's name as part of filename. The variable is also used later in two other UserForms as well as a routine that makes an entry in an Excel spreadsheet.

So, I have created a message box before the filesave subroutine is called and the variable works. Then I have a msgbox on the first line of code in the called filesave subroutine - it's empty. Then, when the filesave sub is done, I have a msgbox on the first line of code when it returns to the calling sub - it works! But it's lost again when it leaves the UserForm.

It's a simple file search and saveas subroutine. 20 lines of looping code more or less. It's a private sub in the UserForm module. The code doesn't go anywhere else in between.

[fumei]

Are you using Option Explicit? If you are NOT, and you have a variable with the same name in the subroutine that could cause this.

If it is truly public this should not happen. So until we can see the actual situation it is hard to offer any ideas.

[Frosty]

Actually, option explicit won't help you here, unless you don't have it on AND you have a typo where you're actually using the variable.
Option Explicit
Public myVar As String
Sub Demo()
Dim myVar As String
MyVar = "hello world"
End Sub
Sub ScopeDemo()
Msgbox myVar
End Sub

Would all compile, and still exhibit the behavior you're describing. That would be a scope problem.

But use of End somewhere could cause a problem. So could no Option Explicit and a typo of myVer with out procedure level dim statement.

As Fumei said--without showing the code... It's all a guessing game

[Mavila]

Well, I didn't want to post the code because it's too much and I wouldn't know how to go about it being in multiple modules. There is only the one declaration, I can tell you that. The variable is assigned only once in that Click event.

I will say this, though, that the variable can be assigned in one of two ways. First is through a combobox control and second is through a textbox control There's an if/elseif/endif test to determine if a checkbox is true/false and then the variable is assigned accordingly.

Before the variable is sent on to this subroutine, it's used to enter a record in a DB. Then it's used to enter the name into a Word document in a bookmark. Then it's used to save that document and the variable is used in the filename. Then it goes to the subroutine where it's lost. It is used in that subroutine, but it's after the value is lost. It's lost on the first line of code and then reappears when returned to the calling subroutine.

[Frosty]

Then I would start by learning how to set a Watch for all procedures and modules, for your variable, and have execution stop when the value of the watched variable changes. Best guess at this point is that it is either
1) you have multiple events firing you are unaware of the events being related
2) something about the DB write process is returning the value changed. If you were using parameters I would suggest changing the implicit ByRef to ByVal... but since you're not, I really don't have better ideas without seeing the actual code.

You could also recreate the problem in a demo project and then post that simplified project-- I'm guessing you won't be able to recreate the problem in a simpler version of the project.

[Mavila]

I'm embarrased to say that I discovered the problem. I dimmed the variable in the UserForm. It's actually 3 variables (first, middle and last name) and I dimmed them on one line with commas which I never do. So in skimming it, I missed it cause it looked like a regular line of code.

Sorry to waste your time guys!

[Frosty]

As my fifth grade teacher used to say: "It's always easy when you know the answer."

[Mavila]

Yes, ain't it the truth!

Frosty, do you know of a good article that could help me get off my dependency on Public variables? I have another project where I literally have a few dozen of them. Would it be better to put the data in a database table and then retrieve the values when their needed (like user preferences)?

[Frosty]

There is no "best" way to code. But there are better ways and worse ways. Universally, the "best" code is code that works and never breaks.

That said, the better structured and encapsulated (and commented!!) your code is, the easier it is to revise and troubleshoot. I don't have any specific articles for you, unfortunately. However, I have worked with a very mature product that made widespread use of public variables. It had very few bugs (because it was around for a long time), but the bugs it did have were very difficult to troubleshoot.

It would have been prohibitive to move all of those public variables into parameters of the various procedures that used the public variables, but that would be my first recommendation, if you're only talking about dozens (they had hundreds).

However, you could look at organizing your public variables into UDTs (user defined types) as a better organization of these clumps of data. Then, at least, it becomes easier to set a single watch (look up you watch window and locals windows, and how to use them).

Other than that, I would look at playing around with the use of passing parameters (and the use of ByRef vs ByVal).

Without knowing exactly what you want to accomplish, I can only give general principles. But if you posted some simple code, I could revamp it for you as a demonstration of the kind of structure I would advocate.

It generally takes a little longer to get "good" code up and running, but it is a lot easier to debug/revise.

[Mavila]

Thanks, Frosty, I appreciate your insight very much.

I'm a little weak on the fundamentals if you haven't already noticed. I have a habit of just wanting to get the thing to work. The consequences of inelegant or poorly organized code are secondary more often than not. I've gotten better because, well. I've spent a lot of time doing trial and error, dubugging, and re-writing a lot of code. I need to get better command of the fundamentals. Like ByRef and ByVal - I've seen them before, but I don't really understand them and I definitely don't use them. I will read up.

Thanks again, Frosty.

[Frosty]

Use option explicit in all of your modules.
F1 is your friend.
Learn how to pass parameters
Read up on optional parameters
Research scope (public, private and dim)
Comment
Use error trapping
Learn the various windows (locals, watch, immediate)
All of those will be good tools and things to invesigste. The help files are pretty good, at least some times.

[Joel007]

A variable will also lose/change its value, for example, when you use it in a FOR loop, f.e.

for each YOURVARIABLE in Sheet1.range("A1:A100")

So just sticking to generic advice like "option explicit" or declaring it public is not the complete picture. Search thru the code for every occurrence of your variable.