在 Excel VBA 中将两个数组相乘
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22054292/
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 Two Arrays in Excel VBA
提问by Chris2015
I am trying to multiply two arrays placing the value of each iteration into a spreadsheet. Here is what I have so far:
我试图将两个数组相乘，将每次迭代的值放入电子表格中。这是我到目前为止所拥有的：
Sub Test1()
Dim x As Long
Dim myArray
Dim myArrayAdj
myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)
For x = 1 To 1000
Cells(x, x) = myArray * myArrayAdj
Next x
End Sub
When I run this I get a Run-time13 error with the following highlighted:
当我运行它时，我收到一个 Run-time13 错误，其中突出显示了以下内容：
Cells(x, x) = myArray * myArrayAdj
Can someone explain to me where I've gone wrong? Thank you!
有人可以向我解释我哪里出错了吗？谢谢！
采纳答案by ARich
Your have a few issues which I think I've corrected below. One of the things you'll notice about my code is that I use Variantvariables to loop through the arrays instead of identifying the element by number (e.g. myArrayElminstead of myArray(x)). This is just my personal preference.
你有几个问题，我想我已经在下面更正了。关于我的代码，您会注意到的一件事是我使用Variant变量来遍历数组，而不是通过数字标识元素（例如，myArrayElm而不是myArray(x)）。这只是我个人的喜好。
Sub Test1()
Dim x As Long
Dim myArray 'Your first array
Dim myArrayElm 'A variable for the elements in your first array
Dim myArrayAdj 'Your second array
Dim myArrayAdjElm 'A variable for the elements in your second array
'Add values to your arrays
myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)
'Loop through the elements in your first array
For Each myArrayElm In myArray
'Loop through the elements in your second array
For Each myArrayAdjElm In myArrayAdj
x = x + 1
'Multiply the two array elements together
Cells(x, 1) = myArrayElm * myArrayAdjElm
Next myArrayAdjElm
Next myArrayElm
End Sub
This code loops through each element in both arrays, multiplies the two elements, and stores the values in a list beginning in cell A1.
此代码循环遍历两个数组中的每个元素，将这两个元素相乘，并将值存储在以 cell 开头的列表中A1。
Now, if you have a large dataset you're working with, the below example will be more efficient and will finish quicker since it stores the results in another array and then pastes the results to a sheet all at once instead of individually:
现在，如果您正在处理一个大型数据集，下面的示例将更高效并且完成得更快，因为它将结果存储在另一个数组中，然后将结果一次性粘贴到工作表中，而不是单独粘贴：
Option Base 1
Sub Test1()
Dim x As Long
Dim myArray 'Your first array
Dim myArrayElm 'A variable for the elements in your first array
Dim myArrayAdj 'Your second array
Dim myArrayAdjElm 'A variable for the elements in your second array
Dim Results 'An array for your results
Dim r As Range 'Range to store values
'Add values to your arrays
myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)
'Set the size of the results array
ReDim Results(1 To UBound(myArray) * UBound(myArrayAdj))
'Loop through the elements in your first array
For Each myArrayElm In myArray
'Loop through the elements in your second array
For Each myArrayAdjElm In myArrayAdj
x = x + 1
'Multiply the two array elements together
Results(x) = myArrayElm * myArrayAdjElm
Next myArrayAdjElm
Next myArrayElm
'Set the destination range
Set r = Range("A1:A" & UBound(Results))
'Paste results to sheet
r = Application.Transpose(Results)
End Sub
Note the Option Base 1at the top. This just means that all the arrays will now start at element 1 instead of element 0 which is the default.
注意Option Base 1顶部的。这只是意味着所有数组现在将从元素 1 开始，而不是默认的元素 0。
回答by Mike Dinescu
The whole problem stems from your statement right here: "I want to multiply two arrays"
整个问题源于您在这里的陈述：“我想将两个数组相乘”
I assume by that you meant you would like to multiple the individual elements in the two arrays, one by one.
我假设您的意思是您想将两个数组中的单个元素一个一个地多个。
In that case you want to do something this:
在这种情况下，你想做这样的事情：
Cells(x, x) = myArray(x) * myArrayAdj(x)
That said, I'm not sure whether your intention was to store the results of the multiplication in cells on the diagonal of the work sheet or in some other place
也就是说，我不确定您的意图是将乘法结果存储在工作表对角线上的单元格中还是其他地方
If it's the former then Cells(x,x)makes sense but if it's the latter, than you need to be more specific about your expectation in multiplying the two arrays.
如果是前者则Cells(x,x)有意义，但如果是后者，则您需要更具体地了解将两个数组相乘的期望。
