Optimisation temps macro (enable calculation)

Bonjour à tous,

Merci d'avance de prendre de votre temps pour vous occuper de mon problème.

J'ai une macro VBA ( trop compliquée et confidentielle pour que je puisse la poster sur ce forum, désolé :/) sur laquelle je travaille depuis plusieurs semaines dans le cadre de mon travail.

Pour faire simple, cette macro utilise le solver Excel pour résoudre un système de 28équations à 28inconnues... De nombreuses configurations sont à simuler, et tous les résultats que me donnent le solver sont regroupés dans une seule feuille Excel: «results». Ensuite d'autres feuilles Excel («sketch_select») vont piocher les valeurs qui vont bien dans cette feuille «results» pour tracer les bonnes courbes.

Pour optimiser le temps de réalisation de ma macro, j'ai désactiver le calcul de ces feuilles «sketch_select».

Ma macro tourne alors pendant 2 min... C'est pas trop mal, mais cela peut devenir handicapant lorsqu'elle doit tourner 50fois de suite... Mais jusque là, tout va plutôt bien!

Mon problème arrive ici: quand je supprime les feuilles sketch_select (dont le calcul avait tout de même était désactivé pendant la macro), ma macro ne met plus que 1 minute pour tourner!

Comment est ce possible, sachant que je désactive le calcul de ces feuilles pendant tout le temps de réalisation de la macro?
Avez-vous des solutions?

Je vois bien la solution avec la macro de déplacer les feuilles «Sketch_select» vers un autre classeur, remplacer les formules pour casser les liens, faire tourner la macro puis ré-déplacer les feuilles vers l'excel d'origine... Mais cette solution est lourde :/

Toute idée et aide est la bienvenue :)

Merci à vous et bon week-end ;)

Bonjour,

J'aurais bien une réponse mais trop compliquée et confidentielle pour que je puisse la poster sur ce forum, désolé...

Bonjour,

rares sont les codes VBA qui réinventent la poudre, et peuvent se considérer comme confidentiels

et en général, les auteurs de ce type de macros ne butent pas sur un problème, ils vont le résoudre

comme tu l'auras peut être compris, si c'est trop compliqué pour nous, pourquoi nous poser une question ?

si le code est bien pensé et bien structuré, tu n'est pas censé nous le montrer intégralement, mais uniquement sélectionner les partie où tu penses que ça impacte ta problématique.

tu n'as même pas pris la peine de montrer comment tu utilises ton enable calculation (à quel endroit, de quelle manière, quand il est remis à True) ou le code que pourrait contenir cette feuille

en aveugle, on ne pourra pas te fournir quoi que ce soit qui puisse être une piste ... sur des procédures compliquées

Moi, je me dis qu'en ce bas monde, il peut y avoir mieux qu'Excel.

Pour juste l'effort de télécharger et d'attendre que l'installation se fasse; c'est l'affaire de quelques heures, dépendant de la vitesse de téléchargement, ont peut avoir Visual Studio Community.

