VBA Array dans Excel — déclarer, parcourir et lire une plage 100× plus vite (7 exemples)

Testé sur : Excel 365 v2509 · Excel 2021 · Excel 2019 · dernière vérification le 05/06/2026

En bref — Un tableau (array) est une variable à plusieurs cases. Le geste décisif en Excel VBA : lire toute une plage dans un tableau, travailler en mémoire et réécrire une seule fois — au lieu de toucher les cellules une à une :

Sub MiseAJourRapide()
    Dim arr As Variant
    arr = Range("A2:C10000").Value2          ' UNE lecture -> tableau 2D

    Dim i As Long
    For i = 1 To UBound(arr, 1)
        arr(i, 3) = arr(i, 2) * 1.1          ' travailler en mémoire (instantané)
    Next i

    Range("A2:C10000").Value2 = arr          ' UNE écriture
End Sub

Sur 10 000 lignes, c'est la différence entre ~8 secondes et ~0,1. Le reste du guide construit cette ligne et ses pièges.

Pourquoi des tableaux — et ce qu'ils sont

Une variable ordinaire contient une valeur. Un tableau contient une liste de valeurs sous un seul nom, atteinte par un indice :

Dim notes(1 To 3) As Long
notes(1) = 90
notes(2) = 75
notes(3) = 60

Pourquoi se donner cette peine ? Vitesse et structure. Chaque fois que VBA lit ou écrit une cellule, il franchit une frontière lente entre le code et Excel. Un tableau vit en mémoire : parcourir 10 000 cases de tableau est des milliers de fois plus rapide que parcourir 10 000 cellules.

Exemple 1 — Tableaux statiques et base d'indice

Un tableau statique a une taille fixe définie à la déclaration. Indiquez les bornes explicitement pour ne jamais deviner :

Dim mois(1 To 12) As String        ' indices 1..12 — lisible
Dim baseZero(0 To 9) As Double      ' indices 0..9

Astuce : écrivez toujours (1 To n) plutôt que (n). Dim a(10) crée en réalité 11 cases (0–10), sauf avec Option Base 1. Le (1 To 10) explicite lève toute ambiguïté.

Exemple 2 — Tableaux dynamiques avec ReDim

Quand vous ignorez la taille au départ, déclarez un tableau vide et dimensionnez-le plus tard avec ReDim :

Dim noms() As String
Dim n As Long
n = Cells(Rows.Count, 1).End(xlUp).Row - 1   ' lignes de données

ReDim noms(1 To n)                           ' dimensionner maintenant
Dim i As Long
For i = 1 To n
    noms(i) = Cells(i + 1, 1).Value
Next i

Un ReDim simple efface le contenu. Pour garder l'existant, utilisez ReDim Preserve.

Exemple 3 — ReDim Preserve (et son grand piège)

ReDim Preserve redimensionne sans perdre les données — parfait pour une liste qui grandit :

Dim resultats() As String
Dim compte As Long
compte = 0

Dim cellule As Range
For Each cellule In Range("A2:A100")
    If cellule.Value <> "" Then
        compte = compte + 1
        ReDim Preserve resultats(1 To compte)  ' grandir d'une case, garder le reste
        resultats(compte) = cellule.Value
    End If
Next cellule

Le piège : pour un tableau 2-D, ReDim Preserve ne peut redimensionner que la dernière dimension. ReDim Preserve grille(1 To 5, 1 To 10) passe si seul le 10 change ; modifier le 5 déclenche « Indice en dehors de la plage ». Solution : placer l'axe qui grandit en dernier, ou transposer.

Exemple 4 — UBound et LBound (ne jamais coder la taille en dur)

Demandez sa taille au tableau au lieu de la supposer :

Dim arr As Variant
arr = Array("Lun", "Mar", "Mer")             ' un tableau base 0

Dim i As Long
For i = LBound(arr) To UBound(arr)           ' quelle que soit la base
    Debug.Print arr(i)
Next i

Pour un tableau 2-D issu d'une plage, précisez la dimension : UBound(arr, 1) est le nombre de lignes, UBound(arr, 2) le nombre de colonnes.

