VBA-Leçon 13.2. Utilisation de tableaux (Arrays) (suite)

Afin de stocker plus d’une colonne de données, nous avons besoin d’une dimension différente du tableau. Exemple:

Sauvegarde des données dans un tableau à deux dimensions :

 'Déclaration
 Dim array_example(10, 2) 'Tableau 11 x 3 "défini"

 'Sauvegarde des données dans un tableau
 For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
 Next

Voici quelques exemples de travail avec ces valeurs :

MsgBox array_example(0, 0)  '=> retours : 03.11.2026
MsgBox array_example(0, 1)  '=> renvoie : 24
MsgBox array_example(9, 2)  '=> renvoie : NO
MsgBox array_example(10, 2) '=> renvoie : YES

Tableau dynamique

Imaginons un instant que nous devions mettre à jour régulièrement les données de notre tableau, et que nous ne puissions donc pas attribuer de valeurs fixes au moment de la déclaration...

Pour connaître le numéro de ligne de la dernière cellule non vide, ou autrement dit, la dernière ligne de notre base de données, nous utiliserons la formule suivante :

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

Excel n'accepte pas les variables dans la déclaration.

Déclarez plutôt un tableau dynamique (en utilisant des parenthèses vides), puis définissez sa taille à l'aide de Redim :

Dim array_example()
ReDim array_example(last_row - 2, 2)

En utilisant la procédure suivante, vous pouvez stocker toutes les lignes de votre ensemble de données (table) dans notre tableau :

Dim array_example()
ReDim array_example(last_row - 2, 2)

À destination

Dans l'exemple précédent, le dernier nombre de notre tableau était last_row - 2 :

For i = 0 To last_row - 2

Une autre façon de déterminer le dernier numéro de notre tableau pourrait être d'utiliser Ubound :

For i = 0 To UBound(array_example)

Cette fonction renvoie le plus grand nombre du tableau pour la dimension sélectionnée (la première dimension est la dimension par défaut).

Voici quelques exemples qui rendront cela plus clair :

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

Sauvegarde des données dans une plage d'éléments du tableau

Il est possible de remplir un tableau avec les valeurs d'une plage de cellules sur une feuille de calcul sans même utiliser de boucle :

 'Déclaration
 Dim array_example(10, 2) 'Tableau 11 x 3 "défini"

 'Sauvegarde des données dans un tableau
 For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
 Next

Le code précédent peut être effectivement remplacé par ceci :

 'Déclaration
 Dim array_example()

 'Sauvegarde des données dans un tableau
 array_example = Range("A2:C12").Value

Bien que la deuxième méthode semble plus attractive que la première, attention, dans la plupart des cas, elle peut vous coûter plus de temps à mettre en œuvre que la première.

Si vous stockez les données dans votre tableau de cette façon, le premier nombre sera 1 au lieu de 0, ce qui peut prêter à confusion...

Plus tard dans le processus d'augmentation du code, si vous décidez de stocker uniquement les données qui répondent à certains critères de recherche dans le tableau (ou d'effectuer une opération complètement différente), vous devrez réécrire complètement le code en utilisant une fonction de boucle différente...

Mais cette deuxième méthode est très utile si vous devez sauvegarder l’intégralité du contenu d’un grand ensemble de données, car elle est plus rapide que la boucle (économisant environ 0,2 seconde tous les 15 000 enregistrements).

Tableau (Array)

Mais si vous devez créer un tableau dont le contenu est "fixe".

Une solution pourrait être d'enregistrer les valeurs bande par bande :

Dim en(5)

en(0) = "IF"
en(1) = "VLOOKUP"
en(2) = "SUM"
en(3) = "COUNT"
en(4) = "ISNUMBER"
en(5) = "MID"

Heureusement, vous pouvez simplifier ce code en utilisant un tableau (Array) :

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Voici une démonstration d'utilisation de la fonction Replace (cela vous aidera à comprendre l'exemple suivant) :

Sub replace_example()
     Dim var_translate As String

     'Ruban de texte pour cet exemple
     var_translate = "Hello World !"
     
     'Remplacez « Monde » par « vous » dans le ruban de texte
     var_translate = Replace(var_translate, "World", "you")

     'Bande après remplacement
     MsgBox var_translate '=> renvoie "Bonjour à toi !"
End Sub

Maintenant, si l'on veut remplacer une série de valeurs par un autre ensemble de données, utiliser des tableaux et la fonction Array sera extrêmement utile :

Sub translate() 'Un exemple simplifié de traduction de formules de l’anglais vers le français
     Dim var_translate As String

     'Ruban de texte pour cet exemple
     var_translate = "Formula to translate : SUM(IF(ISNUMBER(A1:E1),A1:E1,0))"
     
     'Deux ensembles de valeurs
     en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
     fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
     
     'Remplacez "SI" par "IF", "RECHERVEV" par "VLOOKUP", etc.
     For i = 0 To UBound(en)
         var_translate = Replace(var_translate, en(i), fr(i))
     Next

     'Bande après remplacement
     MsgBox var_translate '=> renvoie "Formula à traduire : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))"
End Sub

Convertir le texte en tableau (VBA Split)

La fonction Split nous permet de diviser la chaîne de caractères en parties et d'écrire les valeurs résultantes dans un tableau.

Pour convertir un ruban en tableau, procédez comme suit :

variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"

Utilisez la fonction VBA Split et précisez le séparateur :

en = Split(variable, "/")

Le tableau "en" renverra les valeurs suivantes :

MsgBox en(0) '=> renvoie : IF
MsgBox en(1) '=> renvoie : RECHERCHEV
MsgBox en(2) '=> renvoie : SOMME
MsgBox en(3) '=> renvoie : COMPTE
MsgBox en(4) '=> renvoie : ISNUMBER
MsgBox en(5) '=> renvoie : MID

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

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
en = Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")
en = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

L'exemple suivant renvoie la troisième valeur de la chaîne :

MsgBox Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")(2) '=> renvoie : SOMME

L'inverse de Split est la fonction VBA Join.

Cette fonction collecte les valeurs d'un tableau dans une chaîne.

MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> renvoie : 12345