Where to store variables from UserForm? 
Author Message
 Where to store variables from UserForm?

This is from my USERFORM CODE:

Public Sub OptionButton2_Change()
gameserver = UserForm1.OptionButton2.Value
End Sub

This is from my MODULE CODE:

.....If gameserver = True Then.....

How can I store gameserver variable in my USERFORM CODE to use it later in
MODULE CODE???

Usually when I declare a variable with Public declaration it can be used
between different modules. But when a variable is declared in UserForm Code
it is not in use in my modules.

I dont want to store the information in excel sheets (cells).

--
Harri Vesterinen
CE-Simulations
System development manager

mobile. +358 41 461 55 64
office. +358 9 406 660
fax. +358 9 406 662



Sat, 22 Feb 2003 23:48:04 GMT  
 Where to store variables from UserForm?
Declare the public variable in the module instead.

<module>
Public gameserver as boolean

<userform>
gameserver = optionbutton.value

Ray Costanzo

http://www.excelfiles.com



Sun, 23 Feb 2003 00:15:06 GMT  
 Where to store variables from UserForm?
Hi Harri,

You can store your variables in public variables defined in standard modules, or you can store it into named workbook-level constants (
Workbook("aBook").Names.Add ) or on the spreadsheet, or in external files, or in the registry, etc.

You are not allowed of public variables in form modules.

You may also be interested to know that the following can work in astandard module as well, and its probably the best way (with a tradeoff
in coding effort) to manipulate variables (IMHO)
----------------------------------------------

Dim myVariable_Local As Boolean

Property Let myVariable(aVar As Boolean)
    myVariable_Local = aVar
End Property

Property Get myVariable() As Boolean
    myVariable = myVariable_Local
End Property

Sub test()
Module1.myVariable = True
Debug.Print Module1.myVariable
End Sub
-------------------------------------------------

If you make it Private (i.e. Private Property ...) you can use it as a procedure in a form module fully qualifying your syntax (as in the
example).

HTH
Stratos

Quote:

> This is from my USERFORM CODE:

> Public Sub OptionButton2_Change()
> gameserver = UserForm1.OptionButton2.Value
> End Sub

> This is from my MODULE CODE:

> .....If gameserver = True Then.....

> How can I store gameserver variable in my USERFORM CODE to use it later in
> MODULE CODE???

> Usually when I declare a variable with Public declaration it can be used
> between different modules. But when a variable is declared in UserForm Code
> it is not in use in my modules.

> I dont want to store the information in excel sheets (cells).

> --
> Harri Vesterinen
> CE-Simulations
> System development manager

> mobile. +358 41 461 55 64
> office. +358 9 406 660
> fax. +358 9 406 662



Sun, 23 Feb 2003 00:20:35 GMT  
 Where to store variables from UserForm?

Declare the variable in the standard Module as a public variable.  Then the
module and the userform will be able to see it.

Regards,
Tom Ogilvy
MVP Excel


Quote:
> This is from my USERFORM CODE:

> Public Sub OptionButton2_Change()
> gameserver = UserForm1.OptionButton2.Value
> End Sub

> This is from my MODULE CODE:

> .....If gameserver = True Then.....

> How can I store gameserver variable in my USERFORM CODE to use it later in
> MODULE CODE???

> Usually when I declare a variable with Public declaration it can be used
> between different modules. But when a variable is declared in UserForm
Code
> it is not in use in my modules.

> I dont want to store the information in excel sheets (cells).

> --
> Harri Vesterinen
> CE-Simulations
> System development manager

> mobile. +358 41 461 55 64
> office. +358 9 406 660
> fax. +358 9 406 662



Sun, 23 Feb 2003 01:27:11 GMT  
 Where to store variables from UserForm?
Sorry to differ - but you are certainly able to have public variables in
form modules. I just tried this on XL97.

A simple procedure run from a module ...

---------------
Option Explicit

Public Sub getvar()

