vba 试图返回一个范围作为函数输出，得到类型不匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8855801/
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
Trying to return a range as function output, get type mismatch?
提问by BuZz
The following function returns a "type mismatch". I don't understand, as I paid attention to using the "Set" instruction to return my resulting range.
以下函数返回“类型不匹配”。我不明白，因为我注意使用“设置”指令来返回我的结果范围。
I debugged the function, I get a proper range to return, so the problem is elsewhere.. Hmmmm...
我调试了这个函数，我得到了一个合适的返回范围，所以问题出在别处..嗯......
Function getVals(column As String) As Range
Dim col As Variant
col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)
Dim rng As Range
Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)
Set rng = rng.Offset(1, 0)
Set rng = Range(rng, rng.End(xlDown))
Set getVals = rng
End Function
Thanks in advance guys for any help :)
在此先感谢大家的帮助:)
UPDATE : I am looking at how to send my results as an array. I tried combinations of the function returning "variant"/"variant()" type, and passing rng.value2 as result, but no success.
更新：我正在研究如何将我的结果作为数组发送。我尝试了返回“variant”/“variant()”类型并传递 rng.value2 作为结果的函数的组合，但没有成功。
采纳答案by mischab1
To return your results as an array of values, simply change the return type to Variantand return rng.Value. The below code works for me as long as the passed columnstring exists in ThisWorkbook.ActiveSheet.Range("1:1").
要将结果作为值数组返回，只需将返回类型更改为Variant和 return rng.Value。只要传递的column字符串存在于ThisWorkbook.ActiveSheet.Range("1:1").
Function getVals(column As String) As Variant
Dim col As Variant
col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)
Dim rng As Range
Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)
Set rng = rng.Offset(1, 0)
Set rng = Range(rng, rng.End(xlDown))
getVals = rng.Value
End Function
Sub TestingGetVals()
Dim v As Variant, i As Integer
v = getVals("a") ' returns a 2-D array
For i = 1 To UBound(v)
Debug.Print v(i, 1)
Next i
End Sub
回答by Siddharth Rout
You are getting that error because Match is not able to find what you want and hence your rng is evaluating to "nothing" :)
您收到该错误是因为 Match 无法找到您想要的内容，因此您的 rng 评估为“无”:)
Consider this code
考虑这个代码
Option Explicit
Sub Sample()
Dim Ret As Range
If Not getVals("Value To Match") Is Nothing Then
Set Ret = getVals("Value To Match")
MsgBox Ret.Address
Else
MsgBox "Value To Match - Not Found"
End If
End Sub
Function getVals(column As String) As Range
Dim col As Variant
Dim rng As Range
On Error GoTo Whoa
col = Application.Match(column, ThisWorkbook.ActiveSheet.Range("1:1"), 0)
Set rng = ThisWorkbook.ActiveSheet.Cells(1, col)
Set rng = rng.Offset(1, 0)
Set rng = Range(rng, rng.End(xlDown))
Set getVals = rng
Exit Function
Whoa:
Set getVals = Nothing
End Function
回答by Tony Dallimore
Firstly I do not understand what you are doing. You have a parameter columnbut you are searching for a cell within row 1 that contains that value. For example, if column = 23, and P1 contains 23, Match should return 16.
首先我不明白你在做什么。您有一个参数，column但您正在第 1 行中搜索包含该值的单元格。例如，如果 column = 23，并且 P1 包含 23，则 Match 应返回 16。
Your routine fails because if the Match fails, col is set to Error 2042. You should test col before using it as a number.
您的例程失败，因为如果匹配失败，则 col 设置为Error 2042。您应该在将 col 用作数字之前对其进行测试。
In my test I set row 1 to numbers in a random sequence. My Match failed because cell P1 contained number 23 but variable column contains string "23". When I reclassified column as Long, the Match worked.
在我的测试中，我将第 1 行设置为随机序列中的数字。我的匹配失败，因为单元格 P1 包含数字 23 但变量列包含字符串“23”。当我将列重新分类为 Long 时，Match 起作用了。
I am unhappy with Siddharth's use of On Error. I do not like to use On Errorfor errors I expect. I would test col to be numeric after the Match.
我对悉达多使用On Error. 我不喜欢On Error用于我预期的错误。我会在匹配后测试 col 为数字。
回答by mischab1
Other people are faster at writing then I am. :-) There is one other possibility that hasn't been mentioned yet.
其他人的写作速度比我快。:-) 还有另一种可能性尚未提及。
Since you didn't get the error when debugging, the issue might be your use of ActiveSheet. If the wrong worksheet is active then Match will cause the error as stated by the other answers.
由于您在调试时没有收到错误消息，问题可能出在您使用ActiveSheet. 如果错误的工作表处于活动状态，则 Match 将导致其他答案所述的错误。
If you are explicit, does the error go away?
如果你是明确的，错误会消失吗？
col = Application.Match(column, ThisWorkbook.Sheet(1).Range("1:1"), 0)
