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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related