Visual Studio Community donne accès à la réalisation de programmes VB (F#, C#, C++, etc.) compilés au lieu de juste interprétés. Cela donne aussi accès à de nombreux utilitaires gratuits ou presque, grâce à NuGet et l'accès facile `GitHub. Même qu'il existe une DLL du Solver ($$$$) compatible VB.net

Et puis, une adresse tellement confidentielle que personne ne pense de l'essayer:

http://www.solver.com

Et puis un autre truc tellement confidentiel que tout le monde connaît:

Code:

---

application.screenupdating = false

---

Bonjour à tous,

Merci à ceux qui se sont donnés la peine de me donner une réponse construite...

Apparemment le mot "confidentiel" vous pose problème... Je me doute bien que je ne viens pas de ré-inventer l'eau chaude, mais ce fichier a simplement été rédigé lors de mes heures de travail (dans un centre R&D). Voila pourquoi je ne peux me permettre de vous le transmettre. Cependant, s'il vous est impossible de m'aider sans avoir ce fichier, je comprends parfaitement, et auquel cas je continuerai à chercher de mon côté.

Concernant la complexité de la macro, je n'ai bien évidement pas utilisé de fonctions dont je suis le seul à connaitre (bien au contraire)... Et croyez-moi, je ne me considère pas (du tout!) comme meilleur que vous sur le sujet des macros Excel, sinon je ne serai pas ici en train de vous demander de l'aide... A la place du mot compliqué, j'aurais plutôt dû dire qu'elle est longue (ou tout du moins par rapport à celles que j'avais faite précédemment : 2 500 lignes)... Beaucoup de temps sera donc nécessaire pour comprendre cette macro, et je ne suis pas sur que comprendre le fonctionnement entier de la macro fasse avancer le schmilblick :/ ...

Je vais tenter de travailler sur une version light de mon fichier excel+macro, histoire de ne garder que les bases et voir si le problème est toujours là. Auquel cas, je mettrai le fichier dans un prochain post pour que vous puissiez constater le problème par vous-même ;)

@Joe.levrai
Merci pour ton aide.
Le problème est que je ne vois pas quelle partie du code pourrait me générer ce ralentissement car je n'appelle JAMAIS la feuille "Sketch_select" (celle qui est incriminé dans le ralentissement de ma macro). Et cette feuille ne contient aucun code!

1ère ligne de la macro:

Code:

---

Worksheets("Sketch_select").EnableCalculation = False

---

Voici la première ligne de mon code... Cette feuille "Sketch_Select" fait référence à une UNIQUE feuille: la feuille "results" dans laquelle tous les résultats issus de la macro et du solver sont mis à jour en temps réel pendant que la macro tourne. Dans la feuille Sketch_select, je n'utilise que des formules directes et des recherchesV... En mode debug, j'ai bien pu observer que la mise à jour de cette feuille "Sketch_Select" ne se fait pas pendant que la macro tourne... Elle ne se fait qu'à la fin de ma macro...

Dernière ligne de la macro

Code:

---

Worksheets("Sketch_select").EnableCalculation = True

---

Seulement, je n'arrive pas à comprendre pourquoi en supprimant cette feuille "Sketch_select", la macro tourne deux fois plus vite. Sachant que pendant que la macro tourne, Excel ne doit pas (et il ne le fait pas!) mettre à jour les résultats de cette feuille... Quelque chose m'échappe à ce niveau... En fait, il faudrait plutôt une ligne de code me permettant de dire à Excel de faire comme si cette feuille n'existait pas du tout!

@clementmarcotte
Merci pour ta réponse. Ayant ce problème dans le cadre de mon travail, je ne peux pas me permettre (et je n'en ai pas les droits) d'installer les logiciels que je veux. Mais je prends note de ton message pour des projets personnels ;)

De plus, je ne pense pas que le problème vienne du solver en lui-même, car que la feuille "Sketch_select" soit là ou non, la macro tourne sans erreur tout en me fournissant les mêmes résultats.

J'ai bien évidemment pensé à cette ligne de code:

Code:

---

application.screenupdating = false

---

Cependant, le solver a cette particularité de le remettre automatiquement à "true" à chaque fois qu'il est lancé. Et ça, même en cherchant sur des forums, je n'ai trouvé personne capable de contourner ce problème. Mais si tu étais au courant de ce problème et que tu as la solution, je suis bien évidemment preneur, bien que cela ne soit pas le coeur de ma problématique ;).

Bonjour
de toute facon une macro qui plombe l'app pendant 2 minutes et plus c'est plus une macro c'est une soufrance

non sans rire
regarde dans ton code si tu peut pas supprimer quelque select

voir plutot qu'ecrire dans le sheets de destination resultat par resultat regarde si tu peut pas utiliser de variable Tableaux
en general sa peut reduire jusqu'a 90% la durré de la macro
n'ayant pas ton fichier sous les yeux je ne peut t'en dire plus

a bon entendeur

Bonjour PatrickToulon,

J'ai conscience que 2 minutes c'est très long, c'est pourquoi j'essaie de trouver des moyens pour réduire ce temps :)

Au niveau des Select, je ne pense pas que je puisse en supprimer énormément.

En revanche, l'idée du tableau pour enregistrer les résultats à la place de les écrire au fur et à mesure dans les feuilles excel me parait être une très bonne idée. Je testerai cela Lundi! Merci ;)



