Macros-commandes VBA/Exercices/Contrôle de données

Consolidation de classeurs Excel

Nous nous proposons ici d'effectuer un exercice complexe pour mettre en pratique vos connaissances en termes de macros et ainsi automatiser certaines tâches (reporting par exemple). Dans le cas présent, il s'agit du contrôle de conformité d'un fichier de notes EXCEL.

Début de l'exemple

Exemple

Voici son énoncé :

• Créer un classeur de notes comprenant 4 colonnes : A ==> nom, B ==> matière à choisir parmi "info", "stat" ou "logi", C==> date, D==> note
• Remplir manuellement 10 lignes avec des valeurs du mondé réel (lignes 2 à 12)
• Laisser quelques fautes de saisie de nom sur quelques lignes : nom non renseigné, nom avec des caractères numériques
• Laisser quelques fautes de saisie de matière sur quelques lignes : matière non renseignée, matière inexistante
• Laisser quelques fautes de saisie de date sur quelques lignes : date impossible, date futuriste
• Laisser quelques fautes de saisie de note sur quelques lignes : note non numérique, note non comprise entre 0 et 20
• Concevoir la macro qui va identifier et signaler ces fautes

Fin de l'exemple

Solution

• Nous allons réaliser une macro qui permettra de balayer et contrôler chacune des informations saisies en fonction des règles de conformité demandées, pour ce faire
  • il faut créer le fichier notes.xlsm
  • il faut saisir les entêtes de colonnes demandées
  • il faut saisir les données demandées (avec quelques fautes de saisies)
• puis dans l'onglet DÉVELOPPEUR Visual Basic insérer le code VBA suivant :

 ' débute la macro qui effectue un contrôle de validité des données
 Sub ControleNotesSaisies()
    ' déclare une variable chaine permettant de récupérer les noms des étudiants
    Dim unnom As String
    ' déclare une variable chaine permettant de récupérer les matières
    Dim unematiere As String
    ' déclare une variable chaine permettant de récupérer les dates de devoir
    Dim unedate As String
    ' déclare une variable chaine permettant de récupérer les notes brutes
    Dim unenote As String
    
    ' variables utiles après le contrôle de type
    Dim unevraiedate As Date
    Dim unevraienote As Integer
    Dim unseulcaractere As String
    Dim i As Integer
    Dim nbErreursLigne As Long
    
    ' commence la boucle for qui va balayer les lignes de 2 à 11
    For ligne = 2 To 11
        'initialise le résultat
        nbErreursLigne = 0
        
        ' récupère et stocke le nom ligne par ligne
        unnom = Range("A" & ligne)
        ' récupère et stocke la matière ligne par ligne
        unematiere = Range("B" & ligne)
        ' récupère et stocke la date ligne par ligne
        unedate = Range("C" & ligne)
        ' récupère et stocke la note ligne par ligne
        unenote = Range("D" & ligne)
        
        ' vérifie qu’il y a au moins 2 caractères dans le nom
        If Len(unnom) < 2 Then
            nbErreursLigne = nbErreursLigne + 1
        Else
            ' vérifie si ces caractères sont alphabétiques
            For i = 1 To Len(unnom)
                unseulcaractere = Mid(unnom, i, 1)
                If Not unseulcaractere Like "[A-Za-z]" Then
                    nbErreursLigne = nbErreursLigne + 10
                    Exit For
                End If
            Next
        End If
        
        ' vérifie que la matière est connue
        Select Case unematiere
            Case "info", "stat", "logi":
            Case Else: nbErreursLigne = nbErreursLigne + 100
        End Select
        
        ' vérifie que la date est une date
        If Not IsDate(unedate) Then
            nbErreursLigne = nbErreursLigne + 1000
        Else
            ' vérifie que la date est plausible
            unevraiedate = CDate(unedate)
            If unevraiedate > Now Then nbErreursLigne = nbErreursLigne + 10000
        End If
        
        ' vérifie que la note est un nombre
        If Not IsNumeric(unenote) Then
            nbErreursLigne = nbErreursLigne + 100000
        Else
            ' vérifie que la note est valide
            unevraienote = CInt(unenote)
            If Not (unevraienote >= 0 And unevraienote <= 20) Then nbErreursLigne = nbErreursLigne + 1000000
        End If
        
        ' affiche le resultat dans la cellule de la colonne E
        If nbErreursLigne > 0 Then
                Range("F" & ligne) = nbErreursLigne
                Range("F" & ligne).Interior.Color = vbRed
                Range("F" & ligne).Font.Color = vbYellow
            Else
                Range("F" & ligne).Interior.Color = vbGreen
                Range("F" & ligne).Font.Color = vbWhite
            End If
            
        ' se positionne sur la ligne suivante
    Next ligne
    
 ' ferme la macro
 End Sub

• Ce code VBA
  • parcourt dans la feuille unique du classeur les lignes 1 à 1 (2 à 11)
  • parcourt dans la ligne en cours du classeur les colonnes 1 à 1 (A à D)
  • pour chaque cellule lue, effectue les contrôles demandés dans l'ennoncé
  • signale chaque erreur en colonne F
• Pour exécuter la macro, il vous suffit de
  • vider le classeur "notes.xlsm" de ses lignes (sauf la ligne 1)
  • saisir vos propres lignes avec ou sans erreurs (2 à 11)
  • retourner dans l'onglet DÉVELOPPEUR Macros
  • de sélectionner la macro "ControleNotesSaisies"
  • de l'exécuter

Remarque

EXCEL fournit en standard un outil de contrôle de saisie des données, mais pas aussi puissant que celui-ci

Conseil

Ne jamais oublier de bien nommer vos objets EXCEL, vos procédures et vos variables VBA

Conseil

Ne jamais oublier de bien commenter vos procédures, fonctions, variables et instructions VBA