Créer une plage dynamique favorisant les compétences en travail d’équipe, Excel VBA

Créer une plage dynamique favorisant les compétences en travail d’équipe, Excel VBA

Voici un code VBA détaillé pour créer une plage dynamique dans Excel, avec une explication complète étape par étape. Ce code est conçu de manière à être compréhensible et facilement maintenable, ce qui favorise les compétences en travail d’équipe.
Objectif :
Nous allons créer une plage dynamique en VBA qui s’ajuste automatiquement lorsque de nouvelles données sont ajoutées ou supprimées. Cela est particulièrement utile dans des cas comme la création de rapports, de tableaux de bord, ou de tableaux croisés dynamiques.
Nous allons aussi mettre en œuvre des compétences en travail d’équipe en structurant bien le code, en utilisant des fonctions modulaires, des commentaires et en gérant les erreurs pour rendre le code facile à comprendre et à maintenir par une équipe.
Code VBA : Créer une Plage Dynamique
Supposons que nous avons un jeu de données dans la feuille « Feuille1 », à partir de la cellule A1, avec des en-têtes dans la première ligne. Nous voulons définir une plage dynamique.
Approche étape par étape :
1. Identifier la dernière ligne et la dernière colonne dynamiquement.
2. Créer une plage nommée qui se met à jour automatiquement.
3. Rendre le code réutilisable et maintenable.
4. Gérer les erreurs potentielles.
Code VBA :

Option Explicit
Sub CreerPlageDynamique()
    Dim ws As Worksheet
    Dim dernièreLigne As Long, dernièreColonne As Long
    Dim nomPlage As String
    Dim plageDynamique As Range
    ' Définir la feuille de travail
    Set ws = ThisWorkbook.Sheets("Feuille1") ' Ajuster le nom de la feuille si nécessaire
    ' Définir le nom de la plage
    nomPlage = "PlageDynamique"
    ' Trouver la dernière ligne utilisée dans la colonne A
    dernièreLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Trouver la dernière colonne utilisée dans la ligne 1
    dernièreColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(dernièreLigne, dernièreColonne))
    ' Créer ou mettre à jour la plage nommée
    On Error Resume Next
    ThisWorkbook.Names(nomPlage).Delete ' Supprimer le nom existant s'il existe
    On Error GoTo 0
    ThisWorkbook.Names.Add Name:=nomPlage, RefersTo:=plageDynamique
    ' Avertir l'utilisateur
    MsgBox "Plage Dynamique '" & nomPlage & "' créée avec succès !", vbInformation, "Succès"
End Sub

Explication détaillée du code :
1. Déclaration des variables

• ws : Contient la référence de la feuille de travail.
• dernièreLigne : Identifie la dernière ligne avec des données dans la colonne A.
• dernièreColonne : Identifie la dernière colonne avec des données dans la ligne 1.
• nomPlage : Définit le nom de la plage dynamique.
• plageDynamique : Contient la référence de la plage dynamique.

2. Définir la feuille de travail
Set ws = ThisWorkbook.Sheets("Feuille1")

• Cette ligne définit la référence à la feuille « Feuille1 ».
• Adaptez le nom de la feuille selon vos besoins.

3. Trouver la dernière ligne et la dernière colonne

dernièreLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
dernièreColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• ws.Rows.Count : Retourne le nombre total de lignes (1 048 576 dans Excel moderne).
• .End(xlUp) : Trouve la dernière ligne utilisée dans la colonne A (supposant qu’il n’y a pas de lignes vides).
• .End(xlToLeft) : Trouve la dernière colonne utilisée dans la ligne 1 (supposant que les en-têtes existent).

4. Définir la plage dynamique
Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(dernièreLigne, dernièreColonne))

• La plage commence à A1 et s’étend jusqu’à la dernière ligne et colonne utilisées.

5. Créer ou mettre à jour la plage nommée

On Error Resume Next
ThisWorkbook.Names(nomPlage).Delete ' Supprimer le nom existant s'il existe
On Error GoTo 0

• Si la plage nommée existe déjà, elle est supprimée avant de créer la nouvelle.

ThisWorkbook.Names.Add Name:=nomPlage, RefersTo:=plageDynamique

• Attribue la plage dynamique à un nom de plage appelé "PlageDynamique".

6. Avertir l’utilisateur
MsgBox "Plage Dynamique '" & nomPlage & "' créée avec succès !", vbInformation, "Succès"

• Affiche une boîte de message confirmant la création de la plage dynamique.

Avantages de cette approche
✅ Mise à jour dynamique : Plus besoin de mettre à jour manuellement les plages nommées.
✅ Code adapté au travail d’équipe : Bien structuré et facile à comprendre et à modifier pour d’autres membres de l’équipe.
✅ Scalabilité : Fonctionne pour des ensembles de données de tailles variées.
✅ Gestion des erreurs : Empêche la duplication des plages nommées.
Bonus : Automatiser avec l’événement Worksheet_Change
Si vous souhaitez que la plage dynamique se mette à jour automatiquement lorsque des données sont modifiées, placez ce code dans le module de la feuille1 :

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    CreerPlageDynamique
    Application.EnableEvents = True
End Sub

• Chaque modification de donnée déclenche la mise à jour de la plage.
• Désactive les événements temporairement pour éviter une boucle infinie.

Conclusion
Ce script VBA crée une plage dynamique qui peut être utilisée dans des formules, des tableaux croisés dynamiques et des tableaux de bord. Il favorise des pratiques de travail d’équipe avec une bonne structuration et des commentaires pour une compréhension et une maintenance facilitées.