Bonjour le forum

Je suis actuellement confronté à un problème sur le choix des variables que je souhaite utiliser.

Je m’explique :
Sur un fichier excel, j’ai un texte qui ressemble à ceci : "il était une fois [NOMPERSO] qui vivait à [NOMVILLE]"

A côté de ça, j’ai un programme VBA qui ressemble à ça :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub test()
    Dim NOMPERSO As String, NOMVILLE As String
    Dim a As String, b As String, phrase As String
    Dim nbvarb As Integer
 
    NOMPERSO = "Perso1"
    NOMVILLE = "Ville1"
 
 
    phrase = Range("A1").Value
    nbvarb = CompterMot(phrase, "[")
 
    For i = 1 To nbvarb
        a = InStr(phrase, "[")
        b = InStr(phrase, "]")
 
        If Mid(phrase, a + 1, b - a - 1) = "NOMPERSO" Then
            phrase = Left(phrase, a - 1) & NOMPERSO & Right(phrase, Len(phrase) - b)
        ElseIf Mid(phrase, a + 1, b - a - 1) = "NOMVILLE" Then
            phrase = Left(phrase, a - 1) & NOMVILLE & Right(phrase, Len(phrase) - b)
        End If
 
    Next i
 
End Sub
 
 
Public Function CompterMot(strPhrase As String, strMot As String) As Integer
    Dim strTab() As String
    strTab = Split(strPhrase, strMot)
    CompterMot = UBound(strTab())
End Function
et qui me renvoie la phrase : ""Il était une fois Perso1 qui vivait à Ville1""

Comme vous le voyez, mon code VBA parcourt la chaine de caractère de la cellule excel et remplace les mots entre crochets par la variable dont le nom est le même que le mot entre crochets.
Le problème est que sur mon VRAI programme (le petit bout de code ici présent étant un exemple simplifié), je risque d'avoir pas loin d'une centaine de variables, et je n'ai pas vraiment envie de mettre un énorme pavé de code avec à chaque fois un elseif.

Je suis donc à la recherche d'un petit bout de code qui récupère la variables en fonction du mot contenu entre crochets.

J'ai bien trouvé la fonction callbyname, mais je n'ai pas vraiment compris comment celle-ci fonctionnait.

Est-ce que quelqu'un aurait une solution?