Créer des plages nommées dynamiques, Excel VBA

Créer des plages nommées dynamiques, Excel VBA

Créer des plages nommées dynamiques avec VBA dans Excel vous permet d’ajuster automatiquement les plages nommées chaque fois que la taille de vos données change. Cela est particulièrement utile pour les feuilles de calcul où des données sont régulièrement ajoutées ou supprimées, garantissant que vos formules et graphiques qui se réfèrent à des plages nommées restent précis. 

Voici une explication détaillée de la manière dont vous pouvez utiliser VBA pour créer des plages nommées dynamiques dans Excel : 

1. Comprendre les Plages Nommées

Une plage nommée dans Excel est une plage spécifique de cellules à laquelle un nom unique est attribué. Au lieu de faire référence à des adresses de cellules (par exemple A1:B10), vous pouvez utiliser ces noms dans vos formules, ce qui rend celles-ci plus faciles à comprendre et plus flexibles. 

Les plages nommées dynamiques s’ajustent automatiquement lorsqu’il y a un ajout ou une suppression de données. Elles peuvent être définies pour s’ajuster en fonction de certaines conditions ou variables, comme le nombre de lignes ou de colonnes de données dans une zone spécifique. 

2. Pourquoi Utiliser VBA pour des Plages Nommées Dynamiques ?

Vous pouvez créer manuellement des plages nommées dynamiques dans Excel, mais utiliser VBA (Visual Basic for Applications) permet de : 

• Automatiser la création et la mise à jour des plages dynamiques. 
• Créer des plages plus flexibles qui peuvent s’adapter à différentes sources de données. 
• Appliquer la même logique à plusieurs feuilles ou classeurs. 

3. Exemple de Code pour Créer des Plages Nommées Dynamiques avec VBA

Voici un exemple de code VBA qui montre comment créer des plages nommées dynamiques. Cet exemple suppose que vous avez un jeu de données où le nombre de lignes dans une colonne (disons la colonne A) peut changer. 

Sub CreateDynamicRangeNames() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim dynamicRange As String 
    Dim rangeName As String 
    ' Définir la feuille de calcul avec laquelle vous travaillez 
    Set ws = ThisWorkbook.Sheets("Feuil1") 
    ' Trouver la dernière ligne avec des données dans la colonne A 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Définir la référence de la plage dynamique pour la colonne A 
    dynamicRange = "A1:A" & lastRow 
    ' Définir le nom de la plage que vous souhaitez attribuer 
    rangeName = "PlageDynamiqueA" 
    ' Vérifier si le nom de la plage existe déjà et le supprimer si c'est le cas 
    On Error Resume Next 
    ThisWorkbook.Names(rangeName).Delete 
    On Error GoTo 0 
    ' Créer la plage nommée dynamique 
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:="=" & ws.Name & "!" & dynamicRange 
    ' Informer l'utilisateur que la plage a été créée 
    MsgBox "La plage dynamique '" & rangeName & "' a été créée, référant à " & dynamicRange 
End Sub

4. Explication du Code

• Dim ws As Worksheet : Cette ligne définit une variable ws pour la feuille de calcul sur laquelle la plage dynamique sera créée. Vous pouvez changer le nom de la feuille en fonction de vos besoins. 
• lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row : Cette ligne trouve la dernière ligne contenant des données dans la colonne A. Elle commence à partir du bas de la feuille (ws.Rows.Count) et remonte (xlUp) jusqu’à ce qu’elle trouve une cellule non vide. 
• dynamicRange = « A1:A » & lastRow : Cela construit l’adresse de la plage dynamique. Par exemple, si vos données vont de A1 à A10, le code s’ajustera automatiquement pour « A1:A10 » en fonction de la dernière ligne trouvée. 

• rangeName = « PlageDynamiqueA » : Cette ligne définit le nom que vous souhaitez attribuer à la plage dynamique. 
• On Error Resume Next / On Error GoTo 0 : Ces lignes gèrent le cas où le nom de la plage existe déjà. Si c’est le cas, il supprime l’ancien avant de créer une nouvelle plage dynamique. 
• ThisWorkbook.Names.Add : Cette ligne crée la nouvelle plage nommée en utilisant les propriétés Name et RefersTo. La propriété RefersTo définit la plage à laquelle le nom pointe, ajustant dynamiquement la plage en fonction de la dernière ligne. 
• MsgBox : Cette ligne affiche une boîte de message pour confirmer que la plage dynamique a bien été créée. 

5. Comment Utiliser le Code

1. Insérer le Code dans l’Éditeur VBA : Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. Insérez un nouveau module en allant dans Insertion > Module. Collez le code ci-dessus dans le module. 
2. Exécuter le Code : Fermez l’éditeur VBA et appuyez sur Alt + F8 pour ouvrir le menu des macros. Sélectionnez CreateDynamicRangeNames et cliquez sur Exécuter. 
3. Tester la Plage Dynamique : Vous pouvez maintenant utiliser le nom de la plage PlageDynamiqueA dans vos formules. Par exemple, =SOMME(PlageDynamiqueA) s’ajustera automatiquement pour inclure toutes les données dans la colonne A, quel que soit le nombre de lignes. 

6. Améliorations Que Vous Pouvez Apporter

• Plages Nommées Dynamiques Multiples : Vous pouvez étendre ce code pour créer des plages dynamiques pour plusieurs colonnes ou feuilles. 
• Types de Plages Différents : Au lieu de se référer à une seule colonne, vous pouvez créer des plages dynamiques pour des données sur plusieurs colonnes, par exemple A1:B & lastRow pour capturer une plage de A à B. 
• Créer des Plages Dynamiques pour les Graphiques : Vous pouvez lier des plages dynamiques aux séries de graphiques, ce qui permet au graphique de se mettre à jour automatiquement lorsque les données changent. 
• Appliquer à D’autres Feuilles : Vous pouvez utiliser une boucle pour appliquer la même logique à toutes les feuilles de votre classeur. 

7. Conclusion

Créer des plages nommées dynamiques avec VBA rend la gestion des données plus facile et plus flexible. L’exemple fourni montre les bases de la définition et de l’attribution d’une plage nommée dynamique basée sur la taille d’un ensemble de données. Avec VBA, vous pouvez étendre cette approche pour créer des solutions plus complexes et adaptables pour vos classeurs Excel.