XL 2013 Formule VBA trop longue alors que correcte en excel

[escouger]

Bonjour,
J'ai écrit une macro en VBA qui génère une formule, que je charge ensuite dans une cellule.
Cette formule est très longue (1557 caractères!).
Si je la saisi manuellement dans une cellule de mon tableau excel (2013) çà marche.
Par contre lorsque je la charge (ActiveCell.FormulaR1C1 = zresul) (qui fait 1557 caractères) excel rejette la formule dès que l'instruction et ne l’interprète pas.
Si je réduis artificiellement la formule à moins de 1000 caractères (mais en perdant le résultat souhaité) çà passe.

Y-aurait-il une manière de dépasser ce problème lié à l'interprétation de cette formule que voici:

=IF(ISNA(VLOOKUP(RC[-5],'[20150114_RM2.xls]Rando '!R1C16:R138C16,1,FALSE)),"000000-000000-000000-000000-000000-000000-000000-000000-000000",

if(RC[-5] = A9,

REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Distance))& '[20150114_RM2.xls]Rando '!Distance & ","& REPT(0,6-LEN(R[-2]C ))&R[-2]C &","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!DenivPos)) & '[20150114_RM2.xls]Rando '!DenivPos&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!DenivNeg)) & '[20150114_RM2.xls]Rando '!DenivNeg&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!TRJKM)) & '[20150114_RM2.xls]Rando '!TRJKM&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Heures_de_Bénévolat)) & '[20150114_RM2.xls]Rando '!Heures_de_Bénévolat&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!KM_de_Bénévolat)) & '[20150114_RM2.xls]Rando '!KM_de_Bénévolat&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Pausa)) & '[20150114_RM2.xls]Rando '!Pausa&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Durea)) & '[20150114_RM2.xls]Rando '!Durea,

REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Distance))& '[20150114_RM2.xls]Rando '!Distance & ","& REPT(0,6-LEN(R[-2]C ))&R[-2]C &","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!DenivPos)) & '[20150114_RM2.xls]Rando '!DenivPos&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!DenivNeg)) & '[20150114_RM2.xls]Rando '!DenivNeg&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!TRJKM)) & '[20150114_RM2.xls]Rando '!TRJKM&","&000000,000000&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Pausa)) & '[20150114_RM2.xls]Rando '!Pausa&","& REPT(0,6-LEN('[20150114_RM2.xls]Rando '!Durea)) & '[20150114_RM2.xls]Rando '!Durea))

Si je saisis la même formule dans excel çà marche :

=SI(ESTNA(RECHERCHEV(B9;'C:\CVS\[20150114_RM2.xls]Rando '!$P$1:$P$138;1;FAUX));"000000-000000-000000-000000-000000-000000-000000-000000-000000";

SI(B9 = A9;

REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!Distance))& 'C:\CVS\[20150114_RM2.xls]Rando '!Distance & ","& REPT(0;6-NBCAR(G7 ))&G7 &","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!DenivPos)) & 'C:\CVS\[20150114_RM2.xls]Rando '!DenivPos&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!DenivNeg)) & 'C:\CVS\[20150114_RM2.xls]Rando '!DenivNeg&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!TRJKM)) & 'C:\CVS\[20150114_RM2.xls]Rando '!TRJKM&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!Heures_de_Bénévolat)) & 'C:\CVS\[20150114_RM2.xls]Rando '!Heures_de_Bénévolat&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!KM_de_Bénévolat)) & 'C:\CVS\[20150114_RM2.xls]Rando '!KM_de_Bénévolat&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!pausa)) & 'C:\CVS\[20150114_RM2.xls]Rando '!pausa&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!durea)) & 'C:\CVS\[20150114_RM2.xls]Rando '!durea;

REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!Distance))& 'C:\CVS\[20150114_RM2.xls]Rando '!Distance & ","& REPT(0;6-NBCAR(G7 ))&G7 &","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!DenivPos)) & 'C:\CVS\[20150114_RM2.xls]Rando '!DenivPos&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!DenivNeg)) & 'C:\CVS\[20150114_RM2.xls]Rando '!DenivNeg&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!TRJKM)) & 'C:\CVS\[20150114_RM2.xls]Rando '!TRJKM&","&"000000-000000"&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!pausa)) & 'C:\CVS\[20150114_RM2.xls]Rando '!pausa&","& REPT(0;6-NBCAR('C:\CVS\[20150114_RM2.xls]Rando '!durea)) & 'C:\CVS\[20150114_RM2.xls]Rando '!durea))

Merci d'avance de votre expertise
GE

 

[gosselien]

Re : Formule VBA trop longue alors que correcte en excel

Bonjour,

je doute que ça soit facile de réduire ça sans que te ne fournisses aux experts (dont je ne fais pas partie) le ou les fichier(s) concernés ou une copie anonymisée...

 

[escouger]

Re : Formule VBA trop longue alors que correcte en excel

Il suffit de faire un copier coller du texte commençant par =IF(ISNA(VLOOKUP.... puis de le coller derriere
une instruction ActiveCell.FormulaR1C1 = dans une macro
puis de regarder ce qui se passe dans la cellule active.

Ce sera plus simple que d'envoyer mes fichiers qui sont bien plus complexes.

 

[Marc L]

Re : Formule VBA trop longue alors que correcte en excel

Bonjour,

quand dans une formule de feuille de calculs il y a des guillemets,
il faut les doubler en VBA car les guillemets y encadrent déjà chaque chaîne de caractères …

​

 

[denis]

Re : Formule VBA trop longue alors que correcte en excel

je peux faire une réflexion svp, car ça représente ce, contre quoi je me battais
J'ai été dans une grande banque française, et leurs fichiers excels étaient remplis de formules ésotériques que seul le Créateur avait le temps de comprendre et de faire fonctionner; mais que de simples employés qu'étaient mes collegues, bah ils laissaient tomber.

Ce truc mérite une petite(?) macro ou une fonction personnalisée, ou un mini-logiciel perso, avec des commentaires qui explique ce que ça fait, si même Excel trouve que c'est trop long, hé bien c'est peut etre un peu long ni efficient O.O !

 

[escouger]

Re : Formule VBA trop longue alors que correcte en excel

Bonjour,
Je comprends parfaitement ce commentaire ety souscrit.
Je voudrais ssimplement dire que cette formule est construite dans une macro et en aucun tapée dans la cellule. Elle fonctionne depuis des années.
Le souci vient du fait que j'ai dû ajouter une condition, qui de fait double la longueur du code, le faisant passer de 750 caractères à environ 1500.
J'ai vu dans la DOC Microsoft qu'avec excel 2013 les formules pouvaient aller jusqu'à 8000 caractères.
Malheureusement il semble que la limite soit restée à 1024 lorsqu'on passe par une construction incluse dans une macro.
Merci de votre commentaire qui reste un excellent conseil pour tous développeur soucieux de l'avenir promis à son travail par les autres chargés de la maintenance'