vba 从单元格内将范围传递给自定义函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3018446/
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
Pass a range into a custom function from within a cell
提问by Luis
Hi I'm using VBA in Excel and need to pass in the values from two ranges into a custom function from within a cell's formula. The function looks like this:
嗨，我在 Excel 中使用 VBA，需要将两个范围内的值从单元格的公式中传递到自定义函数中。该函数如下所示：
Public Function multByElement(range1 As String, range2 As String) As Variant
Dim arr1() As Variant, arr2() As Variant
arr1 = Range(range1).value
arr2 = Range(range2).value
If UBound(arr1) = UBound(arr2) Then
Dim arrayA() As Variant
ReDim arrayA(LBound(arr1) To UBound(arr1))
For i = LBound(arr1) To UBound(arr1)
arrayA(i) = arr1(i) * arr2(i)
Next i
multByElement = arrayA
End If
End Function
As you can see, I'm trying to pass the string representation of the ranges. In the debugger I can see that they are properly passed in and the first visible problem occurs when it tries to read arr1(i) and shows as "subscript out of range". I have also tried passing in the range itself (ie range1 as Range...) but with no success.
如您所见，我正在尝试传递范围的字符串表示形式。在调试器中，我可以看到它们被正确传入，并且当它尝试读取 arr1(i) 并显示为“下标超出范围”时出现第一个可见问题。我也尝试过传入范围本身（即 range1 作为 Range...），但没有成功。
My best suspicion was that it has to do with the Active Sheet since it was called from a different sheet from the one with the formula (the sheet name is part of the string) but that was dispelled since I tried it both from within the same sheet and by specifying the sheet in the code.
我最好的怀疑是它与活动表有关，因为它是从与公式不同的工作表调用的（工作表名称是字符串的一部分），但由于我在同一个工作表中尝试了它，所以它被消除了sheet 并通过在代码中指定工作表。
BTW, the formula in the cell looks like this:
顺便说一句，单元格中的公式如下所示：
=AVERAGE(multByElement("A1:A3","B1:B3"))
=AVERAGE(multByElement("A1:A3","B1:B3"))
or
或者
=AVERAGE(multByElement("My Sheet1!A1:A3","My Sheet1!B1:B3"))
=AVERAGE(multByElement("My Sheet1!A1:A3","My Sheet1!B1:B3"))
for when I call it from a different sheet.
因为当我从不同的工作表调用它时。
采纳答案by jtolle
First, see the comment Remou left, since that's really what you should be doing here. You shouldn't need VBA at all to get an element-wise multiplication of two arrays.
首先，请参阅 Remou 留下的评论，因为这正是您在这里应该做的。您根本不需要 VBA 来获得两个数组的元素乘法。
Second, if you want to work with Ranges, you can do that by declaring your function arguments to be of type Range. So you could have
其次，如果您想使用 Ranges，您可以通过将函数参数声明为 Range 类型来实现。所以你可以有
Public Function multByElement(range1 As Range, range2 As Range)
and not need to resolve strings to range references yourself. Using strings prevents Excel from updating references as things get moved around in your worksheet.
并且不需要自己解析字符串来范围引用。使用字符串可以防止 Excel 在工作表中移动时更新引用。
Finally, the reason why your function fails the way it does is because the array you get from taking the 'Value' of a multi-cell Range is two-dimensional, and you'd need to acces its elements with two indices. Since it looks like you're intending to (element-wise) multiply two vectors, you would do either
最后，您的函数失败的原因是因为您从多单元格范围的“值”中获得的数组是二维的，您需要使用两个索引访问其元素。由于看起来您打算（按元素）乘以两个向量，因此您可以执行以下任一操作
arrayA(i) = arr1(i,1) * arr2(i,1)
or
或者
arrayA(i) = arr1(1,i) * arr2(1,i)
depending on what orientation you expected from your input. (Note that if you do this with VBA, orientation of what is conceptually a 1-D array matters, but if you follow Remou's advice above, Excel will do the right thing regardless of whether you pass in rows or columns, or range references or array literals.)
取决于您从输入中期望的方向。（请注意，如果您使用 VBA 执行此操作，概念上一维数组的方向很重要，但是如果您遵循 Remou 上面的建议，无论您是传入行还是列，或范围引用或数组文字。）
As an epilogue, it also looks like you're not using 'Option Explicit'. Google around for some rants on why you probably always want to do this.
作为结语，您似乎也没有使用“Option Explicit”。谷歌周围的一些关于为什么你可能总是想要这样做的咆哮。
