Cours VBA : utilisations des tableaux (exercice)
Exercice pratique
Pour mettre en pratique l'utilisation des tableaux, vous allez réaliser par étapes la macro qui a servi d'exemple pour démontrer la rapidité des tableaux.
Le fichier : tableaux_exercice.xlsm
Pour cet exercice la base de données a été réduite à 1000 lignes.

Objectif de l'exercice : la procédure devra parcourir la base de données en boucle et comptabiliser pour chaque année et chaque numéro de client le nombre de OUI ou de NON (selon le choix de l'utilisateur) et entrer ce décompte dans la cellule correspondante.
Complétez la macro suivante avant de passer à la solution :
Sub exercice()
'Dernière ligne de la base de données
'...
'Valeur recherchée (OUI ou NON)
'...
'Déclaration du tableau dynamique
Dim tableau()
'...
'Enregistrement des données dans le tableau
'...
'Décomptes des OUI ou des NON
'...
End Sub
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Voici une solution pour réaliser cet exercice (des informations complémentaires sont disponibles un peu plus bas) :
Sub exercice()
Dim derniereLigne As Integer, valeurRecherchee As String, numero As Integer, compteur As Integer, ligne As Integer, annee As Integer, client As Integer, i As Integer
'Dernière ligne de la base de données
derniereLigne = Sheets("BD").Cells(Rows.Count, 1).End(xlUp).Row
'Valeur recherchée (OUI ou NON)
If Sheets("RES").OptionButton_oui Then
valeurRecherchee = "OUI"
Else
valeurRecherchee = "NON"
End If
'Déclaration du tableau dynamique
Dim tableau()
ReDim tableau(derniereLigne - 2, 1)
'Numéro du premier enregistrement dans le tableau
numero = 0
'Enregistrement des données dans le tableau
For ligne = 2 To derniereLigne
If Sheets("BD").Range("C" & ligne) = valeurRecherchee Then
tableau(numero, 0) = Year(Sheets("BD").Range("A" & ligne)) 'Année de la date
tableau(numero, 1) = Sheets("BD").Range("B" & ligne) 'Numéro de client
numero = numero + 1
End If
Next
'Décomptes de OUI ou de NON
For annee = 2011 To 2026 'Boucle des années
For client = 1 To 30 'Boucle des clients
'Compteur des OUI ou des NON
compteur = 0
For i = 0 To numero - 1
If tableau(i, 0) = annee And tableau(i, 1) = client Then compteur = compteur + 1
Next
'Affichage dans la cellule
Cells(annee - 2009, client + 1) = compteur
Next
Next
End Sub
Dernière ligne
Calcul de la dernière ligne de données de la feuille BD (détaillé dans le précédent exercice) :
'Dernière ligne de la base de données
derniereLigne = Sheets("BD").Cells(Rows.Count, 1).End(xlUp).Row
Valeur recherchée
Si le contrôle OptionButton_oui (de la feuille RES) est sélectionné, il faudra rechercher les OUI, sinon il faudra rechercher les NON :
'Valeur recherchée (OUI ou NON)
If Sheets("RES").OptionButton_oui Then
valeurRecherchee = "OUI"
Else
valeurRecherchee = "NON"
End If
Déclaration du tableau
Dans le cas présent, il aurait été possible de calculer le nombre de OUI ou de NON de la feuille BD pour redimensionner le tableau dynamique au nombre exact de données qu'il s'apprête à recevoir.
Mais pour éviter ce calcul supplémentaire, le tableau est simplement redimensionné ici à la taille de la base de données :
'Déclaration du tableau dynamique
Dim tableau()
ReDim tableau(derniereLigne - 2, 1)
Numéro
La variable numero va permettre de déterminer la position des enregistrements dans le tableau (et commence à 0) :
'Numéro du premier enregistrement dans le tableau
numero = 0
Enregistrement des données dans le tableau
La boucle For parcourt chaque ligne de la base de données et enregistre les informations dans le tableau si la colonne C contient la valeur recherchée :
'Enregistrement des données dans le tableau
For ligne = 2 To derniereLigne
If Sheets("BD").Range("C" & ligne) = valeurRecherchee Then
tableau(numero, 0) = Year(Sheets("BD").Range("A" & ligne)) 'Année de la date
tableau(numero, 1) = Sheets("BD").Range("B" & ligne) 'Numéro de client
numero = numero + 1
End If
Next
Pour éviter de recalculer de nombreuses fois l'année de chaque date du tableau dans les prochaines boucles, seule l'année de la date est enregistrée dans le tableau.
Après chaque entrée dans le tableau, la variable numero est incrémentée de 1 pour la prochaine entrée.
Décomptes
La boucle For parcourt ici chaque ligne de données du tableau (de 0 à numero - 1 qui correspond au numéro de la dernière entrée) et recherche les correspondances pour l'année définie et le numéro de client défini :
'Compteur des OUI ou des NON
compteur = 0
For i = 0 To numero - 1
If tableau(i, 0) = annee And tableau(i, 1) = client Then compteur = compteur + 1
Next
Le résultat est ensuite inséré dans la cellule correspondant à l'année définie et au numéro de client défini :
'Affichage dans la cellule
Cells(annee - 2009, client + 1) = compteur
Il ne reste ensuite plus qu'à répéter ces opérations pour chaque année et chaque numéro de client en les ajoutant entre 2 boucles For :
'Décomptes de OUI ou de NON
For annee = 2011 To 2026 'Boucle des années
For client = 1 To 30 'Boucle des clients
'Compteur des OUI ou des NON
compteur = 0
For i = 0 To numero - 1
If tableau(i, 0) = annee And tableau(i, 1) = client Then compteur = compteur + 1
Next
'Affichage dans la cellule
Cells(annee - 2009, client + 1) = compteur
Next
Next
ScreenUpdating
Nous ne l'avons pas encore vu jusque-là mais vous pouvez encore ajouter la ligne suivante en début de procédure pour accélérer l'exécution de la macro :
Application.ScreenUpdating = False
En ajoutant cette ligne, vous demandez à Excel de ne pas actualiser l'affichage tant que la procédure n'est pas terminée.
Dans ce cas, au lieu de voir chaque nombre être ajouté l'un après l'autre dans les cellules, tous les nombres seront affichés en une fois.
Fichier
Le fichier complété : tableaux_exercice_complete.xlsm