Use Variable from Useform in different Module

Question

I couldn't quite find what I'm looking for but maybe you can help me anyway.

My problem is that I have a userform where the user has to make an input. I want to store that input and use it later in a different module i.e. paste it into a cell. The simple solution should be to just make it a public variable, but for some reason it won't work. Here is the code I tried to use:

Userform:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()

    VarBezeichnungReifenliste = TextBox1.Value
    Call Übertragen

End Sub

Private Sub CommandButton2_Click()

    Unload Me

End Sub

Module:

Option Explicit

Public Sub Übertragen()

  Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste

End Sub

The error message says the variable is not declared (VarBezeichnungReifenliste) so i guess I didn't declare it publicly enough?

The userform itself is opened via a simple button on the worksheet using Userform1.Show. So nothing fancy here.

Move Public VarBezeichnungReifenliste As String to top of the other sub (if it's in a normal module). — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 7, 2018 at 13:57
Also just noticed - your Private Sub CommandButton2_Click() is inside the Private Sub CommandButton3_Click() procedure. It shouldn't compile with an Expected End Sub error. Move the second End Sub to above Private Sub CommandButton2_Click(). — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 7, 2018 at 14:02
Sorry - meant move it to the top of the other module not sub. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 7, 2018 at 14:36

VBasic2008 · Accepted Answer · 2018-12-07 17:15:02Z

Publicly Enough

Solution1

UserForm1:

Option Explicit

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Module1.Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Module1:

Option Explicit

Public VarBezeichnungReifenliste As String

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste
End Sub

Conclusion

Just move the variable declaration

Public VarBezeichnungReifenliste As String

to a 'normal' module.

Solution2

UserForm1:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Module1.Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Module1:

Option Explicit

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = UserForm1.VarBezeichnungReifenliste
    Worksheets("XY").Cells(1, 1).Select
End Sub

Conclusion

Just use

Worksheets("XY").Cells(1, 1).Value = UserForm1.VarBezeichnungReifenliste

instead of

Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste

in Module1.

Solution3

UserForm1:

Option Explicit

Public VarBezeichnungReifenliste As String

Private Sub CommandButton3_Click()
    VarBezeichnungReifenliste = TextBox1.Value
    Übertragen
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Sub Übertragen()
    Worksheets("XY").Cells(1, 1).Value = VarBezeichnungReifenliste
End Sub

Conclusion

Move everything into UserForm1.

Josh Eller · Accepted Answer · 2018-12-07 14:04:56Z

0

VBA is weird about storing variables over the long-term. As a general rule of thumb, if you're able to interact with Excel workbooks in between a variable being saved and when you need to get the value, you can't count on that variable still holding its value.

The safest way to get around this is to just store your value in a cell of a hidden worksheet, and read it from there when you need it.

answered Dec 7, 2018 at 14:04

Josh Eller

2,0859 silver badges11 bronze badges

5 Comments

Darren Bartrup-Cook Over a year ago

I'd disagree with that. It's a case of knowing when you're variables will go out of scope. If you want to save values between closing and reopening the workbook then store them on a hidden sheet (there's also some other way that Chip Pearson explains on his site, but can't remember it now).

Darren Bartrup-Cook Over a year ago

I'm looking for the documentation on it, but I'm pretty sure all variables declared in a form are Private so aren't available to other modules.

Josh Eller Over a year ago

@DarrenBartrup-Cook Open a VBA module, set a public module-level variable to a value, save the workbook, then view the value of your variable. When I do this, the value is reset. It's not just opening or closing a workbook that'll reset variables, just saving a workbook will do it too. I believe auto-save also resets it, which is why you can't rely on variables holding their values long-term.

Darren Bartrup-Cook Over a year ago

It depends on the scope & lifetime of the variable. In ThisWorkbook add Public MyVar As String at the top of the module, in the Workbook_Open event set a value to MyVar, in the AfterSave event add a msgbox that displays the value of MyVar - the value is still there after a save. Scope Of Variables And Procedures & Lifetime of Variables

T.M. Over a year ago

Further citation: "Public procedures, variables, and constants defined in other than standard or class modules, such as form modules or report modules, are not available to referencing projects, because these modules are private to the project in which they reside." See Understanding scope and visibility

Collectives™ on Stack Overflow

Use Variable from Useform in different Module

2 Answers 2

Publicly Enough

Solution1

Solution2

Solution3

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Publicly Enough

Solution1

Solution2

Solution3

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related