Création et personnalisation dynamique d’une plage de cellules, Excel VBA
Créer une plage dynamique dans Excel à l’aide de VBA consiste à définir une plage de cellules qui peut changer de taille (par exemple, lorsque des données sont ajoutées ou supprimées) et à s’adapter automatiquement à ces changements sans avoir à mettre à jour manuellement la plage. Les plages dynamiques sont particulièrement utiles lorsque vous travaillez avec des données qui peuvent croître ou diminuer au fil du temps. Voici une explication détaillée et un code VBA qui crée une plage dynamique et la personnalise selon les besoins de l’utilisateur.
Objectif :
Nous allons créer un code VBA qui définit une plage dynamique pour un ensemble de données qui s’étend ou se contracte en fonction du nombre de lignes et de colonnes dans une plage spécifique. Cette plage dynamique pourra être utilisée pour diverses tâches, telles que la création de graphiques, la réalisation de calculs ou l’alimentation de données dans une autre partie du classeur.
Étapes pour créer une plage dynamique avec VBA :
- Identifier la zone de données : Vous devez déterminer le point de départ de vos données, comme une cellule spécifique (par exemple, A1). Vous devez également identifier le coin inférieur droit de la plage, ce qui peut être calculé dynamiquement en fonction de l’étendue des données.
- Utiliser VBA pour définir la plage dynamiquement : La méthode la plus courante pour définir une plage dynamique est d’utiliser CurrentRegion ou de trouver la dernière ligne et la dernière colonne d’un ensemble de données.
- Personnaliser la plage : Cela peut être fait en permettant à l’utilisateur de choisir ou en ajustant automatiquement la plage selon certains critères (par exemple, sélectionner uniquement les colonnes contenant des valeurs ou les lignes avec des données).
Exemple de code :
Voici un code VBA qui montre comment créer une plage dynamique en fonction des données présentes dans une feuille de calcul. Ce code s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées.
Sub CreateDynamicRange()
' Définir la feuille de travail où se trouvent les données
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Définir le point de départ de la plage de données (par exemple, la cellule A1)
Dim startCell As Range
Set startCell = ws.Range("A1")
' Trouver la dernière ligne et la dernière colonne de l'ensemble de données
Dim lastRow As Long
Dim lastColumn As Long
lastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row
lastColumn = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column
' Créer la plage dynamique
Dim dynamicRange As Range
Set dynamicRange = ws.Range(startCell, ws.Cells(lastRow, lastColumn))
' Exemple : Nommer la plage dynamiquement
ws.Names.Add Name:="DynamicRange", RefersTo:=dynamicRange
' Exemple : Utiliser la plage dynamique pour un calcul (somme de toutes les valeurs)
Dim total As Double
total = Application.WorksheetFunction.Sum(dynamicRange)
' Afficher le résultat dans une boîte de message
MsgBox "La somme de la plage dynamique est : " & total
' Exemple : Créer un graphique basé sur la plage dynamique
Dim chartObj As ChartObject
Set chartObj = ws.ChartObjects.Add
chartObj.Chart.SetSourceData Source:=dynamicRange
chartObj.Chart.ChartType = xlColumnClustered
End Sub
Explication du code :
1. Définir la feuille de travail et la cellule de départ :
Dim ws As Worksheet: Cette ligne définit la feuille de travail où se trouvent les données. Vous spécifiez la feuille par son nom (« Sheet1 » dans cet exemple).Set startCell = ws.Range("A1"): Cela définit le coin supérieur gauche de la plage de données (dans ce cas, la cellule A1).
2. Déterminer la dernière ligne et la dernière colonne :
lastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row: Cette ligne trouve la dernière ligne utilisée dans la colonne où vos données commencent. Elle fonctionne en comptant les lignes depuis le bas de la feuille jusqu’à la première cellule non vide.lastColumn = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column: Cette ligne trouve la dernière colonne utilisée dans la ligne où vos données commencent. Elle fonctionne en comptant les colonnes depuis la droite vers la gauche.
3. Définir la plage dynamique :
Set dynamicRange = ws.Range(startCell, ws.Cells(lastRow, lastColumn)): Cette ligne crée un objet Range qui couvre la zone allant de la cellule de départ (A1) à la dernière ligne et colonne identifiées.
4. Nommer la plage :
ws.Names.Add Name:="DynamicRange", RefersTo:=dynamicRange: Cela nomme la plage « DynamicRange », ce qui vous permet de la référencer facilement dans d’autres parties du classeur.
5. Effectuer des calculs avec la plage dynamique :
total = Application.WorksheetFunction.Sum(dynamicRange): Cette ligne montre comment utiliser la plage dynamique pour effectuer une somme de toutes les valeurs présentes dans la plage.MsgBox "La somme de la plage dynamique est : " & total: Cette ligne affiche la somme dans une boîte de message.
6. Créer un graphique basé sur la plage dynamique :
Set chartObj = ws.ChartObjects.Add: Cela ajoute un nouveau graphique à la feuille de travail.chartObj.Chart.SetSourceData Source:=dynamicRange: Cette ligne définit la source de données du graphique comme étant la plage dynamique.chartObj.Chart.ChartType = xlColumnClustered: Cette ligne définit le type de graphique comme un graphique en colonnes groupées.
Personnalisation :
Vous pouvez personnaliser le code ci-dessus en :
- Permettant à l’utilisateur de spécifier dynamiquement le point de départ de la plage (par exemple, en utilisant InputBox).
- Créant des conditions pour exclure les lignes ou colonnes vides si nécessaire.
- Modifiant l’utilisation de la plage dynamique, par exemple pour la mettre à jour dans un tableau croisé dynamique, remplir des cellules avec des valeurs ou appliquer une mise en forme conditionnelle.
Conclusion :
Ce script VBA crée une plage dynamique basée sur les données réelles présentes dans la feuille de calcul, s’adaptant automatiquement aux changements dans l’ensemble de données. En nommant la plage, vous pouvez y faire référence facilement dans le classeur, et la plage s’ajustera toujours à mesure que des données sont ajoutées ou supprimées.