MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Variable scope


Posted by Geoff on June 19, 2000 5:58 AM

I'm using Excel for a VBA program I'm writing and I'm having a variable scope problem. I will provide as much detail as possible about the problem. Okay, to start off I have several sheets in my workbook. On the starting sheet, a user has 3 choices they can make (3 different buttons they can click). When the user clicks a button, it opens a form with some options on it. When they are through choosing options and then click "Ok", I unload the form and activate a certain sheet in the workbook depending on what options they chose in the form. What I would like to know is when the form is unloaded, does the execution of the program actually stop? or is it still running? The reason I ask is because I have some global variables declared in a module whose values are not being "remembered" throughout the entire program. I hope this isn't too confusing. Thanks for any help.


Posted by Ryan on June 19, 0100 8:17 AM

Geoff,
If the variables you declared are in the code the UserForm and you "Unload" the userform the variables will be deleted from memory. You must declare "Public" variables in a normal module. When you do this they stay in memory until the program is done executing. If you are grabbing data from the UserForm and not variables, it might be better to Hide the Form instead of Unloading it. Hope this helps.

Ryan

Posted by Katrina on July 21, 0100 8:21 AM

Did you find a solution for this? I'm having the same/similar problem. I have a variable declared in a module that contains only similar declarations:
Public bModCore As Boolean

I have a button on a spread sheet, the following macro is attached to the button:
Sub SHOW_MODIFY()
frmModify.Show
End Sub

I add a breakpoint to the End Sub and a watch to the bModCore.

User clicks the button, the form comes up, user clicks checkboxes, and user hits the OK button on the form. A routine is run that does frmModify.Hide, sets some variables, shows some dialog sheets, and does some calculations. After it ends, we return to the breakpoint. The watch says my variable is True. I hit F8 to advance a step and the variable changes to False.

According to everything I've read, the variable should not be losing its value. It definitely has to do with showing the userform. If I do this:
Sub SHOW_MODIFY()
frmModify.Show
bModCore = True
End Sub

then bModCore loses its value, but if I comment out the Show:
Sub SHOW_MODIFY()
'frmModify.Show
bModCore = True
End Sub

then bModCore stays True.

Posted by Ryan on June 20, 0100 9:31 AM

One thing that I like to do to see step by step what values variables have is to use the msgbox. If you haven't tried this yet, do. Right after a varible get's it's value, either from a textbox event or any other event, use a msgbox to see if the variable is getting it's value correctly. And you can see where it all goes astray. Hope this brings some insight into what's going on.
Ryan

Posted by Geoff on June 19, 0100 12:10 PM

Ryan,

Thanks for the reply. I was thinking exactly along the same line that you were. The variables are declared in a module as public and I even tried to hide the form instead of unloading it and the variables still had empty values. This is what mystifies me. Since the form isn't removed from memory, the variables should retain their original values which they aren't. I can't think of anything else I might be doing incorrectly. I know this is really vague, so I understand if you just ignore this. I can't think of anything else to include in this problem. I've been running the debugger to test this problem and I've come up with nothing. Once again, thanks for the reply.

Posted by Ada on July 21, 0100 10:30 PM

Katrina
I don't have a solution but would suggest that you post your message again as a new message. I doubt that many people(including Geoff) are going to come across it all the way back here.
Ada