Connaître les plages de cellules nommées en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Noms des plages en VBA

Les plages nommées sont importantes avec Excel pour désigner des sources de données aux bornes variables mais aussi pour simplifier la syntaxe des formules les impliquant. Avec cette nouvelle astuce VBA Excel, nous allons voir comment il est possible de pointer dessus par le code. Et par la même occasion, nous allons découvrir comment récupérer leurs dimensions et les sélectionner automatiquement.

Classeur Excel à télécharger
Pour la démonstration de cette nouvelle astuce, nous suggérons d'appuyer l'étude sur un classeur hébergeant une source de données volumineuse et abritant un certain nombre de plages nommées, pour mieux piloter les rangées qu'elles représentent.

• Télécharger le classeur noms-des-plages.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Cliquer sur les boutons Activer la modification et Activer le contenu des bandeaux de sécurité,

Nous découvrons effectivement un tableau de données conséquent. Il est constitué de quatre colonnes et de plusieurs centaines de lignes. Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que la base de données est nommée bdd tandis que tous les autres noms représentent les colonnes intégrales et respectives du tableau.

Vous notez de même la présence d'un bouton en haut à gauche du tableau. Il est déjà associé à une procédure de code VBA. Vous pouvez le constater en réalisant le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel.

Private Sub Recuperer_Click()
On Error Resume Next 'si pas de nom ou erroné

End Sub

Cette procédure est vierge pour l'instant hormis la présence d'une instruction de gestion d'erreurs. C'est cette procédure que nous allons retravailler pour questionner les noms de plages.

La déclaration des variables
Pour débuter, nous avons besoin de variables notamment pour manipuler ces plages nommées.

• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

Private Sub Recuperer_Click()
On Error Resume Next 'si pas de nom ou erroné
Dim nbLignes As Integer: Dim nbColonnes As Byte
Dim nomTab As String: Dim tbl As Variant

End Sub

Nous déclarons tout d'abord les variables nbLignes et nbColonnes comme des entiers (normaux et courts). Nous entendons les exploiter pour prouver que nous sommes en mesure de restituer les dimensions des plages nommées. La variable nomTab est déclarée comme un texte (String). Elle doit mémoriser le nom du tableau mentionné par l'utilisateur par le biais d'une boîte de dialogue. Dès lors, c'est la variable tbl, non encore typée, qui doit piloter cette plage reconnue par ce nom stocké.

Piloter la plage nommée
Pour piloter l'une des plages de cellules portant un nom, nous comptons tout d'abord questionner l'utilisateur. Celui-ci doit répondre par l'un des noms existants. Et grâce à ce nom, nous allons pouvoir créer la variable objet capable de piloter cette plage.

• Ajouter les instructions suivantes :

...
nomTab = InputBox("Quel est le nom de la plage dont vous souhaitez récupérer les dimensions ?", "Nom du tableau")
tbl = ActiveSheet.Range(nomTab)
...

Grâce à la fonction InputBox, nous affichons une boîte de dialogue offrant une zone de saisie. Cette saisie de l'utilisateur en réponse est dès lors stockée dans la variable nomTab. Puis, nous exploitons l'objet Range de la feuille active (ActiveSheet) pour pointer sur la plage reconnue par ce nom passé en paramètre. Désormais, l'objet tbl désigne cette plage nommée.

Longueur et hauteur de la plage
C'est désormais grâce à la fonction UBound à exploiter sur la variable représentant la plage que nous allons pouvoir déterminer le nombre de lignes et le nombre de colonnes qu'elle porte.

• A la suite du code, ajouter les instructions VBA suivantes :

...
nbLignes = UBound(tbl, 1)
nbColonnes = UBound(tbl, 2)
...

La variable tbl n'est autre qu'un tableau de cellules à deux dimensions. La première (1) représente ses lignes. La seconde (2) représente ses colonnes. Nous stockons ces nombres dans les variables respectives nbLignes et nbColonnes.

Sélectionner la plage nommée
Pour finir, nous proposons de restituer ces informations à l'écran, par le biais d'un MsgBox, après avoir sélectionné la plage en question par le code VBA.

• A la suite du code, ajouter les instructions VBA suivantes :

...
ActiveSheet.Range(nomTab).Select
MsgBox "La plage : " & nomTab & " est constituée de " & nbLignes & " lignes et de " & nbColonnes & " colonnes."
...

Nous exploitons la méthode Select sur la plage (Range) pour la sélectionner. Puis, nous assemblons les informations pour les afficher à l'écran, grâce à la fonction MsgBox.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille (ALT + Tab),
• Cliquer sur le bouton Récupérer,
• Puis à l'invite, désigner un nom de plage comme act pour la colonne des activités,
• Dès lors, cliquer sur le bouton Ok pour procéder,

Comme vous pouvez le voir, la plage désignée est parfaitement sélectionnée et dans le même temps, les informations sur le nombre de lignes et le nombre de colonnes qu'elle contient, sont affichées à l'écran.

Le code VBA complet que nous avons construit pour questionner ces plages nommées est le suivant :

Private Sub Recuperer_Click()
'On Error Resume Next 'si pas de nom ou erroné
Dim nbLignes As Integer: Dim nbColonnes As Byte
Dim nomTab As String: Dim tbl As Variant

nomTab = InputBox("Quel est le nom de la plage dont vous souhaitez récupérer les dimensions ?", "Nom du tableau")
tbl = ActiveSheet.Range(nomTab)

nbLignes = UBound(tbl, 1)
nbColonnes = UBound(tbl, 2)

ActiveSheet.Range(nomTab).Select
MsgBox "La plage : " & nomTab & " est constituée de " & nbLignes & " lignes et de " & nbColonnes & " colonnes."

End Sub