Array VBA Excel : maîtriser les tableaux en VBA pour automatiser vos feuilles de calcul

Dans Excel, les tableaux VBA — ou arrays — permettent de stocker plusieurs valeurs dans une seule variable. C’est un outil incontournable dès que l’on souhaite automatiser une feuille de calcul sans multiplier les allers-retours entre le code et les cellules.

Pourquoi est-ce important ? Parce qu’une macro qui lit ou modifie les cellules une par une peut rapidement devenir lente. En revanche, en chargeant une plage dans un tableau, en travaillant directement en mémoire, puis en réinjectant le résultat dans Excel, les performances peuvent être considérablement améliorées.

Dans cet article, nous allons voir comment déclarer, remplir, parcourir et redimensionner un tableau VBA. Nous verrons également comment l’utiliser avec une plage Excel, un cas particulièrement pratique au quotidien.

Qu’est-ce qu’un tableau en VBA ?

Un tableau est une variable capable de contenir plusieurs valeurs. Contrairement à une variable classique, qui stocke une seule information, un tableau fonctionne comme une série de cases accessibles grâce à un ou plusieurs index.

Voici un exemple simple :

Dim villes(0 To 2) As Stringvilles(0) = "Paris"villes(1) = "Lyon"villes(2) = "Nantes"

La variable villes contient ici trois éléments. Chaque élément est identifié par un index compris entre 0 et 2.

On peut ensuite récupérer une valeur précise :

MsgBox villes(1)

Cette instruction affiche Lyon.

Un tableau peut contenir du texte, des nombres, des dates ou tout autre type de données pris en charge par VBA. Il peut également être multidimensionnel, par exemple pour représenter des lignes et des colonnes.

Déclarer un tableau VBA

La déclaration d’un tableau dépend principalement de deux éléments : son type et le fait que sa taille soit connue ou non à l’avance.

Un tableau de taille fixe

Lorsque le nombre d’éléments est connu, la syntaxe est très simple :

Dim notes(1 To 5) As Double

Ce tableau peut stocker cinq nombres décimaux, avec des index allant de 1 à 5.

Il est tout à fait possible de commencer à l’index 0 :

Dim notes(0 To 4) As Double

Dans les deux cas, le tableau contient cinq éléments. La différence concerne uniquement la numérotation des index.

Pour éviter les surprises, je recommande de préciser systématiquement les bornes du tableau. Le code devient plus lisible, surtout lorsqu’il est relu quelques mois plus tard — ou par quelqu’un d’autre, ce qui arrive toujours au moment le moins pratique.

Un tableau dynamique

Un tableau dynamique ne possède pas encore de taille définie au moment de sa déclaration :

Dim produits() As String

Il faut ensuite lui attribuer une taille avec l’instruction ReDim :

ReDim produits(1 To 10)

Le tableau peut maintenant contenir dix éléments. Cette méthode est particulièrement utile lorsque la taille dépend du nombre de lignes présentes dans une feuille Excel.

Remplir et lire un tableau

Une fois le tableau déclaré, chaque élément peut être alimenté individuellement :

Dim prix(1 To 3) As Currencyprix(1) = 12.5prix(2) = 8.9prix(3) = 24.75

Pour parcourir les éléments, on utilise généralement une boucle For :

Dim i As LongFor i = LBound(prix) To UBound(prix)    Debug.Print prix(i)Next i

Les fonctions LBound et UBound renvoient respectivement la première et la dernière borne du tableau.

Cette approche est préférable à l’écriture en dur des index. Si la taille du tableau évolue, la boucle continuera de fonctionner sans modification.

Les tableaux à deux dimensions

Un tableau à deux dimensions ressemble davantage à une petite grille composée de lignes et de colonnes. Il est donc parfaitement adapté aux données issues d’une feuille de calcul.

Dim donnees(1 To 3, 1 To 2) As Variantdonnees(1, 1) = "Clavier"donnees(1, 2) = 29.9donnees(2, 1) = "Souris"donnees(2, 2) = 14.5donnees(3, 1) = "Écran"donnees(3, 2) = 189