Je viens de créer un petit fichier de test qui résume très bien mon problème. J'ai repris une structure très simplifié de mon travail, et je fais tourner dans cet exemple une macro qui n'a pour seul but que de faire des calculs inutiles pendant un certain temps... Je n'ai pas utilisé de solver. Et voici les résultats du temps d’exécution de la macro:

-Toutes les feuilles avec "EnableCalculation = True" => 61sec;

-"Worksheets("Sketch_select").EnableCalculation = False" => 30sec;

-Feuille "Sketch_select" supprimé => 25sec.

Il est vrai qu'ici il n'y a que 5 secondes de différence entre les deux derniers cas (j'ai relancé chaque cas 3 fois pour être sur du temps d'exécution), mais cela suffit à vous illustrer mon problème! (Votre ordi devrait faire tourner cette macro en beaucoup moins de temps que mon PC. Si c'est le cas, n'hésitez pas à augmenter le nombre d'itérations pour avoir des résultats exploitables!)

Avez-vous des idées pour expliquer cette différence de temps entre les deux derniers cas? Peut-être est-ce normal?

Merci d'avance :)

Salut

Ça ne serait pas tout simplement le temps mis par excel pour mettre à jour les calculs qui se trouve sur la feuille Sketch_select ?

Citation:

---

Envoyé par Aide VBA - EnableCalculation

Quand la valeur de cette propriété est False, vous ne pouvez pas demander un recalcul. Quand vous changez la valeur de cette propriété, de False en True, Microsoft Excel recalcule la feuille de calcul.

---

++
Qwaz

Bonjour Qwazerty,

Merci de ta réponse ;)

Mais je ne pense pas que cela soit le problème :/
Tu peux voir dans la macro du fichier test de mon post précédent que quand je calcule le temps mis par la macro, je commence le chrono après la ligne :

Code:

---

Worksheets("Sketch_select").EnableCalculation = False

---

et j'arrête le chrono justement avant le re-calcul de la feuille... Donc techniquement, pendant que le chrono tourne il ne fait aucun calcul sur cette feuille sketch_select.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Sub macro()   Dim i As Integer Dim time As Date   Worksheets("Sketch_select").EnableCalculation = False 'Worksheets("Sketch_select").EnableCalculation = True   time = Format(Now(), "yyyy-MM-dd hh:mm:ss")   Worksheets("Results").Range("B2:K101").ClearContents   For i = 1 To 10000 Worksheets("Feuil1").Range("B3").Value = i For j = 1 To 10 Worksheets("Results").Cells(i + 1, j + 1).Value = Worksheets("Feuil1").Cells(j + 2, 5).Value Next j Next i     MsgBox Format((Now() - time), "hh:mm:ss")   Worksheets("Sketch_select").EnableCalculation = True End Sub

---

Bonjour le fil, bonjour le forum,

Citation:

---

Envoyé par petitchti

Merci à ceux qui se sont donnés la peine de me donner une réponse construite...

---

L'Humour est comme le fil à plomb pour la construction : il fout droit ! D'où expression "Rigoler comme un bossu"... ThauTèmePédia...

Bonjour, bonjour !

Citation:

---

Envoyé par petitchti

Au niveau des Select, je ne pense pas que je puisse en supprimer énormément.

---

Un bon code n'utilise pas de Select fort ralentisseur et inutile ‼

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse

Ne t'inquiètes pas Thautheme, je n'ai pas mal pris ton message. Je ne t'ai d'ailleurs rien écrit d'insultant ou de méchant ;) C'est juste que ça ne fait pas grandement avancer mon problème :/

@Marc_L
Quand ma macro tourne au total pendant 2minutes, il y a à peu près 1min 50 secondes qui sont réservés à faire tourner mon solver 110 fois (représentant 110 situations différentes) pour la résolution du système de 28 équations (non linéaires et non continues de surcroît) à 28 inconnues... Donc niveau code, je ne peux pas gagner énormément de temps dessus, ou alors il me faudra passer énormément de temps (car je ne suis malheureusement pas un expert des macros!)...

En revanche le problème que j'ai soulevé pourrait me permettre de réduire de moitié le temps de réalisation de ma macro, sans toucher à la base même du code (ou alors peut-être très peu!)... Il suffit juste de comprendre (dans le fichier test d'un de mon précédent message) pourquoi la macro tourne plus vite quand on supprime la feuille "sketch_select" que quand on désactive le calcul de la feuille...

