让VBA自定义函数返回多个值

问题的提出：
    因为Excel程序中多次用到Find方法，但是这个方法中的参数过于多，所以想一次性写好封装在一个函数内，把待检查的sheet及其要查找的单元格区域作为一个参数，查找内容作为另外一个参数，Find方法返回一个Range 对象，是否可以获得该对象的行和列？

方法1：传址法
方法2：自定义数据类型法
方法3：直接对象法

'方法1：传址法
Function SearchRef(T As Range, SearchItem As String, ByRef r As Long, ByRef c As Long) As Boolean
    Dim Match As Range
    Set Match = T.Find(What:=SearchItem, After:=T.Cells(T.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=True)
    If Not Match Is Nothing Then    'info found
        r = Match.Row
        c = Match.Column
        SearchRef = True
    Else                            ' nothing found
        SearchRef = False
    End If
End Function

Sub RunSearchRef()
    Dim r As Long, c As Long
    If SearchRef(Sheet1.Range("A1:E10"), "SWITCH COMPONENTS", r, c) Then
        MsgBox r
        MsgBox c
    Else
        MsgBox "Nothing found !"
    End If
End Sub

'方法2：自定义数据类型法
Type SearchRange
    r As Long
    c As Long
    Found As Boolean
End Type

Function SearchType(T As Range, SearchItem As String) As SearchRange
    Dim Match As Range
    Set Match = T.Find(What:=SearchItem, After:=T.Cells(T.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=True)
    If Not Match Is Nothing Then    'info found
        SearchType.r = Match.Row
        SearchType.c = Match.Column
        SearchType.Found = True
    Else                            ' nothing found
        SearchType.Found = False
    End If
End Function

Sub RunSearchType()
    Dim sr As SearchRange
    sr = SearchType(Sheet1.Range("A1:E10"), "SWITCH COMPONENTS")
    If sr.Found Then
        MsgBox sr.r
        MsgBox sr.c
    Else
        MsgBox "Nothing found !"
    End If
End Sub

'方法3 直接对象法，在本例中完全可以直接利用Range对象
Function SearchObj(T As Range, SearchItem As String) As Range
    Set SearchObj = T.Find(What:=SearchItem, After:=T.Cells(T.Cells.Count), _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=True)
End Function

Sub RunSearchObj()
    Dim r As Range
    Set r = SearchObj(Sheet1.Range("A1:E10"), "SWITCH COMPONENTS")
    If Not r Is Nothing Then
        MsgBox r.Row
        MsgBox r.Column
    Else
        MsgBox "Nothing found !"
    End If
End Sub

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。