Cours VBA : utilisations des tableaux (suite)

Le tableau à 2 dimensions

Pour enregistrer plusieurs colonnes de données, une dimension supplémentaire est nécessaire. En voici un exemple :

enreg-2-dim - tableaux vba suite

Enregistrement des données dans un tableau à 2 dimensions :

'Déclaration
Dim tab_exemple(10, 2) 'Tableau de 11 x 3 "cases"

'Enregistrement des valeurs dans le tableau
For i = 0 To 10
    tab_exemple(i, 0) = Range("A" & i + 2)
    tab_exemple(i, 1) = Range("B" & i + 2)
    tab_exemple(i, 2) = Range("C" & i + 2)
Next

Et quelques exemples de valeurs :

MsgBox tab_exemple(0, 0) '=> renvoie : 11.03.2026
MsgBox tab_exemple(0, 1) '=> renvoie : 24
MsgBox tab_exemple(9, 2) '=> renvoie : NON
MsgBox tab_exemple(10, 2) '=> renvoie : OUI

Le tableau dynamique

Imaginons que cette même base de données soit régulièrement mise à jour et que l'on ne puisse donc pas entrer de valeurs fixes à la déclaration ...

enreg-dynamique - tableaux vba suite

Pour connaître le n° de ligne de la dernière cellule d'un bloc de cellules non vides, autrement dit, la dernière ligne de notre base de données, utilisez cette formule :

derniere_ligne = Range("A1").End(xlDown).Row

Si vous entrez une variable lors de la déclaration, Excel ne l'acceptera pas.

Déclarez un tableau dynamique (parenthèses vides), puis définissez ses dimensions avec Redim :

Dim tab_exemple()
ReDim tab_exemple(derniere_ligne - 2, 2)

De cette manière vous enregistrerez automatiquement toutes les lignes de la base de données dans le tableau :

Sub enreg_tab()
    derniere_ligne = Range("A1").End(xlDown).Row 'Dernière ligne de la base de données

    Dim tab_exemple()
    ReDim tab_exemple(derniere_ligne - 2, 2)
   
    'Enregistrement des valeurs dans le tableau
    For i = 0 To derniere_ligne - 2
        tab_exemple(i, 0) = Range("A" & i + 2)
        tab_exemple(i, 1) = Range("B" & i + 2)
        tab_exemple(i, 2) = Range("C" & i + 2)
    Next
End Sub

Ubound

Dans l'exemple ci-dessus, le dernier n° de notre tableau était derniere_ligne - 2 :

For i = 0 To derniere_ligne - 2

Une autre solution pour connaître ce n° consiste à utiliser la fonction Ubound :

For i = 0 To UBound(tab_exemple)

Cette fonction renvoie le plus grand n° pour une dimension choisie (par défaut la première).

Quelques exemples pour mieux comprendre :

Sub enreg_tab()
    Dim tab_exemple(10, 2)
   
    MsgBox UBound(tab_exemple) '=> renvoie : 10
    MsgBox UBound(tab_exemple, 1) '=> renvoie : 10
    MsgBox UBound(tab_exemple, 2) '=> renvoie : 2
End Sub

Enregistrer une plage de cellules

Il est possible d'enregistrer une plage de cellules dans un tableau sans passer par une boucle.

'Déclaration
Dim tab_exemple(10, 2) 'Tableau de 11 x 3 "cases"

'Enregistrement des valeurs dans le tableau
For i = 0 To 10
    tab_exemple(i, 0) = Range("A" & i + 2)
    tab_exemple(i, 1) = Range("B" & i + 2)
    tab_exemple(i, 2) = Range("C" & i + 2)
Next

Le code ci-dessus peut être remplacé par :

'Déclaration
Dim tab_exemple()

'Enregistrement des valeurs dans le tableau
tab_exemple = Range("A2:C12").Value

Même si au premier abord cette seconde méthode semble séduisante, elle peut dans bien des cas vous faire perdre plus de temps que la première méthode ...

