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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td] formula in A1[/td][td]Named Range[/td][td][/td][td] Named range refers to[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]cats are neither dogs nor mice[/td][td] =N_1&" are neither "&N_2 &" nor " & N_3[/td][td]N_1[/td][td]cats[/td][td] =Sheet3!$D$2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td]N_2[/td][td]dogs[/td][td] =Sheet3!$D$3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td]N_3[/td][td]mice[/td][td] =Sheet3!$D$4[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

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.