Global variable not accessible in different sub?

Question

I declared projname globally at the top of the module 1. It is assigned in the userform, and is successfully accessed in the createWB sub. However, when I go to access it in the addWindow sub also in module 1, it becomes empty (""). I'm unsure of why this is happening because I thought that since the variable was globally declared I should be able to access it in any sub.

Module 1

Option Explicit

Public outputWorkbook As Workbook
Public globalcounter As Integer
Public projname As String
Public projnum As String

createWB()
    Dim uf2 As New UserForm2
    uf2.Show

    Set outputWorkbook = Workbooks.Add(xlWBATWorksheet)
    outputWorkbook.SaveAs Filename:=Environ("userprofile") & "\Desktop\" & 
    Replace(projname, " ", "") & ".xlsx"

    outputWorkbook.Activate

    Range("B3") = projname
    Range("B4") = projnum
End Sub

addWindow()
    Workbooks(Replace(projname, " ", "") + ".xlsx").Activate
End Sub

Userform Code

Public Sub CommandButton1_Click()
    projname = Me.TextBox1.Text
    projnum = Me.TextBox2.Text
    Me.Hide
End Sub

Cells B3 and B4 are assigned the correct value, but the addWindow() line causes a subscript out of range error. When I test it with Debug.Print, I see that projname = "". I also simply tried outputWorkbook.Activate, which did not work either.

It sounds like projname is scoped to module 1 and not global - can you show your declaration please? — GinjaNinja
– GinjaNinja, Commented Jul 23, 2019 at 12:56
I think most important is how/ where are you calling addWindow(). If you are calling it outside of your whole process then projName will be "". If you include the caller when you run creatWB for example, your projName will be stored and you will be able to access it. — DDV
– DDV, Commented Jul 23, 2019 at 13:23
Yes, the problem exists because it seems you are calling them as independent processes. If you include Call addWindow to your createWb() you will see that you won't get that error because that information is stored based on the createWB() routine. Else it is just returning the default state as a "" — DDV
– DDV, Commented Jul 23, 2019 at 13:26

Robert Todar · Accepted Answer · 2019-08-06 22:21:05Z

Avoid Global Pollution

Unless there is a really good reason to use them, try to avoid global variables. We want to avoid polluting the global namespace. Captain Planet warned us of that.

Instead, try passing your parameters through your various methods as they are needed. This helps prevent errors, makes your code easier to follow, and utilizes composition.

Using your userform to store and expose your properties

Try to instantiate your userform using a With statement so that you have a captured instance of it where you have access to its various properties that you expose. In your case ProjectName and ProjectNumber.

Additionally, there should be a property to check if the userform was canceled or the X button was pressed.

You userform would look something like this:

Option Explicit

Private cancelled As Boolean

Public Property Get ProjectName() As String
    ProjectName = TextBox1.Value
End Property

Public Property Get ProjectNumber() As Long
    ProjectNumber = TextBox2.Value
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelled
End Property

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

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

Private Sub OnCancel()
    cancelled = True
    Hide
End Sub

Instantiating the userform

Here is the example of now calling your userform (P.S. Change the name from Userform2). Notice we are capturing our instance of your userform using the With block. Within this block, we have access to the properties we exposed: ProjectName, ProjectNumber, IsCancelled.

Private Sub createWB()
    With New UserForm2
        .Show
        If Not .IsCancelled Then
            ' Do neccessaray steps here...

            ' You have access to ProjectName and Project number.
            ' Pass this to your addWindow method.
            addWindow .ProjectName

        End If
    End With
End Sub

The ProjectName now can be accessed from your userform and passed as a parameter to you addWindow method.

Private Sub addWindow(ByVal projName As String)
    Workbooks(Replace(projName, " ", "") + ".xlsx").Activate
End Sub

For more information on using userforms in this way see this helpful Rubberduck Blog Post.

Alvaro CC · Accepted Answer · 2019-07-23 13:20:29Z

0

could you try using Module1 as prefix? , jus like in this code

 Public Sub CommandButton1_Click()
        Module1.projname = Me.TextBox1.Text
        Module1.projnum = Me.TextBox2.Text
        Me.Hide
    End Sub

answered Jul 23, 2019 at 13:20

Alvaro CC

1426 bronze badges

Collectives™ on Stack Overflow

Global variable not accessible in different sub?

2 Answers 2

Avoid Global Pollution

Using your userform to store and expose your properties

Instantiating the userform

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Avoid Global Pollution

Using your userform to store and expose your properties

Instantiating the userform

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related