Dans cet exemple, la première dimension représente les lignes et la seconde les colonnes.

Pour parcourir toutes les valeurs, il faut utiliser deux boucles imbriquées :

Dim ligne As LongDim colonne As LongFor ligne = LBound(donnees, 1) To UBound(donnees, 1)    For colonne = LBound(donnees, 2) To UBound(donnees, 2)        Debug.Print donnees(ligne, colonne)    Next colonneNext ligne

Le deuxième argument de LBound et UBound indique la dimension concernée. Sans cette précision, VBA utilise la première dimension.

Charger une plage Excel dans un tableau

C’est l’un des usages les plus puissants des arrays VBA. Une plage de plusieurs cellules peut être chargée directement dans une variable de type Variant :

Dim donnees As Variantdonnees = Worksheets("Ventes").Range("A2:C100").Value2

La variable donnees contient alors un tableau à deux dimensions. La première dimension correspond aux lignes et la seconde aux colonnes.

Par exemple, pour afficher la valeur située à la deuxième ligne et à la troisième colonne de la plage :

Debug.Print donnees(2, 3)

Attention : les index du tableau ne correspondent pas forcément aux numéros de lignes et de colonnes de la feuille. Dans la plage A2:C100, donnees(1, 1) représente la cellule A2, tandis que donnees(1, 3) représente C2.

Le choix de Value2 est généralement recommandé pour manipuler rapidement les valeurs brutes, sans conversion automatique liée aux dates ou aux devises.

Modifier les données en mémoire

Imaginons une feuille contenant des produits en colonne A et des prix en colonne B. Nous voulons appliquer une remise de 10 % à tous les prix supérieurs à 100 euros.

Sub AppliquerRemise()    Dim donnees As Variant    Dim i As Long    donnees = Worksheets("Produits").Range("A2:B100").Value2    For i = LBound(donnees, 1) To UBound(donnees, 1)        If donnees(i, 2) > 100 Then            donnees(i, 2) = donnees(i, 2) * 0.9        End If    Next i    Worksheets("Produits").Range("A2:B100").Value2 = donneesEnd Sub

Le principe est simple :

• la plage est chargée une seule fois dans le tableau ;
• les calculs sont effectués en mémoire ;
• le tableau complet est réécrit dans la feuille en une seule opération.

Sur quelques dizaines de lignes, la différence est peu visible. Sur plusieurs milliers de lignes, elle peut être spectaculaire.

Pourquoi les tableaux améliorent-ils les performances ?

Chaque accès à une cellule Excel demande un échange entre VBA et l’application Excel. Une boucle qui modifie les cellules une par une répète cette opération des centaines, voire des milliers de fois.

Voici une méthode fonctionnelle, mais souvent lente :

For i = 2 To 10000    If Cells(i, 2).Value2 > 100 Then        Cells(i, 2).Value2 = Cells(i, 2).Value2 * 0.9    End IfNext i

La version utilisant un tableau limite les échanges avec Excel :

Dim valeurs As VariantDim i As Longvaleurs = Range("B2:B10000").Value2For i = LBound(valeurs, 1) To UBound(valeurs, 1)    If valeurs(i, 1) > 100 Then        valeurs(i, 1) = valeurs(i, 1) * 0.9    End IfNext iRange("B2:B10000").Value2 = valeurs

Cette technique est l’un des premiers réflexes à adopter lorsqu’une macro semble prendre trop de temps.

Redimensionner un tableau avec ReDim

Un tableau dynamique peut être redimensionné grâce à ReDim :

Dim clients() As StringReDim clients(1 To 3)clients(1) = "Durand"clients(2) = "Martin"clients(3) = "Bernard"ReDim clients(1 To 5)

Le deuxième ReDim agrandit le tableau, mais efface les valeurs déjà présentes. Pour les conserver, il faut utiliser ReDim Preserve :

ReDim Preserve clients(1 To 5)

Les trois premières valeurs sont alors conservées.

Il existe toutefois une limite importante : avec ReDim Preserve, seule la dernière dimension d’un tableau multidimensionnel peut être redimensionnée. Par exemple, cette instruction fonctionne :

