Pour nous entraîner à utiliser les tableaux, nous allons créer notre propre version de la macro que nous avons utilisée pour démontrer l'avantage de vitesse des tableaux, étape par étape...
Voici le point de départ de cet exercice (vous verrez que l'ensemble de données a été réduit à 1 000 lignes) :
Ici, vous pouvez télécharger le fichier d'exemple Excel : arrays_exercise.xls
Objectif de l'exercice : La procédure doit parcourir les données de l'ensemble de données et compter le nombre de réponses OUI ou NON pour chaque année et pour chaque numéro de client (soit OUI ou NON selon le choix de l'utilisateur) et saisir cette quantité dans la cellule spécifiée de la feuille de travail.
Complétez la macro suivante pour enregistrer les données de la feuille de calcul « DS » dans un tableau :
Sub actualize()
Dim last_row As Integer
'La dernière ligne de l'ensemble de données
'...
'Enregistrer un ensemble de données dans un tableau dynamique
Dim array_db()
'...
End Sub
Voici un exemple de solution :
Sub actualize()
Dim last_row As Integer
'La dernière ligne de la base de données
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Enregistrer un ensemble de données dans un tableau dynamique
Dim array_db()
ReDim array_db(last_row - 2, 2)
For row_number = 2 To last_row
array_db(row_number - 2, 0) = Sheets("DS").Range("A" & row_number)
array_db(row_number - 2, 1) = Sheets("DS").Range("B" & row_number)
array_db(row_number - 2, 2) = Sheets("DS").Range("C" & row_number)
Next
End Sub
Cela répète essentiellement ce que nous avons fait dans la leçon précédente…
Mais maintenant nous devons modifier notre macro en ajoutant les actions suivantes :
Voici un exemple de solution :
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
'La dernière ligne de la base de données
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Valeur de recherche (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Nombre de réponses YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Enregistrer un ensemble de données dans un tableau
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
End Sub
La recherche au choix de l'utilisateur est déterminée en début de procédure par le code suivant :
'Valeur de recherche (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
Nous utiliserons la fonction CountIF pour déterminer le nombre de réponses « OUI » ou « NON » :
'Nombre de réponses YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
Le tableau a été redimensionné pour s'adapter au nombre de réponses OUI ou NON et réduit à deux colonnes :
ReDim array_db(rows_number - 1, 1)
Ces données seront désormais stockées dans le tableau lorsque sa troisième colonne correspondra à la sélection de l'utilisateur :
'Insérer un nombre dans un tableau
insert_row = 0
'Traitement des ensembles de données
For row_number = 2 To last_row
'Valeur de la colonne C (YES ou NO)
value_yes_no = Sheets("DS").Range("C" & row_number)
'Si la valeur correspond à la sélection de l'utilisateur, la chaîne est stockée dans le tableau
If value_yes_no = search_value Then
'Enregistrer la valeur dans la colonne A
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
'Enregistrer la valeur dans la colonne B
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
'Une ligne a été enregistrée => le numéro d'insertion dans le tableau est incrémenté de 1
insert_row = insert_row + 1
End If
Next
Notre tableau contient uniquement les données qui nous intéressent.
Il ne reste plus qu'à :
Voici un exemple de solution :
'Nombre de réponses "YES"/"NO"
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
Problème résolu avec des commentaires l'expliquant en détail :
'Boucle pour chaque ligne
For no_years = 2011 To 2026
'Boucle pour chaque colonne
For no_client = 1 To 30
'Réinitialisation du compteur
counter = 0
'Traitement des tableaux
For i = 0 To UBound(array_db)
'Vérifier que la ligne du tableau correspond à l'année et au numéro de client
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
'Si l'année et le numéro de client correspondent, le compteur augmente de 1
counter = counter + 1
End If
Next
'Après traitement du tableau, le résultat est inscrit dans la cellule correspondante
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
Et enfin, le code de l'intégralité de notre macro :
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer, counter As Integer
'Suppression de contenu
Range("B2:AE17").ClearContents
'La dernière ligne de l'ensemble de données
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Valeur de recherche (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Nombre de réponses YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Sauvegarder les valeurs dans un tableau
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
'Compter les réponses YES ou NO
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
End Sub
Ici, vous pouvez télécharger le fichier d'exemple Excel : arrays_exercise_completed.xls
