XL 2021 VBA: Extraction des paramètres d'une formule (texte)

[p'tit vieux]

Bonjour à tous
Après moulte recherche et écriture de divers fonctions donnant des résultats mitigés, je n'arrive pas à trouver LA bonne solution à mon problème.
Alors ayant très "bobo tête" je men remet à vos esprits d'expert.
PS: J'ai essayé avec Split, Instr, InStrRev etc Parfois en utilisant Like. Mais … Boff ce n'est pas vraiment cela.

Voici le problème.
Exemple de formule à traiter:
"=FOO(((A),(B),"C"),"D,E,F",((G),H))"
Pour simplifié j'ai mis des lettres à la place des valeurs
A, B , C, E, F G etc = sont des "valeurs paramètres".
Exemple de ce peut contenir A, B etc.
A = A1:B2, B5, B7, C8
B = D5:E12
C = 9850
D = Banque, Caisse, Référence -> par exemple.

Ce qui donnerait en développant:
"(((A1:B2, B5, B7, C8),(D5:E12),"9850"),"Banque, Caisse, Référence",((G),H))"

OBJECTIF: Extraire chaque paramètre en gardant sa cohérence.
Dans la formule ci-dessus les paramètres complets a extraire serait :
n°1 : ((A),(B),"C")
N°2 : "D,E,F"
N°3 : ((G),H))
MAIS:
Il peut y avoir imbrication de fonction "Foo()". Comme ceci:
"=FOO1(((A),(B),"C"),"D,E,F",(FOO2((a),(b),"c"),H))"
… Sinon ce ne serait pas drôle. Ici, FOO2 = le paramètre G qui sera analysé à part.

Pour vous aider à m'aider , si j'utilise Split sur la virgule "," ou sur la "(" ou la ")" sans compter le paramètre délimité par " " " -Chr(34)- , j'obtiens un tableau qui ne correspond à rien sans des traitement supplémentaires.

Si quelqu'un a une idée (ou plusieurs) j'en serai très heureux.
Désolé pour les mots de tête. J'aime bien partager

Par avance merci à vous.

PS:
Bien sûr ici dans l'exemple on peut commencer par simplifier la 1ere et la dernière parenthèse. Mais, même si rare, ce n'est pas toujours le cas.

 

[sylvanu]

Re,
Une V3 qui donne bien les paramètres G et H pour l'exemple donné.
Par contre avec votre exemple cela ne peut pas marcher puisque la syntaxe n'est pas respectée.
On attend comme dans le premier exemple une plage puis une chaine, ensuite une caisse référence puis G et H.
Si vous avez plusieurs syntaxes alors le problème sera plus ardu.
PS, j'ai modifié le traitement en rajoutant pour G et H :

        Select Case N
            Case 1:     SplitChamp = T(0) & """"
            Case 2:     SplitChamp = T(1)
            Case Else
                T(2) = Replace(Replace(T(2), "(", ""), ")", "")
                T2 = Split(T(2), ",")
                If N = 3 Then SplitChamp = T2(0) Else SplitChamp = T2(1)
        End Select

 

[p'tit vieux]

Re,
Une V3 qui donne bien les paramètres G et H pour l'exemple donné.
Par contre avec votre exemple cela ne peut pas marcher puisque la syntaxe n'est pas respectée.
On attend comme dans le premier exemple une plage puis une chaine, ensuite une caisse référence puis G et H.
Si vous avez plusieurs syntaxes alors le problème sera plus ardu.
PS, j'ai modifié le traitement en rajoutant pour G et H :

        Select Case N
            Case 1:     SplitChamp = T(0) & """"
            Case 2:     SplitChamp = T(1)
            Case Else
                T(2) = Replace(Replace(T(2), "(", ""), ")", "")
                T2 = Split(T(2), ",")
                If N = 3 Then SplitChamp = T2(0) Else SplitChamp = T2(1)
        End Select

Là je vous réponds de mon téléphone pour à nouveau vous remercier.
Je ne peux pas faire le test avant demain.
Oui il y a plusieurs syntaxes possibles.
Sinon ce ne serait pas un vrai mal de tête
Merci et à demain

 

[sylvanu]

Oui il y a plusieurs syntaxes possibles.

Alors c'est impossible de toutes les traiter avec la même approche.
Il vous faut au moins définir toutes les configurations possibles.
A partir de là faire un module qui détermine la syntaxe utilisée, puis d'autres modules pour traiter chaque configuration. Il sera peut être possible d'optimiser mais il faut déjà analyser les données d'entrée.

 

