MrExcel Publishing
Your One Stop for Excel Tips & Solutions

public variables lose value after userform

Posted by Katrina on August 03, 2000 4:37 AM

I posted a version of this as a follow up to "Variable scope", but Ada suggested I repost as a new message...

Using Excel 97.

I have two modules in my project. "PubVar" has the line:
Public iClick As Integer

"Module1" has:
Public Sub ShowModify()
iClick = iClick + 5
MsgBox "Return from show: " & iClick
End Sub

There is a button on my worksheet. ShowModify is attached to it (I've tried this as a Form control and as an ActiveX control, same result). I put a breakpoint on the End Sub of ShowModify and a watch on iClick.

The form (frmModify) is barebones. It has one button. The click routine is:
Private Sub CommandButton1_Click()
End Sub

I click on the button on the spreadsheet, the form comes up, I click the button on the form, a msgbox comes up that says the iClick is 5. So far so good. The code hits the break point, and I check the watch. iClick still has a value of 5. I hit F8 to advance a line (execute the End Sub of the worksheet button macro), and iClick reverts to 0.

If you comment out the frmModify.Show line, then the value does not get lost when you hit the end of the routine.

Any brilliant suggestions? I'd rather not have to resort to saving variable values on the spreadsheet.

Thanks in advance.

Posted by Celia on August 03, 0100 6:45 AM

I've just run your code and it worked for me.
The "Return from show" value in the message box increased by 5 each time I ran the macro.
However, I think that if you view the userform in the VBE, the global variables in the other modules will be reset.

Posted by Katrina on August 03, 0100 8:12 AM

Gee, that was an easy fix. Thanks. If I close the VB editor before running it, it runs fine. If I have the VB editor open, it automatically brings up the form in the editor when the routine is over, and, like you said, that clears my variables.