Les tableaux sont des "variables" qui permettent de stocker une
multitude de valeurs. Nous avons effleuré le sujet à la leçon sur les
variables, nous allons maintenant l'approfondir ...
Intérêt des tableaux
Imaginez que dans une procédure vous ayez besoin de stocker 500
valeurs. S'il fallait créer 500 variables pour stocker toutes ces
valeurs, cela deviendrait très compliqué, tandis qu'avec un tableau, le
stockage et l'utilisation de ces valeurs seront grandement simplifiés.
Le second intérêt des tableaux est leur "rapidité". Parcourir un
tableau de données demande beaucoup moins de temps que parcourir un
"tableau" (composé de cellules) sur une feuille Excel ...
Rien de tel qu'un exemple pour mieux comprendre ...
Sur la première feuille ("BD") : une base de données de 5000 lignes sur 3 colonnes :
Sur la seconde feuille : un "tableau" récapitulatif où seront comptabilisés les "OUI" en fonction des années et des clients :
Dans le cas présent, la procédure va parcourir la base de données en
boucle et comptabilisera pour chaque année et chaque n° de client le
nombre de "OUI" avant de l'entrer dans la cellule correspondante.
Sans utiliser de tableau, il faudra
131.44 secondes à Excel pour exécuter la procédure :
En enregistrant d'abord la base de données (de la feuille "BD") dans
un tableau et en effectuant ensuite les mêmes calculs (en utilisant le
tableau à la place de la base de données de la feuille "BD"), il ne
faudra que
1.74 secondes pour exécuter la procédure :
Et si l'on décide d'optimiser la procédure en n'enregistrant que les
données avec les "OUI" dans le tableau (ce qui représente environ le 3/4
des données),
1.02 secondes suffisent :
Dans cet exemple, l'utilisation d'un tableau a permis d'exécuter la procédure environ
128x plus rapidement et cette différence peut encore augmenter sensiblement lorsque l'on travaille avec plusieurs bases de données en même temps (
voir un second exemple).
Vous l'avez compris, l'utilisation de tableaux peut vraiment faire la différence.
Cet exemple sera détaillé en fin de leçon ...
Déclaration d'un tableau
Voici quelques exemples de déclarations (si les 2 premiers ne sont pas clairs pour vous,
relisez ceci) :
'Exemple de déclaration d'un tableau à 1 dimension
Dim tab1(4)
'Exemple de déclaration d'un tableau à 2 dimensions
Dim tab2(6, 1)
'Exemple de déclaration d'un tableau dynamique
Dim tab()
Si vous ne pouvez pas entrer de valeurs fixes (parce que cela dépend
de la taille de la base de données par exemple), laissez les parenthèses
vides.
Vous n'avez pas besoin de déclarer un type (string, long, etc.), dans bien des cas cela ralentirait votre procédure ...
Enregistrer des données dans un tableau
Commençons par enregistrer ces quelques données dans un tableau :
Nous voulons enregistrer ici 11 x 1 valeurs, il faudra donc créer un tableau à une dimension :
'Déclaration
Dim tab_exemple(10)
Rappelez-vous que
la numérotation d'un tableau commence à 0
(c'est une norme en programmation, autant prendre de bonnes habitudes
dès le début même s'il est possible de modifier cela en VBA).
Chaque élément du tableau reçoit ensuite sa valeur :
'Enregistrement des valeurs dans le tableau
tab_exemple(0) = Range("A2")
tab_exemple(1) = Range("A3")
tab_exemple(2) = Range("A4")
tab_exemple(3) = Range("A5")
tab_exemple(4) = Range("A6")
tab_exemple(5) = Range("A7")
tab_exemple(6) = Range("A8")
tab_exemple(7) = Range("A9")
tab_exemple(8) = Range("A10")
tab_exemple(9) = Range("A11")
tab_exemple(10) = Range("A12")
Vous pouvez utiliser, modifier chaque élément du tableau comme une variable.
En voici un exemple avec
tab_exemple(8) :
Sub enreg_tab()
'Déclaration
Dim tab_exemple(10)
'Enregistrement des valeurs dans le tableau
tab_exemple(0) = Range("A2")
tab_exemple(1) = Range("A3")
tab_exemple(2) = Range("A4")
tab_exemple(3) = Range("A5")
tab_exemple(4) = Range("A6")
tab_exemple(5) = Range("A7")
tab_exemple(6) = Range("A8")
tab_exemple(7) = Range("A9")
tab_exemple(8) = Range("A10")
tab_exemple(9) = Range("A11")
tab_exemple(10) = Range("A12")
'Test 1
MsgBox tab_exemple(8) '=> renvoie : 04.02.2016
'Modification de l'une des valeurs
tab_exemple(8) = Year(tab_exemple(8))
'Test 2
MsgBox tab_exemple(8) '=> renvoie : 2016
End Sub
Pour enregistrer ce même tableau plus rapidement, une boucle
For est toute indiquée :
'Déclaration
Dim tab_exemple(10)
'Enregistrement des valeurs dans le tableau
For i = 0 To 10
tab_exemple(i) = Range("A" & i + 2)
Next
Le tableau à 2 dimensions
Pour enregistrer plusieurs colonnes de données, une dimension supplémentaire est nécessaire. En voici un exemple :
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 ...
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 3
e valeur de la chaîne de caractères :
MsgBox Split("SI,RECHERCHEV,SOMME,NB,ESTNUM,STXT", ",")(2) '=> renvoie : SOMME
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
Exemple d'utilisation de cette même fonction sur une feuille de calcul :
Avec ScreenUpdating, le temps d'exécution a été réduit à seulement 0.44 secondes :
Si votre macro est un peu lente, pensez-y !
Mais il peut arriver que les macros soient désactivées et qu'Excel ne vous propose pas de les activer ...
Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
Et enfin sur "Paramètres des macros" :
Le choix par défaut (et recommandé) est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
Et enfin sur "Paramètres des macros" :
Le choix par défaut (et recommandé) est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
Et enfin sur "Paramètres des macros" :
Le choix recommandé est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
Le choix recommandé est "Niveau de sécurité moyen" (c'est celui qui vous invite à activer les macros à l'ouverture).
Masquez ensuite toutes les feuilles sauf celle de démarrage (propriété : Visible) :
Et entrez le code suivant dans ThisWorkbook :
Dans cet exemple, la macro passera les 2-3 premiers caractères en italique et le nom de la ville en gras :
La macro en détails :
Les fonctions sont toutes en anglais ...
Dans l'exemple suivant, le nombre de cellules vides de la plage
"A1:D8" est enregistré dans la variable puis affiché dans la boîte de
dialogue :
L'objectif ici est de créer une fonction capable de faire cela 
Il ne reste plus qu'à tester la couleur avec If :
Objectif de l'exercice : la procédure devra parcourir la base
de données en boucle et comptabiliser pour chaque année et chaque n° de
client le nombre de "OUI" ou "NON" (selon le choix de l'utilisateur) et
entrer ce décompte dans la cellule correspondante.
Complétez la macro suivante pour enregistrer la base de données de la feuille "BD" dans un tableau :