ReDim Preserve tableau(1 To 10, 1 To 5)

Mais redimensionner la première dimension tout en conservant les données n’est pas possible directement. Dans ce cas, il faut généralement créer un nouveau tableau et recopier les valeurs.

Éviter les erreurs avec un tableau vide

Un tableau dynamique non dimensionné ne peut pas être parcouru comme un tableau classique. Cette situation peut arriver lorsqu’une recherche ne renvoie aucun résultat ou lorsqu’une procédure reçoit un tableau vide.

Pour vérifier si un tableau dynamique a été dimensionné, on peut utiliser une fonction dédiée :

Function TableauDimensionne(ByRef tableau As Variant) As Boolean    On Error GoTo NonDimensionne    Dim borne As Long    borne = UBound(tableau)    TableauDimensionne = True    Exit FunctionNonDimensionne:    TableauDimensionne = FalseEnd Function

Cette fonction s’appuie sur une gestion d’erreur contrôlée, car l’appel à UBound provoque une erreur si le tableau n’a pas encore reçu de taille.

Le cas particulier d’une seule cellule

Lorsqu’une plage contient plusieurs cellules, l’affectation à une variable Variant renvoie un tableau. En revanche, pour une seule cellule, VBA renvoie directement une valeur simple.

Dim donnees As Variantdonnees = Range("A1:A10").Value2

Ici, donnees est un tableau à deux dimensions.

donnees = Range("A1").Value2

Dans ce second cas, donnees contient simplement la valeur de la cellule. Si votre macro peut traiter une plage de taille variable, pensez à gérer ce cas particulier.

Une solution consiste à vérifier le nombre de cellules :

If Range("A1:A10").Cells.CountLarge = 1 Then    ' Traitement d'une seule valeurElse    ' Traitement d'un tableauEnd If

Une macro complète avec tableau et dernière ligne

Pour rendre notre exemple plus réaliste, déterminons automatiquement la dernière ligne utilisée. La macro suivante applique une remise aux prix supérieurs à 100 euros, quelle que soit la longueur de la liste.

Sub ActualiserPrix()    Dim feuille As Worksheet    Dim derniereLigne As Long    Dim donnees As Variant    Dim i As Long    Set feuille = ThisWorkbook.Worksheets("Produits")    derniereLigne = feuille.Cells(feuille.Rows.Count, "B").End(xlUp).Row    If derniereLigne < 2 Then Exit Sub    donnees = feuille.Range("A2:B" & derniereLigne).Value2    For i = LBound(donnees, 1) To UBound(donnees, 1)        If IsNumeric(donnees(i, 2)) Then            If donnees(i, 2) > 100 Then                donnees(i, 2) = donnees(i, 2) * 0.9            End If        End If    Next i    feuille.Range("A2:B" & derniereLigne).Value2 = donneesEnd Sub

La fonction IsNumeric évite une erreur si une cellule contient du texte, une cellule vide ou une information inattendue. Dans une macro destinée à être utilisée par plusieurs personnes, cette petite vérification peut éviter bien des messages d’erreur.

Quelques bonnes pratiques à retenir

• Utilisez Option Explicit en haut de vos modules pour forcer la déclaration des variables.
• Préférez Long à Integer pour les compteurs de lignes.
• Utilisez Value2 pour charger et réécrire rapidement des données.
• Employez LBound et UBound plutôt que des limites écrites en dur.
• Travaillez en mémoire avant de réinjecter les résultats dans la feuille.
• Vérifiez les cellules vides ou non numériques avant d’effectuer un calcul.
• Utilisez des noms de variables explicites comme donnees, derniereLigne ou indice.

Les tableaux VBA deviennent rapidement indispensables dès qu’une macro doit traiter un volume important de données. Ils rendent le code plus rapide, plus structuré et souvent plus facile à maintenir.

La prochaine fois qu’une boucle parcourt plusieurs milliers de cellules une par une, posez-vous la question : ces données ne pourraient-elles pas être chargées dans un array, traitées en mémoire, puis réécrites en une seule fois ? Dans bien des cas, la réponse changera radicalement le comportement de votre automatisation Excel.