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, Commented May 15, 2012 at 15:57

Siddharth Rout · Accepted Answer · 2012-05-15 18:59:59Z

4

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.

edited May 15, 2012 at 18:59

answered May 15, 2012 at 16:01

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

7 Comments

Ehudz Over a year ago

With some additions, I am now able to increment the value once (one click of the button). When I press the button again, the value seems to keep resetting. It seems maybe I'm instantiating the value repeatedly for each time I press the button?

Siddharth Rout Over a year ago

May I see your workbook for a faster resolution? If yes, then please upload it in www.wikisend.com and share the link here :)

Ehudz Over a year ago

Ok, when I reduce the problem and only have the counter increment in the button click sub, everything works. The interesting thing is, when I have other functions being called in the button click sub, the incrementing doesn't work any longer.

Siddharth Rout Over a year ago

Can you post the entire code of button click event in your above question?

Siddharth Rout Over a year 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 :)

|

Collectives™ on Stack Overflow

Accessing Global Variables in VBA (Excel)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related