# Comment utiliser ReDim en VBA pour redimensionner un tableau
La gestion dynamique de la mémoire constitue un enjeu central dans le développement d’applications VBA performantes sous Excel. Lorsque vous travaillez avec des volumes de données variables, la capacité à ajuster la taille de vos tableaux en cours d’exécution devient essentielle pour optimiser l’utilisation des ressources système. L’instruction ReDim représente l’outil privilégié pour redimensionner les tableaux dynamiques, offrant une flexibilité incomparable face aux contraintes des tableaux statiques. Cette fonctionnalité permet d’adapter la structure de vos données aux besoins réels de votre application, évitant ainsi le gaspillage mémoire ou les limitations fonctionnelles. Maîtriser cette instruction vous permettra de créer des macros robustes capables de traiter efficacement des ensembles de données dont la taille ne peut être déterminée à l’avance.
Comprendre la déclaration et l’initialisation des tableaux dynamiques en VBA
Avant de pouvoir redimensionner un tableau, vous devez comprendre comment VBA distingue les structures de données selon leur mode d’allocation mémoire. Cette distinction fondamentale détermine les opérations que vous pourrez effectuer ultérieurement sur vos variables.
Syntaxe de déclaration avec dim pour les tableaux à taille variable
La déclaration d’un tableau dynamique en VBA s’effectue avec le mot-clé Dim suivi du nom de la variable et de parenthèses vides. Cette syntaxe particulière indique au compilateur que la taille du tableau sera déterminée ultérieurement lors de l’exécution du programme. Par exemple, Dim tabloClient() As String déclare un tableau de chaînes de caractères sans spécifier sa dimension. Contrairement aux tableaux statiques où vous indiquez immédiatement la taille entre parenthèses, cette approche réserve simplement un emplacement pour la référence du tableau sans allouer de mémoire pour les éléments. Vous pouvez également utiliser Public, Private ou Static selon la portée souhaitée pour votre tableau dynamique. Cette flexibilité s’avère particulièrement précieuse lorsque vous développez des procédures devant s’adapter à des contextes variés.
Différence entre les tableaux statiques et les tableaux dynamiques en mémoire
Les tableaux statiques allouent une quantité fixe de mémoire dès leur déclaration, ce qui garantit des performances prévisibles mais limite leur adaptabilité. Lorsque vous déclarez Dim nombres(10) As Integer, VBA réserve immédiatement l’espace nécessaire pour 11 éléments entiers (indices 0 à 10 par défaut). Cette allocation reste constante pendant toute la durée de vie de la variable, qu’elle soit réellement utilisée ou non. Les tableaux dynamiques, en revanche, fonctionnent selon un principe radicalement différent : ils ne réservent initialement qu’un pointeur vers une zone mémoire qui sera allouée ultérieurement. Cette approche permet d’ajuster précisément la consommation mémoire aux besoins réels de votre application, particulièrement pertinente lorsque vous traitez des données issues de sources externes dont le volume varie considérablement.
Utilisation des parenthèses vides lors de la déclaration initiale
Les parenthèses vides constituent la signature visuelle distinctive des tableaux dynamiques dans votre code VBA. Cette notation spécifique communique explicitement votre intention de gérer manuellement le dimensionnement via l’instruction ReDim. Omettre complètement les parenthèses transformerait la variable en type scalaire simple, tandis que spécifier
spécifier un indice – même 0 – créerait immédiatement un tableau statique. En d’autres termes, la présence de parenthèses vides est le signal qui indique à VBA : « je connais le type, mais pas encore le nombre d’éléments ». C’est précisément ce scénario qui autorise ensuite l’utilisation de ReDim pour allouer et réallouer la mémoire à la volée. Dans un module, vous verrez donc très souvent un schéma du type Dim tData() en haut, puis plusieurs ReDim au fil de la procédure pour adapter la taille du tableau au contexte réel d’exécution.
Gestion des types de données avec variant, string et integer dans les tableaux dynamiques
Le choix du type de données pour vos tableaux dynamiques a un impact direct sur la mémoire consommée et sur les performances de vos macros. Un tableau de type Integer ou Long sera beaucoup plus léger qu’un tableau de Variant, car chaque élément occupe un espace fixe plus réduit. Cependant, un tableau de Variant reste extrêmement pratique lorsque vous devez stocker des données hétérogènes (nombres, dates, chaînes) ou récupérer directement le contenu d’un Range Excel, qui renvoie par défaut un tableau 2D de variants.
Dans la plupart des cas de traitement de données structurées (listes d’IDs, montants, index de lignes, etc.), privilégier Integer ou, mieux encore, Long est recommandé pour éviter le dépassement de capacité. Pour les textes, String conviendra parfaitement, à condition de ne pas multiplier inutilement les redimensionnements. Enfin, réserver Variant aux cas où la flexibilité est réellement nécessaire permet de limiter la consommation mémoire, surtout lorsque vous manipulez des centaines de milliers de cellules importées depuis Excel ou des fichiers CSV.
Syntaxe complète de l’instruction ReDim et ses paramètres essentiels
Structure de base ReDim avec spécification des dimensions LBound et UBound
L’instruction ReDim suit une syntaxe simple, mais très précise. Dans sa forme la plus courante, on écrit : ReDim nomTableau(lower To upper), où lower désigne la borne inférieure et upper la borne supérieure de la dimension. Si vous omettez lower, VBA utilise la valeur définie par Option Base (0 par défaut), ce qui peut entraîner des décalages d’index si vous ne maîtrisez pas bien ce comportement. Pour un contrôle total, il est donc recommandé d’indiquer explicitement les deux bornes, par exemple ReDim tClients(1 To nbClients).
Sur les tableaux multidimensionnels, la structure reste la même, mais chaque dimension reçoit son couple de bornes : ReDim tData(1 To nbLignes, 1 To nbColonnes). Cette écriture rend la logique du tableau beaucoup plus claire, notamment lorsque vous mappez directement une plage Excel vers un tableau VBA. En pratique, vous combinerez souvent ReDim avec les fonctions LBound et UBound pour sécuriser vos boucles et éviter les erreurs d’indice hors limites, en particulier dans les macros génériques qui doivent traiter des tableaux de tailles variables.
Utilisation du mot-clé preserve pour conserver les données existantes
Sans le mot-clé Preserve, chaque appel à ReDim recrée purement et simplement le tableau, effaçant toutes les valeurs précédemment stockées. Pour conserver les données existantes tout en modifiant la taille du tableau, il est indispensable de recourir à ReDim Preserve. Cette forme demande à VBA de copier les éléments de l’ancien tableau dans le nouveau, dans la limite de la plus petite taille commune entre les deux. Autrement dit, si vous rétrécissez le tableau, les éléments en excès sont perdus ; si vous l’agrandissez, les nouveaux éléments sont initialisés à la valeur par défaut du type.
Un exemple classique de liste dynamique de clients serait : ReDim Preserve tClients(1 To n) à chaque ajout, où n représente le nouveau nombre d’enregistrements. Attention toutefois : ReDim Preserve n’autorise pas le changement de type (vous ne pouvez pas passer de String à Integer) et impose une contrainte forte sur les tableaux multidimensionnels que nous allons détailler. D’un point de vue performance, chaque ReDim Preserve implique une copie de mémoire ; il est donc à utiliser avec discernement, surtout dans des boucles très fréquentes.
Redimensionnement des tableaux unidimensionnels versus multidimensionnels
Sur les tableaux unidimensionnels, l’usage de ReDim et ReDim Preserve est très direct : vous ajustez simplement la borne inférieure et/ou supérieure, et VBA s’occupe du reste. Un tableau de type Dim tId() As Long peut ainsi passer de 10 à 10 000 éléments sans aucune modification structurelle de votre code. C’est le scénario idéal pour les listes de données où seule la longueur varie, comme des index de lignes ou des montants de factures à traiter.
En revanche, pour les tableaux multidimensionnels, VBA impose une règle importante : en présence de Preserve, seule la dernière dimension peut être redimensionnée. Vous pouvez passer de ReDim tData(1 To 10, 1 To 5) à ReDim Preserve tData(1 To 10, 1 To 8), mais pas l’inverse (modifier la première dimension). Cette limitation tient au mode de stockage interne des tableaux en mémoire : les éléments sont linéarisés par blocs selon les dimensions, ce qui rend complexe la reconfiguration des premières dimensions sans recopie complète et recomposition du tableau.
Gestion des erreurs subscript out of range lors du redimensionnement
L’erreur « Subscript out of range » (Erreur 9) survient dès que vous accédez à un indice en dehors des bornes définies par LBound et UBound. Cette situation est fréquente lorsqu’un tableau n’a pas encore été dimensionné, a été vidé par Erase, ou que la logique de redimensionnement ne correspond pas aux boucles d’itération. Pour la prévenir, une bonne pratique consiste à tester systématiquement si le tableau est initialisé avant d’y accéder, par exemple en encapsulant un appel à UBound dans une gestion d’erreur contrôlée.
Dans les macros plus avancées, vous pouvez créer une fonction utilitaire, comme IsArrayInitialized, qui renvoie True uniquement si UBound ne déclenche pas d’exception. Vous utiliserez ensuite cette fonction avant tout ReDim Preserve ou toute boucle For i = LBound(...) To UBound(...). Cette approche robuste vous évite de nombreux plantages lors du traitement de données Excel dynamiques, en particulier lorsque le volume de données peut être nul (plage vide, import raté, feuille sans ligne de détail, etc.).
Manipuler les tableaux multidimensionnels avec ReDim en VBA
Limitation du redimensionnement à la dernière dimension uniquement
Comme mentionné plus haut, ReDim Preserve ne permet de modifier que la dernière dimension d’un tableau multidimensionnel. Concrètement, si vous disposez d’un tableau tData(ligne, colonne), vous pouvez augmenter le nombre de colonnes tout en conservant les valeurs (ReDim Preserve tData(1 To nbLignes, 1 To nbColonnes + 1)), mais pas le nombre de lignes. Cette contrainte surprend souvent les développeurs débutants, car on a naturellement tendance à considérer les lignes comme la dimension « extensible », à l’image d’un tableau Excel où l’on ajoute des lignes de données.
Pour contourner cette limitation, de nombreux développeurs inversent volontairement la logique et stockent les lignes sur la dernière dimension. Ainsi, au lieu de tData(ligne, colonne), on adopte un schéma tData(colonne, ligne). Cela peut sembler contre-intuitif au départ, mais permet de redimensionner facilement le nombre de lignes avec ReDim Preserve tData(1 To nbColonnes, 1 To nbLignes). Au moment d’écrire en feuille, il suffit soit d’utiliser Application.Transpose, soit de manipuler les indices en conséquence.
Technique de transposition pour redimensionner les premières dimensions
Lorsque vous devez absolument redimensionner une première dimension tout en conservant vos données, une technique consiste à transposer temporairement le tableau. L’idée est simple : vous inversez les dimensions, redimensionnez la dernière dimension (devenue la première), puis transposez à nouveau pour retrouver la structure initiale. C’est un peu comme retourner un tableau Excel, ajouter ou supprimer des lignes, puis le rebasculer dans son orientation d’origine.
En pratique, cette technique s’appuie sur Application.WorksheetFunction.Transpose, qui renvoie un nouveau tableau avec les axes inversés. Vous pouvez alors appliquer ReDim Preserve sur la nouvelle dernière dimension sans violer les règles de VBA. Cette approche présente toutefois deux limites : elle nécessite de travailler avec des types compatibles (souvent Variant) et elle peut devenir coûteuse en temps de calcul sur de très grands tableaux, chaque transposition impliquant une recopie complète de la structure en mémoire.
Application pratique avec des tableaux bidimensionnels pour les plages excel
La plupart des plages Excel lues via Range.Value retournent un tableau 2D de variants dimensionné de (1 To nbLignes, 1 To nbColonnes). Pour les traiter efficacement, vous pouvez les copier dans un tableau dynamique explicitement typé si vos données sont homogènes, par exemple un Double pour des montants. Vous gagnez alors en performance lors de calculs numériques intensifs : la boucle sur un tableau Double sera sensiblement plus rapide que sur un tableau de Variant.
Lorsqu’il s’agit d’ajouter des colonnes calculées (par exemple des totaux, des pourcentages ou des indicateurs), ReDim Preserve sur la dernière dimension est particulièrement adapté. Vous pouvez ainsi passer de (1 To nbLignes, 1 To nbColonnes) à (1 To nbLignes, 1 To nbColonnes + 1), puis remplir la nouvelle colonne dans une boucle. Une fois le traitement terminé, il suffit d’écrire le tableau résultant d’un seul coup dans une plage de taille adaptée, ce qui est bien plus rapide qu’une écriture cellule par cellule.
Utilisation de ReDim preserve sur les tableaux à deux dimensions
Pour illustrer l’utilisation de ReDim Preserve sur un tableau 2D, imaginons un tableau tResult(1 To nbLignes, 1 To 3) représentant une extraction simple (ID, Date, Montant). Si vous souhaitez ajouter une colonne Catégorie après calcul, vous pouvez écrire : ReDim Preserve tResult(1 To nbLignes, 1 To 4). Les trois premières colonnes sont conservées, et la quatrième est initialisée avec la valeur par défaut (Empty pour Variant, chaîne vide pour String, 0 pour Double, etc.).
La clé pour éviter les erreurs consiste à toujours préserver intactes les dimensions précédentes lors du redimensionnement : la nouvelle déclaration ReDim Preserve tResult(1 To nbLignes, 1 To nouvelleDerniereDimension) doit reprendre exactement les bornes originales de la première dimension. Une simplification consiste à stocker nbLignes = UBound(tResult, 1) avant le redimensionnement, puis à s’y référer plutôt que de répéter des valeurs magiques dans votre code. Cette discipline améliore la lisibilité et réduit le risque de divergences en cas de changement ultérieur de la logique de calcul.
Stratégies d’optimisation mémoire avec ReDim dans les macros excel
Technique d’allocation progressive pour éviter les redimensionnements fréquents
Un des pièges classiques avec ReDim Preserve est de le placer dans une boucle qui s’exécute à chaque ajout d’élément. À chaque itération, VBA doit allouer un nouveau bloc mémoire, copier les données existantes, puis libérer l’ancien bloc. Sur quelques dizaines d’éléments, l’impact est négligeable ; sur plusieurs dizaines de milliers, la macro peut devenir dramatiquement lente. Pour éviter cet écueil, une stratégie consiste à procéder par allocation progressive.
Concrètement, vous allouez le tableau par « paquets » (par exemple 100 ou 1 000 éléments) plutôt qu’un par un. Vous maintenez un compteur nbAlloues et un compteur nbUtilises ; lorsqu’une nouvelle insertion ferait dépasser nbAlloues, vous augmentez la capacité (ReDim Preserve tData(1 To nbAlloues + pas)) au lieu de redimensionner pour chaque élément. Cette technique, inspirée des stratégies d’array lists dans d’autres langages, réduit drastiquement le nombre de recopies mémoire et peut multiplier par 5 ou 10 la vitesse de vos traitements sur grands volumes.
Utilisation de Application.Transpose comme alternative au redimensionnement
Dans certains scénarios, plutôt que de redimensionner un tableau pour changer son orientation (ajouter des lignes au lieu de colonnes, ou inversement), il est plus simple et plus performant d’utiliser Application.Transpose. Cette fonction Excel, accessible depuis VBA, permet de transformer rapidement une liste verticale en liste horizontale, ou un tableau 2D lignes/colonnes en colonnes/lignes. C’est un peu l’équivalent programmatique de la fonctionnalité « Transpose » disponible dans le collage spécial d’Excel.
Par exemple, si vous avez construit un tableau 1D en ajoutant des éléments par colonnes et que vous souhaitez l’écrire verticalement, vous pouvez tout simplement faire : Range("A1").Resize(UBound(tData) + 1, 1).Value = Application.Transpose(tData). Vous évitez ainsi un ReDim intermédiaire ou des manipulations d’index. Attention toutefois : Transpose possède ses propres limites (taille maximale, types de données supportés) et peut renvoyer des erreurs si le tableau est très volumineux ou contient certains objets. Dans ces cas-là, il faudra revenir à une gestion plus traditionnelle avec ReDim et boucles contrôlées.
Impact sur les performances avec de grands ensembles de données
Lorsque vos macros VBA manipulent des plages de plusieurs dizaines de milliers de lignes, la façon dont vous utilisez ReDim influence très fortement le temps d’exécution. Les bonnes pratiques modernes recommandent de limiter au maximum les allers-retours entre Excel et VBA, en chargeant les données en mémoire via un tableau, en les traitant intégralement côté VBA, puis en écrivant le résultat en une fois dans la feuille. Dans ce contexte, ReDim sert principalement lors de la phase de structuration initiale, et non à chaque transformation.
Pour mesurer l’impact réel sur vos projets, vous pouvez encapsuler des sections critiques de code dans un chronométrage rudimentaire (Timer avant/après) et comparer différentes stratégies de redimensionnement. Vous constaterez souvent qu’une allocation légèrement surdimensionnée au départ (par exemple prévoir 20 % de marge) suivie d’un redimensionnement final pour ajuster à la taille exacte offre un excellent compromis entre performance et consommation mémoire. À l’inverse, une utilisation naïve de ReDim Preserve dans une boucle d’ajout élément par élément peut faire passer un traitement de quelques secondes à plusieurs minutes.
Cas pratiques de ReDim pour le traitement de données excel dynamiques
Création d’une liste dynamique à partir d’un range à taille variable
Un cas d’usage typique de ReDim en VBA consiste à construire une liste dynamique à partir d’une colonne dont la taille varie selon les fichiers ou les périodes. Supposons que vous souhaitiez récupérer toutes les valeurs non vides de la colonne B, de la ligne 2 jusqu’à la dernière ligne utilisée. Vous pouvez d’abord déterminer la dernière ligne avec Cells(Rows.Count, "B").End(xlUp).Row, puis allouer un tableau de la bonne taille avec ReDim tListe(1 To nbLignes) et enfin le remplir dans une boucle.
Si vous devez filtrer certaines lignes (par exemple ignorer les doublons ou les zéros), une approche consiste à allouer d’abord un tableau avec la taille maximale possible, puis à compter réellement le nombre d’éléments retenus. À la fin du processus, un ReDim Preserve tListe(1 To nbValides) vous permet de tronquer le tableau à la taille utile. Ce schéma est particulièrement adapté pour générer des listes dynamiques utilisées ensuite dans des validations de données, des contrôles d’intégrité ou des interfaces utilisateur (ListBox, ComboBox) alimentées par VBA.
Implémentation d’un système de collecte de données avec boucles for each
Les boucles For Each sur des collections de cellules ou d’objets se marient très bien avec ReDim pour créer des structures de données dynamiques. Imaginons que vous deviez parcourir une plage complexe (plusieurs zones, cellules non contiguës) et collecter uniquement les cellules correspondant à un certain critère (par exemple toutes les cellules de format date supérieures à aujourd’hui). Vous pouvez initialiser un tableau dynamique vide, puis, à chaque correspondance, stocker l’adresse ou la valeur de la cellule.
Pour que ce système de collecte reste performant, il est judicieux de combiner la technique d’allocation progressive avec la boucle For Each. Vous démarrez avec un petit tableau, par exemple ReDim tMatches(1 To 50), puis dès que le compteur atteint la capacité maximale, vous augmentez la taille par blocs grâce à ReDim Preserve. Au terme de la boucle, un redimensionnement final ajuste le tableau à la taille réelle des données collectées. Cette approche transforme votre code en un véritable « aspirateur à données » fiable et évolutif, capable de s’adapter à des feuilles très hétérogènes.
Construction de tableaux croisés dynamiques personnalisés avec ReDim
Au-delà des fonctions natives de tableaux croisés dynamiques d’Excel, vous pouvez construire vos propres structures agrégées en VBA à l’aide de tableaux dynamiques. Par exemple, pour analyser des ventes par Zone et par Produit, vous pouvez d’abord extraire les listes uniques de zones et de produits dans deux tableaux 1D créés et ajustés via ReDim. Ensuite, vous allouez un tableau 2D tPivot(1 To nbZones, 1 To nbProduits) qui contiendra les montants agrégés.
Au fil de la lecture de vos lignes de données sources, vous localisez l’index de la zone et du produit correspondants (par exemple via une fonction de recherche dans les tableaux 1D), puis incrémentez la cellule appropriée du tableau 2D. Une fois le traitement terminé, vous pouvez écrire le tableau croisé personnalisé dans une feuille dédiée, avec les en-têtes générés à partir des tableaux de zones et de produits. Grâce à ReDim, cette approche s’adapte automatiquement aux variations de combinaisons (nouvelles zones, nouveaux produits) sans que vous ayez à modifier manuellement la taille des structures.
Gestion des imports de données CSV avec allocation mémoire adaptative
Lors de l’import de fichiers CSV, vous ne connaissez souvent pas à l’avance le nombre de lignes ni parfois le nombre de colonnes, surtout si les fichiers proviennent de systèmes externes. Une stratégie efficace consiste à lire le fichier ligne par ligne (via Line Input), à découper chaque ligne avec Split, puis à stocker le résultat dans un tableau dynamique 2D. Vous pouvez commencer par allouer un nombre de colonnes égal au maximum attendu, ou l’ajuster à la volée en surveillant la longueur des tableaux renvoyés par Split.
Pour les lignes, l’allocation par blocs est une fois de plus recommandée : par exemple, initialiser le tableau avec 1 000 lignes, puis augmenter la capacité dès que le compteur de lignes atteint cette limite, au moyen d’un ReDim Preserve tCsv(1 To nbColonnes, 1 To nbLignesAllouees + 1_000). Une fois l’import terminé, vous redimensionnez à la taille exacte du fichier lu. Cette allocation mémoire adaptative permet de gérer des fichiers CSV de taille très variable, tout en maintenant des temps de traitement raisonnables, même lorsque vous traitez plusieurs dizaines de milliers de lignes de données brutes.