Excel-VBA：如何在 vba 代码中获取所选数组的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12028173/
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
Excel-VBA: How to get size of selected array in vba code?
提问by Yashvardhan
i'm writing an vba function in excel which takes in an odd sized empty square matrix and filling it with numbers. However, I can't get to retrieve the size of the array that it dynamically selected in the spreadsheet before the function is called. How would I do this?
我正在用 excel 编写一个 vba 函数，它接收一个奇数大小的空方阵并用数字填充它。但是，在调用函数之前，我无法检索它在电子表格中动态选择的数组的大小。我该怎么做？
Thanks!
谢谢！
回答by Nigel Heffernan
Pass a Range object into your function and look at the value:
将 Range 对象传递到您的函数中并查看其值：
Public Function WhatIsTheMatrix(SourceRange as Excel.Range) As Variant
Dim arrSource as Variant
Dim arrOutPut as Variant
Dim i As Long, j As Long
arrSource = SourceRange.Value2
i = Ubound(arrSource, 1)
j = Ubound(arrSource, 2)
' The lower bounds of an array obtained from a range are always 1
' code
' more code
' even more code
WhatIsTheMatrix = arrOutPut
End Function
Now you have two problems:
现在你有两个问题：
Your original problem, which is solved by inspecting i and j
A special case, in which the value of a single-cell range isn't an array variant
你原来的问题，这是通过检查 i 和 j 解决的
一种特殊情况，其中单个单元格范围的值不是数组变体
So you'll need this:
所以你需要这个：
If SourceRange.Cells.Count = 1 Then
Redim arrSource(1 to 1, 1 To 1)
arrSource(1,1) = SourceRange.Value2
Else
arrSource = SourceRange.Value2
End If
Any other Business:
其他业务：
There's an unrecognized assumption, namely: you thought you could pass in the current Application.Selection and it will always be a range. It can also be a control, or a chart, or a chart series...
有一个无法识别的假设，即：您认为可以传入当前的 Application.Selection 并且它始终是一个范围。它也可以是一个控件，或一个图表，或一个图表系列......
...And no, you can't rely on this raising a runtime error for the type mismatch. Yes, you've invoked Option Explicit and yes, you typed SourceRange as Excel.Rangeand your Mother, your Parish Priest and a Justice of the Peace watched you do it; try sacrificing something on a sinister-looking rock after midnight, and maybethe VBA runtime will believe you.
...不，你不能依赖这引发类型不匹配的运行时错误。是的，您已经调用了 Option Explicit 并且是的，您输入了SourceRange as Excel.Range并且您的母亲、您的教区牧师和治安法官看着您这样做；尝试在午夜之后在看起来险恶的岩石上牺牲一些东西，也许VBA 运行时会相信你。
So you also need this:
所以你还需要这个：
If Not TypeOf SourceRange Is Excel.Range Then
' Raise an error or Exit Sub
End If
I thinkthat's all the surprises. Unless you're into exotic stuff and awkward questions like "Is this range calculated?"
我想这就是所有的惊喜。除非你喜欢异国情调和尴尬的问题，比如“这个范围是计算出来的吗？”
