VBA how to pass userform variables to subroutine

Question

I am trying to use a subroutine call a variable from a userform input. But, even though I can display the userform inputs correctly in the userform by printing it to the sheet, I can't seem to pass it as a public variable to the subroutine. When I call the variables in the subroutine, they are all empty.

I was using this as a guide: Passing variable from Form to Module in VBA

Here is my code:

Public a As String
Public b As Double
Public c As String

Private Sub OKButton_Click()
'from userform
    a = TextBox1.Value
    b = TextBox2.Value
    c = TextBox3.Value

    Cells(1, 1).Value = a
    Cells(2, 1).Value = b
    Cells(3, 1).Value = c
    'this displays the inputs properly
    Unload UserForm1
End Sub

And in the module:

Public Sub Login()
'in module
    UserForm1.Show
    MsgBox (a)
    MsgBox (b)
    MsgBox (c)

End Sub

The Value of a TextBox is always a string – EvR Nov 21 '18 at 15:48 — EvR, Nov 21 '18 at 15:48

score 1 · Accepted Answer · answered Nov 21 '18 at 12:49

1

Do like this. Put your public variable in the module code.

Public a As String
Public b As Double
Public c As String

Public Sub Login()
'in module
    UserForm1.Show
    MsgBox (a)
    MsgBox (b)
    MsgBox (c)

End Sub

answered Nov 21 '18 at 12:49

Dy.Lee

7,527
1
12
14

Hmm I'm getting the same issue whether the public variables are declared in the module or userform or both – abcdefg12345 Nov 21 '18 at 13:14
@abcdefg12345, The public variables must be declared in the module only, then it works well. – Dy.Lee Nov 21 '18 at 14:03
Thanks. I was getting confused because I had a couple of subs on top of the lines calling the public variables, but then I put the lines calling the public variables at the very top of the module and it ended up working. – abcdefg12345 Nov 21 '18 at 15:55

score 1 · Answer 2 · edited Nov 21 '18 at 13:47

I was developing a user application with the same problems as you face now and here is the answer I got there.

Please check the link above because Mat's Mugs explanations are beyond my capabilities to explain the topic. But here is an abbreviation.

Basically what you do is the following. You have three classes: a model, a view and a presenter class. This sounds super complicated but really is not as difficult once you get the hang of it.

The Model

Is a class module where all your data is stored. So instead of declaring a bunch of public varibales you have one big class that stores all data. You can also have multiple model classes and classes as classmembers but for simplicity we only take the mentioned three integers.

Here is an example of a model class: ( put all of it in a class module called model)

   Option Explicit

    ' encapsulated data
    Private Type TModel
        a As Integer
        b As Integer
        c As Integer
    End Type

    Private this As TModel

    ' property get and property let define the way you can interact with data
    Public Property Get a() As String
         a = this.a
    End Property
    Public Property Let a(ByVal value As String)
         this.a = value
    End Property

    Public Property Get b() As String
         b = this.b
    End Property
    Public Property Let b(ByVal value As String)
         this.b = value
    End Property

    Public Property Get c() As String
         c = this.c
    End Property
    Public Property Let c(ByVal value As String)
         this.c = value
    End Property

The View

This is your Userform. But your UserForm is a class again so you but besides all other code you have this code in:

Private Type TView
    M As Model
    IsCancelled As Boolean
    IsBack As Boolean
End Type

Private this As TView

Public Property Get Model() As Model
    Set Model = this.M
End Property

Public Property Set Model(ByVal value As UImodel)
    Set this.M= value
    'Validate
End Property

' This is responsible for not destroying all data you have when you x-out the userform
Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        this.IsCancelled=True
        Cancel = True
        OnCancel
    End If
End Sub

Private Sub OKButton_Click()
    Model.a = TextBox1.value
    Model.b = TextBox2.value
    Model.c = TextBox3.value

    Cells(1, 1).value = Model.a
    Cells(2, 1).value = Model.b
    Cells(3, 1).value = Model.c
    'this displays the inputs properly
    Me.Hide
End Sub

The Presenter

This is a normal module. Where you simple put your code where you use the stuff in. So for your example code like this:

Public Sub Login()
'in module
Dim Ufrm As New UserForm1
Dim M As New Model

    Set Ufrm.Model = M
    Ufrm.Show
    If Ufrm.IsCancelled Then Exit Sub
    Set M = Ufrm.Model

    MsgBox M.a
    MsgBox M.b
    MsgBox M.c
End Sub

VBA how to pass userform variables to subroutine

2 Answers2

Linked