calcul formule dans une cellule

[Kanetsugu]

Bonjour,

J'ai une macro qui applique une formule à des cellules d'une plage. La formule est assez lourde (récupération et somme de valeur dans un autre fichier)
et à chaque fois que j'applique ma formule, excel en effectue l'évaluation ... ce qui est très lent. Du coup avec plusieurs milliers de cellules, ça dure des heures.

J'aimerais pouvoir désactiver totalement le calcul (même manuel car avec xlCalculationManual ça ne fonctionne pas mieux).

Avez vous une solution ?
Merci d'avance

[Access Newbie]

Bonjour,

Avez-vous essayé d'enlever l'affichage ? et les evenements ?

Code :

1
2
application.screenupdating=false
application.enableevents=false

Laissez egalement le xlManual que vous repassez en auto a la fin du traitement.

Il faut bien sur les remettre a true en fin de traitement ou en cas d'erreur.

[Kanetsugu]

oui j'ai essayé d'ajouter ces instructions, j'ai aussi essayé Sheet("Feuil1").EnableCalculation = False et c'est toujours aussi lent.

[Qwazerty]

Salut

Essai comme cela, si tu es sûr que l’état de départ et d'arrivée sera toujours Automatique, inutile d'utiliser la mémorisation, je te la met pour info.

Code :

1
2
3
4
5
6
7
8
9
10
Dim MemoCalcul As XlCalculation
'On mémorise l'etat actuel
MemoCalcul = Application.Calculation
'On passe en mode de calcul manuel
Application.Calculation = xlCalculationManual
 
'[...]Suite de ton code
 
'On repasse à l'etat du mode de calcul de départ
Application.Calculation = MemoCalcul

Si ton tableau est vraiment volumineux, il serait peut-être judicieux de passer par un tableau interne dont la vitesse est sans commune mesure avec une modification à la volée directement sur une feuille de calcul. Si tu es intéressé, place ton code pour voir se qu'il est possible de faire (avec des explications si nécessaire).

++
Qwaz

[Kanetsugu]

J'ai trouvé un moyen d'accélérer les choses, je n'applique plus la formule à chaque ligne mais seulement à la première ligne. Ensuite je fais un autofill sur le reste de la plage.

Cependant, pour appliquer cette méthode je dois modifier légèrement ma formule pour que ça fonctionne et j'ai un peu de la peine.

Ma formule de base est la suivante :
Je vais faire la somme d'un certains nombre de cellules dans une autre feuille qui ont une référence commune, je plafonne ensuite cette somme.

Code :

=PLAFOND(SOMMEPROD(('C:\Admin\x\[Journal.xls]Journal'!$F$4:$F$4000=$C8)*('C:\Admin\x\[Journal.xls]Journal'!$F$4:$F$4000>0);'C:\Admin\x\[Journal.xls]Journal'!$E$4:$E$4000);"00:01")

Le chemin est variable (x) et il est d'abord calculé et mis dans une plage de cellule. J'aimerais donc que ma formule prenne le chemin voulu dans la plage.

J'ai essayé quelque chose comme ça (avec le chemin pour chaque cellule en AA) :

Code :

=PLAFOND(SOMMEPROD((CONCATENER(AA1;"!$F$4:$F$4000=$C1"))*(CONCATENER(chemin;"!$F$4:$F$4000>0"));CONCATENER(chemin;"!$E$4:$E$4000"));"00:01")

Mais ça ne fonctionne pas

Est-ce que quelqu'un pourrait m'aider à construire cette formule ?
Merci d'avance.

[Access Newbie]

Bonjour,

Avez-vous essayé comme ceci ?

Code :

=PLAFOND(SOMMEPROD((CONCATENER(AA1;"!$F$4:$F$4000=$C1"))*(CONCATENER(AA1;"!$F$4:$F$4000>0"));CONCATENER(AA1;"!$E$4:$E$4000"));"00:01")

[Qwazerty]

Salut
Concatener renvoi une chaîne de texte, sommeProd attend une plage de cellules.

Il faut a mon avis utiliser Indirect

Code :

=PLAFOND(SOMMEPROD((INDIRECT("'" & AA1 & "'!$F$4:$F$4000")=$C1)*(INDIRECT("'" & AA1 & "'!$F$4:$F$4000")>0);INDIRECT("'" & AA1 & "'!$E$4:$E$4000"));"00:01")

Pas testé.

++
Qwaz

[Kanetsugu]

Merci pour vos réponses

j'ai essayé ta solution Qwazerty, mais j'obtiens un #REF! comme résultat

Peut-être pour simplifier, est-ce qu'il existe un moyen de coller la formule complète sous forme de texte (puisqu'elle fonctionne) dans une cellule temporaire et ensuite d'utiliser la chaîne de texte comme formule d'une autre cellule ?

[Qwazerty]

Salut

Je viens de faire quelques recherche, en effet Indirect ne peut pas pointer un fichier externe identifié par un lien. Il existe une autre version d'Indirect, nommé Indirect.ext, mais celle ci n'est pas disponible les functions de base d'Excel (pack MoreFunction).

Malheureusement, tu ne peux pas utiliser une chaîne de texte comme étant un chemin valide vers une cellule extérieur à ton classeur (Classeur fermé).
Il ne faut pas confondre

Code :

'C:\Admin\x\[Journal.xls]Journal'!$F$4:$F$4000

Et

Code :

"'C:\Admin\x\[Journal.xls]Journal'!$F$4:$F$4000"

Dans le premier cas tu fais référence à une cellule, dans le 2ème cas tu notes le chemin d'une cellule au format texte. Donc dans le 1er cas tu peux travailler avec pour regarder le contenu de ta cellule, dans le 2ème cas ... il faudrait utiliser Indirect.Ext, ce qui impose d'installer un package supplémentaire (pas toujours possible sur des PC d'une entreprise).

Je n'ai pas d'autre solution en tête, peut-être d'autre auront mieux à te proposer.

++
Qwaz