Is there a way to use local variables across modules?

6
votes

I have a VBA project with a Module and a Userform. I want to use some variables from my module in my Userform.

Is it necessary that I declare these variables globally? I don't like global variables because they stay in memory after the code's execution, and I have to reset these variables one-by-one at the beginning of my macro. I can't use the keyword End to clear the variables from memory because I need to remember one or two of these variables. How do people usually do?

Edit: is there a way to pass a variable from the Userform to the module without creating a global variable?

2
vbaexcel

2 Answers

6
votes

Create a Public Property in your userform and pass the variable into the form using the property. Useforms are just class module except that they have a user interface component. Example: In your userform's code module

Option Explicit

Private msLocalVar As String

Public Property Get LocalVar() As String
    LocalVar = msLocalVar
End Property

Public Property Let LocalVar(sLocalVar As String)
    msLocalVar = sLocalVar
End Property

Private Sub CommandButton1_Click()

    Me.LocalVar = Me.LocalVar & " more strings attached"

    Me.Hide

End Sub

And in a standard module

Sub ShowForm()

    Dim sLocalVar As String
    Dim ufUserForm1 As UserForm1

    sLocalVar = "Some string"

    Set ufUserForm1 = New UserForm1
    ufUserForm1.LocalVar = sLocalVar 'pass in variable
    ufUserForm1.Show

    'This executes after the .Hide in the form
    Debug.Print ufUserForm1.LocalVar

    'Close out the form - previously just hidden
    Unload ufUserForm1

End Sub
1
votes

One way you can do this would be to declare public variables like this:

Public strvar as String
Sub Abfrage()
'Your Code
End Sub

But if you don't prefer this way, you could insert a new Excel sheet, set the properties of the new sheet to "2 - xlSheetVeryHidden" and you can write your values in the cells of that sheet and read them in from your UserForm.

So you will have no global variables, but all the values are on that sheet. Then, you can take out the values from that sheet if you need to.