Créer une plage dynamique dans Excel et la tester de manière efficace, Excel VBA

Créer une plage dynamique dans Excel et la tester de manière efficace, Excel VBA

Voici un code VBA détaillé pour créer une plage dynamique dans Excel et la tester de manière efficace. Je vais également vous fournir une explication détaillée de chaque partie du code.
Objectif
L’objectif est de créer une macro VBA qui définit une plage dynamique dans une feuille Excel, la met à jour en fonction des données présentes, puis teste si elle fonctionne correctement.
Code VBA : Créer et Tester une Plage Dynamique
Voici un code complet et détaillé :

Option Explicit
Sub CreateAndTestDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dynamicRange As Range
    Dim testCell As Range
    ' Définir la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Trouver la dernière ligne utilisée dans la colonne A (en supposant que les données commencent en A1)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Trouver la dernière colonne utilisée dans la ligne 1 (en supposant que les en-têtes commencent en A1)
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Nommer la plage dynamique (optionnel)
    ws.Names.Add Name:="DynamicRange", RefersTo:=dynamicRange
    ' Afficher un message pour confirmer
    MsgBox "La plage dynamique a été définie de " & dynamicRange.Address, vbInformation, "Plage définie"
    ' --- TEST DE LA PLAGE DYNAMIQUE ---
    ' Boucle à travers la plage pour vérifier ses valeurs
    For Each testCell In dynamicRange
        ' Mettre en surbrillance les cellules vides pour vérifier les erreurs
        If IsEmpty(testCell) Then
            testCell.Interior.Color = RGB(255, 200, 200) ' Rouge clair pour les cellules vides
        Else
            testCell.Interior.Color = RGB(200, 255, 200) ' Vert clair pour les cellules remplies
        End If
    Next testCell
    MsgBox "Plage dynamique testée ! Les cellules vides sont surlignées en rouge.", vbInformation, "Test terminé"
End Sub

Explication Détailée du Code
1. Option Explicit :

• Cette ligne permet d’obliger à déclarer toutes les variables, ce qui évite les erreurs dues à des fautes de frappe ou des variables non définies.

2. Définition des Variables

• ws : Fait référence à la feuille de calcul où la plage dynamique est créée.
• lastRow : Trouve la dernière ligne utilisée dans la colonne A.
• lastCol : Trouve la dernière colonne utilisée dans la ligne 1.
• dynamicRange : Contient la plage dynamique de données.
• testCell : Utilisée dans la boucle pour tester et surligner les cellules vides.

3. Définir la Feuille de Calcul

Set ws = ThisWorkbook.Sheets("Sheet1")

• Cette ligne affecte à ws la feuille Sheet1 du classeur actif.

4. Trouver la Dernière Ligne Utilisée
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• Cette ligne part de la dernière ligne de la colonne A et se déplace vers le haut pour trouver la dernière cellule non vide.

5. Trouver la Dernière Colonne Utilisée
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• Cette ligne part de la dernière colonne de la ligne 1 et se déplace vers la gauche pour trouver la dernière cellule non vide.

6. Définir la Plage Dynamique
Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

• Crée une plage de A1 à la dernière ligne et colonne détectées.

7. Nommer la Plage Dynamique (Optionnel)
ws.Names.Add Name:="DynamicRange", RefersTo:=dynamicRange

• Donne un nom à la plage dynamique (DynamicRange).

8. Afficher un Message de Confirmation
MsgBox "La plage dynamique a été définie de " & dynamicRange.Address, vbInformation, "Plage définie"

• Affiche un message à l’utilisateur pour confirmer que la plage a bien été définie.

9. Tester la Plage Dynamique
For Each testCell In dynamicRange

• La boucle parcourt chaque cellule de la plage dynamique.

10. Surligner les Cellules Vides et Remplies

If IsEmpty(testCell) Then
testCell.Interior.Color = RGB(255, 200, 200) ' Rouge clair pour les cellules vides
Else
testCell.Interior.Color = RGB(200, 255, 200) ' Vert clair pour les cellules remplies
End If

• Rouge clair (255, 200, 200) : Cellules vides.
• Vert clair (200, 255, 200) : Cellules remplies.

11. Message Final de Confirmation
MsgBox "Plage dynamique testée ! Les cellules vides sont surlignées en rouge.", vbInformation, "Test terminé"

• Notifie l’utilisateur que le test est terminé et que les cellules vides ont été surlignées en rouge.

Comment Utiliser Ce Code
1. Ouvrez un fichier Excel.
2. Appuyez sur ALT + F11 pour ouvrir l’éditeur VBA.
3. Allez dans Insertion > Module.
4. Collez le code VBA dans le module.
5. Modifiez « Sheet1 » si nécessaire (si votre feuille s’appelle différemment).
6. Exécutez CreateAndTestDynamicRange depuis la liste des macros.
Avantages de Cette Approche
✔ Entièrement automatisée : Détecte automatiquement la plage de données.
✔ Facile à modifier : Vous pouvez ajuster les critères de la plage.
✔ Retour visuel : Surligne les cellules vides pour faciliter la validation.
✔ Sécurisée : Utilise Option Explicit et des messages de confirmation pour éviter les erreurs.