Unlimited Number of Parameters to a Subroutine

There are many times when you want the ability to pass an unlimited number of parameters to a subroutine. For example, C has a 'sprintf()' function that folds variables into a formatted string. You specify place holders in the string, and then a list of variables, you get back a formatted string. Its very useful for building SQL statements and debugging messages. Here's a simplified example for VB:

x = 1000
y = "hello there"
Msgbox sprintf("At the moment, X='%1' and y='%2'", x, y)

Or, in a SQL context:

SqlStatement = "SELECT * FROM Products WHERE ProID=%1 OR ProdName='%2'"
Set rs = GetRecordset(Sprintf(SQLStatement, ProductID, ProdName), myDSN)

Here's the code for sprintf() which demonstrates how to declare, and work with, unlimited parameters:

'Args[0] contains the string that will be expanded, args[1..n] = replacement vals
Public Function sprintf(ParamArray args()) As String
    Dim s As String
    Dim n As Integer
    Dim arg As String
    
    s = Replace(args(0), "\t", vbTab)
    s = Replace(s, "\n", vbCr)
    s = Replace(s, "\r", vbLf)
    
    For n = 1 To UBound(args)
        arg = "%" & Format(n)
        s = Replace(s, arg, args(n))    'replace %1 with args[1] etc
    Next
    sprintf = s
End Function