How would one relay (pass) a paramarray argument received by one function onto a second function in vba?

Question

In VBA, there are times when you might want to pass an indeterminant number of arguments onto a function. In the VB world, paramarray handles such. But what if one needs, in turn to pass such onto a sub-function?

In the challenge here, I have a couple of different places that format a string, inserting subsequent values, for example:

tx = SetSQL("UPDATE tabA SET cd={1} WHERE id={2}", "'123'", 89)

Depending upon the situation, there might be 1 or n parameters.

This worked well when I handled all the SQL directly. For example:

set rsNEW = currentdb.execute (SetSQL("UPDATE tabA SET cd={1} WHERE id={2}", "'123'", 89))

But, I then found that I had several situations where the same processing was repeated and I wanted to refactor some of that into a more all encompassing call some of the time. I wanted to wrap the SetSQL inside of another routine. For example:

public sub DoMyDBThing("UPDATE tabA SET cd={1} WHERE id={2}", "'123'", 89)

And this put me in a situation where I needed to transfer a paramarray to a paramarray .

Answer 1

The solution is to write a wrapper function that converts the paramarray to an array variant.

As I already used SetSQL, it remained accepting the paramarray. And what it once did became SetSQLBase. In this way, my other extensions that needed the same functionality could also take paramarrays and then use SetSQLBase to generate the text. This way I didn't have to replicate the program.

Hence:

Dim v() As Variant
  v = p
  SetSQL = SetSQLBase(sql, v())

End Function

This still handles the n number of trailing parameters as it rewraps the paramarray as variant array to pass to the core routine:

Dim ct As Long
Dim tx As String
  
  While InStr(1, sql, "{") > 0 And ct < 8
    On Error Resume Next
    tx = IIf(ct > UBound(p), "", p(ct))
    On Error GoTo 0
    ct = ct + 1
    
    sql = Replace(sql, "{" & ct & "}", tx)
  Wend
  
  SetSQLBase = sql
End Function

Now other routines can also call the same thing, also accepting an unknown number of arguments, get the sql back and process. For example:

Private Function PassTag_RunSQL(ByRef ds As clsSideXDB, cdStep As String, sql As String, ParamArray p() As Variant) As Boolean

Dim v() As Variant

  v = p
  PassTag_RunSQL = True
  sql = SetSQLBase(sql, v)
  ...