Dynamically replace text in vba

Question

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

"Your choice of vehicle {0} means that you must have between {1} and {2} tires. However, you entered {3} tires for this vehicle. Please update the entry accordingly.

I would like to replace the tokens with either a constant value from the application, or some other variable entered by the user. I am 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 ResplacementValues ​​is an array of n number of variables:

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

I cannot figure out how to iterate over the text and replace each token without repeating the entire text of the base message at each iteration. If possible, I would like the routine to be generic enough and not specific to Excel if I need to move the application to the database at some point later.

Hope I have explained it enough; Any help would be appreciated.

Answer 1

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 in addition with ordinal arguments;

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

Answer 2

First change the base line 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 that you have special Keywords , for example

 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 set of spreadnets, just use REPLACE to replace the above Keywords with the corresponding 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 assume the values ​​are stored in Sheet 1 from the range A5 to D5.

EDIT

Snapshot

NTN

Answer 3

You can try this

 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