excel - 声明公共(public)变量并赋值 - VBA

Question

有没有办法声明一个公共(public)变量并赋值？

这是一个完美运行的子代码:

Option Explicit

Sub test()


    Dim arrStatistics As Variant

    'Set arrStatistics
    arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
                , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

End Sub

这就是声明变量作为 Public 的方式。 :

Public arrStatistics As Variant

但是当我使用它时，我收到一个错误:

Public arrStatistics As Variant

'Set arrStatistics
arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
                , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

Answer 1

不，您必须在过程之外声明您的变量 public

Option Explicit

Public arrStatistics As Variant

然后用程序初始化内容。

Public Sub InitPublicVariabels()

    arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
            , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

End Sub

但是，与其在代码中包含大量数据，不如将数据放入隐藏的工作表中，您可以轻松地将其读入数组。编辑数据更方便，分离代码和数据的好习惯。