在 VBA Excel 中的不同 Subs 中引用相同的数组

Referencing the same array in different Subs in VBA Excel

提问by DeeWBee

I have a function that fills a certain array with cell values depending on which OptionButton is selected. How would I reference those same arrays in a seperate function which would feed those values back into the cells? Here is my (working) code so far.

我有一个函数，它根据选择的 OptionButton 用单元格值填充某个数组。我将如何在单独的函数中引用这些相同的数组，该函数会将这些值反馈回单元格？到目前为止，这是我的（工作）代码。

Private Sub CommandButton1_Click()
Dim wave1Array(0 To 30) As String
Dim wave2Array(0 To 30) As String
Dim wave3Array(0 To 30) As String
Dim wave4Array(0 To 30) As String
Dim wave5Array(0 To 30) As String
Dim rng As Range
Dim cell As Range
Dim counter As Long

Set rng = Range("B2", "AF2")
counter = 0

If OptionButton6.Value = True Then
    For Each cell In rng
    wave1Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton7.Value = True Then
    For Each cell In rng
    wave2Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton8.Value = True Then
    For Each cell In rng
    wave3Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton9.Value = True Then
    For Each cell In rng
    wave4Array(counter) = cell.Value
    counter = counter + 1
Next cell
ElseIf OptionButton10.Value = True Then
    For Each cell In rng
    wave5Array(counter) = cell.Value
    counter = counter + 1
Next cell
End If

End Sub

回答by David Zemens

You have a few different options that I can think of.

你有几个我能想到的不同选择。

As others have mentioned, make a module-level variable(s) as needed. These declarations should go in the same code module as your form controls. If the form controls are on a userform, then they should be declared in the form's code module, not a "standard" module.

正如其他人所提到的，根据需要创建一个模块级变量。这些声明应与表单控件位于同一代码模块中。如果表单控件在用户表单上，那么它们应该在表单的代码模块中声明，而不是“标准”模块。

'-------------------------------- all in the same code module -------------
Option Explicit
Dim myVariable as String

Private Sub CommandButton1_Click()

    myVariable = "Hello, world!"

End Sub

Private Sub CommandButton2_Click()

    msgBox myVariable

End Sub
'------------------------------- end of this example ----------------------

Public/GLobal variable may be an option but I recall there are some limitations using these with UserForms, and since I'm not sure if you're using a UserForm, I won't recommend that.

公共/全局变量可能是一个选项，但我记得在用户窗体中使用这些有一些限制，而且由于我不确定您是否使用用户窗体，因此我不建议这样做。

A third option would be to pass the arguments from one procedure to another, but that usually only works with "chained" procedures/functions, like when one function calls another function and that does not seem to be what you're doing at all.

第三种选择是将参数从一个过程传递到另一个过程，但这通常只适用于“链式”过程/函数，例如当一个函数调用另一个函数时，这似乎根本不是你在做什么。

For your specific case:

对于您的具体情况：

You can also streamline your code to avoid using the counterand cellvariables, using direct range-to-array assignment.

您还可以使用直接范围到数组赋值来简化代码以避免使用counter和cell变量。

'Module-level array variables, accessible by other procedures in this module:
Dim wave1Array()
Dim wave2Array()
Dim wave3Array()
Dim wave4Array()
Dim wave5Array()
Dim wave6Array()

Private Sub CommandButton1_Click()

Dim rng As Range
Dim arr() 

Set rng = Range("B2", "AF2")
'## Converts the row to an array (0 to 30)
arr = Application.Transpose(Application.Transpose(rng.Value))

'## Assigns the array from above to one of the module-level array variables:

If OptionButton6.Value = True Then wave1Array = arr
If OptionButton7.Value = True Then wave2Array = arr
If OptionButton8.Value = True Then wave3Array = arr
If OptionButton9.Value = True Then wave4Array = arr
If OptionButton10.Value = True Then wave5Array = arr
If OptionButton11.Value = True Then wave6Array = arr

End Sub

Notethat to do this, you will have to declare them as variant arrays, since a range of cells .Valueis a variant type (cells can contain error values which I believe will fail if you try to assign to a string array).

请注意，要做到这一点，您必须将它们声明为变体数组，因为一系列单元格.Value是变体类型（单元格可能包含错误值，如果您尝试分配给字符串数组，我相信这些值会失败）。

IFyou must use strict String arrays, then you will need to use the counterand celliteration.

如果您必须使用严格的字符串数组，那么您将需要使用counter和cell迭代。