I'm trying to write the SWITCH function in VBA for my coworker who has Excel 2013. I feel that my VBA is strong enough to code this function once I set up all my function parameters. However, I'm not sure how to have an unlimited number of optional parameters in a function (similar to *args in Python). How can I set up a function so that it may have an unlimited number of optional arguments?
2 Answers
You need to use ParamArray, e.g.
Public Function TestSum(ParamArray a())
Dim i As Long
For i = LBound(a) To UBound(a)
TestSum = TestSum + a(i)
Next i
End Function
Comments
An interesting question, here's my attempt at replicating the Switch functionality.
You will need to use a ParamArray argument:
Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. It may not be used with ByVal, ByRef, or Optional. (source)
Revised, thanks to the comments with @TinMan, we no longer use a Dictionary so this will be compatible with Mac OS without further tweaks.
Function FSwitch2(ValueToMatch As Variant, ParamArray ValuesToMatchAndReturn())
' example of replicating the Switch function available in Office 365, etc.
' https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e
Dim i As Integer
Dim retVal As Variant
Dim default As Variant
If (UBound(ValuesToMatchAndReturn) + 1) Mod 2 <> 0 Then
' if the array is not evenly sized, assume the last argument is the default value.
default = ValuesToMatchAndReturn(UBound(ValuesToMatchAndReturn))
Else
' Otherwise, default to #N/A error if no match.
default = CVErr(2042)
End If
For i = LBound(ValuesToMatchAndReturn) To UBound(ValuesToMatchAndReturn) Step 2
If ValueToMatch = ValuesToMatchAndReturn(i) Then
retVal = ValuesToMatchAndReturn(i + 1)
Exit For
End If
Next
FSwitch2 = IIf(IsEmpty(retVal), default, retVal)
End Function
8 Comments
TinMan
I was actually have a little fun with the post. I was, however, thinking of a hard coded
Switch() statement.TinMan
A VBA
Switch() statement uses an index. I don't think that you actually need a Dictionary because you only need to find a single value. Simply return the value when found. Or an error is not found. In any case, there really isn't much need to optimise something like this. +1
David Zemens
@TinMan You don't need a dictionary, you could do this with a pair of Arrays, or a pair ArrayLists, and I think you could do it within a Select Case if you defined all 124 optional pairs of value/resul;t, but it gets really messy when needing to check parameters exist. The problem with doing a straight index against the
ParamArray is that you won't differentiate the values from the results and could end up with some wrong results that way.TinMan
This is the best that I can do in under 5 minutes fswitch.txt
TinMan
No worries, I posted a complete answer too soon. The only reason that I was able to write it so fast was that your answer is quite well structured. Copy, paste, refactor..life of a VBA Hack...
|
Switchfunction in VBA, you could simply use theSelect Caseoperator in VBA, which is more or less equivalent to the c#switchstatement. But if you need to take an arbitrary set of pairs from the user, maybe you do need to recreate Switch.