En enregistrant vos données dans le tableau de cette manière, le premier n° n'est pas 0 mais 1, cela peut être source de confusion ... De plus, si au cours du développement vous choisissez de n'enregistrer dans le tableau que les données répondant à certains critères (ou effectuer toute autre opération), vous aurez besoin de tout réécrire avec une boucle ...

Cette seconde méthode reste tout de même intéressante lorsque vous avez besoin d'enregistrer l'ensemble du contenu d'une grande base de données, car plus rapide qu'avec une boucle (gain d'environ 0.2 secondes pour 15'000 entrées).

Array

Vous aurez peut-être parfois besoin de créer un tableau contenant des données "fixes".

Une solution consiste à l'écrire ligne par ligne :

Dim fr(5)

fr(0) = "SI"
fr(1) = "RECHERCHEV"
fr(2) = "SOMME"
fr(3) = "NB"
fr(4) = "ESTNUM"
fr(5) = "STXT"

Heureusement, vous pouvez vous simplifier la tâche en utilisant Array :

fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")

Voici un exemple d'utilisation de la fonction Replace (utile pour mieux comprendre l'exemple suivant) :

Sub remplacement()
    Dim chaine_a_traiter As String

    'Une chaîne pour cet exemple
    chaine_a_traiter = "Hello World !"
   
    'Remplacement de "World" par "toi" dans la chaîne de caractères
    chaine_a_traiter = Replace(chaine_a_traiter, "World", "toi")

    'La chaîne après remplacement
    MsgBox chaine_a_traiter '=> renvoie "Hello toi !"
End Sub

Maintenant si l'on veut remplacer une série de valeurs par une autre série, l'utilisation de tableaux (Array) est toute indiquée :

Sub traduction() 'Exemple simplifié de traduction FR-EN de formules
    Dim chaine_a_traiter As String

    'Une chaîne pour cet exemple
    chaine_a_traiter = "Formule à traduire : SOMME(SI(ESTNUM(A1:E1);A1:E1;0))"
   
    'Les 2 séries de valeurs
    fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
    en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
   
    'Remplacement de "SI" par "IF", de "RECHERCHEV" par "VLOOKUP", etc.
    For i = 0 To UBound(fr)
        chaine_a_traiter = Replace(chaine_a_traiter, fr(i), en(i))
    Next

    'La chaîne après les remplacements
    MsgBox chaine_a_traiter '=> renvoie "Formule à traduire : SUM(IF(ISNUMBER(A1:E1);A1:E1;0))"
End Sub

Split

La fonction Split permet de convertir une chaîne de caractères en un tableau.

Pour convertir cette chaîne de caractères en tableau :

chaine = "SI/RECHERCHEV/SOMME/NB/ESTNUM/STXT"

Utilisez la fonction Split et définissez le séparateur :

fr = Split(chaine, "/")

Le tableau fr renverra les valeurs suivantes :

MsgBox fr(0) '=> renvoie : SI
MsgBox fr(1) '=> renvoie : RECHERCHEV
MsgBox fr(2) '=> renvoie : SOMME
MsgBox fr(3) '=> renvoie : NB
MsgBox fr(4) '=> renvoie : ESTNUM
MsgBox fr(5) '=> renvoie : STXT

Les 3 tableaux suivants renvoient également les mêmes valeurs :

fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
fr = Split("SI,RECHERCHEV,SOMME,NB,ESTNUM,STXT", ",")
fr = Split("SI RECHERCHEV SOMME NB ESTNUM STXT", " ")

L'exemple suivant renvoie la 3e valeur de la chaîne de caractères :

MsgBox Split("SI,RECHERCHEV,SOMME,NB,ESTNUM,STXT", ",")(2) '=> renvoie : SOMME
somme - tableaux vba suite

La fonction à l'opposé de Split est Join.

Cette fonction permet d'assembler les valeurs d'un tableau en une chaîne de caractères.

MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> renvoie : 12345
join - tableaux vba suite
Télécharger ce cours au format PDF