VBA: Checking optional parameters

Question

I have two subs and want to pass values from one to the other.

Option Explicit

Sub Test()
    Call HandleInput(ActiveSheet.Range("A1:C4"), 4, 2)
End Sub

Sub HandleInput(rng As Range, rowNumber As Long, colNumber As Long)
    Debug.Print rng.Cells(rowNumber, colNumber).Value
End Sub

However, sometimes I want to apply the same routine over the same range, but with a different rownumber and a different colnumber. I could just call the sub again with the new values and right now that seems to be by far the easiest option, but I still want know if there's a smart way to handle it with optional parameters in HandleInput:

Sub HandleInput(rng As Range, rowNumber As Long, colNumber As Long, Optional rowNumber2 As Long, _
Optional colNumber2 As Long, Optional rowNumber3 As Long, Optional colNumber3 As Long)
   ...
End Sub

This made me wonder:

Can I somehow tell VBA that, if rowNumber2 is being provided, a value for colNumber2 needs to be passed as well? I know I could try it with IsMissing() and switching the data type to Variant:

Sub HandleInput(rng As Range, rowNumber As Long, colNumber As Long, Optional rowNumber2 As Variant, 
     _ Optional colNumber2 As Variant, Optional rowNumber3 As Variant, Optional colNumber3 As Variant)
          If Not IsMissing(rowNumber2) Then
              If IsMissing(colNumber2) Then
                   MsgBox "Please enter a value for colNumber2."
                   End
              End If
          End If
End Sub

This requires lots of if-Statements, also in the other direction, (If NOT IsMissing(colNumber2) Then). And it only gets worse if more than two variables should be tied together. Any calculation that I try as a workaround gives me an error ("Type mismatch") when one value is missing, for example I tried:

If IsError(rowNumber2 * colNumber2) Then
   MsgBox "Error, please supply both rowNumber2 and colNumber2"
End If

Is there a native functionality for this? The only solution I came up with is supplying default values that I know won't occur "naturally":

Sub HandleInput(rng As Range, rowNumber As Long, colNumber As Long, Optional rowNumber2 As Long = -100, _
Optional colNumber2 As Long = -100, Optional rowNumber3 As Long = -100, Optional colNumber3 As Long = -100)

     If rowNumber2 = -100 Or colNumber2 = -100 Then
        MsgBox "Please enter a value for both rowNumber2 and colNumber2."
        End
    End If
End Sub

You could use an array as the arguments bettersolutions.com/vba/arrays/passing-arrays.htm — Nathan_Sav
– Nathan_Sav, Commented Feb 12, 2020 at 22:10
Review the concepts in passing parameters either ByRef or ByVal. Then make it a habit to tag every single parameter with one of those keywords. — PeterT
– PeterT, Commented Feb 12, 2020 at 22:22

Mathieu Guindon · Accepted Answer · 2020-02-13 13:43:59Z

Magic defaults are a bad idea.

You need a concept of "something that represents two values that need to always go together" - that sounds very much like needing some kind of Tuple object that encapsulates two values; I'd go with the nuclear strongly-typed option, and add two new class modules - first some general-purpose ITuple interface:

'@Interface
Option Explicit

Public Property Get Item1() As Variant
End Property

Public Property Get Item2() As Variant
End Property

Public Function ToString() As String
End Function

And then a RangeLocation class that implements it:

'@PredeclaredId 'see https://github.com/rubberduck-vba/Rubberduck/wiki/VB_Attribute-Annotations
Option Explicit
Implements ITuple

Private Type TInternal
    RowIndex As Long
    ColumnIndex As Long
End Type

Private this As TInternal

Public Function Create(ByVal atRow As Long, ByVal atColumn As Long) As ITuple
    Dim result As RangeLocation
    Set result = New RangeLocation
    result.RowIndex = atRow
    result.ColumnIndex = atColumn
    Set Create = result
End Function

Public Property Get RowIndex() As Long
    RowIndex = this.RowIndex
End Property

Public Property Let RowIndex(ByVal value As Long)
    If value <= 0 Then Err.Raise 5
    this.RowIndex = value
End Property

Public Property Get ColumnIndex() As Long
    ColumnIndex = this.ColumnIndex
End Property

Public Property Let ColumnIndex(ByVal value As Long)
    If value <= 0 Then Err.Raise 5
    this.ColumnIndex = value
End Property

Private Property Get ITuple_Item1() As Variant
    ITuple_Item1 = this.RowIndex
End Property

Private Property Get ITuple_Item2() As Variant
    ITuple_Item2 = this.ColumnIndex
End Property

Private Function ITuple_ToString() As String
    ITuple_ToString = "R" & this.RowIndex & "C" & this.ColumnIndex
End Function

Note how it's impossible to have an instance of that object that encapsulates a negative row or column index. And now we can do this:

Dim a As ITuple
Set a = RangeLocation.Create(1, 1)

Which means we can also do this:

Public Sub DoSomething(ByVal source As Range, ParamArray values() As Variant)
    Dim i As Long
    For i = LBound(values) To UBound(values)

        Dim location As ITuple
        Set location = values(i)

        On Error Resume Next
        Debug.Print source.Cells(location.Item1, location.Item2).Value
        If Err.Number <> 0 Then Debug.Print "Location " & location.ToString & " is outside the specified source range."
        On Error GoTo 0

    Next
End Sub

...and it's now somebody else's job to make sure they're providing valid values - more precisely, it's the calling code's job:

Dim source As Range
Set source = ActiveSheet.Range("A1:C4")

DoSomething source, _
    RangeLocation.Create(4, 2), _
    RangeLocation.Create(1, 1), _
    RangeLocation.Create(2, 2)
    '...

If the caller tries to do RangeLocation.Create(0, -12), there will be a runtime error (because the Property Let members of the RangeLocation class don't allow negative values) and DoSomething will not even be invoked.

chris neilsen · Accepted Answer · 2020-02-12 22:41:09Z

You could handle the whoile thig with a ParamArray and range check the incomoing array

Sub HandleInput(rng As Range, ParamArray RCPairs() As Variant)

    If UBound(RCPairs) < 1 Then
        Err.Raise 513, "HandleInput", "Please enter at least one pair of RowNumber, ColNumber."
    ElseIf UBound(RCPairs) Mod 2 = 0 Then
        Err.Raise 513, "HandleInput", "Please enter a value for both RowNumber and ColNumber."
    End If

    ' ...

End Sub

Called like this

Sub Demo()
    HandleInput SomeRange, r1, c1, r2, c2 ' Works

    HandleInput SomeRange ' Error "Please enter at least one pair of RowNumber, ColNumber."

    HandleInput SomeRange, r1, c1, r2, c2, x ' Error: "Please enter a value for both RowNumber and ColNumber."

End Sub

Note: I cahnged your MsgBox, End to raising an error, so your calling code can decide what to do about the error. BTW using End is unwise, see here

Collectives™ on Stack Overflow

VBA: Checking optional parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related