Excel, Where to store variables from UserForm?

Where to store variables from UserForm?

Author	Message
Harri Vesterine #1 / 10	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

Ray Costanz #2 / 10	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

Stratos Malasioti #3 / 10	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

Tom Ogilv #4 / 10	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

Mick Jenning #5 / 10	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 - Hide quoted text - - 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

Mick Jenning #6 / 10	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

Stratos Malasioti #7 / 10	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

Stratos Malasioti #8 / 10	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

Tim Zyc #9 / 10	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

Mick Jenning #10 / 10	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

Page 1 of 1

[ 10 post ]