VBA Function that replaces placeholders in a string

[Dstars21]

i am currently trying to make a VBA function that replaces placeholders in a string with a value on an excel spreadsheets.

I'd like it to be able to take any number of placeholders, for example:

{} are not {}.

input values for the function: Dogs, Cats

Therefore, the function would return : {dogs} are not {cats}.

Does anyone have any pointers as to how to set this up? New to VBA, any help is appreciated!

 

[Yongle]

The example below uses named ranges, and whereby the value in A1 is amended by changing the values in D2, D3 or D4

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​		formula in A1	Named Range		Named range refers to
2​	cats are neither dogs nor mice	=N_1&" are neither "&N_2 &" nor " & N_3	N_1	cats	=Sheet3!$D$2
3​			N_2	dogs	=Sheet3!$D$3
4​			N_3	mice	=Sheet3!$D$4

Sheet: Sheet3

VBA
- using Named Ranges is not necessary but simple to illustrate
- what determines replacement value for each placeholder (N_1, N_2, N_3)

 

[Yongle]

Is this what you want ?

String : The {} is in the {}, but the {} is in the {} whereas the {} is in the {}
Values : mottled cat,water,black dog,flower garden,stripey tiger,jungle
Result : The mottled cat is in the water, but the black dog is in the flower garden whereas the stripey tiger is in the jungle

Sub CallFunction()
    Dim a As String, b As String
    a = "The {} is in the {}, but the {} is in the {} whereas the {} is in the {}"
    b = "mottled cat,water,black dog,flower garden,stripey tiger,jungle"
    MsgBox ReplaceAll(a, b)
End Sub

Function ReplaceAll(aStr As String, Vals As String) As String
    Dim arr As Variant, i As Integer, c As Integer
    Const P = "{}"
    If InStr(aStr, P) = 0 Then GoTo TheEnd
    arr = Split(Vals, ",")
    aStr = "@" & aStr & "@"                 'add leading\trailing characters
    
    For i = 0 To UBound(arr)
        c = InStr(aStr, P)
        If c = 0 Then Exit For
        aStr = Left(aStr, c - 1) & arr(i) & Mid(aStr, c + 2, Len(aStr))
    Next
    aStr = Mid(aStr, 2, Len(aStr) - 2)      'remove leading\trailing characters

TheEnd:
    ReplaceAll = aStr
End Function

 

[Akuini]

Another way:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1113703a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1113703-vba-function-replaces-placeholders-string.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], sInput [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

tx = [COLOR=Darkcyan]"I am currently {} to make a {}  that replaces {} in a {}"[/COLOR]
    sInput = [COLOR=Darkcyan]"trying:VBA function:placeholders:string"[/COLOR] 'input separated by [COLOR=Darkcyan]":"[/COLOR]
        MsgBox toReplace(tx, sInput)

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Function[/COLOR] toReplace(tx1 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], sInput1 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]) [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x

n = [COLOR=Royalblue]UBound[/COLOR](Split(tx1, [COLOR=Darkcyan]"{}"[/COLOR]))

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x In Split(sInput1, [COLOR=Darkcyan]":"[/COLOR])
    i = i + [COLOR=Brown]1[/COLOR]
    tx1 = WorksheetFunction.Replace(tx1, WorksheetFunction.Find([COLOR=Darkcyan]"{}"[/COLOR], tx1), [COLOR=Brown]2[/COLOR], [COLOR=Darkcyan]"{"[/COLOR] & x & [COLOR=Darkcyan]"}"[/COLOR])
    [COLOR=Royalblue]If[/COLOR] i = n [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

toReplace = tx1

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Function[/COLOR][/FONT]

 

[Akuini]

The code in post #4 can be simplified to this:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1113703a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1113703-vba-function-replaces-placeholders-string.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], sInput [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

tx = [COLOR=Darkcyan]"I am currently {} to make a {}  that replaces {} in a {}"[/COLOR]
    sInput = [COLOR=Darkcyan]"trying:VBA function:placeholders:string"[/COLOR] 'input separated by [COLOR=Darkcyan]":"[/COLOR]
        MsgBox toReplace(tx, sInput)

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Function[/COLOR] toReplace(tx1 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], sInput1 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]) [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] x

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x In Split(sInput1, [COLOR=Darkcyan]":"[/COLOR])
    tx1 = Replace(tx1, [COLOR=Darkcyan]"{}"[/COLOR], [COLOR=Darkcyan]"{"[/COLOR] & x & [COLOR=Darkcyan]"}"[/COLOR], [COLOR=Brown]1[/COLOR], [COLOR=Brown]1[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

toReplace = tx1

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Function[/COLOR][/FONT]

the result:
I am currently {trying} to make a {VBA function} that replaces {placeholders} in a {string}

but if you want the result without the "{}", then change this part:

tx1 = Replace(tx1, "{}", "{" & x & "}", 1, 1)

with this:

tx1 = Replace(tx1, "{}", x, 1, 1)

the result:
I am currently trying to make a VBA function that replaces placeholders in a string

 

[Yongle]

@Akuini - post#5 code is very elegant
@Dstars21 - ignore my code. @Akuini code is better

 

[Akuini]

Thanks for your kind word, Yongle.