excel - excel vba - 返回两个值，传递一个输入参数

Question

情况就是这样：
我需要从一个函数返回两个值：这个函数需要一个输入参数才能工作。

strTitleName：输入参数
sName：输出参数
sScope：输出参数

Function getScenarioName(strTitleName As String, sName As String, sScope As String)
    activateSheet ("Test Scenarios")
    Dim rng1 As Range
    Dim strSearch As String
    strSearch = strTitleName & "*"
    Set rng1 = Range("B:B").Find(strSearch, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then
     'getScenarioName = rng1.Offset(0, 0)
     sName = rng1.Address
     sScope = rng1.Offset(1, 1).Address
    Debug.Print "sName=" & sName
    Debug.Print "sScope=" & sScope
End If

如何在子程序中获取 sName 和 sScope 的值？

标签： excelvbafunction

Answer 1

您可以在函数内部创建一个数组来存储这两个值。然后，返回数组。

例如：

'If strTitleName is your only argument, then:
Function getScenarioName(strTitleName As String) As Variant

    Dim rng1 As Range
    Dim strSearch As String
    Dim result(1) As String

    activateSheet ("Test Scenarios")

    Set rng1 = Range("B:B").Find(strSearch, , xlValues, xlWhole)
    strSearch = strTitleName & "*"

    result(0) = ""
    result(1) = ""

    If Not rng1 Is Nothing Then

        sName = rng1.Address
        sScope = rng1.Offset(1, 1).Address

        Debug.Print "sName=" & sName
        Debug.Print "sScope=" & sScope

        result(0) = "sName=" & sName
        result(1) = "sScope=" & sScope

    End If

    getScenarioName = result

End Function