Discussion :

[têtedebois]

Bonjour,

Pourriez-vous me donner des pistes pour résoudre mon problème de débutant, s'il vous plaît ?

Je souhaite récupérer dans une cellule de fichier Excel, le nom des feuilles du classeur.

J'ai commencé par écrire dans un module, une fonction qui récupère un integer (l'index de la feuille dans le classeur) et restitue une String (le nom de la feuille)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Option Explicit
Function carNomFeuille(intIndexFeuille As Integer) As String
    carNomFeuille = Sheets(intIndexFeuille).Name
End Function

En revenant sur Excel, j'appelle la fonction de la manière suivante : je saisi les caractères suivants dans la cellule : =carNomFeuille(1)
Echec : la cellule m'affiche : #NOM ?


J'ai essayé d'écrire une fonction encore moins complexe dans un autre module. La fonction ne prend aucun argument et renvoie le nom de la feuille active :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Option Explicit
Function carNomFeuilleActive() As String
    carNomFeuilleActive = ActiveSheet.Name
End Function

Nouvel échec : la cellule m'affiche encore : #NOM ?


D'avance merci pour votre aide.

[Philippe Tulliez]

Bonjour,
Pourtant le premier code fonctionne parfaitement. A condition de ne pas déplacer la feuille ensuite car la fonction ne se mettra pas à jour.
Pourquoi son numéro ? qui définit sa position dans le classeur à partir de la gauche ?

Sinon il existe d'autres possibilités sans VBA =CELLULE("adresse";Feuil2!B2), renvoie l'adresse complète de la cellule en référence définit par le second argument. Le nom de la feuille pourra ainsi être extrait à l'aide des fonctions Texte

[Pierre Fauconnier]

Salut.

Ne pas oublier les parenthèses derrière le nom de la fonction perso (erreur courante)

La fonction perso suivante fonctionne très bien, il me semble, et renvoie le nom de la feuille qui contient la plage passée en argument, ou le nom de la plage active en l'absence d'argument). Attention que la valeur renvoyée par la fonction en l'absence de paramètre sera recalculée sur base de la feuille active.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Function NomFeuille(Optional Ref As Range)
  If Ref Is Nothing Then
    NomFeuille = ActiveSheet.Name
  Else
    NomFeuille = Ref.Parent.Name
  End If
End Function

Sinon, l'idée de Philippe est bonne, mais demande de formuler un truc à mon sens un peu imbuvable, selon moi =>

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(CELLULE("adresse";Feuil2!A1);CHERCHE("]";CELLULE("adresse";Feuil2!A1))+1;CHERCHE("!";CELLULE("adresse";Feuil2!A1))-CHERCHE("]";CELLULE("adresse";Feuil2!A1))-1)

La vraie question que je me pose: A quoi ça sert? Pourrais-tu expliquer pourquoi tu as besoin de connaître le nom de la feuille d'une cellule?

[têtedebois]

Merci à Philippe et Pierre pour vos réponses. Je ne pense pas avoir déplacé ma feuille depuis la création de la macro. J'ai écrit mes 2 variantes de code dans des modules obtenus en cliquant sur "Insertion" puis "Module". J'espère que la copie d'écran ci-dessous illustre l'arborescence du fichier .xlsxm

Pour répondre à la question sur l'objectif de mon code, je pense qu'il faut que je commence par décrire le contexte en quelques lignes :



J'ai des actions à mener sur une dizaine de projets. J'ai créé un classeur Excel avec une feuille dédiée à chaque projet. Le nom des feuilles correspond au nom des projets.
Je me suis dit que pour que mon responsable puisse en un coup d'oeil, connaître l'état d'avancement de la dizaine de projets, je pourrai ajouter une 1ère feuille, appelée synthèse, dans laquelle j'utilise une colonne de cellules pour rappeler le nom de chacun des projets. Dans une 2nde colonne j'afficherai dans les cellules, l'état d'avancement des projets.
Dans chacune des feuilles, j'ai indiqué dans la cellule C2 l'état d'avancement du projet.

J'ai voulu procéder par étapes en commençant par la recopie du nom des projets (i.e : le nom de chaque feuille). La recopie de la cellule C2 de chaque feuille me semble une tâche qui ne nécessite pas VBA. Il me faudra concaténer le nom de la feuille, le caractère "!" et "C2" (dans toutes les feuilles dédiées aux projets, j'ai utilisé la cellule C2 pour indiquer l'état d'avancement, ex : "TBD" ou "IN PROGRESS" ou "DONE").


Je ne suis pas certain d'avoir compris la remarque de Pierre au sujet des parenthèses : s'agit-il d'une remarque générale, à garder à l'esprit ou d'une erreur dans mes 2 bouts de code ? En effet, j'ai relu le code et ne vois pas où j'aurais oublié de mettre des parenthèses après le nom de la fonction.

[Pierre Fauconnier]

Un peu de construction

Dans LA feuille qui n'est pas une feuille de projet, on place un tableau structuré t_Projets composé de deux colonnes. On nommera cette feuille de manière à pouvoir l'exclure du traitement lors de la boucle de récupération des projets. Dans l'exemple, j'ai nommé cette feuille coté VBA shMain⁽¹⁾.



