Création d’une plage dynamique, Excel VBA

Création d’une plage dynamique, Excel VBA

Voici une explication détaillée sur la création d’une plage dynamique avec VBA dans Excel, en prenant l’exemple des compétences en communication. 

Scénario : 

Supposons que nous ayons un ensemble de données qui suit les compétences en communication d’un groupe d’individus, avec des colonnes telles que le nom, le niveau de compétence, le style de communication et les commentaires. Au fil du temps, de nouveaux individus ou de nouvelles données seront ajoutées, nous avons donc besoin que la plage se mette à jour automatiquement. 

Concepts Clés : 

1. Plage Dynamique : Une plage qui s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées. 
2. Plages Nomées : Ce sont des plages auxquelles on attribue un nom pour les référencer facilement dans des formules, et on peut créer des plages dynamiques nommées à l’aide de VBA. 

Exemple de Code VBA : 

Sub CreerPlageDynamiqueCompSkills() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim dynamicRange As Range 
    Dim rangeName As String 
    ' Définir la feuille de travail où se trouvent vos données 
    Set ws = ThisWorkbook.Sheets("Feuil1") 
    ' Trouver la dernière ligne des données dans la première colonne (on suppose que les données commencent dans la colonne A) 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Définir la plage dynamique (on suppose que les données commencent en A1 et se terminent à la dernière ligne en colonne D) 
    Set dynamicRange = ws.Range("A1:D" & lastRow) 
    ' Définir un nom pour la plage dynamique 
    rangeName = "PlageCompSkills" 
    ' Créer ou mettre à jour la plage nommée 
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=dynamicRange     
    ' Avertir l'utilisateur 
    MsgBox "Plage dynamique 'PlageCompSkills' créée de A1 à D" & lastRow, vbInformation 
End Sub

Explication du Code : 

1. Définir la feuille de travail : 
La première étape consiste à définir l’objet ws pour faire référence à la feuille contenant les données. Dans cet exemple, nous supposons que les données sont sur la feuille Feuil1. Vous pouvez remplacer « Feuil1 » par le nom réel de votre feuille de travail. 

Set ws = ThisWorkbook.Sheets("Feuil1") 

2. Trouver la dernière ligne de données : 
Pour rendre la plage dynamique, nous devons d’abord trouver la dernière ligne de données dans une colonne spécifique (dans ce cas, la colonne A). La méthode xlUp est utilisée pour se déplacer vers le haut depuis la dernière ligne possible jusqu’à ce que l’on trouve une cellule avec des données. Cela garantit que nous capturons la dernière ligne contenant des données, même si des lignes vides sont présentes entre les données. 

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 

3. Définir la plage dynamique : 
Nous créons un objet Range (dynamicRange) qui s’étend de A1 à la dernière ligne de la colonne D (vous pouvez ajuster les colonnes selon vos besoins). Cela permet que la plage s’ajuste automatiquement à mesure que de nouvelles données sont ajoutées ou supprimées. 

Set dynamicRange = ws.Range("A1:D" & lastRow) 

4. Créer ou mettre à jour la plage nommée : 
Ensuite, nous définissons un nom pour la plage dynamique (PlageCompSkills). Nous utilisons la méthode Names.Add pour créer ou mettre à jour la plage nommée pour qu’elle pointe vers la plage dynamique que nous avons définie précédemment. 

ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=dynamicRange 

5. Afficher une boîte de message : 
Enfin, le code affiche une boîte de message confirmant que la plage dynamique a été créée, avec l’emplacement de la plage. Cette étape est optionnelle mais utile pour fournir un retour à l’utilisateur. 

MsgBox "Plage dynamique 'PlageCompSkills' créée de A1 à D" & lastRow, vbInformation 

Cas d’Utilisation Pratique : 

1. Ajout de nouvelles données : Si de nouvelles données de compétences en communication sont ajoutées (par exemple, à la ligne 101), la plage dynamique s’ajustera automatiquement pour inclure la nouvelle ligne. 
2. Utilisation de la plage nommée dans les formules : Vous pouvez référencer la plage dynamique dans des formules comme SOMME, MOYENNE, ou dans des tableaux croisés dynamiques. Par exemple, pour trouver la moyenne du niveau de compétence, vous pourriez utiliser : 

=MOYENNE(PlageCompSkills[Niveau de compétence]) 

Avantages des Plages Dynamiques : 

• Flexibilité : La plage s’ajuste automatiquement au nombre d’entrées. 
• Efficacité : Plus besoin de mettre à jour manuellement les références de plage dans les formules lorsque les données changent. 
• Clarté : L’utilisation de noms significatifs comme PlageCompSkills facilite la référence aux plages dans des classeurs complexes. 

Conclusion : 

Créer des plages dynamiques à l’aide de VBA dans Excel vous permet d’automatiser et de gérer vos ensembles de données de manière plus efficace, surtout lorsqu’il s’agit de l’ajout de données continues, comme l’évaluation des compétences en communication. Cette flexibilité garantit que, lorsqu de nouvelles données sont ajoutées, vos formules et calculs restent précis sans avoir besoin d’ajustements manuels.