Create an evaluation string in VBA

Question

I have a list of strings defined as

Dim replyFormat(0 To 999) As String

and a list of answers as

Dim answers(0 to 999) As String

and throughout the code certain strings get added to replyFormat that look similar to this:

Name: {1} {3}

When everything is done, I define a string called sendBack and start looping through each line in replyFormat. I want to set sendBack equal to itself plus what replyFormat is, evaluating answers for the numbers in the curly brackets and finally adding vbCrLf to the end. For exmaple if answers contains { Yes, John, H, Doe } and replyFormat is "Name: {1} {3}" it would ouput "Name: John Doe"

score 0 · Accepted Answer · answered Nov 22 '18 at 10:09

It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):

Function FormattedString(stringToFormat As String, replacements() As String) As String

    Dim placeholder As Variant
    Dim index As Long

    With CreateObject("VBScript.RegExp")
        .Pattern = "\{([\d]{1,3})\}"
        .Global = True
        .MultiLine = False
        .IgnoreCase = True

        If .Test(stringToFormat) Then
            For Each placeholder In .Execute(stringToFormat)
                index = CLng(placeholder.SubMatches(0))
                stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
            Next
        End If
    End With

    FormattedString = stringToFormat

End Function

Example use:

Sub FooBar()

    Dim answers(0 To 3) As String
    Const testString = "Name: {1} {3}"

    answers(0) = "Test"
    answers(1) = "John"
    answers(2) = "Testing"
    answers(3) = "Doe"

    Debug.Print FormattedString(testString, answers) '// "Name: John Doe"

End Sub

Alina Li · Answer 2 · 2018-11-23T01:32:03.613

-2

If this is your object:

Ob = { Yes, John, H, Doe},

You could select object item like this:

Ob(1), Ob(3)

For more information, Please refer to this link:

Retrieve the index of an object stored in a collection using its key (VBA)

edited Nov 23 '18 at 01:32

answered Nov 22 '18 at 09:20

Alina Li

884
1
6
5

You can't create "objects" like this in VBA – SierraOscar Nov 22 '18 at 09:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam – Alina Li Nov 22 '18 at 09:37
If you create an _array_ or a _collection_ you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code. – SierraOscar Nov 22 '18 at 09:48

Create an evaluation string in VBA

2 Answers2