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.

bd tableaux vba exercice

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.

tableau excel clients annees exercice png tableaux vba

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

tableau excel clients annees nombres png tableaux vba exercice