[p'tit vieux]

Alors c'est impossible de toutes les traiter avec la même approche.
Il vous faut au moins définir toutes les configurations possibles.
A partir de là faire un module qui détermine la syntaxe utilisée, puis d'autres modules pour traiter chaque configuration. Il sera peut être possible d'optimiser mais il faut déjà analyser les données d'entrée.

Si ce n'est pas trop exagéré, je vous propose de faire un échantillon des différentes syntaxes possibles.
Si vous êtes d'accord.
L'aspirine est pour moi C'est ma tournée .
Merci de vous intéresser. Ça fait du bien de ne pas être seul à réfléchir.
Bon je vous dis à demain... Si vous le voulez bien
Le p'tit vieux va se coucher.
Bonne nuit

Précision: jusqu'à présent j'avais réussi à faire le traitement nécessaire mais maintenant j'ai besoin d'être plus fin dans la lecture des paramètres. Avant c'était déjà pas coton.
Demain je vais vous schematiser le traitement car sinon trop de code.

 

[jurassic pork]

Hello,
j'ai fait un essai en python en utilisant les expressions régulières et voilà ce que j'obtiens :

formula = '=FOO(((A),(B),"C"),"D,E,F",((G),H))'
params = extract_params(formula)
print(params)
formula = '=FOO(((A1:B2, B5, B7, C8),(D5:E12),"9850"),"Banque, Caisse, Référence",((G),H))'
params = extract_params(formula)
print(params)
formula = '=FOO1(((A),(B),"C"),"D,E,F",(FOO2((a),(b),"c"),H))'
params = extract_params(formula)
print(params)

résultat:

['((A),(B),"C")', '"D', 'E', 'F"', '((G),H)']
['((A1:B2, B5, B7, C8),(D5:E12),"9850")', '"Banque', 'Caisse', 'Référence"', '((G),H)']
['((A),(B),"C")', '"D', 'E', 'F"', '(FOO2((a),(b),"c"),H)']

est-ce que cela correspond à ce que tu veux extraire (en python les [] correspondent à des tableaux et on peut utiliser les ' ou les " pour délimiter des chaînes ) ?
sachant que la fonction extract_params utilisent des expressions régulières et devrait être adaptable en VBA

Ami calmant, J.O

 

[p'tit vieux]

Hello,
j'ai fait un essai en python en utilisant les expressions régulières et voilà ce que j'obtiens :

formula = '=FOO(((A),(B),"C"),"D,E,F",((G),H))'
params = extract_params(formula)
print(params)
formula = '=FOO(((A1:B2, B5, B7, C8),(D5:E12),"9850"),"Banque, Caisse, Référence",((G),H))'
params = extract_params(formula)
print(params)
formula = '=FOO1(((A),(B),"C"),"D,E,F",(FOO2((a),(b),"c"),H))'
params = extract_params(formula)
print(params)

résultat:


est-ce que cela correspond à ce que tu veux extraire (en python les [] correspondent à des tableaux et on peut utiliser les ' ou les " pour délimiter des chaînes ) ?
sachant que la fonction extract_params utilisent des expressions régulières et devrait être adaptable en VBA

Ami calmant, J.O

Bonjour Jurassic
Merci pour ton intérêt.
Désolé mais ce n'est pas le résultat escompté.
La formule contient des paramètres (Euh? Si, si).
Ci après, pour vous et Sylvanu je donne plus d'explication sur la syntaxe.
Encore merci pour l'aide
PS: Je ne cause pas le Python

 

[p'tit vieux]

Voici les syntaxes.
Les exemples ne donnent une chronologie impérative des emplacements des différents séparateurs.

Exemple de base:
=Foo(A,B,C, … , n)

• P1 = A
• P2 = B
• P3 = C …

Les paramètres a lire sont A, B, C. Un petit split et le tour est joué.

Exemple plus complet:
=Foo1((A),(B),"C"),"D,E,F",Foo2((a),(b),"c"),H)
Décomposons pour montrer le résultat que l'on voudrait:

1. P1 = ((A),(B),"C") -> (A) = A1:B2 (B) = B5, B7, "C" = "Banque". A et B -> ici adresses de cellules
2. P2 = "D,E,F" -> peut-être plusieurs mots séparés par des ","
3. P3 = Foo2((a),(b),"c") -> P3 peut-être une fonction à analyser plus tard si nécessaire
4. P4 = H

Chaque paramètre sont séparés par des ",".
Mais là faire un Split sur les virgules ne donne "rien" de valable.
Sauf dans l'exemple 2 il y a parfois aussi des virgules séparant les éléments dans un paramètre.

Voilà pour la syntaxe générale.
En résumé je dois gérer différents séparateurs imbriqués. les " , " les " " " les " ( " et les " ) "
Merci de m'avoir lu.

 

[sylvanu]

Bonjour,
Détecter de quel type de formule on a affaire, et en extraire les paramètres en conséquence me semble trop compliqué pour moi. Sans indice sur la syntaxe je ne vois pas comment s'en sortir.

 

[jurassic pork]

Bon j'ai réussi à convertir le python en VBA. Voici ce que ce la donne :
1 - Une première fonction à base d'une regex : Extract_Params qui extrait ce qui est entre les parenthèses dans la fonction appelée par la formule.
2 - Une deuxième fonction Parse_inside_parentheses qui extrait les arguments entre parenthèses ou guillemets. L'astuce consiste à tester le niveau de profondeur des parenthèses et de guillemets.
3 - Une procédure PrintParams qui affiche les arguments trouvés dans la fenêtre d'exécution VBA
Le code :

Function Parse_inside_parentheses(s As String) As Object
        Dim x As Integer, car As String, depthp As Integer, depthg As Integer
        Dim params As Object, current_param As String
        Set params = CreateObject("System.Collections.ArrayList")
        Set Parse_inside_parentheses = params
        current_param = ""
        ' # Enlever les parenthèses externes
        's = Mid(s, 2, Len(s) - 2)
        depthp = 0
        depthg = 0
        For x = 1 To Len(s)
            car = Mid(s, x, 1)
            If car = "(" Then
               depthp = depthp + 1
            ElseIf car = ")" Then
               depthp = depthp - 1
            ElseIf car = """" And depthg = 0 Then
               depthg = depthg + 1
            ElseIf car = """" And depthg = 1 Then
               depthg = depthg - 1
            End If
            If (car = "," And depthp = 0 And depthg = 0) Then
                params.Add (Trim(current_param))
                current_param = ""
            Else
                current_param = current_param & car
            End If
        Next
        If Not current_param = "" Then params.Add (Trim(current_param))
        Set Parse_inside_parentheses = params
 End Function

Function Extract_Params(formula As String) As Object
Dim Regex As Object, ArrayObj As Object
Set Regex = CreateObject("VBScript.RegExp")
Regex.pattern = "^.+?\((.+)\)\s*$" ' on capture ce qui est entre les parenthèses extérieures"
Regex.Global = True
Set matches = Regex.Execute(formula)
If matches.Count > 0 Then Set Extract_Params = Parse_inside_parentheses(matches(0).submatches(0))
End Function

Sub PrintParams(params As Object)
 Dim N As Integer
 Debug.Print "==============================="
 For N = 0 To params.Count - 1
     Debug.Print N, params(N)
 Next N
End Sub

Code de test :

Sub TestExtraction()
Dim formula As String
Dim params As Object
formula = "=FOO(((A),(B),""C""),""D,E,F"",((G),H)) "
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=FOO(((A1:B2, B5, B7, C8),(D5:E12),""9850""),""Banque, Caisse, Référence"",((G),H))"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=Foo1(((A),(B),""C""),""D,E,F"",Foo2((a),(b),""c""),H)"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
End Sub

Résultat :

===============================
0 ((A),(B),"C")
1 "D,E,F"
2 ((G),H)
===============================
0 ((A1:B2, B5, B7, C8),(D5:E12),"9850")
1 "Banque, Caisse, Référence"
2 ((G),H)
===============================
0 ((A),(B),"C")
1 "D,E,F"
2 Foo2((a),(b),"c")
3 H

 

[p'tit vieux]

Alors c'est impossible de toutes les traiter avec la même approche.
Il vous faut au moins définir toutes les configurations possibles.
A partir de là faire un module qui détermine la syntaxe utilisée, puis d'autres modules pour traiter chaque configuration. Il sera peut être possible d'optimiser mais il faut déjà analyser les données d'entrée.

Bonjour Sylvanu
Voici quelques exemples:
1) "=Foo1(($N$2,$N$3),[Classeur1.xlsx]Feuil1!$B$2:$B$13,([Classeur1.xlsx]Feuil1!$A$2:[Classeur1.xlsx]Feuil1!$B$2))

2) "=Foo1(($N$2:$N$4),Foo2(""banque,caisse,référence,guichet"",B2:B366,(A2,C2)),""A2,C2"")"

3) "=Foo1(""I25,cheque,leclerc,SFR,pharm,J24"",C23:C35,(A2,C2,F2))

4) "=Foo1(""velo, leclerc,Super U,pharmacie"",Tableau1[[Date]:[RELEVE DE BANQUE]],Tableau1[@[RELEVE DE BANQUE]])

Chaque couleur correspond à un paramètre à extraire.

Si cela vous 'inspire ...

 

[p'tit vieux]

Bon j'ai réussi à convertir le python en VBA. Voici ce que ce la donne :
1 - Une première fonction à base d'une regex : Extract_Params qui extrait ce qui est entre les parenthèses dans la fonction appelée par la formule.
2 - Une deuxième fonction Parse_inside_parentheses qui extrait les arguments entre parenthèses ou guillemets. L'astuce consiste à tester le niveau de profondeur des parenthèses et de guillemets.
3 - Une procédure PrintParams qui affiche les arguments trouvés dans la fenêtre d'exécution VBA
Le code :

Function Parse_inside_parentheses(s As String) As Object
        Dim x As Integer, car As String, depthp As Integer, depthg As Integer
        Dim params As Object, current_param As String
        Set params = CreateObject("System.Collections.ArrayList")
        Set Parse_inside_parentheses = params
        current_param = ""
        ' # Enlever les parenthèses externes
        's = Mid(s, 2, Len(s) - 2)
        depthp = 0
        depthg = 0
        For x = 1 To Len(s)
            car = Mid(s, x, 1)
            If car = "(" Then
               depthp = depthp + 1
            ElseIf car = ")" Then
               depthp = depthp - 1
            ElseIf car = """" And depthg = 0 Then
               depthg = depthg + 1
            ElseIf car = """" And depthg = 1 Then
               depthg = depthg - 1
            End If
            If (car = "," And depthp = 0 And depthg = 0) Then
                params.Add (Trim(current_param))
                current_param = ""
            Else
                current_param = current_param & car
            End If
        Next
        If Not current_param = "" Then params.Add (Trim(current_param))
        Set Parse_inside_parentheses = params
 End Function

Function Extract_Params(formula As String) As Object
Dim Regex As Object, ArrayObj As Object
Set Regex = CreateObject("VBScript.RegExp")
Regex.pattern = "^.+?\((.+)\)\s*$" ' on capture ce qui est entre les parenthèses extérieures"
Regex.Global = True
Set matches = Regex.Execute(formula)
If matches.Count > 0 Then Set Extract_Params = Parse_inside_parentheses(matches(0).submatches(0))
End Function

Sub PrintParams(params As Object)
 Dim N As Integer
 Debug.Print "==============================="
 For N = 0 To params.Count - 1
     Debug.Print N, params(N)
 Next N
End Sub

Code de test :

Sub TestExtraction()
Dim formula As String
Dim params As Object
formula = "=FOO(((A),(B),""C""),""D,E,F"",((G),H)) "
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=FOO(((A1:B2, B5, B7, C8),(D5:E12),""9850""),""Banque, Caisse, Référence"",((G),H))"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=Foo1(((A),(B),""C""),""D,E,F"",Foo2((a),(b),""c""),H)"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
End Sub

Résultat :

WAOUH !! Plein les yeux
Je connais RegEx mais … de nom seulement
Je ne l'ai jamais "pratiqué". C'est presque un quasi langage et à l'air très puissant.
Je regarde … avec gourmandise et je reviens.

 

[p'tit vieux]

Bon j'ai réussi à convertir le python en VBA. Voici ce que ce la donne :
1 - Une première fonction à base d'une regex : Extract_Params qui extrait ce qui est entre les parenthèses dans la fonction appelée par la formule.
2 - Une deuxième fonction Parse_inside_parentheses qui extrait les arguments entre parenthèses ou guillemets. L'astuce consiste à tester le niveau de profondeur des parenthèses et de guillemets.
3 - Une procédure PrintParams qui affiche les arguments trouvés dans la fenêtre d'exécution VBA
Le code :

Function Parse_inside_parentheses(s As String) As Object
        Dim x As Integer, car As String, depthp As Integer, depthg As Integer
        Dim params As Object, current_param As String
        Set params = CreateObject("System.Collections.ArrayList")
        Set Parse_inside_parentheses = params
        current_param = ""
        ' # Enlever les parenthèses externes
        's = Mid(s, 2, Len(s) - 2)
        depthp = 0
        depthg = 0
        For x = 1 To Len(s)
            car = Mid(s, x, 1)
            If car = "(" Then
               depthp = depthp + 1
            ElseIf car = ")" Then
               depthp = depthp - 1
            ElseIf car = """" And depthg = 0 Then
               depthg = depthg + 1
            ElseIf car = """" And depthg = 1 Then
               depthg = depthg - 1
            End If
            If (car = "," And depthp = 0 And depthg = 0) Then
                params.Add (Trim(current_param))
                current_param = ""
            Else
                current_param = current_param & car
            End If
        Next
        If Not current_param = "" Then params.Add (Trim(current_param))
        Set Parse_inside_parentheses = params
 End Function

Function Extract_Params(formula As String) As Object
Dim Regex As Object, ArrayObj As Object
Set Regex = CreateObject("VBScript.RegExp")
Regex.pattern = "^.+?\((.+)\)\s*$" ' on capture ce qui est entre les parenthèses extérieures"
Regex.Global = True
Set matches = Regex.Execute(formula)
If matches.Count > 0 Then Set Extract_Params = Parse_inside_parentheses(matches(0).submatches(0))
End Function

Sub PrintParams(params As Object)
 Dim N As Integer
 Debug.Print "==============================="
 For N = 0 To params.Count - 1
     Debug.Print N, params(N)
 Next N
End Sub

Code de test :

Sub TestExtraction()
Dim formula As String
Dim params As Object
formula = "=FOO(((A),(B),""C""),""D,E,F"",((G),H)) "
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=FOO(((A1:B2, B5, B7, C8),(D5:E12),""9850""),""Banque, Caisse, Référence"",((G),H))"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
formula = "=Foo1(((A),(B),""C""),""D,E,F"",Foo2((a),(b),""c""),H)"
Set params = Extract_Params(formula)
If (Not params Is Nothing) Then PrintParams params
End Sub

Résultat :

BRAVO!
J'ai à priori toute les possibilité et … Ca fonctionne!!
J'en suis baba.
Bon je n'ai pas encore compris comment fonctionne RegEx mais c'est puissant. Surtout Pattern et la profondeur. Dés que j'ai un moment il faut que je vois ces histoires de Pattern. pas l'air simple tout de même.

Quelle épine en moins! Encore merci.
Grâce à toi Je vais pouvoir avancer en faisant l'analyse de chacun des paramètres.

Merci à vous Jurassic et Sylvanu pour votre aide.

PS:
Un petit cadeau qui pourrait vous être utile. Il s'agit d'une classe qui encapsule Dictionary en y apportant des fonctionnalités supplémentaires.
Bon ok, ce n'est pas grand chose mais on ne sait jamais.
Bientôt je vais mettre en ligne une autre classe basée sur Collection mais en contournant certaine de ses limites.

 

[jurassic pork]

Bon je n'ai pas encore compris comment fonctionne RegEx mais c'est puissant. Surtout Pattern et la profondeur. Dés que j'ai un moment il faut que je vois ces histoires de Pattern. pas l'air simple tout de même.

Ben en fait Regex n'est pas beaucoup utilisé dans mon code ( seulement dans Extract_Params). La gestion de la profondeur des ( et des " c'est du pur VBA. A noter que pour simplifier le code j'ai utilisé des ArrayList au lieu de tableaux.

 

[p'tit vieux]

Ben en fait Regex n'est pas beaucoup utilisé dans mon code ( seulement dans Extract_Params). La gestion de la profondeur des ( et des " c'est du pur VBA. A noter que pour simplifier le code j'ai utilisé des ArrayList au lieu de tableaux.

Oui après lecture plus approfondi de votre code j'ai (enfin) compris que tout le travail est construit autour de la boucle caractère par caractère avec les variables de "profondeur" DepthP et DepthG.
J'avais tenté cette approche mais je n'avais pas réussi à aller au bout.. Je me suis noyé .

Donc encore merci … pour la leçon.

PS:
Je trépigne de ne pas l'avoir trouvé. Je suis jaloux là.