Saturation mémoire VBA

[chris]

Bonjour

J'ai un programme VBA qui tourne très bien (aucune erreur et objectif atteint avec des classeurs créés) mais il manipule de gros fichiers et après la fin du programme, Excel devient instable et plante avec proposition d'envoyer un rapport à MS. Je pense qu'il s'agit d'un problème de gestion mémoire : le plantage se produit lorsqu'on ferme manuellement un classeur.
Y a t'il une commande qui permette de vider la mémoire ?

Sinon toute piste serait la bienvenue.

 

[chris]

Re thierry

Bonne idée ça : je pourrais mettre un calcul manuel en workbookopen et un calcul auto en workbookclose sur les classeurs générés. Tous les onglets sont verrouillés donc rien à calculer.
Si ça marche je pourrai convaincre l'utilisateur sur cette limitation
Je teste cela.
Peut-être pas ce soir car j'ai un autre taf à finir.

donc @ +

 

[chris]

Bonsoir

Dernier round du soir avant d'aller dormir.
Même en désactivant le calcul (y compris à la fermeture) si j'ouvre 7 ou 8 classeurs toujours le même phénomène, ça plante quand le bouton fermer ne répond pas et que j'insiste mais pas si je tourne autour du classeur récalcitrant (jamais le même mais cela semble souvent être le premier ouvert).
Il y a un truc qui plait pas à Excel ou à Windows dans les classeurs mais quoi ?
Rageant ce truc.
Bonne nuit.

 

[_Thierry]

Re Re Re Chris 😉

Et une version sans les Graphs ..... t'as testé juste pour voir ?

Bonne Nuit
[ol]@+Thierry[/ol]

 

[chris]

Bonjour Thierry et le forum

En fait je n'ai ouvert que des fichiers sans graphs.

Je vais essayer de nommer les formules (celles des formules), puis de nommer aussi les formules des MFC et voir si cela joue. Certaines MFC utilisent des motifs : je vais aussi voir de ce côté.

Le classeur avec les graphs est celui qui a le moins de formules (2000) alors que les autres atteignent 50 000 à multiplier par 4 avec les MFC.

En ne quadrillant que les plages utilisées au lieu de l'onglet entier, mon classeur est plus gros.

J'attends d'avoir un peu de temps pour m'y remettre ...

Bonne soirée

 

[Jam]

Salut Chris et à tous les autres,

En terme d'optimisation y'a pas mal de choses à faire:
1. Nettoyer le code...arf, je sais c'est facile à dire, plus difficile à faire mais c'est une nécessité. Pour t'aider j'ai quelques outils:
- MZtools: un utilitaire (free) qui permet (entre autre) de te donner un compte rendu précis de toutes les variables (in)utilisées.