Le code suivant rafraîchit le tableau des projets

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub PrepareDashBoard()
  Dim r As Range
  Dim sh As Worksheet
 
  If Not Range("t_Projets").ListObject.DataBodyRange Is Nothing Then Range("t_Projets").ListObject.DataBodyRange.Delete
  For Each sh In Worksheets
    If sh.CodeName <> "shMain" Then
      Set r = Range("t_Projets").ListObject.ListRows.Add().Range
      r(1).Value = sh.Name
      r(2).Value = sh.Range("c2").Value
    End If
  Next
End Sub

Le tableau des projets après exécution:






⁽¹⁾ Ce billet de Philippe explique comment codenommer une feuille...

[eriiic]

Bonjour à tous,

une fonction personnalisée doit être écrite dans un module standard si tu veux l'utiliser depuis une feuille.
Tu les as mise dans un module feuille.

Je rejoins les autres pour dire que ta fonction n'est utile que si vraiment tu veux le nom de la feuille x, qui peut bouger... Ca parait hasardeux
eric

[Pierre Fauconnier]

Salut Eric,

Perso, je les vois dans Module1 et Module2


@TeteDeBois

En fait, si la finalité est d'avoir un tableau résumant le statut des projets ayant leur propre feuille, on peut se passer de VBA, sauf si on est vraiment paresseux


S'appuyer sur C2 pour chaque feuille signifie que tu ne pourras jamais déplacer cette cellule dans une ou plusieurs de tes feuilles. C'est pour moi un très gros risque. Il me semble de loin préférable de s'appuyer sur des plages nommées. Pour cela, je créerais une feuille modèle et je nommerais C2 (par exemple avec Statut). Cela signifie que lorsque je vais copier cette feuille, Excel va ajouter le nom Statut dans le gestionnaire de noms avec la feuille comme étendue. On remarque ainsi un Statut avec une étendue de classeur et x Statuts dont l'étendue est la feuille qui supporte la plage nommée.




Dans le tableau récap, on peut utiliser ce nom avec INDIRECT en recomposant l'adresse



Pour créer un projet, il suffit alors de dupliquer la feuille Modèle, de la renommer et d'ajouter ce nom dans le tableau récap. La formule s'étend et tu récupères le statut. Pas besoin de VBA pour cela. Si demain tu dois déplacer C2 sur une ou plusieurs feuilles, ton code continue à fonctionner et la formule dans le tableau aussi, car ta formule est générique grâce aux plages nommées et à INDIRECT.





Si tu veux vraiment du VBA pour remplir ton tableau, une bonne idée serait de préfixer systématiquement le codename de tes feuilles de projet. Ainsi, par boucle en VBA, tu peux traiter juste les feuilles de projet en testant le début du nom. Lorsqu'il faut vraiment concevoir ce genre de classeur, je m'appuie sur une feuille modèle dont le codename est shProjet





C'est intéressant de pratiquer ainsi car lorsque tu vas copier la feuille modèle pour un nouveau projet, elle portera le codename de la feuille modèle suffixé d'un numéro




Dès lors, une simple boucle permet de récupérer les noms des feuilles projets (et donc des projets). En VBA, on a alors juste besoin de pousser les noms dans le tableau préalablement vidé grâce à la procédure PrepareDashBoard

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub PrepareDashBoard()
  Dim Projects
 
  Projects = getProjects
  If Not Range("t_Projets").ListObject.DataBodyRange Is Nothing Then Range("t_Projets").ListObject.DataBodyRange.Delete
  Range("t_Projets[Projet]").Resize(UBound(Projects)).Value = Projects
End Sub
 
Function getProjects()
  Dim sh As Worksheet
  Dim Names As String
 
  For Each sh In ThisWorkbook.Sheets
    If sh.CodeName Like "shProjet*" And sh.CodeName <> "shProjet" Then Names = Names & sh.Name & ";"
  Next
  getProjects = Application.Transpose(Split(Left(Names, Len(Names) - 1), ";"))
End Function

Comme tu le vois, le code VBA ne se préoccupe plus ni de la position des feuilles dans le classeur, ni du fait que le statut est en C2.


Cerise sur le gâteau: Normalement, la procédure de vidange d'un tableau devrait être générique et stockée dans un module spécifique dédié à la gestion des tableaux structurés et nommé par exemple xlTable. On y place la procédure suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub ClearTable(Table As ListObject)
  If Not Table.DataBodyRange Is Nothing Then _
    Table.DataBodyRange.Delete
End Sub

Dès lors, on systématise son approche et la procédure PrepareDashboard utilise le nettoyage générique

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub PrepareDashBoard()
  Dim Projects
 
  Projects = getProjects
  xlTable.ClearTable Range("t_Projets").ListObject
  Range("t_Projets[Projet]").Resize(UBound(Projects)).Value = Projects
End Sub

[eriiic]

Bonjour,

Ah mince, voir le module Feuil1 sélectionné m'a trompé.
Désolé... :-)
Etrange qu'il ait #NOM! dans ce cas
eric

[Pierre Fauconnier]

j'avais aussi été eu au premier regard

@TeteDeBois: Je parlais des parenthèses car il faut mettre des parenthèses après le nom de la fonction