Créer un système de suivi dynamique des plages de données, Excel VBA

Créer un système de suivi dynamique des plages de données, Excel VBA

Créer un système de suivi dynamique des plages de données dans Excel avec VBA peut être un excellent moyen d’automatiser la surveillance des modifications dans une plage de cellules spécifique. Voici un guide détaillé en français pour vous aider à créer un tel système. 

Objectif : 

Nous allons créer un système qui suit les changements effectués dans une plage dynamique de cellules et met automatiquement à jour une liste de changements, incluant la valeur précédente et la nouvelle valeur, l’heure du changement et l’adresse de la cellule modifiée. 

Concepts Clés : 

1. Plage Dynamique : Une plage de cellules dont la taille peut changer en fonction de certaines conditions (par exemple, ajout ou suppression de lignes ou de colonnes). 
2. Suivi des Modifications : Surveillance des modifications dans une plage de cellules spécifique. 
3. Gestionnaire d’Événements : En VBA, nous utilisons la programmation événementielle, en particulier l’événement Worksheet_Change, pour capturer et répondre aux modifications effectuées dans une feuille de calcul. 

Étapes pour Créer un Système de Suivi de Plage Dynamique : 

Étape 1 : Définir la Plage Dynamique 

Dans Excel, la plage dynamique peut être définie à l’aide de plages nommées ou directement via VBA. Dans cet exemple, nous définirons une plage dynamique à l’aide de l’objet Range en VBA. 

Imaginons que vous ayez des données dans une colonne ou un tableau dont la taille varie au fil du temps, et que vous souhaitiez surveiller les changements dans cette plage. 

Étape 2 : Utiliser l’Événement Worksheet_Change 

L’événement Worksheet_Change permet de suivre toute modification effectuée dans une feuille de calcul. Cet événement est déclenché dès qu’un utilisateur modifie une cellule dans la feuille. 

Nous utiliserons cet événement pour suivre les changements dans la plage dynamique et les consigner. 

Étape 3 : Créer une Feuille de Suivi 

Nous créerons une feuille séparée (par exemple, « ChangeLog ») où nous enregistrerons les changements. Cette feuille comprendra les colonnes suivantes : 

• La date et l’heure du changement. 
• L’adresse de la cellule. 
• La valeur précédente. 
• La nouvelle valeur. 

Étape 4 : Le Code VBA 

Voici le code VBA qui permet d’atteindre cet objectif : 

' Ce code doit être placé dans le module "ThisWorkbook" ou dans le module de la feuille spécifique. 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim PlageSuivi As Range 
    Dim FeuilleSuivi As Worksheet 
    Dim LigneLog As Long 
    Dim AncienneValeur As Variant 
    Dim NouvelleValeur As Variant 
    ' Définir la plage dynamique à surveiller 
    Set PlageSuivi = Me.Range("A1:A100") ' Ajustez cette plage selon vos besoins (par exemple, toute une colonne ou un tableau) 
    ' Vérifier si la cellule modifiée fait partie de la plage surveillée 
    If Not Intersect(Target, PlageSuivi) Is Nothing Then 
        ' Accéder à la feuille de suivi des changements 
        Set FeuilleSuivi = ThisWorkbook.Sheets("ChangeLog") 
        ' Trouver la prochaine ligne vide dans la feuille de suivi 
        LigneLog = FeuilleSuivi.Cells(FeuilleSuivi.Rows.Count, 1).End(xlUp).Row + 1 
        ' Obtenir la valeur avant modification 
        Application.EnableEvents = False ' Désactiver les événements pour éviter une boucle infinie 
        AncienneValeur = Target.Value 
        Application.EnableEvents = True 
        ' Obtenir la valeur après modification 
        NouvelleValeur = Target.Value      
        ' Consigner le changement dans la feuille de suivi 
        FeuilleSuivi.Cells(LigneLog, 1).Value = Now ' Loguer la date et l'heure actuelles 
        FeuilleSuivi.Cells(LigneLog, 2).Value = Target.Address ' Loguer l'adresse de la cellule modifiée 
        FeuilleSuivi.Cells(LigneLog, 3).Value = AncienneValeur ' Loguer la valeur avant modification 
        FeuilleSuivi.Cells(LigneLog, 4).Value = NouvelleValeur ' Loguer la valeur après modification 
    End If 
End Sub

Explication du Code : 

1. Événement Worksheet_Change : Cet événement est déclenché chaque fois qu’un changement est effectué dans la feuille. L’argument Target représente la cellule ou la plage modifiée. 

2. Plage Dynamique : La variable PlageSuivi est définie sur « A1:A100 », mais vous pouvez la modifier pour surveiller n’importe quelle plage de cellules. Par exemple, si vous souhaitez surveiller une colonne entière, vous pouvez utiliser Me.Range(« A:A »). Si votre plage dépend du nombre de lignes de données, vous pouvez utiliser : 

Set PlageSuivi = Me.Range("A1:A" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row) 

3. Feuille de Suivi : Nous utilisons l’objet FeuilleSuivi pour faire référence à la feuille où les changements seront enregistrés. Cette feuille doit déjà exister dans le classeur. Vous pouvez la créer manuellement ou programmétiquement. 

4. Consignation des Modifications : Chaque fois qu’un changement se produit dans la plage surveillée, le code : 

• Désactive temporairement les événements avec Application.EnableEvents = False pour éviter de déclencher à nouveau l’événement Worksheet_Change lors de la mise à jour de la feuille de suivi. 
• Récupère la valeur avant la modification (ancienne valeur) et après la modification (nouvelle valeur). 
• Enregistre la date et l’heure du changement (Now), l’adresse de la cellule (Target.Address), l’ancienne valeur (AncienneValeur) et la nouvelle valeur (NouvelleValeur) dans la prochaine ligne vide de la feuille de suivi. 

5. Désactivation des Événements : Application.EnableEvents = False est utilisé pour éviter la récursivité. Sans cela, chaque fois que le code écrit dans la feuille de suivi, cela pourrait déclencher l’événement Worksheet_Change , entraînant une boucle infinie. 

Remarques Supplémentaires : 

• Vous pouvez personnaliser la plage à surveiller en ajustant la variable PlageSuivi. 
• La feuille ChangeLog doit avoir des colonnes pour la date/heure, l’adresse de la cellule, la valeur ancienne et la valeur nouvelle. Si la feuille n’existe pas encore, vous pouvez l’ajouter manuellement ou automatiser sa création en VBA. 
• Cette solution suit tous les changements (insertions, suppressions et modifications). Si vous souhaitez exclure certaines actions (comme les modifications de formules ou des colonnes spécifiques), vous pouvez ajouter des conditions dans le code pour les gérer. 

Exemple de Résultat dans la Feuille « ChangeLog » : 

Ainsi, vous pouvez suivre toutes les modifications dans la plage surveillée et analyser les données au fil du temps.