Comment déclarer et remplir un tableau VBA dans Excel

Déclarer un tableau en VBA et fixer ses bornes

Un tableau se déclare comme une variable ordinaire, avec des parenthèses qui annoncent le nombre de cases. La forme 1 To 12 fixe le premier et le dernier indice, si bien qu’on lit la taille du tableau dans sa déclaration. Le type qui suit, As String ou As Long, s’applique alors à toutes les cases à la fois.

Compter les cases d’un tableau déclaré sans borne

La déclaration Dim fixe(10) ne crée pas dix cases mais onze, parce que la numérotation commence à zéro tant qu’on ne demande rien d’autre. La case fixe(0) existe donc bel et bien, et une boucle qui part de 1 laisse une valeur derrière elle sans que rien ne le signale. C’est la raison pour laquelle la forme 1 To 10 vaut mieux partout, puisqu’elle dit à la lecture ce que le tableau contient.

Changer la base avec Option Base 1

L’instruction Option Base 1, écrite tout en haut d’un module, fait commencer à 1 les tableaux que ce module déclare sans borne. Dim t(10) y donne alors dix cases numérotées de 1 à 10, et la fonction Array suit la même règle. La fonction Split, elle, n’en tient aucun compte et rend toujours un tableau qui commence à zéro, ce qui rend ce réglage plus trompeur qu’utile.

Remplir un tableau et le parcourir avec UBound

Chaque case se remplit par son indice, puis se relit de la même façon, exactement comme on écrirait dans une variable ordinaire. Pour parcourir le tableau sans recopier ses bornes, LBound donne le premier indice et UBound le dernier, quelle que soit la déclaration. La boucle survit ainsi à un tableau qui change de taille, ce qui arrive dès qu’on le redimensionne en cours de route.

VBA ne propose aucune propriété qui donne directement le nombre d’éléments, et le calcul se fait à partir des bornes, par UBound(mois) - LBound(mois) + 1. Sur un tableau déclaré en 1 To 12, UBound suffit d’ailleurs à lui seul, puisque le dernier indice est aussi le nombre de cases.

Créer un tableau en une ligne avec la fonction Array

La fonction Array fabrique un tableau à partir des valeurs qu’on lui énumère, sans déclaration de taille ni boucle de remplissage. La variable qui le reçoit doit être un Variant, et le tableau produit commence à zéro, même quand le reste du module travaille à partir de 1. C’est la bonne forme pour une liste courte et figée, comme des noms de jours ou une série de codes.

Redimensionner un tableau dynamique avec ReDim

Un tableau déclaré avec des parenthèses vides n’a pas encore de taille, et ReDim la lui donne au moment où on la connaît. C’est ce qu’il faut dès que le nombre de valeurs dépend des données, comme le nombre de lignes d’un export qui change chaque mois. Appliquer ReDim à un tableau déjà déclaré avec des bornes ne compile pas et affiche « Tableau déjà dimensionné ».

Garder les valeurs déjà rangées avec Preserve

Un ReDim employé seul vide le tableau qu’il redimensionne, et les valeurs déjà rangées disparaissent sans le moindre avertissement. Le mot Preserve les conserve, ce qui permet d’agrandir un tableau au fil d’une boucle qui découvre ses données. Le faire rétrécir garde en revanche les premières cases et perd les dernières, celles qui sortent du nouveau domaine.

Pourquoi Preserve refuse de changer la première dimension

Sur un tableau à deux dimensions, Preserve n’accepte de changer que la dernière, c’est-à-dire le nombre de colonnes. Toucher à la première lève l’erreur 9, « L’indice n’appartient pas à la sélection », au moment où la macro s’exécute. Quand ce sont les lignes qui doivent grandir, on range donc les lignes en deuxième dimension et on transpose à la fin, ou bien l’on redimensionne un tableau neuf avant d’y recopier l’ancien.

Ranger des lignes et des colonnes dans un tableau à 2 dimensions

Un tableau à deux dimensions range ses valeurs comme une grille, avec un numéro de ligne et un numéro de colonne. La déclaration donne les deux couples de bornes d’un coup, et chaque case s’atteint ensuite par ses deux indices, dans ce même ordre. UBound prend alors un second argument, le numéro de la dimension, faute de quoi il rend toujours celle des lignes.

