On travaillera sur Tableau de 4 colonnes et N lignes dans les différents exemples
INTRODUCTION : Qu’est-ce qu’un Tableau en VBA ?
Un tableau permet de travailler sur un groupe de valeurs qu’on peut traiter de manière individuelle.=>Avec Excel c’est facile de schématiserr puisque ce n’est qu’une feuille de calcul avec un nombre de lignes et de colonnes.
Ex : On veut stocker dans des variables 20 contributeurs du Forum, on pourrait déclarer :
On se rend compte que cela devient vite pénible à faire, pour pallier à ce problème on utilise des tableaux en programmation.
1)Les Tableaux Unidimensionnels : 1 dimension.
Reprenons les contributeurs XLD :
Déclaration du Tableau : Dim TabContributeurs (19) as string
Le tableau TabContributeurs est donc constitué de 20 éléments qui accueilleront des données de type String (Ex : les noms des contributeurs). Avec les tableaux l’indexation des éléments commence à 0. Il y a possibilité de démarrer l’indexation à 1 avec l’option Base 1=> à mettre en haut d’un module. Sinon de manière explicite:
Dim TabContributeurs (0 to 19) as string ou Dim TabContributeurs (1 to 20) as string
Assignons les pseudos en admettant que les noms soient stockés en A1:A20.
Il serait intéressant de stocker aussi les prénoms, le courriel et la ville de nos contributeurs, on se retrouve donc avec un tableau de 20 lignes sur 4 colonnes. On parle alors de Tableaux Multidimensionnels.
2) Les Tableaux Multidimensionnels
Pour la déclaration c’est le même principe :
En base 0=> Dim TabContributeurs(0 To 19,0 to 3) as string
En base 1=> Dim TabContributeurs(1 To 20,1 to 4) as string
Les données sont sur la plage A120
On peut écrire aussipropre au VBA Excel)
Dim TabContributeurs(1 To 20, 1 To 4)as string
TabContributeurs= Range("A120")
Pour mettre le résultat
Range("E1:H20")= TabContributeurs
ATTENTION: : Sous Excel 97 et MAC, quand on affecte une plage de cellules à un tableau, il ne faut pas déclarer de variable Tableau mais tout simplement en Variant=> .Dim TabContributeurs et non Dim TabContributeurs()
Enfin, même si on affecte qu’une colonne=>TabContributeurs=Range("A1:A20"),on aura un tableau bidimensionnel de 20 lignes sur une colonne.(Ubound(TabContributeurs,1)=20 et Ubound(TabContributeurs,1)=1
Ce qu'on vient de survoler rapidement, on les appelle des tableaux statiques c'est-à-dire qu’on connaît le nombre de lignes et de colonnes en les définissant à la déclaration, Passons aux
3) Les Tableaux dynamiques
A) Agrandissement d'un tableau:
Si on ne connaît pas le nombre de lignes ou de colonnes d’un tableau, on déclare alors :
1er exemple:
Particularités liées à Excel, pour avoir la dernière ligne avec la propriété End.
Ecrit de cette façon, les tableaux en VBA sont toujours en Base 1, donc TabContributeurs est en base 1.
VBA permet de connaître les limites inférieures et supérieures d’un tableau avec LBound et UBound. (toujours le même exemple).
Pour les lignes=> LBound(TabContributeurs,1)=1 et UBound(TabContributeurs,1)=20
Pour les colonnes=> LBound(TabContributeurs,2)=1 et UBound (TabContributeurs,2)=4
2 eme exemple :
Dim TabContributeurs (1 to 20)
Pour rajouter des données dans un tableau il faut l’agrandir, pour cela on utilise
Redim TabContributeurs(1 to 21), ici on rajoute 1 élément.
L’instruction ReDim permet de redimensionner mais efface en même temps tous les éléments du tableau.
On utilise alors
ReDim Preserve TabContributeurs (1 to 22)
On peut agrandir un tableau en même temps qu’on le construit le tableau :
Ex en Base 0:
Même si on a vu plus haut, qu'affecter un plage de cellules à un tableau retournait un tableau en Base 1. On peut quand même mettre un tableau en base 0 dans une plage de cellules, on écrira:
Range("A1:U1") = TabContributeurs
Pour mettre sur une colonne un tableau unidimensionel, il suffit juste de transposer le tableau:
Range("A1:A21") = Application.Transpose(TabContributeurs)
=> Jusqu'à Excel 2002, Application.Transpose est limité à 5700 et quelques éléments. (On y reviendra un peu plus bas)
ATTENTION: Pour un tableau multidimensionnel seule la dernière dimension peut être changée. Dim TabContributeurs(1 To 20, 1 To 4)
Ecrire ReDim Preserve TabContributeurs(1 to 21, 1 To 5) provoquera une erreur
Par contre ReDim Preserve TabContributeurs(1 to 20, 1 To 5) est correct.
On a vu plus haut qu’on pouvait écrire :
TabContributeurs= Range("A120")
Pour mettre le résultat
Range("E1:H20")= TabContributeurs
Mais si on connaît pas le nombre de lignes et colonnes on va utiliser Resize(Nb de lignes,Nbde colonnes). Rappel : Le nombre de Lignes d’un tableau est donné par !UBound(TabContributeurs,1) et le nombre de colonnes par !UBound(TabContributeurs,2). Donc :
En base 1=>Range("A1").Resize(UBound(TabContributeurs),UBound(TabContributeurs, 2)) = TabContributeurs
En base 0=>Range("A1").Resize(UBound(TabContributeurs)+1,UBound(TabContributeurs, 2)+1) = TabContributeurs
Au passage écrire
UBound(TabContributeurs) est identique à !UBound(TabContributeurs,1).
B) Construction d'un tableau :
On a vu que seule la dernière dimension pouvait être redimensionnée,
Admettons qu’on est toujours notre tableau de contributeurs ; On veut tous les Thierry avec leur Prénom, nom et adresse.
Il nous faut donc un tableau de 3 colonnes(Nom,Prénom, Adresse) mais comme on ne connaît pas le nombre de Thierry, on va construire un tableau à l’envers
1 er exemple, en Base 0 :
2 ème exemple, en Base 1 en passant par une fonction qui retourne un tableau
=> 2 fonctions qui remplacent Application.transpose :
:La 1 ère pour les tableaux bidimensionnels :
Une 2ème pour les tableaux uni et bidimensionnels :
Exemple d'utilisation:
4) Tri d'un tableau
En programmation, les algorithmes de tri sont nombreux, j'en avais étudié 7 quand je faisais du C, mais tout cela je l'ai oublié. On va voir 2 types de tri: le plus lent (Bubblesort) et un des plus rapides (le quicksort).
=>Le bubblesort ou le tri à bulle en français, C'est un des plus connus car c'est le plus facile à programmer. Son principe est de comparer chaque élément d'un tableau avec son suivant pour échanger leur place le cas échéant.
Voici une façon de l'écrire, la variable senstri permet de trier croissant ou décroissant
Cet algo est conseillé pour trier des tableaux de moins 1000 éléments, donc pour trier une listbox dans un userform il est trés pratique. @+Thierry l'utilise d'ailleurs dans ses démos.
=>Quicksort ou le tri rapide encore appelé tri dichotomique C'est le plus rapide dans la plupart des cas, du moins sur Excel car on a rarement des tableaux de plus de 65000 éléments. Son principe consiste à trier une partie d'un tableau délimité par 2 indices, on choisit une valeur de ce tableau qui servira de pivot qu'on place de manière définitive de manière à ce que tous les éléments précdents ce pivot lui soient inférieurs ou égaux et que tous les suivants lui soient supérieurs ou égaux. On prend comme pivot la valeur médiane. Dnas les 2 exemples ci dessous , le tri est fait de manière récursive. Pour un tableau à 1 dimension, une procédure de Ti.
Pour un tableau à 2 dimensions, pratique pour trier une plage de cellules par exemple:
T=range("A1:C50000").value
'On veut trier le tableau sur la colonne B:
Trimulti T,2,lbound(T),Ubound(T)
INTRODUCTION : Qu’est-ce qu’un Tableau en VBA ?
Un tableau permet de travailler sur un groupe de valeurs qu’on peut traiter de manière individuelle.=>Avec Excel c’est facile de schématiserr puisque ce n’est qu’une feuille de calcul avec un nombre de lignes et de colonnes.
Ex : On veut stocker dans des variables 20 contributeurs du Forum, on pourrait déclarer :
Code:
Dim Nom1 as string, Nom2 as string, …, Dim Nom20 as string
Nom1=range(“A1”).value
Nom2=range(“A2”).value
…
Nom20=range(“A20”).value
On se rend compte que cela devient vite pénible à faire, pour pallier à ce problème on utilise des tableaux en programmation.
1)Les Tableaux Unidimensionnels : 1 dimension.
Reprenons les contributeurs XLD :
Déclaration du Tableau : Dim TabContributeurs (19) as string
Le tableau TabContributeurs est donc constitué de 20 éléments qui accueilleront des données de type String (Ex : les noms des contributeurs). Avec les tableaux l’indexation des éléments commence à 0. Il y a possibilité de démarrer l’indexation à 1 avec l’option Base 1=> à mettre en haut d’un module. Sinon de manière explicite:
Dim TabContributeurs (0 to 19) as string ou Dim TabContributeurs (1 to 20) as string
Assignons les pseudos en admettant que les noms soient stockés en A1:A20.
Code:
Sub RemplirTab()
Dim I As Byte, TabContributeurs(1 To 20) For I = 1 To 20
TabContributeurs(I) = Range("A" & I)
Next I
End Sub
Il serait intéressant de stocker aussi les prénoms, le courriel et la ville de nos contributeurs, on se retrouve donc avec un tableau de 20 lignes sur 4 colonnes. On parle alors de Tableaux Multidimensionnels.
2) Les Tableaux Multidimensionnels
Pour la déclaration c’est le même principe :
En base 0=> Dim TabContributeurs(0 To 19,0 to 3) as string
En base 1=> Dim TabContributeurs(1 To 20,1 to 4) as string
Les données sont sur la plage A120
Code:
Sub RemplirTab2()
Dim I As Byte, J As Byte, TabContributeurs(1 To 20, 1 To 4)
For I = 1 To 20
For J = 1 To 4
TabContributeurs(I, J) = Cells(I, J)
Next J
Next I
End Sub
On peut écrire aussipropre au VBA Excel)
Dim TabContributeurs(1 To 20, 1 To 4)as string
TabContributeurs= Range("A120")
Pour mettre le résultat
Range("E1:H20")= TabContributeurs
ATTENTION: : Sous Excel 97 et MAC, quand on affecte une plage de cellules à un tableau, il ne faut pas déclarer de variable Tableau mais tout simplement en Variant=> .Dim TabContributeurs et non Dim TabContributeurs()
Enfin, même si on affecte qu’une colonne=>TabContributeurs=Range("A1:A20"),on aura un tableau bidimensionnel de 20 lignes sur une colonne.(Ubound(TabContributeurs,1)=20 et Ubound(TabContributeurs,1)=1
Ce qu'on vient de survoler rapidement, on les appelle des tableaux statiques c'est-à-dire qu’on connaît le nombre de lignes et de colonnes en les définissant à la déclaration, Passons aux
3) Les Tableaux dynamiques
A) Agrandissement d'un tableau:
Si on ne connaît pas le nombre de lignes ou de colonnes d’un tableau, on déclare alors :
1er exemple:
Code:
Dim TabContributeurs()
Particularités liées à Excel, pour avoir la dernière ligne avec la propriété End.
Code:
TabContributeurs=Range ("A1:D" & Range("D65536").End(xlUp).Row)
Ecrit de cette façon, les tableaux en VBA sont toujours en Base 1, donc TabContributeurs est en base 1.
VBA permet de connaître les limites inférieures et supérieures d’un tableau avec LBound et UBound. (toujours le même exemple).
Pour les lignes=> LBound(TabContributeurs,1)=1 et UBound(TabContributeurs,1)=20
Pour les colonnes=> LBound(TabContributeurs,2)=1 et UBound (TabContributeurs,2)=4
2 eme exemple :
Dim TabContributeurs (1 to 20)
Pour rajouter des données dans un tableau il faut l’agrandir, pour cela on utilise
Redim TabContributeurs(1 to 21), ici on rajoute 1 élément.
L’instruction ReDim permet de redimensionner mais efface en même temps tous les éléments du tableau.
On utilise alors
ReDim Preserve TabContributeurs (1 to 22)
On peut agrandir un tableau en même temps qu’on le construit le tableau :
Ex en Base 0:
Code:
Dim TabContributeurs()
For I = 0 To 20
ReDim Preserve TabContributeurs(I)
TabContributeurs(I) = I
Next I
Même si on a vu plus haut, qu'affecter un plage de cellules à un tableau retournait un tableau en Base 1. On peut quand même mettre un tableau en base 0 dans une plage de cellules, on écrira:
Range("A1:U1") = TabContributeurs
Pour mettre sur une colonne un tableau unidimensionel, il suffit juste de transposer le tableau:
Range("A1:A21") = Application.Transpose(TabContributeurs)
=> Jusqu'à Excel 2002, Application.Transpose est limité à 5700 et quelques éléments. (On y reviendra un peu plus bas)
ATTENTION: Pour un tableau multidimensionnel seule la dernière dimension peut être changée. Dim TabContributeurs(1 To 20, 1 To 4)
Ecrire ReDim Preserve TabContributeurs(1 to 21, 1 To 5) provoquera une erreur
Par contre ReDim Preserve TabContributeurs(1 to 20, 1 To 5) est correct.
On a vu plus haut qu’on pouvait écrire :
TabContributeurs= Range("A120")
Pour mettre le résultat
Range("E1:H20")= TabContributeurs
Mais si on connaît pas le nombre de lignes et colonnes on va utiliser Resize(Nb de lignes,Nbde colonnes). Rappel : Le nombre de Lignes d’un tableau est donné par !UBound(TabContributeurs,1) et le nombre de colonnes par !UBound(TabContributeurs,2). Donc :
En base 1=>Range("A1").Resize(UBound(TabContributeurs),UBound(TabContributeurs, 2)) = TabContributeurs
En base 0=>Range("A1").Resize(UBound(TabContributeurs)+1,UBound(TabContributeurs, 2)+1) = TabContributeurs
Au passage écrire
UBound(TabContributeurs) est identique à !UBound(TabContributeurs,1).
B) Construction d'un tableau :
On a vu que seule la dernière dimension pouvait être redimensionnée,
Admettons qu’on est toujours notre tableau de contributeurs ; On veut tous les Thierry avec leur Prénom, nom et adresse.
Il nous faut donc un tableau de 3 colonnes(Nom,Prénom, Adresse) mais comme on ne connaît pas le nombre de Thierry, on va construire un tableau à l’envers
1 er exemple, en Base 0 :
Code:
Sub RechercheLesThierryEnBaseZéro()
Dim Tabcontributeurs
Dim Temp() 'c'est dans ce tableau que l'on va mettre les items trouvé
Dim J& 'Compteur
Tabcontributeurs = Range("A1:D5000") 'Le tableau de départ
For I = 1 To UBound(Tabcontributeurs, 1)
If Tabcontributeurs(I, 2) = "Thierry" Then 'les prénoms sont en 2 ème colonne
ReDim Preserve Temp(2, J)
Temp(0, J) = Tabcontributeurs(I, 1) 'Nom
Temp(1, J) = Tabcontributeurs(I, 1) '¨Prenom
Temp(2, J) = Tabcontributeurs(I, 1) 'Adresse
J = J + 1
End If
Next I
' On met le résultat dans la feuille 2, il faut transposer le tableau puisqu’il est inversé Sheets(2).Range("A1").Resize(UBound(Temp, 2), UBound(Temp, 1)) = Application.Transpose(Temp)
End Sub
2 ème exemple, en Base 1 en passant par une fonction qui retourne un tableau
Code:
Sub RechercheLesThierryEnBase1ParAppeldeFonction()
Dim Tabcontributeurs
Dim Temp 'En variant cette fois on ne sait pas ce que va nous retourner la fonction
Dim J& 'Compteur
Tabcontributeurs = Range("A1:D5000") 'Le tableau de départ
Temp = RechPrenom(Tabcontributeurs, 2, "Thierry") ‘Appel de la fonction dont on affecte le résultat à Temp
'On teste si la fonction RechPrenom nous retourne un tableau
If IsArray(Temp) Then 'On met le résultat
Sheets(2).Range("A1").Resize(UBound(Temp, 2) + 1, UBound(Temp, 1) + 1) = Application.Transpose(Temp)
Else 'On affiche qu'on a pas trouvé de Thierry
MsgBox Temp
End If
End Sub
Code:
Function RechPrenom(T, Colonne As Byte, Chaine As String)
Dim I&, J&, K&, Temp()
J = 1 'On travaille en Base 1
For I = LBound(T) To UBound(T)
If T(I, Colonne) = Chaine Then
ReDim Preserve Temp(1 To 3, 1 To K)
'On passe par une boucle cette fois
For K = 1 To 3
Temp(K, J) = T(I, Colonne)
Next K
J = J + 1
End If
Next I
If J > 2 Then 'On a récupéré au moins un Thierry
'On affecte le tableau Temp à la fonction
RechPrenom = Temp
Else 'On affecte une chaine de caractere
RechPrenom = "Pas de Thierry trouvé"
End If
'On peut écrire ce test comme ceci:
'RechPrenom = IIf(J > 2, Temp, "Pas de Thierry trouvé")
End Function
=> 2 fonctions qui remplacent Application.transpose :
:La 1 ère pour les tableaux bidimensionnels :
Code:
Function InverseTab(T, Optional Base As Byte = 0)'Zon
‘Base par défaut est à 0 mais si on est en base 1 lui donnner la valeur 1
Dim Temp(), I&, J&
ReDim Temp(Base To UBound(T, 2), Base To UBound(T))
For I = LBound(T, 2) To UBound(T, 2)
For J = LBound(T) To UBound(T)
Temp(I, J) = T(J, I)
Next J
Next I
InverseTab = Temp
End Function
Une 2ème pour les tableaux uni et bidimensionnels :
Code:
Function TransposeGrandTab(T) 'Zon
'Application.transpose est limité à 5000 et qques éléments jusqu'à XL2002
Dim Temp, I&, J&, Z As Byte, Nb As Byte
On Error Resume Next
Do
Nb = Nb + 1
Z = UBound(T, Nb + 1) ‘Pour un tableau unidimensionnel ubound(t,2) renvoie une erreur
Loop Until Err
If Nb = 1 Then
ReDim Temp(UBound(T), 1 To 1)
For I = LBound(T) To UBound(T)
Temp(I, 1) = T(I)
Next I
Else
ReDim Temp(1 To UBound(T, 2), 1 To UBound(T, 1))
For I = 1 To UBound(T, 2)
For J = 1 To UBound(T, 1)
Temp(I, J) = T(J, I)
Next J
Next I
End If
TransposeGrandTab = Temp
End Function
Exemple d'utilisation:
Code:
Sub RechercheLesThierryEnBase1ParAppeldeFonction()
Dim Tabcontributeurs
Dim Temp 'En variant cette fois on ne sait pas ce que va nous retourner la fonction
Dim J& 'Compteur
Tabcontributeurs = Range("A1:D5000") 'Le tableau de départ
Temp = RechPrenom(Tabcontributeurs, 2, "Thierry") ‘Appel de la fonction dont on affecte le résultat à Temp
'On teste si la fonction RechPrenom nous retourne un tableau
If IsArray(Temp) Then 'On met le résultat
Temp=TransposeGrandTab(Temp) 'ou Temp=InverseTab(Temp,1)
Sheets(2).Range("A1").Resize(UBound(Temp, 2) + 1, UBound(Temp, 1) + 1) = Temp
Else 'On affiche qu'on a pas trouvé de Thierry
MsgBox Temp
End If
End Sub
4) Tri d'un tableau
En programmation, les algorithmes de tri sont nombreux, j'en avais étudié 7 quand je faisais du C, mais tout cela je l'ai oublié. On va voir 2 types de tri: le plus lent (Bubblesort) et un des plus rapides (le quicksort).
=>Le bubblesort ou le tri à bulle en français, C'est un des plus connus car c'est le plus facile à programmer. Son principe est de comparer chaque élément d'un tableau avec son suivant pour échanger leur place le cas échéant.
Voici une façon de l'écrire, la variable senstri permet de trier croissant ou décroissant
Code:
Sub TriaBulle(T, Optional SensTri As Boolean = True) 'Zon
Dim Test As Boolean, I&, Temp
Do
Test = False
For I = LBound(T) To UBound(T) - 1
If (T(I) > T(I + 1) And SensTri) Or (T(I) < T(I + 1) And Not SensTri) Then
Temp = T(I)
T(I) = T(I + 1)
T(I + 1) = Temp
Test = True
End If
Next I
Loop Until Not Test
End Sub
Cet algo est conseillé pour trier des tableaux de moins 1000 éléments, donc pour trier une listbox dans un userform il est trés pratique. @+Thierry l'utilise d'ailleurs dans ses démos.
=>Quicksort ou le tri rapide encore appelé tri dichotomique C'est le plus rapide dans la plupart des cas, du moins sur Excel car on a rarement des tableaux de plus de 65000 éléments. Son principe consiste à trier une partie d'un tableau délimité par 2 indices, on choisit une valeur de ce tableau qui servira de pivot qu'on place de manière définitive de manière à ce que tous les éléments précdents ce pivot lui soient inférieurs ou égaux et que tous les suivants lui soient supérieurs ou égaux. On prend comme pivot la valeur médiane. Dnas les 2 exemples ci dessous , le tri est fait de manière récursive. Pour un tableau à 1 dimension, une procédure de Ti.
Code:
Sub TrieTableau(Deb As Long, Fin As Long)'Ti
Dim IndiceInf As Long, IndiceSup As Long
Dim Temp1, Pivot
IndiceInf = Deb
IndiceSup = Fin
Pivot = UCase(T((Deb + Fin) \ 2))
Do
While UCase(T(IndiceInf)) < Pivot
IndiceInf = IndiceInf + 1
Wend
While Pivot < UCase(T(IndiceSup))
IndiceSup = IndiceSup - 1
Wend
If IndiceInf <= IndiceSup Then
Temp1 = T(IndiceInf)
T(IndiceInf) = T(IndiceSup)
T(IndiceSup) = Temp1
IndiceInf = IndiceInf + 1
IndiceSup = IndiceSup - 1
End If
Loop Until IndiceInf > IndiceSup
If Deb < IndiceSup Then TrieTableau Deb, IndiceSup
If IndiceInf < Fin Then TrieTableau IndiceInf, Fin
End Sub
Pour un tableau à 2 dimensions, pratique pour trier une plage de cellules par exemple:
T=range("A1:C50000").value
'On veut trier le tableau sur la colonne B:
Trimulti T,2,lbound(T),Ubound(T)
Code:
Sub TriMulti(Tablo, Col As Byte, Min&, Max&) 'ZOn
Dim I&, J&, K&, M, Chaine
I = Min
J = Max
M = Tablo((Min + Max) / 2, Col)
While (I <= J)
While (Tablo(I, Col) < M And I < Max)
I = I + 1
Wend
While (M < Tablo(J, Col) And J > Min)
J = J - 1
Wend
If (I <= J) Then
For K = LBound(Tablo, 2) To UBound(Tablo, 2)
Chaine = Tablo(I, K)
Tablo(I, K) = Tablo(J, K)
Tablo(J, K) = Chaine
Next K
I = I + 1
J = J - 1
End If
Wend
If (Min < J) Then TriMulti Tablo, Col, Min, J
If (I < Max) Then TriMulti Tablo, Col, I, Max
End Sub
Dernière édition: