Excel 中的 VBA 函数可以返回一个范围吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/439510/
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
Can a VBA function in Excel return a range?
提问by Dane O'Connor
I seem to be getting a type mismatch error when trying to do something like this:
尝试执行以下操作时，我似乎遇到了类型不匹配错误：
In new workbook:
在新工作簿中：
A1 B1
5 4
Function Test1() As Integer
Dim rg As Range
Set rg = Test2()
Test1 = rg.Cells(1, 1).Value
End Function
Function Test2() As Range
Dim rg As Range
Set rg = Range("A1:B1")
Test2 = rg
End Function
Adding =Test1() should return 5 but the code seems to terminate when returning a range from test2(). Is it possible to return a range?
添加 =Test1() 应该返回 5，但是当从 test2() 返回一个范围时，代码似乎终止了。是否可以返回一个范围？
回答by BradC
A range is an object. Assigning objects requires the use of the SET keyword, and looks like you forgot one in your Test2 function:
范围是一个对象。分配对象需要使用 SET 关键字，看起来您在 Test2 函数中忘记了一个：
Function Test1() As Integer
Dim rg As Range
Set rg = Test2()
Test1 = rg.Cells(1, 1).Value
End Function
Function Test2() As Range
Dim rg As Range
Set rg = Range("A1:B1")
Set Test2 = rg '<-- Don't forget the SET here'
End Function
回答by ja72
You can also return a Variant()which represents an array of values. Here is an example for a function that reverses values from a range into a new range:
您还可以返回Variant()表示值数组的 a。以下是将值从范围反转到新范围的函数示例：
Public Function ReverseValues(ByRef r_values As Range) As Variant()
Dim i As Integer, j As Integer, N As Integer, M As Integer
Dim y() As Variant
N = r_values.Rows.Count
M = r_values.Columns.Count
y = r_values.value 'copy values from sheet into an array
'y now is a Variant(1 to N, 1 to M)
Dim t as Variant
For i = 1 To N / 2
For j = 1 To M
t = y(i, j)
y(i, j) = y(N - i + 1, j)
y(N - i + 1, j) = t
Next j
Next i
ReverseValues = y
End Function
In the worksheet you have to apply this function as an array formula (with Ctrl-Shift-Enter) with an appropriate number of cells selected. The details of the Swap() function are not important here.
在工作表中，您必须将此函数作为数组公式（带有Ctrl- Shift- Enter）应用，并选择适当数量的单元格。Swap() 函数的细节在这里并不重要。
Notethat for many rows, this is very efficient. Doing the x = Range.Valueand Range.Value = xoperations when xis an array and the range contains multiple rows columns is manytimes faster than doing the operations one by one directly on the cells.
请注意，对于许多行，这是非常有效的。当是一个数组并且范围包含多行列时执行x = Range.Value和Range.Value = x操作比直接在单元格上执行一个接一个操作快很多倍。x
回答by AnthonyWJones
Change last line in Test2 to:
将 Test2 中的最后一行更改为：
Set Test2 = rg
回答by asif
This also works
这也有效
Function Test2(Rng As Range) As Range
Set Test2 = Rng
End Function
