访问VBA中的全局变量（Excel）

Question

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

我有一个全局（公共）变量声明在模块的顶部，然后由同一模块内的子例程赋予值0。每当打开工作簿

Option Explicit 
Public NumNodes As Integer 

Sub Inst_Glob_Vars() 
NumNodes = 0 
End Sub 

该子程序被调用（子被称为在“的ThisWorkbook”对象），这也将实例全局变量和设定值0。

Option Explicit 

Private Sub Workbook_Open() 
Call Inst_Glob_Vars 
End Sub 

我在excel表单中有一个按钮，单击它时会增加这个全局变量。此按钮的定义位于Sheet1对象中。

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

我需要每一个模块中声明全局/公共变量/使用变量对象？每次点击按钮，变量都不会递增，但在调试时会给出一个空/空值。我肯定没有正确地声明我的全局变量，但不知道我在哪里犯错误。

更新：这里是更新的命令按钮子。如果我注释掉第二个子调用（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 

两个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 

Answer 1

一个模块中粘贴此

Option Explicit 

Public myVar As Long 

将此粘贴到工作表1中的命令按钮单击事件中

Option Explicit 

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

现在试试吧。

此外，您不需要在Workbook_Open事件中将值设置为0 :)打开工作簿时默认值为0。

随访

我感觉复制和电子表格中的粘贴的控制元件以某种方式重置变量。我正在试图找到一个解决方案... - user1373525 6分钟前

是的:)添加按钮重新编译VBA代码，因此全局变量得到重置。使用Temp Sheet来保存变量。如果你点击，当你在一个去执行它两次，但不是按钮亚洲时报Siddharth溃败刚才

此行为仅观察到 - 你也可以使用注册表来存储信息:)。例如，

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 

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