Dynamically replace text in vba

Question

I’m trying to create a routine that replaces a variable number of values within a given string. For example, if I have a base text in the spreadsheet of:

“Your vehicle selection of {0} indicates you should have between {1} and {2} tires. However, you have entered {3} tires for this vehicle. Please update the record accordingly.”

I would like to replace the tokens with either a constant value from the application or some other variable a user entered. I’m trying to create a routine with a signature such as the following, where RowID is the row in the spreadsheet where the base text is located and ReplacementValues is an array of n number of variables:

Sub ShowMsg(ByVal RowID As Integer, Optional ByVal ReplacementValues As Variant)

I can’t figure out how to loop through the text and replace each token without repeating the entire text of the base message in each iteration. If possible I would like to keep the routine fairly generic and not specific to Excel in case I need to move the application to a database at some point later.

Hope I have explained this sufficiently; any help would be appreciated.

Alex K. · Accepted Answer · 2012-05-21 14:55:47Z

You can;

Sub ShowMsg(ByVal RowID As Integer, Optional ReplacementValues As Variant)
Dim data As String, i As Long
If Not IsMissing(ReplacementValues) Then
    data = Range("A" & RowID).Value
    For i = 0 To UBound(ReplacementValues)
        data = Replace(data, "{" & i & "}", ReplacementValues(i))
    Next
    msgbox data
End If
End Sub

called with;

Dim a() As Variant: a = Array("aa", "bb", "cc")
ShowMsg 8, a

Or an alternative:

Sub ShowMsg(ByVal RowID As Integer, ParamArray ReplacementValues() As Variant)
Dim data As String, i As Long
If Not IsMissing(ReplacementValues) Then
    data = Range("A" & RowID).Value
    If IsArray(ReplacementValues(0)) Then ReplacementValues = ReplacementValues(0)
    For i = 0 To UBound(ReplacementValues)
        data = Replace(data, "{" & i & "}", ReplacementValues(i))
    Next
    msgbox data
End If
End Sub

which can be called in the same way or additionally with ordinal arguments;

ShowMsg 8, "aa", "bb", "cc"

Siddharth Rout · Accepted Answer · 2012-05-21 15:01:15Z

First change your base string to something like this

BaseString = "Your vehicle selection of {VEHSEL} indicates you should have " & _
             "between {nTYRE1} and {nTYRE2} tires. However, you have entered " & _
             "{nTotTYRE} tires for this vehicle. Please update the record " & _
             "accordingly."

If you notice now that you have specific Keywords like

VEHSEL - Vehicle Selection
nTYRE1 - Lowest selection of tires
nTYRE2 - Highest selection of tires
nTotTYRE - Total tires selected

Once you pick up the values from the spreadhseet, simply use REPLACE to replace the above Keywords with the relevant values

So your code will look like

Option Explicit

Sub Sample()
    Dim lVSell As Long, lT1  As Long, lT2  As Long, ltotT As Long
    Dim lRowID As Long

    lRowID = 5

    With Sheets("Sheet1")
        lVSell = .Range("A" & lRowID).Value
        lT1 = .Range("B" & lRowID).Value
        lT2 = .Range("C" & lRowID).Value
        ltotT = .Range("D" & lRowID).Value

        Debug.Print ShowMsg(lRowID, lVSell, lT1, lT2, ltotT)
    End With
End Sub

Function ShowMsg(ByVal RowID As Integer, ByVal VSel As Long, _
ByVal T1 As Long, ByVal T2 As Long, ByVal totT As Long) As String
    Dim BaseString As String

    BaseString = "Your vehicle selection of {VEHSEL} indicates you should have " & _
                 "between {nTYRE1} and {nTYRE2} tires. However, you have entered " & _
                 "{nTotTYRE} tires for this vehicle. Please update the record " & _
                 "accordingly."

    BaseString = Replace(BaseString, "VEHSEL", VSel)
    BaseString = Replace(BaseString, "nTYRE1", T1)
    BaseString = Replace(BaseString, "nTYRE2", T2)
    BaseString = Replace(BaseString, "nTotTYRE", totT)

    ShowMsg = BaseString
End Function

I am assuming that the values are stored in Sheet 1 from range A5 to D5.

EDIT

SNAPSHOT

enter image description here HTH

Cylian · Accepted Answer · 2012-05-21 15:08:12Z

0

You could try this one

Sub test()
    Dim x As Variant
    x = Split("<String0>,<String1>,<String2>", ",")
    ShowMsg 23, "A", x
End Sub

Sub ShowMsg(ByVal RowID As Integer, ColID As String, Optional ByVal ReplacementValues As Variant)
    Dim nText$
    nText = Cells(RowID, ColID)
    For pos = LBound(ReplacementValues) To UBound(ReplacementValues)
        Dim searchtext$
        searchtext = "{" & CStr(pos) & "}"
        nText = Replace(nText, searchtext, ReplacementValues(pos))
    Next pos
    MsgBox nText
End Sub

answered May 21, 2012 at 15:08

Cylian

11.2k4 gold badges46 silver badges58 bronze badges

Collectives™ on Stack Overflow

Dynamically replace text in vba

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related