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.
