将数组与标量相乘并在 VBA 中添加
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12979034/
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
Multiplying arrays with scalars and adding in VBA
提问by user1759984
I have two columns of data in an excel sheet that are equal in size and both only contain numbers.
我在 Excel 工作表中有两列大小相同的数据，并且都只包含数字。
I'm trying to write a macros which will put both of these sets into arrays, then perform a calculation on them. Specifically ArrayA + 3*ArrayBthen put the result back into the worksheet in a new column. Below is the code I have so far.
我正在尝试编写一个宏，将这两个集合放入数组中，然后对它们执行计算。具体来说，ArrayA + 3*ArrayB然后将结果放回工作表的新列中。下面是我到目前为止的代码。
Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC As Variant
ArrayA = Worksheets("Sheet1").Range("A1:A5")
ArrayB = Worksheets("Sheet1").Range("B1:B5")
'this is where things go bad
ArrayC = ArrayA + 3 * ArrayB
回答by danielpiestrak
YOu need to first make an array of values. Then be sure that you'r elooping htrough the arrays.
您需要首先创建一个值数组。然后确保您正在通过数组进行循环。
Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC() As Variant
ArrayA = Worksheets("Sheet1").Range("A1:A5").Value
ArrayB = Worksheets("Sheet1").Range("B1:B5").Value
'this is where things go bad
'ArrayC = ArrayA + 3 * ArrayB
'manually make array C the same size as array A
ReDim ArrayC(LBound(ArrayA, 1) To UBound(ArrayA, 1), 1 To 1)
Dim i As Long 'variable to count which section of the array we are on
' loop through each spot int he array and perform the math
For i = LBound(ArrayA, 1) To UBound(ArrayA, 1)
ArrayC(i, 1) = ArrayA(i, 1) + 3 * ArrayB(i, 1)
Next i
Worksheets("Sheet1").Range("C1:C5").Value = ArrayC
NoteThe above code is untested so I may have a typo in it somewhere. Also, you may need to declare ArrayC's data type instead of variant when using redim. I am unable to test at the moment as i'm answering from my mobile phone.
注意上面的代码未经测试，所以我可能在某处有一个错字。此外，在使用 redim 时，您可能需要声明 ArrayC 的数据类型而不是变量。我现在无法测试，因为我正在用手机接听。