Charger une plage de cellules dans un tableau VBA

C’est l’usage qui rend les tableaux vraiment utiles dans Excel, parce qu’une plage entière se lit en une seule instruction. Range("A2:E6").Value rend un tableau à deux dimensions dont la première ligne porte le numéro 1 et la première colonne le numéro 1, jamais zéro. La variable qui le reçoit est un Variant, puisqu’elle accueille le tableau tout fait plutôt qu’une valeur isolée.

Une cellule seule échappe à cette règle, et Range("B2").Value rend simplement sa valeur plutôt qu’un tableau d’une case. Une plage d’une seule ligne ou d’une seule colonne, en revanche, reste un tableau à deux dimensions dont l’une ne compte qu’un élément. C’est le piège qui fait échouer une macro le jour où l’export ne contient plus qu’une ligne, et il se referme en comptant les cellules de la plage avant de la lire.

Renvoyer le tableau dans la feuille d’un seul coup

L’écriture emprunte le même chemin dans l’autre sens, en affectant le tableau à une plage de la même taille. Resize fabrique cette plage à partir des bornes du tableau, ce qui évite de compter les lignes à la main et de se tromper d’une. Une plage trop petite tronque les valeurs sans rien dire, quand une plage trop grande remplit les cases en trop avec l’erreur #N/A.

Écrire un tableau à une dimension dans une colonne

Un tableau à une dimension écrit dans une colonne donne un résultat inattendu, car Excel le lit comme une ligne. L’instruction Range("H1:H3").Value = Array(10, 20, 30) écrit ainsi 10 dans les trois cellules, sans erreur ni avertissement. Application.Transpose remet le tableau debout, et les trois valeurs se rangent alors chacune sur sa ligne.

Lire un tableau de la feuille (ListObject) dans un tableau VBA

Le tableau de la feuille, celui que Ctrl+L crée et qui porte un nom, s’appelle un ListObject en VBA, et ses lignes de données forment une plage comme une autre. DataBodyRange la désigne sans la ligne d’en-tête, en commençant à la première ligne de données, si bien que le tableau en mémoire ne contient rien d’autre. Son mérite est de suivre la taille du fichier, alors qu’une adresse écrite en dur oublie les lignes ajoutées depuis.

Les colonnes gardent leur ordre d’affichage, donc ventes(1, 3) désigne la quantité de la première vente, troisième colonne du tableau. Pour ne pas dépendre de cet ordre, ListColumns("Quantité").Index donne le numéro d’une colonne à partir de son en-tête, et la macro survit alors à une colonne déplacée. Reste un troisième sens du mot, le tableau dynamique qu’une formule comme FILTRE déverse dans les cellules voisines, qui n’a rien à voir avec le langage.

Accélérer une macro en travaillant en mémoire

Une boucle qui lit et écrit une cellule à la fois franchit la frontière entre VBA et la feuille à chaque tour, et c’est ce passage qui coûte, pas le calcul lui-même. Charger la plage dans un tableau, calculer en mémoire puis réécrire d’un coup ne la franchit que deux fois, quel que soit le nombre de lignes. La macro qui suit remplit ainsi la colonne Total des dix mille ventes du classeur d’exemple.

La colonne Total se remplit alors sur les dix mille lignes du tableau, et rien ne s’écrit sous le tableau, puisque DataBodyRange s’arrête à la dernière ligne de données. La seconde macro du fichier reprend le même calcul deux fois, une cellule à la fois puis par un tableau, et affiche les deux durées.

Le chronomètre du langage s’appelle Timer, et la différence entre deux relevés donne la durée écoulée en secondes. Sur les dix mille lignes du classeur, la boucle cellule par cellule demande un peu moins d’une demi-seconde quand la version en mémoire tient sous les deux centièmes, soit au moins vingt fois plus vite. Les deux méthodes travaillent écran figé par ScreenUpdating, donc l’écart ne vient pas du rafraîchissement de la feuille mais bien des allers-retours.

Le gain grandit avec le fichier, et il devient décisif dès qu’une macro fait plus qu’une multiplication par ligne. Pour le reste du geste, l’article sur la boucle For en VBA détaille la boucle elle-même, celui sur l’objet Range la façon de désigner la plage à lire, et l’installation d’une macro ce qui se passe quand Excel bloque un fichier téléchargé.