On peut manipuler directement des tableaux de chiffres dans VBA sans les inscrire physiquement dans une page Excel, avec comme avantage la possibilité de faire des tableaux à 3, 4, 5... voire 60 dimensions !
En VBA un tableau s'appelle Array. Ses éléments sont en général numérotés à partir de zéro
- On peut le créer soit directement en passant une liste de valeurs en paramètre de Array :
tabl = Array(10, 20, 30)
MsgBox tabl(2)
tabl(2) renvoie la valeur du troisième élément (numérotation à partir de zéro)
- soit en créant d'abord un tableau vide et en affectant une valeur à chacun de ses éléments (la déclaration du tableau indique la position du dernier élément, en général le nombre d'éléments moins un) :
Sub crée_tabl()
Dim jour(5)
jour(0) = "dimanche"
jour(1) = "lundi"
jour(2) = "mardi"
jour(3) = "mercredi"
jour(4) = "vendredi"
jour(5) = "samedi"
MsgBox jour(2)
End Sub
Si on déclare un tableau par Dim tabl(9), ses éléments seront généralement numérotés de 0 à 9, 0 étant la borne inférieure par défaut.
Si on veut modifier, dans un module, la valeur par défaut de la borne inférieure, pour démarrer la numérotation à 1, il suffit d'inscrire en première ligne du module : Option Base 1.
Mais rien n'empêche de numéroter de 10 à 100 :
Dim tabl(10 To 100)
On peut (utilement) préciser le type de données que doit contenir le tableau :
Dim tabl(10 To 100) As String (pour du texte).
Pour faire un tableau à plusieurs dimensions, il suffit de séparer les paramètres par des virgules :
Dim tabl(10 , 20 , 5), ou bien
Dim tabl(10 To 100 , 5 to 20).
Enfin, on peut modifier les dimensions d'un tableau (qui n'a pas été dimensionné par Dim) en utilisant Redim (qui s'emploie exactement de la même façon que Dim) :
Sub crée_tabl()
Dim tabl As Variant
tabl = Array(10, 20, 30)
ReDim tabl(1 To 6)
MsgBox tabl(3)
Erase tabl '(libère la mémoire)
End Sub
.
Les dimensions du tableau défini par Array, ne sont pas fixées (le tableau est dynamique). On aurait aussi pu définir un tableau dynamique simplement en inscrivant Dim tabl() sans préciser le nombre d'éléments du tableau (on utilise la fonction UBound pour connaitre le plus grand indice disponible dans le tableau)
ReDim tabl(1 To 6) en fait un tableau de six éléments numérotés de 1 à 6, mais en même temps efface toutes les données.
Pour conserver les données, il suffit de remplacer ReDim tabl(1 To 6) par ReDim Preserve tabl(1 To 6). Le tableau contient toujours 6 éléments numérotés de 1 à 6, mais les n° 1 à 3 contiendront 10, 20 et 30 (éléments qui étaient précédemment numérotés de 0 à 2.
Attention, les données ne seront conservées qui si le nouveau dimensionnement leur laisse suffisamment de place ! Si on écrit ReDim Preserve tabl(1 To 2), la troisième valeur sera perdue.
Un exemple simple d'utilisation de tableau :
Sub crée_tabl()
Dim tabl(1 To 150)
For num = 1 To 150
tabl(num) = 2 ^ num
Next
MsgBox Format(tabl(8), "### ### ##0") & " caractères définis par un octet"
MsgBox Format(tabl(10), "### ### ##0") & " octets dans un kilooctet"
MsgBox Format(tabl(20), "### ### ##0") & " octets dans un mégaoctet"
End Sub
La boucle permet d'entrer dans le tableau les valeurs des puissances de 2.
La variable tableau ainsi constituée se comporte comme une fonction, tabl(5) renvoie 2 puissance 5.
Autre exemple, trier un tableau :
Sub trie_tabl()
'définition du tableau
Dim tabl As Variant
tabl = Array(150, 10, 20, 30, 12, 50, 1, 20, 6, 25, 2)
'pour chacun des éléments,
For num = 0 To 9
'on s'assure qu'aucun des éléments qui le suit ne lui est inférieur
For num2 = num + 1 To 10
'sinon, on échange les deux éléments
If tabl(num) > tabl(num2) Then
old = tabl(num)
old2 = tabl(num2)
tabl(num) = old2
tabl(num2) = old
End If
Next num2
Next num
'pour vérifier que le tri a bien été fait,
'on crée une chaine contenant les éléments du tableau
txt = ""
For num = 0 To 10
txt = txt & "," & tabl(num)
Next
MsgBox txt
End Sub
Ce genre de tri sur un tableau "immatériel" est utile par exemple quand on veut pouvoir sélectionner à l'aide d'une liste déroulante les éléments d'un grand tableau Excel. On peut présenter les mots dans l'ordre alphabétqiue et sans doublons (cliquez ici pour télécharger le fichier Excel zippé).
Ce tri de tableau peut être utilisé pour préparer un graphique représentant des données triées à partir d'un tableau de données Excel dans le désordre (sans modifier ni recopier ce tableau).
Le fichier exemple (cliquez ici pour télécharger le fichier Excel zippé) part de statistiques de fréquentation du forum mpfe présentées dans un tableau Excel classé par ordre alphabétique du nom des contributeurs.
Un graphique classique présente ces résultats sous forme d'histogramme, toujours par ordre alphabétique.
D'un simple clic de bouton, on peut par VBA classer les barres de l'histogramme par nombre de contributions.
Les deux séries de données sont chargées dans un tableau à deux dimensions, puis triées, avant d'être réinjectées commes données source du graphique.
(Attention, le nombre de caractères est limité pour les chaines définissant les données source de chaque série, ce qui limite le nombre de points du graphique).
(cliquez ici pour télécharger le fichier Excel zippé).
Une question qui se pose souvent : comment transférer les données d'un tableau dans les cellules d'une page Excel ?
Pas utile de faire une boucle, il suffit d'affecter aux cellules d'une plage la valeur du tableau : Range("A1:D1").Value = tableau
Par exemple :
Sub semaine()
Dim jour(6)
jour(0) = "dimanche"
jour(1) = "lundi"
jour(2) = "mardi"
jour(3) = "mercredi"
jour(4) = "jeudi"
jour(5) = "vendredi"
jour(6) = "samedi"
Range("A1:G1").Value = jour
End Sub
La macro place sur la première ligne les jours de la semaine.
Pour les placer en vertical, il suffit de transposer le tableau :
Sub semaine()
Dim jour(6)
jour(0) = "dimanche"
jour(1) = "lundi"
jour(2) = "mardi"
jour(3) = "mercredi"
jour(4) = "jeudi"
jour(5) = "vendredi"
jour(6) = "samedi"
Range("A1:A7").Value = Application.WorksheetFunction.Transpose(jour)
End Sub
Proche des tableaux, l'objet dictionnaire peut aussi être utile dans certains cas.
Il s'agit d'un tableau à deux colonnes, les éléments de la première colonne étant appelés des clés. Le dictionnaire associe une valeur (de type String, Long, Boolean...) à un nom (nom de clé). Il permet d'accéder directement à l'élément associé à une clé sans avoir à balayer les champs à la recherche de la clé.
Par exemple les clés du dictionnaire "notes" étant les prénoms des élèves, il suffit d'écrire :
MsgBox notes("Arthur")
pour connaître la note d'Arthur.
On utilise donc le dictionnaire un peu comme une fonction, les éléments de la deuxième colonne étant fonction de la clé.
Pour créer un dictionnaire, il faut d'abord créer un objet Dictionnary par
Set notes = CreateObject("Scripting.Dictionary")
puis remplir le dictionnaire de paires clé - éléments :
notes.Add "Arthur", 12
notes.Add "Sophie", 17
notes.Add "Emilie", "absente"
Une fois tous les éments stockés dans le dictionnaire, on peut les consulter par
MsgBox notes("Sophie")
(Ne pas oublier de libérer la mémoire en fin d'utilisation du dictionnaire par Set notes=Nothing.
La macro peut donc s'écrire :
Sub les_notes()
Set notes = CreateObject("Scripting.Dictionary")
notes.Add "Arthur", 12
notes.Add "Sophie", 17
notes.Add "Nicolas", 8
notes.Add "Emilie", "absente"
notes.Add "Marie", 18
MsgBox notes("Nicolas")
Set notes=Nothing
End Sub
Ou bien, sous forme de fonction :
Function les_notes(prenom)
Set notes = CreateObject("Scripting.Dictionary")
notes.Add "Arthur", 12
notes.Add "Sophie", 17
notes.Add "Nicolas", 8
notes.Add "Emilie", "absente"
notes.Add "Marie", 18
les_notes=notes(prenom)
Set notes=Nothing
End function
Si on écrit =les_notes("Marie") dans une page Excel, on obtient 18.
pour manipuler les dictionnaires, on dispose d'un certain nombre de méthodes et propriétés, et notamment :
- key qui permet de remplacer le contenu d'une clé par un autre :
notes.key("Marie")="Julie" permet de corriger une erreur de prénom, c'est Julie qui avait eu 18 !
- Item(clé) qui renvoie ou fixe la valeur de l'élément correspondant à clé :
notes.Item("Nicolas")=5 permet de corriger une erreur de note, Nicolas a eu 5 au lieu de 8 ! (on peut aussi écrire directement notes("Nicolas")=5 comme on l'a vu plus haut).
- Add et Remove qui permettent d'ajouter ou de retirer des paires "clé - valeur" :
notes.Remove "Sophie"
notes.Add "Lucie", 14
On peut vider completement le dictionnaire en utilisant RemoveAll :
notes.RemoveAll
- Dans la mesure où il ne peut pas y avoir plusieurs clés portant le même nom, il est utile de pouvoir déterminer si une clé existe déjà. Pour ce faire, on utilise Exists :
If notes.Exists("Emilie") then
Msgbox "La note d'Emilie est déjà enregistrée"
Else
notes.Add "Emilie", 10
End If
- Enfin, on peut balayer le dictionnaire comme un vulgaire tableau en utilisant Count et les collections Items et Keys :
liste_clés = notes.keys
liste_valeurs = notes.items
msg_clés = ""
msg_valeurs = ""
For num = 0 To notes.Count - 1
msg_clés = msg_clés & " " & liste_clés(num)
msg_valeurs = msg_valeurs & " " & liste_valeurs(num)
Next
MsgBox "le dictionnaire contient " & notes.Count & " entrées," _
& Chr(10) & "Les clés sont :" & msg_clés & Chr(10) & _
"et les valeurs sont :" & msg_valeurs
Cette technique est utile pour recopier les éléments d'un dictionnaire dans un tableau (Array) :
'crée un tableau de la même taille que le dictionnaire
ReDim tabl(0 To notes.Count - 1, 1 To 2)
num = 0
For Each clé In notes.keys 'balayer toutes les clés
tabl(num, 1) = clé 'mettre dans la première colonne la clé
tabl(num, 2) = notes(clé) 'et dans la deuxième, la valeur
num = num + 1
Next clé
'renvoie clé et valeur de la troisième ligne du tableau
MsgBox "clé : " & tabl(3, 1) & ", valeur : " & tabl(3, 2)
Un fichier exemple à télécharger (cliquez ici) :
Dans une page Excel ("valeurs"), un grand tableau contenant dans la première colonne, les nom de sociétés cotées en Bourse, dans la seconde (plage "sicov"), leur code sicovam (qui va nous servir de clé), et dans la troisième, leur cours.
La macro crée simultanément deux dictionnaires, noms et cours, les charge avec les données correspondantes, en prenant pour clé le code sicovam.
Il suffit ensuite d'appeler noms(12007) pour obtenir le nom de la société portant le sicovam 12007 et cours(12007) pour connaitre son cours :
Sub sicovm()
Set noms = CreateObject("Scripting.Dictionary")
Set cours = CreateObject("Scripting.Dictionary")
For Each cel In Sheets("valeurs").Range("sicov").Cells
noms.Add cel.Value, cel.Offset(0, -1).Value
cours.Add cel.Value, cel.Offset(0, 1).Value
Next cel
sicov = 1 * InputBox("tapez un Sicovam", "Bourse")
MsgBox "le sicovam " & sicov & " correspond à " _
& noms(sicov) & Chr(10) & _
"son dernier cours est " & cours(sicov)
Set noms = Nothing
Set cours = Nothing
End Sub
cliquez ici pour télécharger le fichier Excel zippé.
Enfin, on peut bien sûr utiliser l'objet dictionary pour construire un véritable dictionnaire, avec comme clés les mots, et comme valeur leur traduction ou leur définition. Toutes les données peuvent être stockées dans un fichier Excel, mais aussi dans un fichier texte (voir le § sur la manipulation des fichiers texte par VBA).