Excel VBA - Public variable not passing between modules

Question

I am trying to pass a publicly declared variable's value from a userform back to a module where the code initially started. I can print the value of the variable in the userform, but it does not persist when the code jumps back to the starting module. Option Explicit is declared on both sides, and I declared the single variable as well (ignore the var2 - var6 as they are not in use until I fix this singular issue).

The code path starts in the first section below, jumps to the userform at "StatSelection.Show" and then jumps back at the end of the userform sub. The first debug.print provides the variable, but the second one is blank..

The main code is:

Option Explicit

Public var1 As String

Sub NoNameGame2FirstRoundResults()
'
' NoNameGame Macro
'
'
'Load Round 1 file
    Dim r1Name As Variant
    Dim nngr1r As Workbook
    ChDir "D:\Users\stefan.bagnato\Desktop\No Name Game"
    r1Name = Application.GetOpenFilename
    If r1Name <> False Then
        Set nngr1r = Workbooks.Open(r1Name)
    End If

'Load previous week's file
    Dim r1rName As Variant
    Dim wps As Workbook
    ChDir "D:\Users\stefan.bagnato\Desktop\Weekly Performance Summary"
    r1rName = Application.GetOpenFilename
    If r1rName <> False Then
        Set wps = Workbooks.Open(r1rName)
    End If

'Create radio buttons to select week 1 stat
    StatSelection.Show

'Parse Column B for names, and paste corresponding data in column C
nngr1r.Activate

Debug.Print var1

ActiveWorkbook.Worksheets("Sheet1").Columns(2).Find("Adam").Offset(N, 1) = var1

End Sub

The userform is:

    Option Explicit

Public var1 As String

Sub OptionButton1_Click()
    Dim wps As Workbook, x As String
    For Each wps In Workbooks
    If wps.name <> ThisWorkbook.name Then x = wps.name
    Next wps
    Workbooks(x).Activate

    var1 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F4")), "hh:mm:ss")
    var2 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F5")), "hh:mm:ss")
    var3 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F6")), "hh:mm:ss")
    var4 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F7")), "hh:mm:ss")
    var5 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F9")), "hh:mm:ss")
    var6 = Format((ActiveWorkbook.Worksheets("Sheet1").Range("F10")), "hh:mm:ss")


    Debug.Print var1

    Me.Hide
End Sub

Subodh Tiwari sktneer · Accepted Answer · 2017-05-24 16:08:22Z

4

Remove the Public variable declaration from the UserForm Module and declare it on a Standard Module only.

answered May 24, 2017 at 16:08

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Subodh Tiwari sktneer Over a year ago

@sbagnato Glad it worked. Please take a minute to accept the answer to mark your question as Solved.

jkpieterse Over a year ago

Better yet, only declare var1 in Userform and use like so: With StartSelection .Show Msgbox .Var1 ENd With

Mathieu Guindon Over a year ago

@jkpieterse not really much better, still works off the default instance, therefore will hit the exact same issues/bugs as without that With block.

jkpieterse Over a year ago

See, never assume and then post things :-) You're right of course.

Collectives™ on Stack Overflow

Excel VBA - Public variable not passing between modules

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related