UserForm1.Show
MsgBox UserForm1.myInt

End Sub

--------------

And a simple form with a Checkbox and a CommandButton, code as follows ...

--------------

Option Explicit

Public myInt As Integer

Private Sub CheckBox1_Click()

If CheckBox1 Then
    myInt = 10
Else
    myInt = 20
End If

End Sub

Private Sub CommandButton1_Click()

Me.Hide

End Sub

-------------------

The above works fine. The trick is to use the command button to "HIDE" the
form, not to close it. If the user closes the instance of the form using the
"X" then the msgbox shows "0". If they use the command button then the
correct value is returned, because the form is still active in memory. It
does, however, prove that public variables are allowed and accessible within
form modules.

--
Cheers

Mick Jennings
~~~~~~~~~~


Quote:
> Hi Harri,

> You can store your variables in public variables defined in standard

modules, or you can store it into named workbook-level constants (
Quote:
> Workbook("aBook").Names.Add ) or on the spreadsheet, or in external files,

or in the registry, etc.
Quote:

> You are not allowed of public variables in form modules.

> You may also be interested to know that the following can work in

astandard module as well, and its probably the best way (with a tradeoff
Quote:
> in coding effort) to manipulate variables (IMHO)
> ----------------------------------------------

> Dim myVariable_Local As Boolean

> Property Let myVariable(aVar As Boolean)
>     myVariable_Local = aVar
> End Property

> Property Get myVariable() As Boolean
>     myVariable = myVariable_Local
> End Property

> Sub test()
> Module1.myVariable = True
> Debug.Print Module1.myVariable
> End Sub
> -------------------------------------------------

> If you make it Private (i.e. Private Property ...) you can use it as a

procedure in a form module fully qualifying your syntax (as in the

- Show quoted text -

Quote:
> example).

> HTH
> Stratos


> > This is from my USERFORM CODE:

> > Public Sub OptionButton2_Change()
> > gameserver = UserForm1.OptionButton2.Value
> > End Sub

> > This is from my MODULE CODE:

> > .....If gameserver = True Then.....

> > How can I store gameserver variable in my USERFORM CODE to use it later
in
> > MODULE CODE???

> > Usually when I declare a variable with Public declaration it can be used
> > between different modules. But when a variable is declared in UserForm
Code
> > it is not in use in my modules.

> > I dont want to store the information in excel sheets (cells).

> > --
> > Harri Vesterinen
> > CE-Simulations
> > System development manager

> > mobile. +358 41 461 55 64
> > office. +358 9 406 660
> > fax. +358 9 406 662



Sun, 23 Feb 2003 04:53:16 GMT  
 Where to store variables from UserForm?
