Optional parameters are a great new feature in VB4, but the ParamArraykeyword is an overlooked addition to function and subroutine declarations.The ParamArray keyword allows an unspecified number of Variantparameters to be passed to a routine as an array. This subroutineuses the ParamArray keyword to parse a string into a variablenumber of arguments, handling text and numerics as it goes:
Public Sub SplitIt(ByVal S As String, _ ByVal Delim As String, ParamArray _ Out() As Variant) 'Dim xIndex As Integer, xPos As 'Integer, xNext As String 'For xIndex = LBound(Out) To 'UBound(Out) xPos = InStr(S, Delim) If xPos = 0 Then xNext = S S = "" Else xNext = Left$(S, xPos - 1) S = Right$(S, Len(S) - xPos _ + 1 - Len(Delim)) End If Select Case VarType(Out(xIndex)) Case vbInteger To vbCurrency Out(xIndex) = Val(xNext) Case vbString Out(xIndex) = xNext End Select NextEnd Sub
Now you can use SplitIt to split any text string into any variablesat each delimiter. For example:
Dim X As String, Y As Double, Z As IntegerSplitIt "A,5.5,6", ",",X,Y,Zreturns:X="A", Y=5.5, Z=6,While:SplitIt "AxxxBxxxC", "xxx", , Xreturns:X="B"