Automatisation des pistes d’audit des données, Excel VBA

Automatisation des pistes d’audit des données, Excel VBA

L’automatisation des pistes d’audit des données dans Excel avec VBA peut être un outil puissant pour assurer la traçabilité des modifications de données dans un tableau. Une piste d’audit permet de suivre l’historique des changements effectués dans une feuille de calcul, en enregistrant qui a modifié quoi, quand et comment. Ce type de processus est essentiel dans des contextes comme la comptabilité, la gestion de projet, ou tout autre domaine nécessitant une traçabilité.

Voici un exemple détaillé de code VBA pour automatiser une piste d’audit des données dans Excel. Ce code enregistre chaque changement effectué dans une feuille de calcul, avec les informations suivantes :

• L’utilisateur qui a effectué la modification
• La cellule modifiée
• La valeur avant la modification
• La valeur après la modification
• La date et l’heure de la modification

Code VBA pour automatiser la piste d’audit

1. Création d’un module d’audit:

Nous allons utiliser un événement Worksheet_Change pour capturer les modifications effectuées dans une feuille de calcul.

Étape 1 : Préparer la feuille de piste d’audit

-Ajoutez une feuille dans votre classeur Excel, que nous appellerons « Audit ».

– Cette feuille doit comporter des colonnes suivantes :

• • Date et Heure : pour enregistrer la date et l’heure de la modification.
  • Utilisateur : pour enregistrer l’utilisateur qui effectue la modification.
  • Cellule Modifiée : pour indiquer l’adresse de la cellule modifiée.
  • Ancienne Valeur : pour afficher la valeur avant la modification.
  • Nouvelle Valeur : pour afficher la valeur après la modification.

Par exemple, la feuille « Audit » pourrait ressembler à ceci :

• Colonne A : Date et Heure
• Colonne B : Utilisateur
• Colonne C : Cellule Modifiée
• Colonne D : Ancienne Valeur
• Colonne E : Nouvelle Valeur

Étape 2 : Code VBA pour capturer les changements

1. Ouvrez l’éditeur VBA (Alt + F11).
2. Dans le volet « VBAProject », double-cliquez sur la feuille dans laquelle vous souhaitez enregistrer les modifications (par exemple, Feuil1).
3. Collez le code suivant dans cette feuille.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AuditSheet As Worksheet
    Dim LastRow As Long
    Dim OldValue As Variant
    Dim NewValue As Variant
    Dim UserName As String
    Dim CellAddress As String
    Dim CurrentTime As String
    ' Si plusieurs cellules sont modifiées, ignorer (optionnel)
    If Target.Count > 1 Then Exit Sub
    ' Définir la feuille d'audit
    Set AuditSheet = ThisWorkbook.Sheets("Audit")
    ' Obtenir la valeur avant modification (si disponible)
    OldValue = Target.Value
    ' Obtenir la nouvelle valeur après modification
    NewValue = Target.Value
    ' Obtenir le nom de l'utilisateur (utilise le nom d'utilisateur de l'ordinateur)
    UserName = Application.UserName
    ' Obtenir l'adresse de la cellule modifiée
    CellAddress = Target.Address
    ' Obtenir la date et l'heure actuelle
    CurrentTime = Now()
    ' Trouver la dernière ligne vide dans la feuille Audit
    LastRow = AuditSheet.Cells(AuditSheet.Rows.Count, "A").End(xlUp).Row + 1
    ' Enregistrer les informations dans la feuille Audit
    With AuditSheet
        .Cells(LastRow, 1).Value = CurrentTime        ' Date et Heure
        .Cells(LastRow, 2).Value = UserName          ' Utilisateur
        .Cells(LastRow, 3).Value = CellAddress       ' Cellule Modifiée
        .Cells(LastRow, 4).Value = OldValue          ' Ancienne Valeur
        .Cells(LastRow, 5).Value = NewValue          ' Nouvelle Valeur
    End With
End Sub

Explication détaillée du code

1. Événement Worksheet_Change :
Cet événement se déclenche chaque fois qu’une modification est apportée à une cellule dans la feuille de travail. Il prend comme argument Target, qui est la plage (ou cellule) qui a été modifiée.

2. Gestion de la feuille d’audit :
Le code définit une référence à la feuille « Audit », où les informations seront enregistrées. Si vous utilisez une autre feuille pour l’audit, modifiez le nom de la feuille dans Set AuditSheet = ThisWorkbook.Sheets(« Audit »).

3. Vérification de la modification d’une seule cellule :
Si plusieurs cellules sont modifiées à la fois, le code ignore l’événement pour éviter d’ajouter plusieurs lignes dans la feuille d’audit.

4. Récupération des informations :

• • Ancienne valeur : la valeur de la cellule avant la modification (c’est la valeur de la cellule avant qu’elle soit modifiée).
  • Nouvelle valeur : la valeur après modification.
  • Utilisateur : utilise Application.UserName pour récupérer le nom de l’utilisateur actuel sur le système.
  • Cellule modifiée : l’adresse de la cellule modifiée, capturée par Target.Address.
  • Date et heure : la date et l’heure actuelle du système avec Now().

5. Enregistrement dans la feuille « Audit » :
Les informations sont ensuite écrites dans la première ligne vide de la feuille « Audit ». Le numéro de ligne est déterminé par AuditSheet.Cells(AuditSheet.Rows.Count, « A »).End(xlUp).Row + 1.

6. Création d’un journal :
Le journal des modifications est ajouté sous forme de nouvelles lignes dans la feuille « Audit », en enregistrant les informations sur la date, l’utilisateur, la cellule modifiée, la valeur avant et après la modification.

Conclusion

Ce code fournit une piste d’audit automatique pour suivre les modifications effectuées dans un tableau Excel. Vous pouvez l’adapter à vos besoins en ajoutant plus de détails ou en modifiant la structure de l’audit. Ce système garantit la traçabilité des données et peut être utile dans des environnements où il est crucial de savoir qui a effectué une modification et pourquoi.