As an afterthought to the above, I don't normally like the idea of declaring
variables public at the top level (the module usually) so that they can be
changed at the lower levels. IMHO it leads to confusing code (and I should
know I've written a ton of confusing code <g>).

I've gradually come to the conclusion that where possible it's better to put
the code which needs to access the userform controls and variables within
the userform itself, possibly even passing the form an object (eg a Range)
on which to act. The next best alternative (IMHO) is to use one instance of
the form and just hide it (as in my previous example) after which the public
variables and controls can be accessed. I've usually found it possible to
adopt one of these styles.

--
Cheers

Mick Jennings
~~~~~~~~~~


Quote:
> Sorry to differ - but you are certainly able to have public variables in
> form modules. I just tried this on XL97.

> A simple procedure run from a module ...

> ---------------
> Option Explicit

> Public Sub getvar()

> UserForm1.Show
> MsgBox UserForm1.myInt

> End Sub

> --------------

> And a simple form with a Checkbox and a CommandButton, code as follows ...

> --------------

> Option Explicit

> Public myInt As Integer

> Private Sub CheckBox1_Click()

> If CheckBox1 Then
>     myInt = 10
> Else
>     myInt = 20
> End If

> End Sub

> Private Sub CommandButton1_Click()

> Me.Hide

> End Sub

> -------------------

> The above works fine. The trick is to use the command button to "HIDE" the
> form, not to close it. If the user closes the instance of the form using
the
> "X" then the msgbox shows "0". If they use the command button then the
> correct value is returned, because the form is still active in memory. It
> does, however, prove that public variables are allowed and accessible
within
> form modules.

> --
> Cheers

> Mick Jennings
> ~~~~~~~~~~



> > Hi Harri,

> > You can store your variables in public variables defined in standard
> modules, or you can store it into named workbook-level constants (
> > Workbook("aBook").Names.Add ) or on the spreadsheet, or in external
files,
> or in the registry, etc.

> > You are not allowed of public variables in form modules.

> > You may also be interested to know that the following can work in
> astandard module as well, and its probably the best way (with a tradeoff
> > in coding effort) to manipulate variables (IMHO)
> > ----------------------------------------------

> > Dim myVariable_Local As Boolean

> > Property Let myVariable(aVar As Boolean)
> >     myVariable_Local = aVar
> > End Property

> > Property Get myVariable() As Boolean
> >     myVariable = myVariable_Local
> > End Property

> > Sub test()
> > Module1.myVariable = True
> > Debug.Print Module1.myVariable
> > End Sub
> > -------------------------------------------------

> > If you make it Private (i.e. Private Property ...) you can use it as a
> procedure in a form module fully qualifying your syntax (as in the
> > example).

> > HTH
> > Stratos


> > > This is from my USERFORM CODE:

> > > Public Sub OptionButton2_Change()
> > > gameserver = UserForm1.OptionButton2.Value
> > > End Sub

> > > This is from my MODULE CODE:

> > > .....If gameserver = True Then.....

> > > How can I store gameserver variable in my USERFORM CODE to use it
later
> in
> > > MODULE CODE???

> > > Usually when I declare a variable with Public declaration it can be
used
> > > between different modules. But when a variable is declared in UserForm
> Code
> > > it is not in use in my modules.

> > > I dont want to store the information in excel sheets (cells).

> > > --
> > > Harri Vesterinen
> > > CE-Simulations
> > > System development manager

> > > mobile. +358 41 461 55 64
> > > office. +358 9 406 660
> > > fax. +358 9 406 662



Sun, 23 Feb 2003 04:58:04 GMT  
 Where to store variables from UserForm?

Quote:
> Sorry to differ - but you are certainly able to have public variables in
> form modules. I just tried this on XL97.

No need to be sorry at all Mick; you are absolutely right and I wonder why nobody else corected me before. You can have public variable in
all class modules indeed.

There are other things like public constants and public declares that are not allowed.

Thanks,
Stratos



Sun, 23 Feb 2003 05:04:42 GMT  
 Where to store variables from UserForm?
Thats great info Mick,

You should built a web-site (if you don't have one allready) and publish your findings and conclusions in there for general benefit!

Thanks again,
Stratos

Quote:

> As an afterthought to the above, I don't normally like the idea of declaring
> variables public at the top level (the module usually) so that they can be
> changed at the lower levels. IMHO it leads to confusing code (and I should
> know I've written a ton of confusing code <g>).

> I've gradually come to the conclusion that where possible it's better to put
> the code which needs to access the userform controls and variables within
> the userform itself, possibly even passing the form an object (eg a Range)
> on which to act. The next best alternative (IMHO) is to use one instance of
> the form and just hide it (as in my previous example) after which the public
> variables and controls can be accessed. I've usually found it possible to
> adopt one of these styles.

> --
> Cheers

> Mick Jennings
> ~~~~~~~~~~



> > Sorry to differ - but you are certainly able to have public variables in
> > form modules. I just tried this on XL97.

> > A simple procedure run from a module ...

> > ---------------
> > Option Explicit

> > Public Sub getvar()

> > UserForm1.Show
> > MsgBox UserForm1.myInt

> > End Sub

> > --------------

> > And a simple form with a Checkbox and a CommandButton, code as follows ...

> > --------------

> > Option Explicit

> > Public myInt As Integer

> > Private Sub CheckBox1_Click()

> > If CheckBox1 Then
> >     myInt = 10
> > Else
> >     myInt = 20
> > End If

> > End Sub

> > Private Sub CommandButton1_Click()

> > Me.Hide

> > End Sub

> > -------------------

> > The above works fine. The trick is to use the command button to "HIDE" the
> > form, not to close it. If the user closes the instance of the form using
> the
> > "X" then the msgbox shows "0". If they use the command button then the
> > correct value is returned, because the form is still active in memory. It
> > does, however, prove that public variables are allowed and accessible
> within
> > form modules.

> > --
> > Cheers

> > Mick Jennings
> > ~~~~~~~~~~



> > > Hi Harri,

> > > You can store your variables in public variables defined in standard
> > modules, or you can store it into named workbook-level constants (
> > > Workbook("aBook").Names.Add ) or on the spreadsheet, or in external
> files,
> > or in the registry, etc.

> > > You are not allowed of public variables in form modules.

> > > You may also be interested to know that the following can work in
> > astandard module as well, and its probably the best way (with a tradeoff
> > > in coding effort) to manipulate variables (IMHO)
> > > ----------------------------------------------

> > > Dim myVariable_Local As Boolean

> > > Property Let myVariable(aVar As Boolean)
> > >     myVariable_Local = aVar
> > > End Property

> > > Property Get myVariable() As Boolean
> > >     myVariable = myVariable_Local
> > > End Property

> > > Sub test()
> > > Module1.myVariable = True
> > > Debug.Print Module1.myVariable
> > > End Sub
> > > -------------------------------------------------

> > > If you make it Private (i.e. Private Property ...) you can use it as a
> > procedure in a form module fully qualifying your syntax (as in the
> > > example).

> > > HTH
> > > Stratos


> > > > This is from my USERFORM CODE:

> > > > Public Sub OptionButton2_Change()
> > > > gameserver = UserForm1.OptionButton2.Value
> > > > End Sub

> > > > This is from my MODULE CODE:

> > > > .....If gameserver = True Then.....

> > > > How can I store gameserver variable in my USERFORM CODE to use it
> later
> > in
> > > > MODULE CODE???

> > > > Usually when I declare a variable with Public declaration it can be
> used
> > > > between different modules. But when a variable is declared in UserForm
> > Code
> > > > it is not in use in my modules.

> > > > I dont want to store the information in excel sheets (cells).

> > > > --
> > > > Harri Vesterinen
> > > > CE-Simulations
> > > > System development manager

> > > > mobile. +358 41 461 55 64
> > > > office. +358 9 406 660
> > > > fax. +358 9 406 662



Sun, 23 Feb 2003 05:07:50 GMT  
 Where to store variables from UserForm?

Interesting observation Mick.

When you access myInt via UserForm1.myInt, myInt acts as a Property of
UserForm1, requiring specific object reference when accessed from outside
the UserForm, and not so much as a Global Variable. I suppose the
distinction should have been made between Public variables and Global
variables.

I guess that when I read some of the other responses I was thinking along
the lines of enabling a variable to be "seen" from all modules and forms in
the VBA project as the below code demonstrates when it fails to recognize
the declaration because it was declared in a UserForm:

'In a UserForm
Option Explicit
Public x as Long
'----------------------------

'In a Module
Option Explicit
Sub BeginHere()
    x = 10
End Sub

You pointed out a seldom-discussed topic. Thanks for the info.

Tim


Quote:
> Sorry to differ - but you are certainly able to have public variables in
> form modules. I just tried this on XL97.

> A simple procedure run from a module ...

> ---------------
> Option Explicit

> Public Sub getvar()

> UserForm1.Show
> MsgBox UserForm1.myInt

> End Sub

> --------------

> And a simple form with a Checkbox and a CommandButton, code as follows ...

> --------------

> Option Explicit

> Public myInt As Integer

> Private Sub CheckBox1_Click()

> If CheckBox1 Then
>     myInt = 10
> Else
>     myInt = 20
> End If

> End Sub

> Private Sub CommandButton1_Click()

> Me.Hide

> End Sub

> -------------------

> The above works fine. The trick is to use the command button to "HIDE" the
> form, not to close it. If the user closes the instance of the form using
the
> "X" then the msgbox shows "0". If they use the command button then the
> correct value is returned, because the form is still active in memory. It
> does, however, prove that public variables are allowed and accessible
within
> form modules.

> --
> Cheers

> Mick Jennings
> ~~~~~~~~~~



> > Hi Harri,

> > You can store your variables in public variables defined in standard
> modules, or you can store it into named workbook-level constants (
> > Workbook("aBook").Names.Add ) or on the spreadsheet, or in external
files,
> or in the registry, etc.

> > You are not allowed of public variables in form modules.

> > You may also be interested to know that the following can work in
> astandard module as well, and its probably the best way (with a tradeoff
> > in coding effort) to manipulate variables (IMHO)
> > ----------------------------------------------

> > Dim myVariable_Local As Boolean

> > Property Let myVariable(aVar As Boolean)
> >     myVariable_Local = aVar
> > End Property

> > Property Get myVariable() As Boolean
> >     myVariable = myVariable_Local
> > End Property

> > Sub test()
> > Module1.myVariable = True
> > Debug.Print Module1.myVariable
> > End Sub
> > -------------------------------------------------

> > If you make it Private (i.e. Private Property ...) you can use it as a
> procedure in a form module fully qualifying your syntax (as in the
> > example).

> > HTH
> > Stratos


> > > This is from my USERFORM CODE:

> > > Public Sub OptionButton2_Change()
> > > gameserver = UserForm1.OptionButton2.Value
> > > End Sub

> > > This is from my MODULE CODE:

> > > .....If gameserver = True Then.....

> > > How can I store gameserver variable in my USERFORM CODE to use it
later
> in
> > > MODULE CODE???

> > > Usually when I declare a variable with Public declaration it can be
used
> > > between different modules. But when a variable is declared in UserForm
> Code
> > > it is not in use in my modules.

> > > I dont want to store the information in excel sheets (cells).

> > > --
> > > Harri Vesterinen
> > > CE-Simulations
> > > System development manager

> > > mobile. +358 41 461 55 64
> > > office. +358 9 406 660
> > > fax. +358 9 406 662



Sun, 23 Feb 2003 06:02:44 GMT  
 Where to store variables from UserForm?
Thanks - can you e-mail my wife to ask her to deal with our 18-month old
permanently for a couple of days, then I might find the time. Oh and I'd
need to quit the band. Oh and work would need to lay off as well ...

<g>

--
Cheers

Mick Jennings
~~~~~~~~~~


Quote:
> Thats great info Mick,

> You should built a web-site (if you don't have one allready) and publish

your findings and conclusions in there for general benefit!
Quote:

> Thanks again,
> Stratos


> > As an afterthought to the above, I don't normally like the idea of
declaring
> > variables public at the top level (the module usually) so that they can
be
> > changed at the lower levels. IMHO it leads to confusing code (and I
should
> > know I've written a ton of confusing code <g>).

> > I've gradually come to the conclusion that where possible it's better to
put
> > the code which needs to access the userform controls and variables
within
> > the userform itself, possibly even passing the form an object (eg a
Range)
> > on which to act. The next best alternative (IMHO) is to use one instance
of
> > the form and just hide it (as in my previous example) after which the
public
> > variables and controls can be accessed. I've usually found it possible
to
> > adopt one of these styles.

> > --
> > Cheers

> > Mick Jennings
> > ~~~~~~~~~~



> > > Sorry to differ - but you are certainly able to have public variables
in
> > > form modules. I just tried this on XL97.

> > > A simple procedure run from a module ...

> > > ---------------
> > > Option Explicit

> > > Public Sub getvar()

> > > UserForm1.Show
> > > MsgBox UserForm1.myInt

> > > End Sub

> > > --------------

> > > And a simple form with a Checkbox and a CommandButton, code as follows
...

> > > --------------

> > > Option Explicit

> > > Public myInt As Integer

> > > Private Sub CheckBox1_Click()

> > > If CheckBox1 Then
> > >     myInt = 10
> > > Else
> > >     myInt = 20
> > > End If

> > > End Sub

> > > Private Sub CommandButton1_Click()

> > > Me.Hide

> > > End Sub

> > > -------------------

> > > The above works fine. The trick is to use the command button to "HIDE"
the
> > > form, not to close it. If the user closes the instance of the form
using
> > the
> > > "X" then the msgbox shows "0". If they use the command button then the
> > > correct value is returned, because the form is still active in memory.
It
> > > does, however, prove that public variables are allowed and accessible
> > within
> > > form modules.

> > > --
> > > Cheers

> > > Mick Jennings
> > > ~~~~~~~~~~



> > > > Hi Harri,

> > > > You can store your variables in public variables defined in standard
> > > modules, or you can store it into named workbook-level constants (
> > > > Workbook("aBook").Names.Add ) or on the spreadsheet, or in external
> > files,
> > > or in the registry, etc.

> > > > You are not allowed of public variables in form modules.

> > > > You may also be interested to know that the following can work in
> > > astandard module as well, and its probably the best way (with a
tradeoff
> > > > in coding effort) to manipulate variables (IMHO)
> > > > ----------------------------------------------

> > > > Dim myVariable_Local As Boolean

> > > > Property Let myVariable(aVar As Boolean)
> > > >     myVariable_Local = aVar
> > > > End Property

> > > > Property Get myVariable() As Boolean
> > > >     myVariable = myVariable_Local
> > > > End Property

> > > > Sub test()
> > > > Module1.myVariable = True
> > > > Debug.Print Module1.myVariable
> > > > End Sub
> > > > -------------------------------------------------

> > > > If you make it Private (i.e. Private Property ...) you can use it as
a
> > > procedure in a form module fully qualifying your syntax (as in the
> > > > example).

> > > > HTH
> > > > Stratos


> > > > > This is from my USERFORM CODE:

> > > > > Public Sub OptionButton2_Change()
> > > > > gameserver = UserForm1.OptionButton2.Value
> > > > > End Sub

> > > > > This is from my MODULE CODE:

> > > > > .....If gameserver = True Then.....

> > > > > How can I store gameserver variable in my USERFORM CODE to use it
> > later
> > > in
> > > > > MODULE CODE???

> > > > > Usually when I declare a variable with Public declaration it can
be
> > used
> > > > > between different modules. But when a variable is declared in
UserForm
> > > Code
> > > > > it is not in use in my modules.

> > > > > I dont want to store the information in excel sheets (cells).

> > > > > --
> > > > > Harri Vesterinen
> > > > > CE-Simulations
> > > > > System development manager

> > > > > mobile. +358 41 461 55 64
> > > > > office. +358 9 406 660
> > > > > fax. +358 9 406 662



Sun, 23 Feb 2003 18:25:43 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. show userform when name is stored in variable

2. Storing Variables 2 Display in UserForm

3. Variable(s) Reset by Showing 2nd UserForm on top 1st UserForm

4. Passing a Variable from one Userform to another Userform

5. Two Questions - Making a userform active / Accessing a userform via a variable

6. Referencing a variable name stored in a variable

7. Userform to store data

8. userform to return value stored in excel book...

9. calling subs stored outside userforms

10. Storing userform data on a network


 
Powered by phpBB® Forum Software © phpBB Group