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

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

Voici une explication détaillée et un code en VBA pour créer un système de gestion dynamique de plages de données, ainsi qu’un suivi des changements apportés à cette plage. 

Objectif : 

Créer une plage de données dynamique qui se met à jour automatiquement à chaque ajout ou suppression de données et suivre les modifications apportées à cette plage. 

Étape 1 : Définir la Plage Dynamique 

Pour créer une plage dynamique, vous pouvez utiliser VBA pour définir la plage en fonction de la taille des données. Cela peut être fait en utilisant la fonction OFFSET et les fonctions COUNTA ou COUNT pour ajuster dynamiquement la taille de la plage. 

Code pour Créer une Plage Dynamique : 

Sub CreateDynamicRange() 
    Dim ws As Worksheet 
    Dim rangeName As String 
    Dim dynamicRange As String 
    Dim lastRow As Long 
    Dim lastCol As Long 
    ' Définir la feuille de calcul sur laquelle travailler 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Définir le nom de la plage dynamique 
    rangeName = "DynamicDataRange" 
    ' Trouver la dernière ligne et la dernière colonne contenant des données 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique en utilisant OFFSET et COUNTA 
    dynamicRange = "OFFSET(" & ws.Name & "!$A$1, 0, 0, " & lastRow & ", " & lastCol & ")" 
    ' Créer la plage dynamique nommée 
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=dynamicRange  
    MsgBox "Plage dynamique '" & rangeName & "' créée avec succès !" 
End Sub

Explication du Code : 

1. Configuration de la Feuille (ws) : 

• Le code commence par définir la feuille de calcul (ws) sur laquelle la plage dynamique sera créée. Remplacez « Sheet1 » par le nom de votre feuille. 

2. Variables de Plage : 

• rangeName est le nom que vous souhaitez attribuer à votre plage dynamique. Dans cet exemple, il est défini sur « DynamicDataRange ». 
• dynamicRange va stocker la formule qui définit la plage dynamique en utilisant la fonction OFFSET. 

3. Trouver la Dernière Ligne et la Dernière Colonne : 

• lastRow trouve la dernière ligne contenant des données dans la colonne « A » (vous pouvez ajuster cette colonne en fonction de l’endroit où vos données commencent). Le code utilise xlUp pour trouver la dernière ligne remplie de bas en haut. 
• lastCol trouve la dernière colonne utilisée dans la première ligne à l’aide de xlToLeft. 

4. Définition de la Plage Dynamique : 

• La plage dynamique est créée à l’aide de la fonction OFFSET. Cette formule va ajuster la taille de la plage en fonction des données réelles.  
• OFFSET($A$1, 0, 0, lastRow, lastCol) signifie qu’on commence à la cellule A1 et qu’on étend la plage pour couvrir toutes les données, de l’angle supérieur gauche à l’angle inférieur droit. 

5. Création de la Plage Dynamique : 

• ThisWorkbook.Names.Add est utilisé pour créer la plage nommée. Elle utilise la formule de dynamicRange pour définir la plage dynamiquement. 

6. Message de Confirmation : 

• Une fois la plage créée, le code affiche une boîte de message confirmant la création de la plage dynamique. 

Étape 2 : Suivre les Modifications de la Plage Dynamique (Optionnel) 

Pour suivre les modifications apportées à la plage dynamique, vous pouvez utiliser l’événement Workbook_SheetChange. Cet événement se déclenche chaque fois qu’un changement est effectué dans la feuille, vous permettant ainsi d’enregistrer ou de gérer les modifications. 

Code pour Suivre les Modifications : 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
    Dim dynamicRange As Range 
    Dim logSheet As Worksheet 
    Dim lastRow As Long 
    ' Vérifier que nous travaillons sur la bonne feuille et plage 
    If Sh.Name = "Sheet1" Then 
        ' Définir la plage dynamique 
        Set dynamicRange = ThisWorkbook.Sheets("Sheet1").Range("DynamicDataRange") 
        ' Vérifier si le changement a eu lieu dans la plage dynamique 
        If Not Intersect(Target, dynamicRange) Is Nothing Then 
            ' Enregistrer le changement dans une feuille de log (LogSheet) 
            Set logSheet = ThisWorkbook.Sheets("LogSheet") 
            ' Trouver la prochaine ligne vide dans la feuille de log 
            lastRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1          
            ' Enregistrer les détails du changement 
            logSheet.Cells(lastRow, 1).Value = Now 
            logSheet.Cells(lastRow, 2).Value = "Cellule modifiée : " & Target.Address 
            logSheet.Cells(lastRow, 3).Value = "Nouvelle valeur : " & Target.Value 
        End If 
    End If 
End Sub

Explication du Code de Suivi des Modifications : 

1. Événement Déclencheur : 

• Workbook_SheetChange est un événement intégré qui se déclenche chaque fois qu’un changement est effectué dans une feuille. 

2. Vérification de la Feuille et de la Plage : 

• Le code s’assure que le changement se produit dans la bonne feuille (ici, « Sheet1 »). 

3. Vérification du Changement dans la Plage Dynamique : 

• Il vérifie si le changement a eu lieu dans la plage dynamique (DynamicDataRange). 

4. Enregistrement du Changement : 

• Si un changement est détecté, les détails sont enregistrés dans une feuille de log (LogSheet). 
• Le log inclut l’heure du changement, l’adresse de la cellule modifiée, et la nouvelle valeur de la cellule. 

Étape 3 : Créer la Feuille de Log 

Pour que le suivi fonctionne, vous devez créer une feuille de calcul nommée « LogSheet » qui contiendra les logs. La feuille de log enregistrera l’heure, l’adresse de la cellule modifiée, et la nouvelle valeur de la cellule. 

Avec ces étapes, vous pouvez créer une plage dynamique en VBA et suivre les changements qui y sont apportés pour un meilleur contrôle et suivi des données. Ce système est flexible et peut être adapté pour gérer différentes situations de données.