Comment récupérer le nom de la feuille dans une cellule avec et sans macro VBA
Dans cet article, nous allons voir comment procéder pour récupérer le nom d'une feuille de calcul dans une cellule. Pour cela nous allons utiliser deux méthodes distinctes. Dans la première nous allons utiliser une série de formules imbriquées, dont le cœur va reposer sur la formule CELLULE(). Puis dans la seconde méthode nous allons utiliser une fonction développée en macro-commande VBA. Aucune notion de développement n’est requise pour cela, notre commande va être composée d’une seule ligne, très simple à comprendre.
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Première méthode « formule CELLULE() »
Pour commencer, nous allons utiliser une formule peu courante d'Excel : il s’agit de la formule CELLULE(). Attention, avant de pouvoir utiliser cette formule, il est impératif que le fichier soit déjà enregistré. Dans le cas contraire, le résultat retourné sera une réponse vide. («»)
La formule CELLULE() s'utilise de la manière suivante :
- Le premier des deux paramètres est type_info : il s’agit d’une chaîne de caractères (qui doit donc être saisis entre guillemets) et qui définit l'information que nous voulons récupérer via cette formule. Ce qui va nous intéresser dans le cas présent, c'est le paramètre nomfichier. Pour avoir le détail de chacun de ces paramètres, vous pouvez consulter l’aide directement depuis le site de Microsoft. Ainsi nomfichier retourne « le nom de fichier (notamment le chemin d’accès complet) du fichier qui contient une référence, sous forme de texte. »
- Le second paramètre [référence] est un paramètre facultatif qui va correspondre à une des cellules de la feuille sur laquelle nous souhaitons obtenir des informations. La formule étant insérée directement dans l’unique feuille de calcul du classeur, nous pourrions très bien valider la formule sans saisir ce second paramètre. Pour l’exemple, nous tout de même saisir les coordonnées d'une cellule de la feuille, par exemple la cellule « $A$1 ».
- Enfin, nous allons pouvoir valider en appuyant sur la touche entrée du clavier, afin de récupérer le nom du fichier avec le chemin d'accès complet et tout au bout de la chaîne de caractères, nous allons retrouver le nom de l'onglet.
=CELLULE("nomfichier";A1)
C'est cette partie, située tout à la fin qui va nous intéresser. Pour extraire cette partie uniquement, nous allons devoir imbriquer un certain nombre de formules les unes dans les autres afin de ne récupérer que cette partie.
Pour récupérer la fin d’une chaîne de caractères, Excel dispose d’une formule, il s’agit de la formule DROITE(). Celle-ci admet deux paramètres :
- Le premier paramètre correspond à la chaîne de caractères complète (on une référence à cette dernière),
- Et le second paramètre correspond à la taille (exprimée en nombre de caractères) de la chaîne que nous souhaitons extraire.
Avant de modifier la formule CELLULE() que nous avons saisie juste avant, nous allons copier cette dernière, afin de pouvoir la coller facilement par la suite sans avoir à la ressaisir. Pour cela, nous allons la sélectionner directement dans la barre des formule (sans le signe « = »), puis nous allons appuyer simultanément sur les touches « Ctrl » et « C » du clavier. Par la suite, pour coller la formule, il nous suffira d’appuyer sur « Ctrl » et « V ».
=DROITE(CELLULE("nomfichier";A1);20)
Dans cet exemple, nous récupérons donc les 20 derniers caractères de la chaîne renvoyée par la formule CELLULE(). Evidemment, si nous ne connaissons pas le nom de la feuille de travail, nous ne pouvons pas non plus connaître le nombre de caractères.
Nous allons ainsi devoir récupérer cette valeur par des formules.
Si l’on regarde dans le détail le résultat renvoyé par la formule CELLULE(), on peut constater que le caractère situé juste avant le nom de la feuille est un crochet fermant (« ] »), qui permet d’identifier le nom du fichier de travail. Pour déterminer où commence le début de la chaîne de caractères qui nous intéresse, il va alors nous suffire de déterminer la position de ce caractère dans la chaîne totale.
Pour cela nous allons encore devoir utiliser une nouvelle formule : TROUVE().
Les paramètres de la formule TROUVE() sont les suivant :
- texte_cherché, il s’agit du caractère ou de la chaîne de caractères que nous allons vouloir retrouver dans le texte. Ici il s’agit du crochet (« ] »),
- texte, il s’agit ici du texte complet dans lequel nous souhaitons retrouver le « sous-texte » du premier paramètre. Nous allons à nouveau coller la formule CELLULE().
- Enfin, le dernier paramètre [no_départ] est la position à partir de laquelle nous allons souhaiter réaliser notre recherche. Ici nous souhaitons démarrer dès le début de la chaîne, nous allons donc omettre de renseigner ce paramètre, qui est facultatif.
DROITE("]";CELLULE("nomfichier";A1))
À présent que nous connaissons la position de départ du nom de la feuille de calcul dans la chaîne de caractères. Nous allons pouvoir en déduire le nombre de caractères à extraire, en le soustrayant du nombre total de caractères. Pour obtenir ce nombre de caractères, nous allons utiliser une dernière formule : NBCAR().
DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-TROUVE("]";CELLULE("nomfichier";A1)))
N’oubliez pas les trois parenthèses fermantes en fin de formule, puis nous allons pouvoir valider cette grosse formule et si tout se passe bien, vous devriez obtenir le nom de la feuille de calcul !
Cette première méthode a comme avantage de ne pas nécessiter de saisir du code VBA. Par contre vous pouvez constater qu'elle est assez lourde à mettre en place et nécessite l’utilisation de nombreuses formules imbriquées.
2. Seconde méthode « Full VBA »
Nous allons maintenant voir une seconde méthode qui va consister à venir saisir du code VBA.
Pour créer de toute pièce une nouvelle formule VBA, nous allons devoir nous rendre dans l’outil de développement d’Excel, en effectuant un clic droit sur l'onglet de la feuille de calcul, puis sur « Visualiser le code ».
Nous allons insérer notre fonction dans un nouveau module : Menu « Insertion », puis « Module »
Une nouvelle feuille de code blanche s’affiche alors, dans laquelle nous allons pouvoir saisir notre code VBA.
Pour commencer, nous allons créer notre formule en utilisant le mot-clé « Function », suivi du nom de la formule : « nomFeuille » :
Function nomFeuille()
End Function
En appuyant simplement sur la touche entrée du clavier après avoir saisie le nom de la formule, VBA va automatiquement ajouter les parenthèses en bout de ligne, ainsi que la fermeture de la fonction (« End Function »).
Pour que la formule retourne une valeur lorsque que nous allons l’utiliser dans la feuille de calcul, il suffit simplement de réutiliser le nom de la fonction (« nomFeuille ») afin de lui affecter une valeur avec le signe égal (« = »).
Function nomFeuille()
nomFeuille = ActiveSheet.Name
End Function
Nous avons ici donné pour valeur à notre fonction le nom de la feuille active grâce à l’instruction VBA « ActiveSheet.Name ».
Enfin, pour récupérer le nom de la feuille, il nous suffit d’appeler cette nouvelle fonction comme n'importe quelle autre formule Excel.
nomFeuille()
Puis on valide tout simplement en appuyant sur la touche entrée du clavier.
Ainsi, bien que cette seconde méthode nécessite d'avoir recours à du code développé en VBA, vous constaterez qu'elle est quand même beaucoup plus simple et plus rapide à mettre en place.
Pour pouvoir réutiliser cette formule simplement dans tous vos classeurs Excel, vous pouvez également saisir cette fonction dans un classeur de macro personnelle qui sera toujours disponible sur votre poste de travail.