Private Sub - transport value to parent Sub

[DonAndress]

Hello.

I have a code to run a UserForm, insinde I have some code that gives me a variable (it's inside a Private Sub, each Sub which belongs to UserForm is automatically Private).
I could store this variable in a call in workbook but maybe there is a way to transport this value back to parent Sub (from where this UserForm was previously run), say after clicking on a button?

Right now when I get to Unload Me part (this is run after clicking on a button) and the code goes back from UserForm to the original Sub, my variable disappears.

 

[Jaafar Tribak]

Not sure what you are trying to achieve but as you know, a variable declared inside a procedure(Private or Public) dies after the procedure exits .. One would normally declare a (Public) variable in a standard module so it persists after the userform is unloaded
What is the variable type and what does it hold ie: Text, number or an object reference ? there may be workarounds depending on what the variable stores

 

[mikerickson]

You could write a Public Function in the user form code module and have the OK button hide the UF rather than unload it.
(This example returns the value the user entered into a text box, you can alter it to return the value for your situation.)

' in userform's code module

Private Sub butOK_Click()
    Me.Hide
End Sub

Public Function Value() As String
    Me.Show
    Value = Userform1.TextBox1.Text
    Unload UserForm1
End Function

This would be used (in a normal module) like

MsgBox UserForm1.Value

 

[DonAndress]

Thanks guys for your help.

@Jaafar
The values is a number and a text string.


@Mike
Does this method release memory of UserForm after the Sub is done?
I gues when I only hide the UserForm it resides in the memory the whole time macro is runing right?

 

[mikerickson]

A macro in a normal module calls the function, Value.

Value shows the user form
The user presses OK and code execution returns to Value.
The last line of Value is Unload Userform1.

The value is returned to the macro in a normal module.


Userform1 has been unloaded and doesn't reside in memory any more.


(Note that in Value, one should not use the keyword Me after the .Show instruction)