在 VBA 函数中从范围切换到数组并返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11235517/
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
Switching from Range to Array and Back in a VBA Function
提问by nightTrevors
There are a lot of questions, and a lot of responses dealing with Range/Array conversion in VBA. I haven't been able to find an answer that works, so I would really apreciate some help.
有很多问题，还有很多关于 VBA 中范围/数组转换的回复。我一直无法找到有效的答案，所以我真的很感激一些帮助。
Below is what I'm trying to do:
以下是我正在尝试做的事情：
Function RangeToArrayToRange(inputRange As Range) As Range
Dim inputArray As Variant
inputArray = inputRange
'operations on inputArray'
'...'
Dim outputRange As Range
outputRange = inputArray
Set RangeToArrayToRange = outputRange
End Function
Thanks in advance for your help!
在此先感谢您的帮助！
回答by Tim Williams
If outputRange is the top-left cell of the range to be populated:
如果 outputRange 是要填充的范围的左上角单元格：
outputRange.Resize(Ubound(inputArray,1), _
Ubound(inputArray,2)).Value = inputArray
EDIT: I think this is what you want to do
编辑：我认为这就是你想要做的
Function RangeToArray(inputRange As Range) As Variant
Dim inputArray As Variant
inputArray = inputRange.Value
'operations on inputArray
'...'
RangeToArray = inputArray
End Function
You can use this on a worksheet as a user-defined function (UDF)
您可以在工作表上将其用作用户定义的函数 (UDF)
- Select a range which is the same dimensions as the 'inputRange' (same number of rows/columns)
- Enter "=RangeToArray([yourinputrange])" in the formula bar and press Ctrl+Shift+Enter to enter the formula as an "array formula"
- 选择与“inputRange”维度相同的范围（行/列数相同）
- 在公式栏中输入“=RangeToArray([yourinputrange])”，然后按Ctrl+Shift+Enter 将公式输入为“数组公式”
This assumes you're not altering the dimensions (upper/lower bounds) of inputArrayin your function.
这假设您没有更改inputArray函数中的维度（上限/下限）。