Exemple 5 — Lire une plage dans un tableau (l'astuce 100×)

C'est toute la raison d'apprendre les tableaux. UnePlage.Value2 vous donne, en une seule lecture, un tableau bidimensionnel à base 1 :

Sub SommerColonneVite()
    Dim arr As Variant
    arr = Range("A2:C10000").Value2          ' arr(ligne, colonne), base 1 partout

    Dim total As Double, i As Long
    For i = 1 To UBound(arr, 1)
        total = total + arr(i, 2)            ' colonne 2 = Quantité
    Next i

    MsgBox "Quantité totale : " & total
End Sub

Deux choses à retenir :

1. Le résultat est toujours base 1 — arr(1, 1) est la cellule en haut à gauche, quelle que soit Option Base.
2. Une seule cellule ne renvoie pas un tableau. Range("A1").Value2 n'est que la valeur ; seule une plage multicellule devient un tableau 2-D.

Exemple 6 — Parcourir un tableau avec For Each

Quand seules les valeurs comptent (pas leur position), For Each est le plus lisible :

Dim regions As Variant
regions = Array("Nord", "Sud", "Est", "Ouest")

Dim r As Variant
For Each r In regions
    Debug.Print r
Next r

Exemple 7 — Construire des tableaux vite avec Array() et Split()

Deux lignes qui évitent une pile d'affectations :

' Array() — une liste littérale rapide (base 0)
Dim jours As Variant
jours = Array("Lun", "Mar", "Mer", "Jeu", "Ven")

' Split() — transformer un texte délimité en tableau (base 0)
Dim parties As Variant
parties = Split("pomme;banane;cerise", ";")
Debug.Print parties(0)        ' pomme
Debug.Print UBound(parties)   ' 2  (trois éléments, 0..2)

Join(parties, ", ") fait l'inverse — du tableau vers une chaîne.

Erreurs fréquentes avec les tableaux (et la solution)

Oubliez la plomberie des tableaux — décrivez la transformation

Les tableaux sont rapides, mais ReDim Preserve, UBound(arr, 2) et la comptabilité base-1-contre-base-0 sont exactement le genre de détail qui transforme cinq minutes en après-midi. ExcelMaster Agent vous laisse énoncer le but — « charge tout le tableau, augmente chaque prix de 10 % et réécris-le » — et génère le motif lire-dans-un-tableau / traiter / réécrire, bornes comprises. Essayer gratuitement →

Guides associés

• VBA Range dans Excel — référencer, lire et écrire des cellules
• VBA Dictionary dans Excel — recherches, dédoublonnage et regroupement
• VBA For dans Excel — 8 exemples concrets

FAQ

À quoi sert un tableau en VBA ? Un tableau stocke plusieurs valeurs de même nature sous un seul nom, atteintes par indice (arr(1), arr(2)…). Il permet de lire et traiter une liste de données en mémoire, bien plus vite que de lire ces mêmes données cellule par cellule.

Comment utiliser un tableau au lieu d'une plage en Excel VBA ? Lisez la plage dans un tableau d'un coup — arr = Range("A1:C10000").Value2 — parcourez-le avec UBound, puis réécrivez avec Range(...).Value2 = arr. Cela remplace des milliers d'accès lents par une lecture et une écriture.

Quelle différence entre ReDim et ReDim Preserve ? ReDim redimensionne un tableau dynamique et efface son contenu. ReDim Preserve redimensionne en conservant les valeurs — mais pour un tableau 2-D, seule la dernière dimension peut changer.

Pourquoi mon tableau VBA est-il en base 0 ? Les tableaux issus de Array() et Split() commencent à l'indice 0 par défaut (sauf Option Base 1). Les tableaux lus depuis une plage (.Value2) sont toujours en base 1. Bouclez LBound(arr) To UBound(arr) et la base n'importe plus.

Comment obtenir la longueur d'un tableau VBA ? Avec UBound(arr) - LBound(arr) + 1. Pour un tableau 2-D issu d'une plage, le nombre de lignes est UBound(arr, 1) et le nombre de colonnes UBound(arr, 2).