Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 31-08-2010 17h46
Bonjour,
Débutant et novice dans le VBA, j'ai associé quelques macros à des boutons dans un fichier excel avec une vingtaine d'onglets dans lesquels j'ai des croisés dynamiques et des graphiques le tout alimenté par un onglet base de donnée avec plusieures formules dont de la recherchev. Tout semble fonctionner et m'apporte le résultat attendu. il n'en demeure pas moins que mon fichier fait 209 Mo et donc est proprement inexploitable sans une attente appuyée ! entre chaque enregistrement. cela me fait dire que mes macros ne doivent pas être correctements agencées et optimisées. Comment puis-je optimiser ces macros ? A vous lire. Bien cordialement |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 18/05/2006
De Saône-et-Loire (71)
Messages:
1539
Système d'exploitation: PC Version Excel utilisée: 97, 2000, 2002, 2003, 2007, 2010, 2013, 2016 et 365 |
Posté le : 31-08-2010 18h09
Bonjour zorbrax,
... sans voir les macros en question, difficile de te dire comment les optimiser ! Avec un tel poids (209 Mo !!! c'est possible ça ?), tu ne pourras jamais joindre ton classeur dans ce forum. Des extraits ciblés de celui-ci seraient de rigueur si tu veux qu'on t'aide ou qu'on te donne des pistes d'optimisation... Ceci dit, j'ai du mal à croire que ce sont tes macros qui alourdissent autant ton classeur De mon expérience, ce serait plutôt l'application de formules et/ou de format sur des colonnes ou lignes entières qui sont souvent à l'origine de ce genre d'effet. Il convient de réserver l'application de mises en forme uniquement sur les plages contenant des données et bannir les modifications inutiles de cellules « hors tableau ». Par ailleurs, les TCD et les Graphiques sont aussi malheureusement sources de prise de poids pour les classeurs Excel. Les formules peuvent, quant à elles, demander un temps de calcul relativement long si mal appropriées. C'est certainement là aussi un élément à prendre en considération pour optimisation. Cordialement, Nb: es-tu obligé d'écrire si petit dans tes posts ? Didier_mDF
Le Webmaster La réponse vous satisfait ? Merci de revenir solder le sujet en [résolu], voir ce lien |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 23/05/2008
De La Varenne Saint-Hilaire
Messages:
356
Système d'exploitation: PC Version Excel utilisée: 2010 |
Posté le : 31-08-2010 18h40
Bonjour zorbrax, Didier
Juste une petite piste du coté des TCD ... peut-être ...? Vérifie que la source de données de tes TCD n'est pas inutilement trop grande et n'intègre pas de lignes vides. Pour cela tu peux faire référence à une zone calculée, ainsi tu n'auras pas une seule ligne en trop (voir l'article "nommer une plage dynamique") Tu peux également regarder si tes TCD peuvent faire référence non pas à ta grosse base mais à un autre TCD déjà construit dans ton fichier, ce qui économise pas mal de mémoire. Tu peux aussi vérifier, à l'aide de la combinaison de touches CTRL FIN, que dans tous tes onglets la dernière cellule atteinte n'est pas éloignée de la zone de travail, si tel est le cas tu peux supprimer des lignes ou des colonnes (et comme le dit myDearFriend éliminer les formats et formules inutiles) 209Mo, c'est vrai que je n'ai jamais vu ça , ça doit être impossible de travailler là dessus .... Jette un oeil sur tout ça, en commençant par les pistes indiquées par myDearFriend, et dis nous. Bonne soirée à tous, Mth |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 31-08-2010 18h49
Ok pour a police !
Effectivement j'ai pas mal de formule avec des SI / EQUIV / et recherchev. Le tableau fait ~ 9000 lignes. Je vais revoir la mise en forme de ce dernier et enlever tout ce qui n'est pas nécessaire.... Je tiens à vous remercier pour d'une part le site qui est vraiment génial, ainsi que vos réponses somme toute sympathiques et pertinentes. Je découvre depuis peu le VBA que je trouve génial, mais n'ayant aucune formation en tant que développeur je rame un peu !!! Surtout au niveau commandes et leurs capacités et comment et où les employer. Bien cordialement |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 01-09-2010 14h35
Bonjour, |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 23/05/2008
De La Varenne Saint-Hilaire
Messages:
356
Système d'exploitation: PC Version Excel utilisée: 2010 |
Posté le : 01-09-2010 16h54
Bonjour à tous
Hé bé, de 209 à 76 Mo, il y a encore de quoi faire mais le régime a été efficace !!!. - Concernant les zones nommées dynamiques, voici un exemple: Ta base dans l'onglet "Feuil1" s'étend de la ligne 1 (qui comprend les titres) à la ligne xxx; et de la colonne A à la colonne BR.
Première remarque concernant les colonnes, si tu n'as pas besoin de la colonne A ou B par exemple, ta source de données de TCD peut démarrer à la colonne C, de même tu n'es pas obligé d'intégrer les dernières colonnes au bout à droite si tu n'en as pas besoin. Plus tu "économises" de colonnes, plus tu perds du poids. Concernant les lignes, tu peux donc calculer le nombre exact de lignes nécessaires grâce à DECALER() et NBVAL(). Le nombre de lignes à prendre en compte, calculé par exemple à partir de la colonne A (mais qui peut-être une autre colonne): =NBVAL(A:A) Imaginons que ton TCD exploite les données situées de la colonne E à AQ, la ligne de départ sera forcément la ligne 1 (où sont situées les en-têtes) et contienda 3 lignes, 3 étant déterminé par NBVAL() ci-dessus. Pour cet exemple, tu crées une zone nommée, ici appelée "ZoneTCD", par le menu "Insertion/nom/Définir" ou sur Excel 2007 en cliquant sur l'onglet "Formules", puis dans le pavé "Noms définis" sur "Définir un nom". Là, tu indiques le nom (par exemple ZoneTCD) et sur la ligne "fait référence à" tu inscrits cette formule: =DECALER(Feuil1!$E$1;;;NBVAL(Feuil1!$A:$A);39) Il n'y a pas de décalage vers le haut ni vers le bas (au lieu de ne rien inscrire j'aurais pu mettre zéro comme ceci: =DECALER(Feuil1!$E$1;0;;NBVAL(Feuil1!$A:$A);39) La hauteur de la zone est calculée par NBVAL(), dans ton fichier joint on a une hauteur de 3 lignes. La largeur peut-être calculée par NBVAL(), elle peut aussi être fixée. Ici par exemple de la colonne E à la colonne AG incluses, il y a 39 colonnes, je mets donc 39. Ceci permet de ne pas intégrer de colonnes inutiles dans le TCD final. A la fin du processus, tu as donc une zone nommée "ZoneTCD", et dans ton Tableau Croisé dynamique, au niveau de la source de données tu indiques =ZoneTCD (n'oublie pas le signe =) Voilà pour les sources de données des TCD. Ceci dit, il n'y a aucun TCD dans le fichier joint donc je n'ai pas pu te fournir d'exemple concret sur tes propres données, mais j'espère que tu arriveras à mettre ces zones dynamiques en place. - Tu peux appliquer le même principe à tes zones de calcul. Quand je vois dans tes formules: [COM_GRC_ENTREPOTS_test.xls]BDD'!$BQ$1:$BQ$65536)-1 De même, ton onglet "Correspondance" est tout petit, 114 lignes dans ton exemple, mais les formules faisant référence à cet onglet calculent sur 65 536 lignes... Là aussi, tu aurais intérêt à nommer les zones pour gagner de la place. Idem pour cette formule colonne AP de l'onglet Feuil1: =RECHERCHEV(K2;'COM_GRC_ENTREPOTS_test.xls]Correspondances'!A$1:B$65536;2) Tu as intérêt également à nommer les zones des colonnes BL et E dont tu te sers dans tes formules, afin d'éviter de caluler sur plus 65 000 lignes Voilà, vois déjà ce que tu obtiens avec ces pistes et dis nous ce qu'il en est. A bientôt, Mth |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 01-09-2010 18h30
Super merci !
ça devient plus clair dans mon esprit. Je vous joins un petit aperçu de ce que j'obtient actuellement sans avoir pu me servir de vos exemples donc avec ma grosse marmitte. bien entendu c'est une copie sur un fichier Word car il m'est impossible de vous expédier autre chose... pour le moment ! Bien cordialement |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 23/05/2008
De La Varenne Saint-Hilaire
Messages:
356
Système d'exploitation: PC Version Excel utilisée: 2010 |
Posté le : 01-09-2010 19h57
Bonsoir zorbrax,
Il me vient une idée en regardant l'image de ton fichier. Les utilisateurs cliquent sur la région de leur choix dans l'onglet Menu, puis consultent les données, puis retournent au menu, où ils pauvent à nouveau choisir une autre région. Tous les TCD et tous les graphes consultés ont la même structure, il n'y a que le filtre région qui change. ... Donc, pourquoi 9 TCD et pas un seul ... Si en cliquant dans le menu, tu affectes une macro qui filtre le TCD sur la région choisie, en choisissant une autre Région tu lances la macro qui filtre sur cette autre région, et le tout sur un seul TCD (et donc un seul graphique aussi). Je me dis qu'il y a là de quoi faire perdre pas mal de poids à ton fichier ... reste bien sûr à savoir si cela te semble une bonne piste? Si oui, et que tu ne parviens pas mettre en place cette idée, tu peux tenter de poster sur le forum ton fichier zippé, tu retires tous les onglets des régions sauf 1, tu réduis la base à quelques dizaines de lignes, et ça devrait passer. A très bientôt, Mth |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 02-09-2010 19h59
Bonsoir,
C'est vraiment sympa. Je vais tenter d'explorer cette piste... seul ! car j'ai à coeur de comprendre et réussir à faire de vrais tableaux avec de vrais macros. Quant je vois ce que êtes capable de faire ça me coupe le souffle et si j'arrive un tant soit peu à construire un début de fichier structuré ce sera pour moi une terrible avancée et un début de progression. Je vais donc m'y atteler ce WE car j'ai un boulot monstre en ce moment. Bien entendu je n'hésiterai pas à vous solliciter !!! Je réitère encore mes remerciements, je suis agréablement surpris de votre prise en compte et de vos réponses aux sollicitations que je vous ai faites. Encore et sincèrement MERCI ! |
|
|
Re: Optimiser mes macros en VBA | ||
---|---|---|
Inscription: 30/08/2010
Messages:
53
Système d'exploitation: PC - MAC Version Excel utilisée: Excel 2003.2007.2010 |
Posté le : 06-09-2010 20h56
Hello !
Après quelques boîtes d'aspirine, j'en suis toujours au même point... hpnte sur moi. En effet j'ai créé une zone_TCD et j'ai lancé l'assistant du TCD et là à mon grand desepoire je me suis retrouvré avec un choix ultra réduit.... colonne / ligne / etc mais rien qui correspond à ce que j'avais auparavant. j'ai dû me trompé ? Une fois que j'ai créé ma zone (qui reprend en partie l'ensemble de mes colonnes. Dois-je y inscrire la formule [DECALER] ? A vous lire Merci |
|
|