arrays - 如何在 VBA 中一次为数组的某些元素分配相同的值？

Question

我很想知道如何在 VBA 中一次为数组的某些元素、行或列分配相同的值？例如，我有一个像这样的 3x3 数组( MyArray )

1   2   3
4   5   6
7   8   9

我需要更改元素的值 我的数组(1,1) , 我的数组(2,2) , 我的数组(1,3) , 和 我的数组(3,2)与 11 或第 2 行的值与 13。现在，如何通过单个语句来执行这些操作？我想是这样的:
Union(MyArray(1,1), MyArray(2,2), MyArray(1,3), MyArray(3,2)) = 11
和

MyArray(2,1 : 2,3) = 13

可以工作，但发生了错误。我处理这件事的方法是将数组的数据放在未使用的工作表上，更改那里的值，然后将工作表中的值读回数组中，如 this method .使用上面的示例，然后我使用以下代码:

Range("A1:C3") = MyArray: Range("A1,B2,C1,B3") = 11: MyArray = Range("A1:C3")

和

Range("A1:C3") = MyArray: Range("A2:C2") = 13: MyArray = Range("A1:C3")

在不使用工作表或循环的情况下还有其他选择吗？

Answer 1

VBA 本身不支持这样的东西。只有在一个范围内才有可能。

理论上……

你可以用 Evaluate 来做这样的事情。以非常有限的方式。展示一些仅更改一行/列或二维数组的特定值的示例:

Dim MyArray() As Variant

Public Function MyArrayOut() As Variant
  MyArrayOut = MyArray
End Function

Sub testing()
  'we are using a 5x4 (4 columns / 5 rows) array
  ReDim MyArray(0 To 4, 0 To 3) As Variant 'also 1 to 5 and 1 to 4 are possible.... doesn't matter

  Dim i As Long, j As Long 'input some numbers
  For i = 0 To 4
    For j = 0 To 3
      MyArray(i, j) = i + 5 * j + 1
    Next
  Next

  [A1:D5].Value2 = MyArray
  Stop ' check the sheet -> should be 1 to 20

  'lets add 11 to the second row
  MyArray = Evaluate("=IF({0;1;0;0;0},MyArrayOut()+11,MyArrayOut())")

  [A1:D5].Value2 = MyArray
  Stop ' check again. row 2 should be 13, 18, 23, 28

  'set only 1,1 ; 2,2 ; 4,2 and 5,4 to 0. just keep in mind that using math will fail if ANY value is not numeric (or empty)
  MyArray = Evaluate("=MyArrayOut()*{0,1,1,1;1,0,1,1;1,1,1,1;1,0,1,1;1,1,1,0}")

  [A1:D5].Value2 = MyArray
  Stop 'A1, B2, B4, D5 all should be 0

  'set column 3 to "abc"

  MyArray = Evaluate("=IF({0,0,1,0},""abc"",MyArrayOut())")

  [A1:D5].Value2 = MyArray
  Stop ' check again. column C should be just abc

End Sub

使用有效的 IF -statement 可以做很多事情，但是您需要对其进行硬编码，或者使用非常复杂的算法来“扼杀”“不循环”的想法。

此外，这将占用更多资源，并且总是比直接执行要慢。您还需要一个额外的函数来将数组带入评估中。

仍然有一种情况对我来说是可用的:通过 INDEX 提取一行/列.请记住，输出仍然是一个二维数组，可以通过 Application.Transpose 取消。 -诡计。它可用的原因只是事实，即不需要知道数组的大小。这样就可以直接通过 extrVar = Evaluate("=INDEX(MyArrayOut(),2,)")提取.而 2 可以通过不同的变量设置。这样你就不需要 ReDim新变量(在本例中为 extrVar)。它只需要变体。也就是说，使用 Application.Index您也可以在不使用 Evaluate 的情况下实现此目的. (这也使用更少的资源并且速度更快)

我希望您对可以使用评估来完成这项工作的方式有所了解。

尽管如此，VBA 本身并没有提供任何方法来通过语法本身一步操作数组的多个内容。 (至少不能以您可以用于这种情况的方式)

编辑
我差点忘了:将变量中的所有值设置为相同的值，例如 MyArray = Evaluate("=IF(MyArrayOut()="""",5,5)")将所有设置为 5 或 MyArray = Evaluate("=IF(MyArrayOut()="""",""abc"",""abc"")")只包含“abc”。要清空它，您可以使用 MyArray = Evaluate("=IF(MyArrayOut()="""","""","""")")但是一个简单的 ReDim没有 Preserve也可以在一行中完成。

编辑 2

为您的 Range("A1:C3") = MyArray: Range("A1,B2,C1,B3") = 11: MyArray = Range("A1:C3")例如，您可以将其用作自己的函数的基础，例如:

Public Sub multiSetSame(ByRef arr As Variant, ByVal val As Variant, ParamArray str() As Variant)
  Dim runner As Variant
  For Each runner In str
    arr(Split(runner, ",")(0), Split(runner, ",")(1)) = val
  Next
End Sub

Sub test()
  Dim MyArray(1 To 3, 1 To 4) As Long

  multiSetSame MyArray, 11, "1,1", "2,2", "1,3", "3,2"

  [A1:D3].Value2 = MyArray
End Sub

而且对于这个例子，虽然我不知道如何评估要更改的值，但最好在评估时直接更改它，或者如果它们始终相同，只需编写一个子程序来完成这项工作所有的值(value)观。任何“解决方法”只会减慢整个过程。

如果您仍有任何问题，请尽管提问；)