2. Nettoyer (encore)...le code...Pour cela la méthode basic (yes) c'est d'exporter TOUS les modules, de les supprimer puis de les réimporter. Si le code a subi de nombreuses modifications cela peut avoir une importance réelle sur sa taille (et donc son fonctionnement). Le moyen automatique c'est d'utiliser Code Cleaner (si tu ne trouves pas sur le net, fait moi signe je te l'enverrai).

3. Nettoyer (et oui, encore). Y'a un truc que mes petits camarades n'ont pas souligné. En fait ton fichier il est peut-être un peu corrompu !!! Si si ça arrive. En fait perso, j'ai déjà travaillé sur des fichiers qui faisaient 50Mo tranquillou, sans jamais avoir de problème. Donc la taille est souvent un faux problème. Pour ce nettoyage pas beaucoup de solution: transférer TOUT ton classeur dans un tout nouveau tout bô. C'est long et compliqué et il ne faut rien oublier. Il existe un outil qui s'appelle Workbook Rebuilder qui fait le travail, problème il est pas gratuit. J'en possède une assez vieille version qui doit elle être encore gratuite..il faudrait que je vois si je la retrouve si cela t'intéresse.

4. Pour finir il faut optimiser. Là pas d'outil miracle, seule la tête fait le boulot (cf les très bons conseils de Thierry et consors). Ah, si dernière chose concernant les formules. Si tu utilises beaucoups des références à des (grandes) plages, j'ose espérer que tu utilises des noms. Ca économise la mémoire et ça rend beaucoup plus rapide les calculs. Dans ton cas vu le nombre de formule...c'est à envisager.

5. Pour les graphs et les images...ben, y'a rien à faire 🙂

6. Sur ce je rentre me coucher car j'ai déjà bien donné au taf.

Bon courage.

 

[chris]

Re:Saturation mémoire et plantage

Salut JAM.

Merci de rejoindre le fil.

Comme je l'ai finalement constaté le code n'est pas en cause puisqu'en démarrant Excel à froid et en ouvrant 4 ou 8 classeurs générés lors d'une autre session, je reproduis le problème juste en me baladant dans les classeurs et les onglets.
Donc c'est bien dans les classeurs générés que se situe le problème.

Je vais effectivement tenter les formules nommées.
Je n'ai pas de référence à de grandes plages, la plupart se réfèrent en semi-absolue à une cellule de la ligne et/ou de la colonne.
Idem pour les formules des MFC.
Les onglets sont verrouillés donc pas de recalcul après leur génération (sauf à l'ouverture évidement). J'ai aussi mis en calcul manuel mais cela ne change rien.

J'ai aussi pensé à reconstruire les modèles servant de base aux classeurs. Mais j'hésite encore vu le boulot.

Une piste inexplorée : tous ces classeurs ont 3 boutons avec un bout de code attaché. Le même puisque construits sur le même modèle. Pas de variables publiques dans ces bouts de codes qui ne servent qu'à l'impression et qui ne sont pas appelés entre l'ouverture et le plantage.

Pour l'instant la solution reste de n'ouvrir à la fois qu'un ou deux classeurs. C'est l'empilage de 4 ou plus qui provoque le plantage.

 

[chris]

Re:Saturation mémoire, plantage - du nouveau

Bonjour Jam, jmps, Pat, Michel, Thierry et le forum

Je crois avoir enfin mis le doigt dessus (je croise les autres !!) : les trois boutons présents dans mes fichiers devaient interférer car associés au même code existant donc dans chacun des classeurs ouverts.

J'ai mis les sub de ce code en private et virer le code du beforeclose de ces classeurs qui était
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

et cela semble résoudre.

Y a t'il une autre méthode pour éviter le message Excel à la fermeture : je pense avoir viré les formules contenant aujourdhui() mais il doit y avoir une autre fonction volatile qui fait qu'Excel pose la question bien qu'on ne fasse rien.

Je continue mes tests pour être sûre...

Bonne soirée.

 

[pat1545.]

Re:Saturation mémoire, plantage - du nouveau

re,


voir dans les modules des feuilles peut etre ..;

Bye

 

[chris]

Re:Saturation mémoire, plantage - du nouveau

Salut jmps et pat

A jmps

C'était juste pour éviter le 'voulez-vous enregistrer...'
Craignant que displayalerts = false reste actif j'ai voulu situer la fermeture eentre false et true mais je ne sais pas si c'est la bonne méthode.
J'ai réussi à retrouver la formule qui donne ce message dans un des modèles mais pas le 2ème.
Donc je cherche toujours comment éviter ce message inutile puique tous les onglets sont verrouillés et donc non modifiés entre ouverture et fermeture.

A pat : il n'y a rien dans les modules de feuilles.

Je n'ai pas encore eu le temps de retester à fond mais j'ai bon espoir ...

Message édité par: chris, à: 26/04/2006 21:08

 

[chris]

Re: VBA portée du displayalerts

Bonjour José et le forum

Le BUG est bien là. Dire que j'ai passé 5 jours pleins à tout décortiquer !

Quand j'ai écrit ce bout de code, je me suis bien demandé si cela n'induisait pas une boucle infernale mais comme sur un seul fichier Excel ne bronche pas ...
Avec 2 fichiers ouverts cela dépend et à partir de trois il faut les fermer dans un certain ordre pour ne pas planter.
Pas très logique comme comportement.

Dans Access quand tu enlèves les alertes c'est une variable d'environnement : tant que tu ne les remets pas, tu n'en as plus nulle part (un peu come le calcul manuel dans Excel) d'où ma recherche de les remettre à TRUE.
Mais il semble que dans Excel la portée soit limitée à la procédure ou au module.
Donc si je mets Alert à false et rien d'autre dans le Beforeclose cela devrait marcher.

Je teste mais si quelqu'un peut me confirmer la portée du displayalerts, je serai très contente.

Message édité par: chris, à: 27/04/2006 09:19

 

[Kotov]

Re : Saturation mémoire VBA (problème bis)

Bonjour le forum,
Je rencontre un problème s'approchant de celui de Chris et j'apprécierai un conseil d'expert avant de remettre les "mains dans le camboui"

Avant d'entrer dans le détail, voici mes 2 questions brutes de décoffrage pour éviter une perte de temps à ceux qui n'auraient pas une réponse à proposer :

Constat : appli VBA, mémoire saturée, imprimante qui plante, utilisateur qui panique, et service informatique qui dit : "demandez à l'auteur"

Q1 : fermer le poste de travail, le relancer, et utiliser de nouveau l'appli concernée permet - il de vider la mémoire ? (solution temporaire le temps de trouver une solution fiable)
Q2 : existe il une ligne de commande vidant la mémoire hormis le Set MonObject = nothing ?

Voici pour les personnes intéressées un diagnostic de la situation, (avec mes chaleureux remerciements pour votre aide et votre patience) :

Contexte
1. Je ne suis pas informaticien de profession, mais développeur amateur (une trentaine d'appli à mon actif en 7 ans). je ne suis pas sensé faire d'informatique au boulot.
2. Je bosse dans une groupe d'envergure nationale. Localement, les informaticiens n'ont plus le droit de développer (réservé aux informaticiens "nationaux")
3. Les outils proposés sont souvent médiocres et ne correspondent pas toujours aux besoins (cahiers des charges mal définis)
4. De ce fait, à titre personnel, et avec accord hiérarchique, j'ai developpé mes propres outils à partir des outils proposés sans jamais le moindre souci et avec de gros gains de productivité.
5. Effet boule de neige : mes collègues les ont adoptés, ma ligne hiérachique les a mis en avant, avec extension sur l'ensemble du site local, une des appli a même été primée au niveau national et dispatchée.
6. Effet nocif : ma direction locale m'a commandé quelques outils me mettant en porte à faux vis à vis des informaticiens locaux qui ne font pas le SAV (et je les comprends parfaitement).


L'appli développée en VBA : (une synthèse de données issues de 2 applis officielles).
Un classeur Excel qui sert de réceptacle :
- l'utilisateur copie dans 9 onglets des données issues de 2 applis officielles (Ctrl+A, Copier + clic sur un bouton Coller qui vérifie la conformité de la page avant de la coller et de passer à l'onglet suivant)
- un onglet "Salle des Machines" formate les données copiées et alimente un tableau de variables en mémoire
- Excel ouvre ensuite Word (de manière invisible pour l'utilisateur), appèle un modèle en .dot, une macro "Word VBA" alimente les champs du modèle à partir du tableau de variables, lance l'impression de la synthèse, ferme la session Word, vide la mémoire du tableau de variables, nettoie les 9 onglets réceptacles et propose la création d'une autre synthèse (ou Quitter)

Cette petite appli fonctionne parfaitement depuis 16 mois. D'autres fonctionnent sans soucis sur le même principe depuis 1999.

Le problème depuis hier c'est l'utilisation intensive qui en est faite (absolument pas prévue dans le cahier des charges initial) : jusqu'içi 50 à 60 utilisateurs montaient eux même une dizaine de synthèses par jour,
maintenant on demande à une ou deux personnes de ne faire que ça pour la collectivité.
(j'ai honte pour les 2 pauvres qui font du copier-coller à longueur de journée).

Problème confirmé par un "informaticien officiel" : la mémoire est saturée.
En phase test, j'avais monté 70 synthèses sans problème soit 6 à 7 fois l'utilisation quotidienne de l'époque. Il semblerait que ça sature entre 90 et 110 synthèses (selon les autres applis utilisées en parrallèle).

Les solutions auquelles j'ai pensé :
- la solution radicale : "c'est une appli perso, non officielle, elle bugue, laissez tomber et utilisez les anciens outils" : solution refusée, ancienne méthode improductive (fiche incomplète à remplir à la main),
- la solution temporaire : éteindre le poste, puis le rallumer toutes les 50 synthèses. Est ce bien efficace à défaut d'être la solution ?
- comment être sûr de vider régulièrement la mémoire : à priori les variables, le clipboard ça pose pas problème. Je ferme bien Word (Set MonWord = nothing). Est ce un problème de mémoire temporaire comme expliqué dans ce fil ?
Existe t'il une commande VBA pour ce problème ?

J'ai bien sûr une solution intermédiaire :
Modifier mon appli à partir de l'élaboration du tableau de variables, et les transférer dans un 10ème onglet servant de synthèse, évitant ainsi l'ouverture et la fermeture de Word.
Ca devrait alléger la mémoire utilisée. Mais se pose alors un autre problème : reconstruire une appli alors que j'ai pas fait de VBA depuis 8 mois et que j'ai débuté un nouveau job lundi dernier, c'est sûr, c'est ma mémoire perso qui va saturer !!
Et là, je connais la solution : tout envoyer "péter" et partir en courant à travers champs.

Bonne soirée à tous, et merci d'avance pour votre aide.
Kotov

 

[chris]

Re : Saturation mémoire VBA

Bonjour

Pas sûre de t'aider beaucoup mais quelques constations faites lors de mes déboires :
la version XP pose davantage de PB mémoire que 2000 : je pense que cela est du au système de récupération. Quel version utilises-tu ?
Quand cela plante n'y a t'il bien qu'un Word en mémoire ?
Il serait sans doute mieux de ne pas fermer Word entre chaque synthèse mais tester s'il est déjà ouvert et ne le fermer qu'à la fin.
En principe en quittant Excel, la mémoire devrait se libérer. Cela pourrait aussi venir du swap disque qui dépasse l'espace alloué et là seul un redémarrage Windows fait le ménage.
Dans mon cas il y avait 2 problèmes : mon before_close mal fichu et la présence de variables public dans les modules VBA de plusieurs classeurs ouverts. J'ai remarqué que si j'ouvre plusieurs versions d'un classeur basé sur un même modèle et contenant donc un même module VBA avec les mêmes variables décalrées, Excel n'aime pas. Donc vérifies si les variables du VBA Word n'ont pas les mêmes noms.

Bon courage.

PS : Tu aurais peut-être du créer un nouveau fil (avec un lien éventuel vers le mien) : je crains qu'au bout du mien, il soit moins visible.

 

[Kotov]

Re : Saturation mémoire VBA

Merci Chris pour ton aide,

En réponse a tes questions :

1. L'appli tourne sur XP.

2. Il n'y a qu'une session Word d'ouverte à partir de mon appli (elle est invisible à l'utilisateur pour éviter une éventuelle confusion s'il a déjà une session Word ouverte pour son travail quotidien. Et elle se ferme automatiquement). En fait, les utilisateurs pensent tous éditer la synthèse à partir d'Excel.

3. Ne pas fermer la session Word ne répond pas à l'utilisation définie à l'origine dans le cahier des charges : au départ chacun éditait ses propres synthèses au "fil de l'eau". Il était donc nécessaire de ne pas conserver Word ouvert.
C'est la "Taylorisation" du mode opératoire qui pose problème.

4. Je vérifierai demain mes variables, mais je ne pense pas donner des noms identiques. J'utilise un système pour les nommer : d'abord mon initiale, puis les 3 premières lettres du type d'objet, puis le nom (ex HcbxNom pour une variable issue d'une checkbox). Mais je vérifierai, j'ai pu me planter.

5. Je n'ai pas ouvert de nouveau fil pour un sujet similaire au tien. Déjà que je met à contribution des bénévoles pour m'aider à régler mes erreurs, alors je ne vais pas mettre le bazar dans un forum clair et précis. Restons groupés par thème. Tant pis si j'ai moins de lecteur, l'essentiel étant d'avoir une aide efficace. Pour le Goncourt on verra une autre fois! 😉

En tout cas, je te remercie pour ton aide.
Bonne nuit,
Kotov