vba - Visual Basic for Applications 中的文本模板引擎/函数

Question

我公司中 VBA 的常见用法是根据 Excel 表格中输入的信息生成源代码。鉴于 VBA 的 native 字符串操作，执行此操作的代码编写起来很乏味，而且可读性不太好。

一个简单的例子(这些变得更复杂)是:

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;"

我正在寻找 VBA 中的解决方案，该解决方案允许我使用“文本模板”来指定此内容，因此定义一个如下所示的文本

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

并进行函数/方法调用，传递@name@和@bit@的值，用相应的值替换@name@和@bit@的所有实例。

Answer 1

基本插入功能:

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

对于

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

Foo A Bar B Qux C (A)

map (添加对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