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

**Contrôle de données**
Leçon : Macros-commandes VBA

Exercices n^o9

Exercices de niveau 14.
Exo préc. :	Consolidation de classeurs
Exo suiv. :	Création de formulaire personnalisé

En raison de limitations techniques, la typographie souhaitable du titre, « Exercice : Contrôle de données
Macros-commandes VBA/Exercices/Contrôle de données », n'a pu être restituée correctement ci-dessus.

Consolidation de classeurs Excel[modifier | modifier le wikicode]

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.

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

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