excel vba - returns two value, passing one input parameter

Question

This is the case:
I need to have two values back from a function: this function needs an input parameter to works.

strTitleName: input parameter
sName: output paramter
sScope: output paramter

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

How can I getout in a subroutine the values of sName and sScope?

Answer 1

The concept at play here is ByRef vsByVal parameters.

In VBA parameters are passed by reference unless specified otherwise, which is... an unfortunate default: in most other languages, parameters are passed by value.

99% of the time, you don't need to pass anything ByRef, so ByVal is perfect, and should be specified explicitly... 99% of the time.

Passing parameters ByRef is useful for cases like this, when you need to return two or more values and returning an instance of a class encapsulating the return values would be overkill.

Keep in mind that a Function procedure always returns a value, even if you don't declare a return type. If you fail to declare a return type, and never assign a return value, the function will return Variant/Empty, which makes for a rather confusing and non-idiomatic API.

Here are a couple options:

ByRef return parameters

Say your signature looked like this:

Public Function GetScenarioName(ByVal title As String, ByRef outName As String, ByRef outScope As String) As Boolean

Now you can return True when the function succeeds, False when it doesn't (say, if rng1 happens to be Nothing), and then assign the outName and outScope parameters.

Because they're passed by reference, the calling code gets to see the new values - so the caller would look like this:

Dim scenarioTitle As String
scenarioTitle = "title"

Dim scenarioName As String, scenarioScope As String
If GetScenarioName(scenarioTitle, scenarioName, scenarioScope) Then
    Debug.Print scenarioName, scenarioScope
Else
    Debug.Print "No scenario was found for title '" & scenarioTitle & "'."
End If

What happens is that the function receives a copy of the scenarioTitle variable - that copy is essentially a variable that's local to the function: if you re-assign it in the body of the function, the caller doesn't get to see the updated value, the original argument remains unaffected (and this is why ByVal is the safest way to pass parameters).

But the function also receives a reference to the scenarioName and scenarioScope variables - and when it assigns to its outName and outScope parameters, the value held by that reference is updated accordingly - and the caller gets to see the updated values.

User-Defined Type

Still leveraging ByRef return values, it can sometimes be a good idea to encapsulate members in a cohesive unit: VBA lets you create user-defined types, for the simple cases where you just need to toss a bunch of values around:

Public Type TScenario
    Title As String
    Name As String
    Scope As String
    '...
End Type

Public Function GetScenarioInfo(ByRef info As TScenario) As Boolean

Now this function would work similarly, except now you no longer need to change its signature whenever you want to add a parameter: simply add the new member to TScenario and you're good to go!

The calling code would be doing this:

Dim result As TScenario
result.Tite = "title"
If GetScenarioInfo(result) Then
    Debug.Print result.Name, result.Scope
Else
    Debug.Print "No scenario was found for title '" & result.Title & "'."
End If

Alternatively, you could have a full-fledged class module to encapsulate the ScenarioInfo - in which case...

Full-Blown OOP

Encapsulating everything you need in its own class module gives you the most flexibility: now your function can return an object reference!

Public Function GetScenarioName(ByVal title As String) As ScenrioInfo

Now the function can return Nothing if no scenario is found, and there's no need for any parameters other than the input one:

Dim scenarioTitle As String
scenarioTitle = "title"

Dim result As ScenarioInfo
Set result = GetScenarioInfo(scenarioTitle)
If Not result Is Nothing Then
    Debug.Print result.Name, result.Scope
Else
    Debug.Print "No scenario was found for title '" & scenarioTitle & "'."
End If

This is IMO the cleanest approach, but does require a bit of boilerplate - namely, the ScenarioInfo class module. The simplest possible implementation would simply expose read/write public fields:

Option Explicit
Public Name As String
Public Scope As String

More elaborate implementations could involve an IScenarioInfo interface that only exposes Property Get members, the ScenarioInfo class that Implements IScenarioInfo, a VB_PredeclaredId attribute (that's... hidden... and much easier to handle with the Rubberduck VBIDE add-in) with a public factory method that lets you parameterize the object's creation - turning the function into something like this:

If Not rng1 Is Nothing Then
    Set GetScenarioInfo = ScenarioInfo.Create(rng1.Address, rng1.Offset(1,1).Address)
End If

If that's an approach you find interesting, you can read up about it on the Rubberduck News blog, which I maintain.

Answer 2

You can create an array inside the function to store both values. Then, return the array.

For example:

'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

Answer 3

Using @Freeflow's suggestion of the collection, here's your updated code:

Function getScenarioName(strTitleName As String, sName As String, sScope As String) as Collection
    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

        dim colToReturn as Collection
        set colToReturn = New Collection
        colToReturn.Add sName
        colToReturn.Add sScope

        Set getScenarioName = colToReturn

    End If
End Function