Rapport de message :*
 

Re: Optimiser mes macros en VBA

Titre du sujet : Re: Optimiser mes macros en VBA
par Mth le 01/09/2010 16:54:30

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)
Dans ton fichier joint cela donne 3, c'est à dire la ligne des en-têtes et toutes les lignes pour lesquelles la colonne A n'est pas vide.

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)
Comme tu as pu le lire dans l'article nommer une plage dynamique la cellule de départ à partir de laquelle on va effectuer le décalage est ici la cellule E1,
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)
Idem, il n'y a pas de décalage en colonne
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
je me dis qu'au lieu de calculer systématiquement sur 65 536 lignes, tu peux transformer tes zones nommées "zone_Cal" en zones dynamiques. Si ces données sont dans un fichier différent, tu les crées dans cet autre fichier, et tu y fais référence dans tes formules.

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)
La RECHERCHEV() sur un si grande zone mange pas mal de mémoire.

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