Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Question on variable values carrying from UserForm1 to Module1

  Asked By: Raynard    Date: Nov 20    Category: MS Office    Views: 1011
  

I have a userform with a number of buttons in UserForm1, and the
button assigns a variable value. However, when I call the VBA
routine in Module1, the varible that had a value in UserForm1 is now
either 0 or (in Watches) says "Expression not defined in context."

How do I create a variable in the OKButton_Click routine in
UserForm1 and have it carry over to Module1 to be used in routines
there?

Here's an excerpt of the code in UserForm1. The Msgbox line and the
Watches list shows the correct variable value for OBQ.
'----------------------------------
Private Sub OKButton_Click()

OBSelection:
If OptionButtonQ1 Then OBQ = 1
If OptionButtonQ2 Then OBQ = 2
If OptionButtonQ3 Then OBQ = 3
If OptionButtonQ4 Then OBQ = 4

OBContinue:
MsgBox (OBQ) 'This shows the correct number
Unload UserForm1

Call OpenFiles1 'This calls the VBA routine in Module1

End Sub
'----------------------------------

And here's the first part of the code in Module1. If I Dim the
variable OBQ as an integer, it becomes 0 in this module. If I don't
Dim it, the it says "Expression not defined..." in the Watches list
(but it should be defined as a Variant automatically. Either way,
the Msgbox shows a 0.
'----------------------------------
Sub OpenFiles1()
'---Test for OBQ
MsgBox (OBQ)
End Sub
'----------------------------------

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Viola Hanson     Answered On: Nov 20

There are a number  of ways.

The "easiest" is.. To use a "global" variable... Defing the variable  in the
definitions part  of a module  as public.
Public variable are generally "frowned upon" though.

How are your userforms set up? Do you have mre than one? What are you
actually trying to do?

You could put the code  you want in a subroutine in Module1 and then call  it
depending on the option chosen in the events that happen when closing the
form... Efectively moving what happens to be triggered before the form
actually black holes.

 
Answer #2    Answered By: Arthur Cole     Answered On: Nov 20

If you want to show excepts of code, then you need to provide ALL the
pertinent information. E.g. where and how are these variables defined?
What is their scope?

I suspect you need to show the whole module(s). If it's too long, then you
probably should be cutting back to something shorter and simpler for your
testing anyway.

Also. Please modernise your Basic. You shouldn't be using labels. You
should really be using "If ... Then ... EndIf".

Lastly. Please ensure that you have Option Explicit at the top of each
module.

 
Answer #3    Answered By: Jim Williamson     Answered On: Nov 20

Somehow I knew you'd ding me for not explicitly showing Option
Explicit. ;) So I rewrote new code  to create  a simple test, and
I'll list  all the code here.
Note: I went back to Walkenbach's VBA Power Programming book for
general code reference here to ensure I was doing it correctly.

I created a simple UserForm with two option buttons  titled Yes and
No, and added OK and Cancel buttons. The Yes makes variable  x = 1,
and the No makes x = 2.

Two Msgbox'es show the value at specific points in the code. The
Msgbox in the UserForm module shows  the right value, but in Module1
it shows a 0 as the variable doesn't carry over.

This code is from the Command Button on Sheet1 called Variable Test,
and resides in Microsoft Excel Objects -> Sheet1(Sheet1):
'------------------------------
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
'------------------------------

This code is in Forms -> UserForm1:
'------------------------------
Option Explicit
Public x As Integer

Private Sub OK_Button_Click()
If Yes_Button Then x = 1
If No_Button Then x = 2
MsgBox (x)
Call Var_Test
End Sub

Private Sub Cancel_Button_Click()
Unload UserForm1
End Sub
'------------------------------

And this code is in Module1:
'------------------------------
Option Explicit
Public x As Integer

Sub Var_Test()
MsgBox (x)
Unload UserForm1
End Sub
'------------------------------

It all works without an error, except that the variable x is correct
in the UserForm module  and then goes to 0 in the Module1 module.

 
Answer #4    Answered By: Sherri Parker     Answered On: Nov 20

You are declaring x twice. Do not declare it in the userform  - that makes 2
x's and the userform is only operating on its own x. If you take the Public
x As Integer out of the userform it will work.

 
Answer #5    Answered By: Rachel Barnes     Answered On: Nov 20

But, seriously ... it's so very important to have it on. Excel will insert
them for you. In the VB environment: Tools/Options/Editor/"Require Variable
Declaration" should be ticked.

 
Answer #6    Answered By: Julio Morgan     Answered On: Nov 20

The two "Public x" define different variables, not references to the same
one.

Leave the one in UserForm1 as it is. Remove the one from Module1.

A compile will now give you an error because x is not defined  in Module1.
This is correct  and shows  you that Option Explicit is doing its job.

Now, use "dot" notation in Module1 to access the x in UserForm1:

MsgBox (UserForm1.x)

 
Answer #7    Answered By: Opal Alexander     Answered On: Nov 20

I seem to be in the minority here, but I really don't like this
solution. Pass the variable  to the sub. Then, any form or module
can use the sub not just this one form and this one variable. There
is also no problem with changing the name of x and missing the
reference to it in the module. The sub code  is more robust and the
code is more maintainable.



:: This code is in Forms -> UserForm1:
:: '------------------------------
:: Option Explicit
:: Public x As Integer
::
:: Private Sub OK_Button_Click()
:: If Yes_Button Then x = 1
:: If No_Button Then x = 2
:: MsgBox (x)
:: Call Var_Test

Call Var_Test(x)


:: End Sub
::
:: Private Sub Cancel_Button_Click()
:: Unload UserForm1
:: End Sub
:: '------------------------------
::
:: And this code is in Module1:
:: '------------------------------
:: Option Explicit
:: Public x As Integer

Delete the line  above.


:: Sub Var_Test()

Sub Var_Test(x as Integer)

:: MsgBox (x)
:: Unload UserForm1
:: End Sub

 
Answer #8    Answered By: Coleman Smith     Answered On: Nov 20

> I seem to be in the minority here, but I really don't like this
> solution. Pass the variable  to the sub. Then, any form or module
> can use the sub not just this one form and this one variable. There
> is also no problem with changing the name of x and missing the
> reference to it in the module. The sub code  is more robust and the
> code is more maintainable.

Actually I didn't write any of it - just for the record. :-)

You are probably right, but that wasn't the question. The question  was
about how to use a global variable.

On occasions, I use global variables. They have their uses. Most of the
time I don't need them.

> ... There is also no problem with changing the name of x and missing the
> reference to it in the module.

Well, that's what compilers are for. If you change the name of x, then the
compiler will object.

Another problem with this test code is that it unloads the test form from
within the called subroutine - bad practice. But it is only a quick test
program, after all.

 




Tagged: