vba 如何添加数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23547033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add arrays?
提问by user3617237
I have the following problem in Excel while calculating through a loop: I need a variable (Destination Variable) that sequentially stores the results produced after each loop has been completed (avoiding the use of circular references) that would look like this:
我在通过循环计算时在 Excel 中遇到以下问题：我需要一个变量（目标变量），它按顺序存储每个循环完成后产生的结果（避免使用循环引用），如下所示：
'Let's call it "origin" variable in the worksheet
Origin Variable (50 x 50 array)
1 2 4
2 3 4
2 2 3
'Let's call it "destination" variable in the worksheet
Destination Variable (50 x 50 array)
1 1 1
1 1 1
1 1 1
After each loop, I'd need the macro to perform the following code:
每次循环后，我需要宏来执行以下代码：
range("destination").value = range("destination").value + range("origin").value
So that the destination variable would look like this after the current loop:
这样目标变量在当前循环之后看起来像这样：
Destination Variable
2 3 5
3 4 5
3 3 4
However, Excel does not allow me to perform the previous function.
Does anyone have an answer how this could be solved?
但是，Excel 不允许我执行以前的功能。
有没有人知道如何解决这个问题？
回答by S Meaden
Quite easy. I did this by recording as macro and tidying.
很简单。我通过录制为宏和整理来做到这一点。
Sub Macro1()
Range("origin").Copy
Range("destination").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
回答by David Zemens
I like @S Meaden's answer, it is simple and I had not thought of that. And it certainly works for this purpose.
我喜欢@S Meaden 的回答，很简单，我没想到。它当然适用于此目的。
You could also do simple iteration. IN the example below I add two different ranges and put them in a thirdrange, but this could be re-worked for your needs pretty easily, or it is another option if you ever need to add ranges to anotherrange:
你也可以做简单的迭代。在下面的示例中，我添加了两个不同的范围并将它们放在第三个范围中，但这可以很容易地根据您的需要重新工作，或者如果您需要将范围添加到另一个范围，这是另一种选择：
Sub AddArrays()
Dim rng1 As Range
Dim rng2 As Range
Dim rngSum As Range
Dim arr1 As Variant
Dim arr2 As Variant
Dim arrSum As Variant
Set rng1 = Range("A1:C7") '## Modify as needed
Set rng2 = Range("F1:H7") '## Modify as needed
Set rngSum = Range("K1:M7") '## Modify as needed
'Raises an error, Type Mismatch
'rngSum.Value = rng1.Value + rng2.Value
arr1 = rng1.Value
arr2 = rng2.Value
arrSum = rngSum.Value
Dim x As Integer, y As Integer
For x = LBound(arr1, 1) To UBound(arr1, 1)
For y = LBound(arr1, 2) To UBound(arr1, 2)
arrSum(x, y) = arr1(x, y) + arr2(x, y)
Next
Next
'Print result to sheet
rngSum.Value = arrSum
End Sub
