Créer une évolution de plage dynamique, Excel VBA

Créer une évolution de plage dynamique, Excel VBA

Voici l’explication détaillée et le code VBA pour créer une plage dynamique dans Excel. Une plage dynamique s’ajuste automatiquement lorsque des lignes ou des colonnes sont ajoutées ou supprimées, ce qui est très utile pour les formules, les graphiques ou les tableaux qui doivent se mettre à jour en fonction de la taille des données. 

Objectif : 

Nous allons créer une plage dynamique qui s’ajuste en fonction du nombre de lignes et de colonnes utilisées dans une feuille de calcul donnée. Cela est réalisé en utilisant VBA, en exploitant la fonctionnalité des plages nommées ou directement via des formules dans VBA pour créer une référence dynamique. 

Explication étape par étape : 

1. Plage dynamique avec VBA : Une plage dynamique dans Excel est généralement définie par une plage nommée qui s’ajuste automatiquement lorsqu’il y a un changement dans les données. En VBA, nous pouvons utiliser l’objet Range et la méthode Resize pour créer une plage qui s’adapte dynamiquement à la quantité de données dans une colonne ou une ligne. 
2. Création du code : Nous allons écrire une procédure VBA qui crée une plage dynamique en fonction du nombre de lignes et de colonnes utilisées dans une feuille donnée. 
3. Sélection de la plage : Pour définir dynamiquement la plage, nous allons utiliser la méthode UsedRange ou la méthode End pour trouver l’étendue des données. 

Exemple de code VBA : 

Sub CreerPlageDynamique() 
    ' Déclaration des variables 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Dim derniereLigne As Long 
    Dim derniereColonne As Long 
    Dim celluleDebut As Range 
    ' Définir la feuille de calcul où la plage dynamique sera créée 
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Changez le nom de la feuille si nécessaire 
    ' Trouver la dernière ligne et la dernière colonne contenant des données 
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la cellule de départ (coin supérieur gauche de votre plage de données) 
    Set celluleDebut = ws.Cells(1, 1) ' Supposons que les données commencent en A1 
    ' Créer la plage dynamique en fonction des données 
    Set plageDynamique = ws.Range(celluleDebut, ws.Cells(derniereLigne, derniereColonne)) 
    ' Facultatif : Ajouter la plage en tant que plage nommée 
    ws.Names.Add Name:="PlageDynamique", RefersTo:=plageDynamique 
    ' Afficher un message pour confirmer la création de la plage 
    MsgBox "La plage dynamique 'PlageDynamique' a été créée avec la taille : " & plageDynamique.Address 
End Sub

Explication du code : 

1. Variables : 

• ws : L’objet feuille de calcul où la plage dynamique sera créée. 
• plageDynamique : L’objet plage que nous définirons de manière dynamique. 
• derniereLigne : La dernière ligne contenant des données. 
• derniereColonne : La dernière colonne contenant des données. 
• celluleDebut : Le coin supérieur gauche de la plage dynamique (par exemple, la cellule A1). 

2. Trouver la dernière ligne et la dernière colonne : 

• Nous utilisons Cells(ws.Rows.Count, « A »).End(xlUp).Row pour trouver la dernière ligne contenant des données dans la colonne A. La méthode End(xlUp) fonctionne de manière similaire à un appui sur Ctrl + Flèche Haut dans Excel. 
• De même, Cells(1, ws.Columns.Count).End(xlToLeft).Column permet de trouver la dernière colonne utilisée dans la première ligne, en utilisant la méthode End(xlToLeft). 

3. Définir la plage : 

• En utilisant l’objet Range, nous combinons celluleDebut (qui est le coin supérieur gauche) et les coordonnées dynamiques de derniereLigne et derniereColonne pour définir la plage. Cela s’ajustera automatiquement au fur et à mesure que les données changent. 

4. Créer une plage nommée : 

• La méthode ws.Names.Add permet d’ajouter la plage dynamique en tant que plage nommée dans Excel. Cela vous permet de la référencer dans des formules ou des graphiques dans toute la feuille de calcul, même si la plage change. 

5. Confirmation : 

• Un simple MsgBox est affiché pour informer l’utilisateur que la plage dynamique a été créée avec succès, et il affiche l’adresse de la plage créée. 

Autres considérations : 

• Référence dynamique de colonne : Si vos données ne grandissent que verticalement (dans les lignes) et que vous ne vous attendez pas à ajouter de nouvelles colonnes, vous pouvez ajuster le code pour ne trouver que la derniereLigne et définir la plage à partir d’une colonne fixe (par exemple, A1). 
• Plage nommée : En créant une plage nommée, il devient plus facile de référencer cette plage dynamique dans d’autres formules ou dans le code VBA. Elle peut être utilisée dans des fonctions comme SOMME(PlageDynamique) ou RECHERCHEV(PlageDynamique, …). 
• Mise à jour des graphiques : Si vous créez des graphiques basés sur cette plage dynamique, le graphique sera mis à jour automatiquement lorsque la plage grandit ou se rétrécit. 

Exemple d’utilisation de la plage dynamique dans une formule : 

Une fois la plage dynamique créée et nommée PlageDynamique, vous pouvez la référencer dans n’importe quelle formule de votre classeur. Par exemple : 

• Dans une formule de cellule : =SOMME(PlageDynamique) 

Cela effectuera la somme des valeurs dans la plage dynamique, et la plage s’ajustera automatiquement à mesure que de nouvelles données sont ajoutées. 

Conclusion : 

Cette méthode fournit une solution robuste pour gérer des ensembles de données dynamiques dans Excel. En utilisant VBA pour créer des plages dynamiques, vous pouvez vous assurer que vos éléments dépendants des données, comme les graphiques, les formules et les tableaux croisés dynamiques, s’ajustent automatiquement lorsque les données changent.