Extraire paramètres (arguments) d'une fonction Excel en VBA
Bonjour forum !
Je soumets à votre sagacité un problème que je cherche à résoudre et j'aimerais avoir votre avis là-dessus !
Je cherche à extraire en VBA les arguments d'une fonction excel.
Exemple de formule :
CONCATENER(A1;A2;A3;A5)Je souhaiterais récupérer (dans des cellules, dans une variable tableau, etc.) les paramètres :
A1
A2
A3
A5Lorsque les formules sont simples (ie. pas de fonctions imbriquées), pas de souci. Un Split en VBA fait l'affaire.
Ça se corse un peu lorsqu'on a des fonctions/sous-fonctions/sous-sous-fonctions... imbriquées.
Ex fictif :
CONCATENER([surligner=#40FF00]SI(A2="blabla";GAUCHE(A2;3);"truc");[surligner=#FFFF00]DROITE(A2;3);[surligner=#BFFFFF]A3+MOIS(A4)+A12;A5*7)Dans ce cas, je récupèrerais :
SI(A2="blabla";GAUCHE(A2;3);"truc")
DROITE(A2;3)
A3+MOIS(A4)+A12
A5*7Le but ultime de l'exercice étant de rajouter en VBA un paramètre à cette fonction à une position donnée...
Etant donné que les formules à traiter sont nombreuses, il faudrait une solution qui traite les données très rapidement.
Merci d'avance pour vos idées !
Bonjour,
Il m'arrive de parcourir les forums (Excel) afin de trouver un exercice pour m'occuper ou me changer d'occupation...
Je suis tombé sur ta question hier, à un moment où elle était encore dans le haut de la page, et sans réponse apportée, qui m'a semblé tout à fait convenir pour m'exercer au moment où je l'ai lue...
Habituellement, pour autant que j'avais pu le constater de l'extérieur lors de mes brefs passages, ce forum étant plutôt réactif, peu de temps s'écoule avant qu'un ou plusieurs intervenants apportent des réponses...
Surpris de voir ce matin que ta question était toujours sans réponse, j'ai donc décidé de te faire partager mon travail d'hier, dont j'espère qu'il pourra t'aider à solutionner ton problème.
Tu y trouveras une macro conçue pour extraire les arguments d'une fonction incluse dans une formule. En lui fournissant : la formule, la fonction dont on recherche les arguments, et optionnellement le rang de l'occurrence de la fonction dans la formule si plusieurs y figurent. La longueur de la macro est principalement due aux tests successifs d'identification et délimitation de la fonction. Elle se termine par l'affichage des arguments dans un message. Egalement une macro 'test' pour lancer son exécution en lui fournissant la formule et le nom de la fonction. Ton attention sur le fait que la formule est appelée par : Range.FormulaLocal (Range.Formula la renverrait en anglais...)
A toi de l'adapter à ton projet.
Bonne continuation.
Bonjour à tous les deux,
Voici une solution qui devrait fonctionner peu importe le nombre d'imbrications :
Par exemple : =ARGUMENT_FONCTION(A1;1) renvoie SI(A2="blabla";GAUCHE(A2;3);"truc")
La fonction ARGUMENT_FONCTION :
Function ARGUMENT_FONCTION(cellule As Range, position As Integer)
formule = cellule.FormulaLocal
'Formule sans la première fonction
formule_b = Split(formule, "(")
formule_b(0) = ""
formule = Join(formule_b, "(")
formule = Mid(formule, 2, Len(formule) - 2)
'Split des arguments
compteur = 0
Do
compteur = compteur + 1
formule_b = Split(formule, ";")
recommencer = False
For i = 0 To UBound(formule_b)
If UBound(Split(formule_b(i), "(")) <> UBound(Split(formule_b(i), ")")) Then
formule_b(i) = formule_b(i) & ";;"
formule = Join(formule_b, ";")
formule = Replace(formule, ";;;", "XLP")
recommencer = True
Exit For
End If
Next
Loop While recommencer And compteur < 10000
If compteur < 10000 Then
ARGUMENT_FONCTION = Replace(formule_b(position - 1), "XLP", ";")
Else
ARGUMENT_FONCTION = "Erreur formule"
End If
End FunctionCordialement,
Salut MFerrand, Sébastien, forum
Merci pour vos contributions !
@MFerrand : Si les réponses ont tardé à venir c'est probablement parce que l'exercice n'était pas si simple !
En tout cas pas mal du tout ta solution. J'ai essayé sur plus de 250 000 cellules, la restitution de la liste des arguments dans une variable tableau plutôt que par msgbox prend seulement une vingtaine de secondes.
@Sébastien : merci également pour ta solution. Elle a le mérite d'être plus courte, facilement adaptable si je veux ajouter un paramètre à la fonction, et plus rapide ! En la réadaptant un peu pour stocker en mémoire tous les paramètres, j'obtiens un temps de traitement d'un peu moins de 10 s pour les mêmes 250 000 cellules !
Les algorithmes utilisés sont très différents, ce qui les rend intéressants ! Je laisse le post ouvert encore quelques jours pour voir si d'autres membres proposent d'autres algo !
Encore merci à tous les 2... et à tous ceux qui ont essayé de trouver une solution...
Bonsoir, et merci à toi,
Cela m'a conduit à m'inscrire sur ce forum...
Comme je l'ai dit, le message à la fin était juste pour illustrer en testant. De même selon tes besoins, tu peux simplifier un certain nombre d'éléments.
J'ai noté aussi l'intéressante approche de Sébastien, et la subtilité des transformations successives...
A+
Je te souhaite alors la
Bonjour à tous,
Une autre idée.
Sub test()
Dim f As String, f1 As String, i As Long
Dim ff, ff1, dict
Set dict = CreateObject("Scripting.Dictionary")
f = [A1].Formula
f = Replace(Replace(Replace(f, "=", ","), ")", ","), "(", ",")
f = Replace(Replace(Replace(Replace(f, "+", ","), "-", ","), "*", ","), "/", ",")
f1 = [A1].FormulaR1C1
f1 = Replace(Replace(Replace(f1, "=", ","), ")", ","), "(", ",")
f1 = Replace(Replace(Replace(Replace(f1, "+", ","), "-", ","), "*", ","), "/", ",")
ff = Split(f, ",")
ff1 = Split(f1, ",")
For i = 1 To UBound(ff)
If ff(i) <> ff1(i) Then
dict(ff(i)) = dict(ff(i)) + 1
End If
Next i
[B1].Resize(dict.Count) = Application.Transpose(dict.keys)
End SubAucune idée du temps de traitement
Pas mal de Replace mais comme d'un autre coté il n'y a qu'une boucle courte...
eric
PS: j'ai oublié quelques opérateurs dans les replace : > < ^ et peut-être d'autres
Bonjour eriiic,
Merci pour la proposition !
Mais avec la formule
CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7)Ca me sort un peu n'importe quoi
A2
"blabla"
LEFT
3
"truc"
RIGHT
A3
MONTH
A4
A12
A5
7Je ne cherche à récupérer que les arguments de la fonction CONCATENER dans ce cas.
Ah ? Pourtant...
J'en ai profité pour rajouter les opérateurs oubliés.
Le principe est de spliter la formule en étant sûr de découper de part et d'autre des références, puis de comparer la version adresses type A1 avec la version type L1C1.
Si c'est identique c'est un bout de formule sans intérêt, si c'est différent c'est qu'il s'agit d'une référence à récupérer.
NB: A2 sera considéré comme une ref différente de A$2 etc
eric
Toujours pas
Options linguistiques tu penses...??
Ou alors, je n'ai pas bien compris le principe...
Heuuu, c'est moi qui ne comprend plus, j'ai l'impression d'avoir bu...
Sur ta capture écran je vois bien :
A2
A3
A4
A12
A5
en B1:B5
Et tu l'as en tableau dans dict.keys
Ta demande :
Je souhaiterais récupérer (dans des cellules, dans une variable tableau, etc.) les paramètres :
CODE: TOUT SÉLECTIONNER
A1
A2
A3
A5
Qu'est-ce qui n'est pas bon et que j'ai mal compris ?
Ah ok. Je n'ai pas été assez clair alors.
Le cas que tu viens de citer, c'est le cas simple. Et je n'ai pas de souci avec celui-là.
C'est le cas avec des fonctions imbriquées que je souhaite résoudre :
Lorsque les formules sont simples (ie. pas de fonctions imbriquées), pas de souci. Un Split en VBA fait l'affaire.
Ça se corse un peu lorsqu'on a des fonctions/sous-fonctions/sous-sous-fonctions... imbriquées.
Ex fictif :
CONCATENER([surligner=#40FF00]SI(A2="blabla";GAUCHE(A2;3);"truc");[surligner=#FFFF00]DROITE(A2;3);[surligner=#BFFFFF]A3+MOIS(A4)+A12;A5*7)Dans ce cas, je récupèrerais :
SI(A2="blabla";GAUCHE(A2;3);"truc") DROITE(A2;3) A3+MOIS(A4)+A12 A5*7
Effectivement.
Non, tu avais bien expliqué, c'est moi qui n'en avait lu que la moitié je crois
Bonjour eriiic, forum
Tu es pardonné Eric
@Sebastien : J'ai fait quelques tests supplémentaires et je rencontre un cas où ton algo ne marche pas. C'est lorsque la fonction CONCATENER fait partie d'un argument d'une autre fonction.
Exemple 1 :
=SIERREUR(CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7);"")
Exemple 2 :
=SI(A1="toto";"";CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7))J'ai fait des tests avec l'algorithme de MFerrand (dont la particularité est de permettre de cibler la fonction dont on souhaite extraire les arguments) et ça marche nickel !
Donc Sébastien, si jamais tu as une version optimisée qui tient compte de ce cas, je suis preneur !
Bonjour à tous,
@Sebastien : J'ai fait quelques tests supplémentaires et je rencontre un cas où ton algo ne marche pas. C'est lorsque la fonction CONCATENER fait partie d'un argument d'une autre fonction.
La fonction est prévue pour extraire les arguments de la première fonction.
Si tu veux que la fonction extraie les arguments de CONCATENER au lieu de ceux de la première fonction, il te suffit de modifier la première partie du code de façon à ce que la variable "formule" contienne le contenu entre ( ) de CONCATENER :
formule = Mid(formule, 2, Len(formule) - 2) 'formule = contenu de la fonction à spliter
Cordialement,
Salut forum, Sébastien,
Merci pour la précision Sébastien !
Mais je t'avoue que je n'arrive pas trop à adapter ton code pour tenir compte des cas que j'ai cités, mais aussi d'autres cas comme :
=A3&CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7)&"machinchose"
ou plus tordu
=A3&CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7)&"));)("Donc si tu as une solution pour ça, je serais curieux de voir comment tu fais !
Salut vba-new, Salut à tous,
Animé finalement ce sujet ! Je recontribue avec une fonction à laquelle on fournit la référence de la plage contenant la formule et le rang de la fonction à décomposer dans la formule (j'ai d'ailleurs oublié de le mettre par défaut à 1 mais pas important...).
Illustration en utilisation sur feuille de calcul :
'pf = cellule contenant la formule (contenant la fonction recherchée)
'rf = rang de la fonction recherchée dans la formule
'
Function ARGFCTN(pf As Range, rf As Integer)
Dim i%, h%, k%, lf%, fa$, af
fa = pf.Cells(1, 1).FormulaLocal
If Left(fa, 1) = "=" Then 'On ôte le signe =
lf = Len(fa) - 1
fa = Right(fa, lf)
af = Split(fa, "(")
For i = 0 To UBound(af)
For h = Len(af(i)) To 1 Step -1
k = Asc(Mid(af(i), h, 1))
If k < 65 Or k > 90 Then
af(i) = Right(af(i), Len(af(i)) - h)
Exit For
End If
Next h
If af(i) = "" Then af(i) = "@"
Next i
af = Join(af, ";")
af = Replace(af, ";@", "")
af = Replace(af, "@", "")
af = Split(af, ";") 'Tableau des noms de fonctions incluses dans la formule
If rf <= UBound(af) + 1 Then
For i = 0 To rf - 1
h = InStr(1, fa, af(i))
h = InStr(h, fa, "(")
lf = lf - h + 1
fa = Right(fa, lf)
Next i
h = 0
k = 0
For i = 1 To lf
If Mid(fa, i, 1) = "(" Then
h = h + 1
ElseIf Mid(fa, i, 1) = ")" Then
k = k + 1
If k = h Then Exit For
End If
Next i
If i <= lf Then
lf = i - 2
fa = Mid(fa, 2, lf) 'Arguments fonction (sans prenth. d'extrémités)
h = 0
For k = 1 To lf
If Mid(fa, k, 1) = "(" Then h = h + 1
If Mid(fa, k, 1) = ")" Then h = h - 1
If Mid(fa, k, 1) = ";" Then
If h = 0 Then Mid(fa, k, 1) = Chr(135)
End If
Next k
af = IIf(fa <> "", Split(fa, Chr(135)), "sans") 'Tableau des arguments
ARGFCTN = af
Exit Function
End If
End If
End If
ARGFCTN = CVErr(xlErrNA)
End FunctionJe n'ai pas encore suffisamment testé pour être sûr qu'il n'y a plus de cas à régler, j'ai réglé de façon un peu trop "intuitive" 2 bogues : l'un pour le cas où la fonction n'a pas d'argument (qui devait afficher "sans") et l'autre pour le cas où la formule ne comporte pas de fonction (qui devait afficher #N/A). Mais comme pour le cas standard ça avait l'air de fonctionner...
Cordialement,
Ferrand
Re MFerrand,
L'idée est très intéressante ! Bien que j'aie peur du temps d'exécution...
J'ai fait quelques tests et j'ai vu un cas où ça ne fonctionne pas
=A1&CONCATENER(SI(A2="blabla";GAUCHE(A2;3);"truc((");DROITE(A2;3);A3+MOIS(A4)+A12;A5*7)&B1C'est le &B1 à la fin qu'il n'aime pas...
Merci,
Je vais revoir...
C'est sûrement ce morceau : "truc((" qui fausse la suite ! Deux parenthèses incluses dans un élément chaîne qui n'intervient pas dans le fonctionnement de la fonction.
Pour prévoir ce type de cas, il va falloir détecter les chaînes, voir si elles contiennent des (, ) ou ; et les remplacer par des caractères de substitution, puis les rétablir à la fin.
Là, ça va allonger un petit peu ! Mais pour une utilisation à l'unité, pas vraiment un problème. Je regarderai plus dans le détail (quand j'aurai un peu de temps pour) à quel moment ça décroche pour basculer en "non reconnu comme fonction valide" sur le décompte des parenthèses.
Mais elle produit #N/A et non une autre erreur résultant d'une erreur d'exécution dans le code !
Bonne nuit
Ferrand