在 VBA (Excel) 中访问全局变量

Accessing Global Variables in VBA (Excel)

提问by Ehudz

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

我从未在 VBA 中使用过全局变量，但我知道全局变量是在函数/子声明之外实例化的吗？

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.

我在模块顶部声明了一个全局（公共）变量，然后由同一模块内的子例程将其赋值为 0。

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.

每当打开工作簿时都会调用此子例程（在“ThisWorkbook”对象中调用 sub），它还将实例化全局变量并设置 0 值。

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.

我在 Excel 工作表中有一个按钮，单击该按钮会增加此全局变量。此按钮的定义在 Sheet1 对象中。

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.

我是否需要在使用该变量的每个模块/对象中声明全局/公共变量？每次单击按钮时，该变量不会增加，而是在调试时给出 Null/Blank 值。我肯定没有正确声明我的全局变量，但不确定我在哪里犯了错误。

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...

更新：这是更新的命令按钮子。如果我注释掉第二个子调用 (Node_Button_Duplication)，一切正常。有可能是那个导致问题的子......

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:

Channel_Selection_Duplication 和 Node_Button_Duplication 都定义在同一个单独的模块中：

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

回答by Siddharth Rout

Paste this in a module

将此粘贴到模块中

Option Explicit

Public myVar As Long

Paste this in the command button click event in sheet1

将此粘贴到 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_Openevent :) It takes the value 0 by default when you open the workbook.

此外，您不需要在Workbook_Openevent 中将该值设置为 0 :) 当您打开工作簿时，默认情况下它采用值 0。

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

我有一种感觉，在电子表格中复制和粘贴控制元素会以某种方式重置变量。我目前正在寻找解决方案... – user1373525 6 分钟前

是的 :) 添加按钮会重新编译 VBA 代码，因此全局变量会被重置。使用临时表来保存变量。您也可以使用注册表来存储该信息 :) – Siddharth Rout 刚才

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.

在这种情况下，它将始终增加该值。但是，下次单击该按钮时，您会注意到该变量已被重置。