Text templating engine/function in Visual Basic for Applications

Question

A common usage of VBA at my company is generation of source code based on information entered in Excel tables. Given VBA's native string manipulation, the code that does this is tedious to write and not very readable.

A simple example (these get more complex) is:

Print #fileIdentifier, SSpace(9) & "update_" & print_string & "[" & CStr(j) & "] <= 1'b0;"
Print #fileIdentifier, SSpace(9) & print_string & "_ack_meta" & "[" & CStr(j) & "] <= 1'b0;"
Print #fileIdentifier, SSpace(9) & print_string & "_ack_sync" & "[" & CStr(j) & "] <= 1'b0;"

I am looking for a solution in VBA that would allow me to specify this using a "text template", so define a text that would look something like this

    update_@name@[@bit@] <= 1'b0;
    @name@_ack_meta[@bit@] <= 1'b0;
    @name@_ack_sync[@bit@] <= 1'b0;

and have a function/method call, passing the values of @name@ and @bit@, replace all instances of @name@ and @bit@ with corresponding values.

Answer 1

Basic insertion function:

Function insert(template As String, ParamArray inserts() As Variant) As String
    Dim i As Long
    For i = 0 To UBound(inserts)
        template = Replace$(template, "%" & i + 1 & "%", inserts(i))
    Next

    '// some special cases perhaps
    template = Replace$(template, "%SSPACE%", SSpace(9))
    template = Replace$(template, "\r\n", VbCrLf)

    insert = template
End Function

For

?insert("Foo %1% Bar %2% Qux %3% (%1%)", "A", "B", "C")

Foo A Bar B Qux C (A)

Map (add a reference to Microsoft Scripting Runtime):

Dim col As New Scripting.Dictionary
col("name") = "bob"
col("age") = 35

MsgBox insert2("Hello %name% you are %age%", col)

...

Function insert2(template As String, map As Scripting.Dictionary) As String
    Dim name
    For Each name In map.Keys()
        template = Replace$(template, "%" & name & "%", map(name))
    Next
    insert2 = template
End Function