Mais pour l'instant personne n'a encore été capable de m'expliquer pourquoi, peut-être suis-je le premier à soulever ce problème? :/

Pour ceux qui ont téléchargés mon fichier test, retrouvez-vous ces différences de temps d’exécution entre le cas où la feuille est désactivée (enableCalculation = false) et le cas où la feuille est supprimée?

Bonjour le fil, bonjour le forum,

Citation:

---

Envoyé par petitchti

Ne t'inquiètes pas Thautheme, je n'ai pas mal pris ton message. Je ne t'ai d'ailleurs rien écrit d'insultant ou de méchant ;) C'est juste que ça ne fait pas grandement avancer mon problème :/

---

D'abord je ne suis pas inquiet. Ensuite je n'ai, moi non plus, rien écrit de méchant ou d'insultant et pour terminer, je ne suis pas si sûr que ma petite vanne n'ai pas fait avancer ton problème...

Pas de soucis, "l'incident" est clos alors ;)

Par ailleurs, dans le fichier test que j'ai mis dans un de mes posts précédents, retrouves-tu le même problème que moi? As-tu une explication ou une solution?

Salut

En effet je n'ai pas été assez attentif au code.

J'ai fait les essais:

EnableCalculation à true = 21s
EnableCalculation à false = 12s
Suppression des formules de la feuille Sketch_select (en gardant la feuille) = 6s (le problème ne vient pas de la feuille mais peut-être du contenu)

Par contre j'ai voulue tester en redémarrant le fichier et en débutant
avec les formules et EnableCalculation à false, résultat 6s... (ah... ça ne vient pas du contenu de la feuille non plus...)
EnableCalculation à true et Sketch_select affichée à l'écran = 1min03 (ok normal, l'affichage est très gourmand das ce style de boucle)
EnableCalculation à true et Feuil1 affichée à l'écran = 20s (résultat identique au début)
EnableCalculation à false et Feuil1 affichée à l'écran = 11s (idem)
EnableCalculation à false et Sketch_select affichée à l'écran = 07s (idem)

En conclusion je pense que lorsque l'on fait des calculs x (x valant de 2 à ....) fois de suite, Excel et/ou Windows conserve(nt) des valeurs ou des fonctions en mémoire afin de pouvoir répondre plus rapidement à une sollicitation identique.
Je n'ai pas de preuve mais au vu des résultats précédents c'est l'explication qui me vient.

++
Qwaz

Salut Qwazerty,

Merci d'avoir pris du temps pour t'occuper de mon problème :)

Voici mes résultats pour les mêmes cas:
-enable.calculation = true => 26sec
-enable.calculation = false => 17sec (plus rapide, logique)
-suppression des formules de la feuille "sketch_select" + enable.calculation = false =>16sec
-suppression des formules de la feuille "sketch_select" + enable.calculation = true => 16sec (logique par rapport au résultat précédent, car il n'y a pas de formules dans cette feuille)
-suppression de la feuille "sketch_select" => 17sec (pas de changement, logique... mais mon problème ne se présente plus pour cet exemple!!!)

En redémarrant excel:
-avec les formules et enable.calculation=false => 18sec (Je ne trouve pas le même résultat que toi... Le temps n'a pas du tout diminué pour moi)
-avec les formules et enable.calculation=true=> 25sec (logique)
-avec les formules et enable.calculation=false=> 18sec (logique)
-avec la feuille "sketch_select" supprimé => 15sec (On gagne 3 sec!)

Pour cet exemple, les différences sont bien moins flagrantes sur mon PC que sur le tien et sont mêmes parfois différentes des temps que tu as donnés! Très étrange ce problème...

Salut Qwazerty,

je viens de faire un petit test,
En démarrant Excel, en mettant "enable.calculation = false" et en affichant la feuille sketch_select, la macro tourne en 9sec. (7sec pour toi, on est dans le même ordre de grandeur...)

Citation:

---

Par contre j'ai voulue tester en redémarrant le fichier et en débutant
avec les formules et EnableCalculation à false, résultat 6s... (ah... ça ne vient pas du contenu de la feuille non plus...)

---

pour cet essai, tu avais fait afficher quelle feuille? Feuil1 comme pour les autres tests ou la feuille sketch_select?