Accessing Global Variables in VBA (Excel)

Question

I've never used global variables in VBA, but I understand global variables are instantiated outside of function/sub declarations?

I have a global (public) variable declared at the top of a module which is then given a value of 0 by a subroutine within the same module.

Option Explicit
Public NumNodes As Integer

Sub Inst_Glob_Vars()
NumNodes = 0
End Sub

This subroutine is called whenever the workbook is opened (sub is called in the "ThisWorkbook" object) which will also instantiate the global variable and set the 0 value.

Option Explicit

Private Sub Workbook_Open()
Call Inst_Glob_Vars
End Sub

I have a button in the excel sheet which, when clicked, will increment this global variable. The definition for this button is in the Sheet1 object.

Private Sub CommandButton2_Click()
'NumNodes = NumNodes + 1
Debug.Print "NumNodes = " & NumNodes 'Debug
End Sub

Do I need to declare the global/public variables in every module/object the variable is used? Every time I click the button, the variable is not incrementing but giving a Null/Blank value when debugging. I am for sure not declaring my global variable correctly but not sure where I'm making mistakes.

Update: Here is the updated command button sub. If I comment out second sub call (Node_Button_Duplication), everything works fine. Chances are it might be that sub which is causing problems...

Private Sub CommandButton2_Click()
Call Channel_Selection_Duplication
Call Node_Button_Duplication
NumNodes = NumNodes + 1
Debug.Print "NumNodes = " & NumNodes 'Debug
End Sub

Both Channel_Selection_Duplication and Node_Button_Duplication are both defined in the same seperate module:

Option Explicit

Public Sub Channel_Selection_Duplication()
'
' Description: Macro which duplicates the 'Channel Usage Selection' columns at a specific cell reference

    Range("Q8:S8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("Q8:S8").Select
    ActiveCell.FormulaR1C1 = "Channel Usage Selection"
    Range("Q8:S52").Select
    Range("Q52").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("Q8:S8").Select
    Selection.Interior.ColorIndex = 36

'NumNodes = NumNodes + 1
'Debug.Print NumNodes
End Sub

Public Sub Node_Button_Duplication()

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Range("Q5").Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementTop -14.25
End Sub

You have to declare it once. You can do it in any module :) Please show us the code. Also show us how are you incrementing the variable? — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-05-15T16:01:41

Paste this in a module

Option Explicit

Public myVar As Long

Paste this in the command button click event in sheet1

Option Explicit

Private Sub CommandButton1_Click()
    myVar = myVar + 1
    MsgBox myVar
End Sub

Now try it.

Also you don't need to set the value to 0 in Workbook_Open event :) It takes the value 0 by default when you open the workbook.

FOLLOWUP

I have a feeling copying and pasting a control element in the spreadsheet somehow resets the variable. I'm currently trying to find a solution... – user1373525 6 mins ago

Yes :) Adding the button recompiles the VBA code and hence the global variables get reset. Use a Temp Sheet to hold the variables. You could also use registry to store that information :) – Siddharth Rout just now

This behaviour is only observed if you click the button twice but not when you execute it in one go. For example

Private Sub CommandButton2_Click()
    NumNodes = NumNodes + 1
    MsgBox NumNodes, vbInformation, "1"

    Node_Button_Duplication

    NumNodes = NumNodes + 1
    MsgBox NumNodes, vbInformation, "2"

    Node_Button_Duplication

    NumNodes = NumNodes + 1
    MsgBox NumNodes, vbInformation, "3"
End Sub

In such a case it will always increment the value. However, the next time you click on the button, you will notice that the variable has been reset.

Accessing Global Variables in VBA (Excel)

1 Answers