Variable Passing from FORM to sub procedure 1

[ljsmith91]

I am a new to VBA and have a simple but aggravating to me question.

I am trying to set a variable in a user form when OK is clicked and then have the variable available to the calling sub procedure for use. But the variable always gets dropped. I would prefer to not make it PUBLIC since it shouldn't have to be. My latest code was an attempt at using STATIC but it still gets dropped. What am I missing ?

Here is an example of what I am doing in a simpler block of code:

VBA code

Sub Get_Variable()
form_System.Show
MsgBox " Within VBA the variable is " & Save_VARIABLE
End Sub
**********************************************
User Form form_SYSTEM code

Sub button_OK_Click()
Static save_VARIABLE As String
If ob_01 = True Then
save_VARIABLE = "One"
ElseIf ob_02 = True Then
save_VARIABLE = "Two"
ElseIf ob_03 = True Then
save_VARIABLE = "Three"
Else
MsgBox " You failed to make a choice!!"
Exit Sub
End Ifthe Form the variable is " & save_VARIABLE
Unload form_System
End Sub

The first message box, initiated by the FORM code, gives the variable, the second message box, initiated by VBA procedure, does not.

The manuals are very weak on explaining this.

Thanks for any help. -LJS

 

[Zathras]

Nothing wrong with using global variables as long as you don't over-do it. But in this case, just hide the form instead of unloading it:
[blue]

Option Explicit
Public save_VARIABLE As String

Sub button_OK_Click()
    If ob_01 = True Then
           save_VARIABLE = "One"
    ElseIf ob_02 = True Then
           save_VARIABLE = "Two"
    ElseIf ob_03 = True Then
           save_VARIABLE = "Three"
    Else
           MsgBox " You failed to make a choice!!"
           Exit Sub
    End If
    Me.Hide
End Sub

[/color]
and then qualify the variable name with the form name:
[blue]

Sub Get_Variable()
    form_System.Show
    MsgBox " Within VBA the variable is " & form_System.save_VARIABLE
End Sub

[/color]

 

[ljsmith91]

Zathras,

Thanks.

Can I take it from what you have wrote that STATIC is not a proper declaration to use ? I was reluctant to use PUBLIC because I thought it kept that variable for the life of the application instead of life of the calling procedure and so why get in the habit of tying up memory as small as it may be ?? My preference would be that I only save the variables for the life of the calling procedure. I guess I am trying to establish good early habits as I begin to learn this.

I wish I understood this better. If it is public, then do I need the Form reference in the variable or is that just a precaution?

Thanks again for the info.

LJS

 

[ljsmith91]

It also just learned that Hiding the form saves the form in memory too. Again, would I be creating bad habits by doing it this way ? Isn't there are way of keeping the variables and forms and constants etc for the life of the calling procedure only ??

 

[Zathras]

AFAIK the Public keyword merely allows the variable/function/sub to be visible from some other module outside of the module in which the declaration appears. The Private keyword ensures that the variable/function/sub is ONLY visible inside the current module.

Unless you're working with 100 different forms and thousands of variables (or large arrays), I wouldn't be too concerned about memory in a VBA project. But it doesn't hurt to consider it, either. The public variable in the general code module would obviously retain less memory that the entire form. However, if you plan to show the form more than once, it should be slightly faster to keep it in memory rather than have to allocate memory before each time you show it. -- The classic trade-off is between time and space. You can usually save a little time by expending some memory or you can save memory at the cost of things taking a little longer.

You can actually save all of the memory if the only time you need to reference the variable is immediately after closing the form. Consider this:
[blue]

Sub Get_Variable()
    form_System.Show
    MsgBox " Within VBA the variable is " & form_System.save_VARIABLE
    Unload form_System
    MsgBox " (2) The variable is gone: " & form_System.save_VARIABLE
End Sub

[/color]
I find I learn best by just trying various combinations to see what happens.

 

[ljsmith91]

Thanks Zathras.....it all makes sense. Have a great day !!

LJS