Utiliser formule excel dans VBA

[david84]

Bonjour,
j'ai la formule Excel suivante :

=TEXTE(MAX(SI(ESTNUM(SI(NB.SI(Tableau4[N° AGREMENT];"84-" & ANNEE(MAINTENANT()) &"-"&"*");CNUM(STXT(Tableau4[N° AGREMENT];CHERCHE(ANNEE(MAINTENANT());Tableau4[N° AGREMENT])+5;99))));SI(NB.SI(Tableau4[N° AGREMENT];"84-" & ANNEE(MAINTENANT()) &"-"&"*");CNUM(STXT(Tableau4[N° AGREMENT];CHERCHE(ANNEE(MAINTENANT());Tableau4[N° AGREMENT])+5;99));""))+1);"00")

C'est une formule matricielle.
Elle me ramène le numéro max +1 des codes commençant par 84-2010- (année) présent dans la colonne AGREMENT et dans un format "00".

J'aimerais l'utiliser cette formule dans une textbox afin de permettre à l'utilisateur d'enregistrer le numéro d'agrément sans avoir à la taper puisque la formule ramène directement le n° suivant (MAX+1).
Est-ce possible ?
J'ai crû comprendre que oui mais je n'y arrive pas.
J'ai tenté avec FormulaLocale entre autre mais sans succès.
Cette formule fonctionne dans une feuille Excel mais lorsque je la recopie en l'état dans mon code VBA , il me signale une erreur de compilation (erreur de syntaxe).

J'ai tenté de tester ce code à l'initialisation de mon UserForm mais il ne se lance même pas (cela plante au niveau de la formule) :

MaxAgrément = la formule
TexBox1.formulaLocale = MaxAgrément

Faut-il adapter la syntaxe de la formule à VBA ou peut-on la rentrer telle quelle ?
Où ai-je fait l'erreur ?
Et d'ailleurs peut-on utiliser des formules matricielles de ce type dans VBA, et si oui comment s'y prendre ?

Merci pour vos conseils.
A+

 

[tototiti2008]

Re : Utiliser formule excel dans VBA

Bonjour David,

Il me semble qu'il faudrait passer par Evaluate et dans ta formule, remplacer les noms de fonctions par leurs équivalents anglais et les points-virgules par des virgules
Ou tout recoder en VBA, bien sûr

ici :
https://www.excel-downloads.com/threads/calcul-semaine-vba.153077/

post 17, Michel propose l'utilisation d'Evaluate par exemple

 

[JNP]

Re : Utiliser formule excel dans VBA

Bonjour le fil ,
Oui, TotoTiti a bien raison .
Plusieurs remarques :
Un TextBox n'est pas une cellule, il n'accepte pas de formule
.FormulaLocal ne prends pas de "e"
De plus, il ne traduit qu'une formule traditionnelle en langage local, pour une matricielle, c'est .FormulaArray, mais comme je ne pense pas qu'il y ait de FormulaArrayLocal, la formule doit être en anglais .
En anglais, la formule de départ donne

=TEXT(MAX(IF(ISNUMBER(IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)))),IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)),""""))+1),""00"")"

mais elle est tellement longue que si on l'enregistre avec l'enregistreur de macro en la mettant dans une cellule, la macro enregistrée beugue car elle n'arrive pas à l'écrire dans l'autre sens ...
C'est bête parce que le plus simple aurait été de mettre la formule dans une cellule le temps de récupérer son résultat
Si on passe par Evaluate, il va en plus falloir traduire les Tableau4[N° AGREMENT] en Range ...
Au passage, pour utiliser des fonctions de feuilles, c'est

Application.WorksheetFunction.CounIf

par exemple pour NB.SI mais certaine fonctions ne sont pas dans celles-ci et il faut utiliser les fonctions natives comme CDbl car il n'y a pas d'équivalent de CNUM dans Application.WorksheetFunction ...
Du fait, j'utiliserais plutôt la solution

Ou tout recoder en VBA, bien sûr

soit

Dim Résultat As Double, c As Range, firstAddress As String
With Range("Tableau4[N° AGREMENT]")
    Set c = .Find("84-" & Year(Now()) & "-", LookAt:=xlPart)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            If CDbl(Split(c, "-")(2)) > Résultat Then Résultat = CDbl(Split(c, "-")(2))
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
MsgBox Résultat + 1

Bonne lecture

 

[david84]

Re : Utiliser formule excel dans VBA

Re
Merci à vous deux.
Tototiti : par Evaluate cela ne donnait rien de particulier.
Je suis alors passé par l'enregistreur de macro et cela m'a donné :

FormulaArray = _
        "=TEXT(MAX(IF(ISNUMBER(IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)))),IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)),""""))+1),""00"")"

Mais le résultat n'était pas au rendez-vous (j'avais un beau FALSE qui s'affichait dans mon Textbox ).

JN :

En anglais, la formule de départ donne
Code:

=TEXT(MAX(IF(ISNUMBER(IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)))),IF(COUNTIF(Tableau4[N° AGREMENT],""84-"" & YEAR(NOW()) &""-""&""*""),VALUE(MID(Tableau4[N° AGREMENT],SEARCH(YEAR(NOW()),Tableau4[N° AGREMENT])+5,99)),""""))+1),""00"")"

mais elle est tellement longue que si on l'enregistre avec l'enregistreur de macro en la mettant dans une cellule, la macro enregistrée beugue car elle n'arrive pas à l'écrire dans l'autre sens .

Ben oui je l'ai constaté.
Je me suis donc rabattu sur ton code et comme d'habitude, cela fonctionne nickel (t'es vraiment un chef !).
Merci à tous les deux et à +