Jeff a un classeur Excel sophistiqué qui utilise VBA pour générer de nombreux rapports au format PDF. Il fonctionne très bien sur Excel 2013 32 bits sur plusieurs PC. Inversement, sur plusieurs autres PC exécutant Office 365, à mi-chemin de la sortie, Jeff reçoit une erreur «Mémoire insuffisante».

Ainsi, les macros fonctionnent parfaitement sur l’ancienne version d’Excel, mais pas sur la dernière version. (Encore une fois, Jeff a testé cela sur plusieurs PC afin que les résultats soient reproductibles.) Jeff se demande s’il existe des fuites de mémoire connues ou des différences de macro dans Office 365 qui pourraient être à l’origine de ce problème.

En général, l’utilisation de la mémoire est liée à un problème principal: les variables et la manière dont elles sont utilisées dans votre macro. Il entre spécifiquement en jeu avec les tableaux de variables. C’est toujours une bonne idée de vous assurer de déclarer toutes vos variables (utilisez la directive Option Explicit pour vous aider à cet égard) et de vérifier le dimensionnement de votre tableau pour vous assurer que vous n’essayez pas de déclarer un tableau incroyablement énorme.

Les variables objet peuvent également utiliser beaucoup de mémoire. Si vous utilisez le mot clé Set pour affecter un objet à une variable, assurez-vous de définir la variable objet sur Nothing lorsque vous avez terminé. Ceci est particulièrement important à l’intérieur des boucles – si chaque passage dans la boucle entraîne la déclaration d’une autre variable objet, si vous ne parvenez pas à effacer cette variable à chaque itération de la boucle, vous pouvez utiliser la mémoire très rapidement.

Il y a aussi un point à faire pour désactiver la mise à jour de l’écran pendant que votre macro traverse ses girations. Si votre macro essaie de mettre à jour continuellement l’écran, cela utilise des ressources et ralentit votre macro.

On pourrait penser que dans la situation de Jeff, cependant, l’utilisation de la mémoire ne serait pas un énorme problème. Après tout, il passe d’une version 32 bits d’Excel à (plus que probablement) une version 64 bits. Cela permet à Excel d’utiliser un espace mémoire plus grand que jamais. Ainsi, on pourrait penser que les erreurs «Out of Memory» seraient moins probables que plus probables.

Malheureusement, il y a une clé à jeter dans le mélange ici. Lorsque vous obtenez une erreur « Mémoire insuffisante », il se peut que ce ne soit pas la mémoire qui soit le coupable.

Microsoft a documenté, au fil des ans, que cette mémoire particulière est de nature générique et qu’elle peut être causée par à peu près n’importe quoi. (Dans mon livre, cela rend le message d’erreur inutile, mais qui suis-je pour interroger Microsoft. Frustrant!)

L’autre chose que j’envisagerais de faire (ne pas avoir vu le code de Jeff) est de m’assurer que vous ne programmez pas de manière linéaire. En d’autres termes, divisez votre code en sous-programmes individuels qui préforment de petites tâches discrètes. Vous pouvez ensuite appeler chaque sous-programme à partir d’une routine principale de contrôle. L’avantage est la gestion de la mémoire en raison de l’étendue.

Les variables n’ont de portée que dans la procédure dans laquelle elles sont utilisées.

(Eh bien, c’est vrai à moins que vous ne les déclariez comme ayant une portée globale, mais c’est une autre marmite de poisson.) Cela signifie que les variables utilisées dans une procédure sont détruites et leur mémoire libérée, automatiquement, lorsque la procédure est quittée.

Si votre macro, cependant, est écrite de manière linéaire afin qu’il n’y ait pas de sous-programmes, toutes les variables sont conservées dans votre système pendant toute la durée de l’exécution de la macro. Il est préférable, du point de vue de la gestion de la mémoire, de diviser votre code en procédures afin de minimiser l’utilisation de la mémoire.

L’autre avantage de modulariser votre code de cette manière est que si vous rencontrez une erreur, même le « Out of Memory » généralement sans valeur

erreur – elle se produira probablement dans le cadre d’une procédure spécifique et vous pourrez ainsi avoir une meilleure chance de déterminer où se produit l’erreur réelle. À mon avis, cette capacité à détecter plus efficacement le point de défaillance potentiel est un grand avantage.

Si cela ne résout toujours pas le problème, j’ai vu certains rapports selon lesquels lorsque vous enregistrez votre classeur, cela oblige VBA à effectuer un ramassage des ordures sur votre espace mémoire. Pour cette raison, certaines personnes suggèrent que votre macro enregistre le classeur de temps en temps, plutôt que juste à la fin de la macro. Je ne sais pas à quel point ce rapport est précis, mais si vous utilisez déjà votre macro pour enregistrer le classeur, cela ne peut pas faire de mal de l’enregistrer plus souvent plutôt que moins.

Il y a une dernière chose à mentionner. Si vous utilisez maintenant Office 365 Business Premium, il inclut SharePoint Online et Excel Web App. Ces versions limitent donc la taille d’un fichier Excel à 10 Mo. Si votre fichier est assez volumineux, il a peut-être très bien fonctionné sous Excel 2013, mais échoué sous Office 365 Business Premium en raison de cette limitation de taille de fichier.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13619) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.