syntaxe avec codename pour un classeur mis en variable

Version imprimable

28/09/2017, 14h02
eriiic

syntaxe avec codename pour un classeur mis en variable
Bonjour à tous,

je bute sur un truc à-priori simple : comment désigner une feuille par son codename pour un classeur mis en variable ?
Code:

1 2 3 4 5 6 Sub test() Dim wb As Workbook, sh As Worksheet Set wb = ThisWorkbook Set sh = wb.Feuil1 ' erreur Set sh = wb.Worksheets(Feuil1) ' erreur End Sub
Peu de possibilités mais je suis à court d'idée pour la bonne syntaxe.
Ca n'est quand même pas réservé au classeur actif (?)
eric

Salut Eric,

Je passe par cette fonction, qui est depuis longtemps dans mon module wbTools...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Function getWorksheetByCodename(Codename As String, Optional wb As Workbook) As Worksheet
  Dim Counter As Long
  Dim Found As Boolean
 
  If wb Is Nothing Then Set wb = ActiveWorkbook
 
  Counter = 1
  Do While Counter <= wb.Worksheets.Count And Not Found
    If UCase(wb.Worksheets(Counter).Codename) = UCase(Codename) Then
      Set getWorksheetByCodename = wb.Worksheets(Counter)
      Found = True
    End If
    Counter = Counter + 1
  Loop
End Function

Code:

1
2
3
4
5
6
7
Sub Test1()
  Dim sh As Worksheet
  Dim wb As Workbook
 
  Set wb = Workbooks("Book1")
  Set sh = getWorksheetByCodename("shTest", wb)
End Sub

28/09/2017, 15h44
Menhir

Citation:

Envoyé par eriiic

Peu de possibilités mais je suis à court d'idée pour la bonne syntaxe.

Je pense que tu as simplement oublié les guillemets :

Code:

Set sh = wb.Worksheets("Feuil1")

Ecrire, comme tu l'as fait, wb.Worksheets(Feuil1) signifie de chercher le nom de la feuille dans la variable Feuil1 (variable qui, vraisemblablement, n'exixte pas).

Met en première ligne de ton module un Option Explicit qui t'obligera à déclarer tes variables et te permettra de détecter plus facilement ce type de problèmes.
28/09/2017, 15h49
Pierre Fauconnier

Salut Menhir,

Citation:

Envoyé par Menhir

[...]

Code:

Set sh = wb.Worksheets("Feuil1")

[...]

Ici, Eric parle du codename. Dans le code que tu donnes, tu utilises le name, c'est-à-dire le nom de l'onglet Excel.

Je pense que je ne m'amuserais pas à passer par une fonction perso si la solution était aussi simple que celle que tu proposes, tu ne crois pas?

... :?
28/09/2017, 16h57
Menhir

Citation:

Envoyé par Pierre Fauconnier

Ici, Eric parle du codename. Dans le code que tu donnes, tu utilises le name, c'est-à-dire le nom de l'onglet Excel.

Etant donné les erreurs que contient le code qu'il a fourni, je ne suis pas sûr qu'il ait une définition du codename aussi précise que la notre.
D'où ma proposition pour vérifier si, après tout, ce n'est pas simplement le name dont il a besoin (étant donné la simplicité, on ne perd rien à essayer).
28/09/2017, 17h42
Pierre Fauconnier

Citation:

Envoyé par Menhir

[...]je ne suis pas sûr qu'il ait une définition du codename aussi précise que la notre.[...]

Jamais vu quelqu'un parler du codename d'une feuille sans savoir de quoi il parlait... ;)
28/09/2017, 17h46
eriiic

Si si, le codename est bien le codename chez moi aussi.
Les 'erreurs' sont des exemples infructueux, la 2nde étant la tentative en désespoir de cause ;-)

Merci Pierre, boucler j'y pensais bien mais ça me paraissait dommage. J'espérais une syntaxe plus directe.
Tant pis :-)
eric
28/09/2017, 20h09
Marc-L

Bonjour,

le CodeName étant déjà une variable et n'a une utilité directe uniquement pour le classeur contenant le code !
Dans ce cas créer une variable sur un CodeName est totalement superfétatoire,
du genre prendre l'ascenseur au 5è étage pour descendre au Rez de Chaussée puis remonter directement au 6è par l'escalier !

Dans le cas d'une feuille située sur un classeur différent de celui contenant le code,
ceci à une fin de sécurité vis à vis du changement de l'ordre de la feuille ou de son nom,
il faut alors parcourir la collection des feuilles de ce classeur afin d'y comparer leurs CodeName
et là il y a un intérêt de créer une variable sur la feuille ciblée mais
sans toutefois omettre de libérer cette variable après usage !
Voir par exemple la fonction GetSheetByCode publiée dans les discussions de ce forum …

___________________________________________________________________________________________________________

Je suis Paris, Barcelone, London, Manchester, Egypte, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …
28/09/2017, 21h58
eriiic

Citation:

le CodeName étant déjà une variable et n'a une utilité directe uniquement pour le classeur contenant le code !

J'aurais dit propriété plutôt.
J'ai mis ThisWorkbook pour simplifier l'exemple, en l'occurrence il s'agit d'un autre classeur x dont une feuille est susceptible de changer de nom.
L'utilité est là quelque soit le type de classeur.
J'ai plus l'impression d'un oubli plus que d'un choix délibéré car inutile.
29/09/2017, 09h13
Marc-L

C'est pour cela que c'est écrit en italique …

Il y a une sacré différence d'utilisation de CodeName entre un classeur contenant le code exécuté et un autre !
La référence directe au CodeName ne vaut que pour le classeur depuis lequel le code est exécuté
même si ce n'est pas celui actif !
Pour un autre classeur, il faut boucler sur sa collection de feuilles pour trouver celle selon son CodeName
- voir fonction GetSheetByCode de ce forum - et si nécessaire lui affecter une variable objet, variable à libérer après usage.
ThisWorkbook induit donc en erreur.

Une fois ceci acquis il ne peut y avoir aucun « exemple infructueux » …
29/09/2017, 12h37
eriiic

Bonjour,

ThisWorkbook était pour l'exemple, désolé si ça a amené des confusions, ça aurait tout aussi être :

Code:

Set wb = Workbooks("mon_classeur_qui_va_bien.xlsx")

Et je sais bien qu'on peut boucler pour y pallier, Pierre proposait également une fonction au-dessus.
Ma question était : existe-t-il une syntaxe du type :

Code:

Set sh = wb.Feuil1

a-priori c'est donc non, c'est limité au classeur de la macro (et non au classeur actif comme je l'ai écrit).
On ne peut pas mettre une expression devant, dommage mais je ferai avec ;-)
Je trouvais étrange que ce ne soit pas possible et aurais très bien pu ne pas avoir trouvé la façon correcte de le faire, d'où la question.
eric
29/09/2017, 13h30
NVCfrm

Saluts

Citation:

Envoyé par eriiic

J'aurais dit propriété plutôt.
...

Je dirais pas tout à fait.
Enfin, cela de la façon dont y accède comme le rappelle Marc. Il est disponible en tant que variable globale de la portée du projet.
29/09/2017, 13h31
Marc-L

Feuil1 est une variable Worksheet implicite au niveau projet comme ThisWorkbook et
CodeName est une propriété retournant le nom de cet objet implicite, pas l'objet lui même …

Juste en effectuant le test dans la fenêtre Exécution du VBE …
29/09/2017, 14h11
NVCfrm

Enfin, je sentais qu'on était parti sur des rapports différents. Je reviens après suppression d'une réponse rapide pour lire et comprendre l'endroit qui cloche.
Soyons clair, je ne penses pas à CodeName; mais à Feuil1 !
En écrivant cette réponse, je vois encore que c'est peut-être plus que du niveau Projet.
A vérifier au niveau Application si Feuil1 désigne celle du classeur l'ayant invoqué ou celle du classeur actif. Dans ce dernier cas une fonction native VBA existe déjà.
29/09/2017, 15h13
eriiic

Citation:

A vérifier au niveau Application si Feuil1 désigne celle du classeur l'ayant invoqué ou celle du classeur actif.

J'avais testé set sh=Feuil1 avec un autre fichier actif, son Parent est bien le classeur de la macro comme annoncé par Marc-L
eric

Bonjour,
un variante de la version de Pierre!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub test()
Set sh = CodeName("Feuil1", ThisWorkbook)
Set sh1 = CodeName("Feuil1", ThisWorkbook)
Set sh3 = CodeName("Feuil3", ThisWorkbook)
End Sub
Public Function CodeName(Cname As String, ByRef wb As Workbook) As Worksheet
Static dic As Object
Set CodeName = Nothing
If TypeName(dic) = "Nothing" Then Set dic = CreateObject("Scripting.Dictionary")
If Not dic.Exists(Cname & "©" & wb.Name) Then
    For i = 1 To wb.Sheets.Count
        If UCase(wb.Sheets(i).CodeName) = UCase(Cname) Then
             dic.Add Cname & "©" & wb.Name, wb.Sheets(i)
             Set CodeName = wb.Sheets(i)
             Exit For
         End If
 
 
    Next
Else
   Set CodeName = dic(Cname & "©" & wb.Name)
End If
End Function

29/09/2017, 17h51
eriiic

Merci à tous :-)
29/09/2017, 18h04
NVCfrm
Citation:

Envoyé par eriiic

J'avais testé set sh=Feuil1 avec un autre fichier actif, son Parent est bien le classeur de la macro comme annoncé par Marc-L
eric

Que c'est lassant quand le temps nous manque de réfléchir à tous les contours pour formuler une idée avec des termes clairs sans ambiguïtés.

Oui c'est visible au niveau application.

parmi, immédiatement après le clic envoyant ma réponse, je me suis rendu que j'ai été trop rapide à l'image du message qui succéda a mon premier post. Message qui a été instantanément corrigé à plusieurs reprises (m'amenant d'ailleurs à supprimer un post qui répondait directement à un message qui n'était plus ce qu'on voit et qui avait trop vite contredit avant de se corriger).

Aussi soyons clair: le but de ma contribution n'est motivé que par une volonté d'apporter des précisions supplémentaires à des notions importantes qui me semblent occultés voir ignorés volontairement ou non. (Ce sera toujours utile à ceux qui ne les savent pas).
Pour être précis, il n'y a pas de volonté de rivalité ou de démonstration de compétence quelconque par rapport à un autre ici (ce qui serait ennuyant pour moi en tous cas, car je ne vois pas de concours de compétence digne de ce nom à travers de simples sollicitations d'internautes en difficultés, je ne participerais pas a ce genre d'idioties), nous sommes tous des maîtres quelque part.

Ceci dit revenons a nos moutons :
J'ai tout de suite pensé que la visibilité de cette variable reste au niveau projet, mais une autre particularité va me venir aussitôt dans la tête en rapport avec les concepts fondamentaux du langage presqu'identique dans tous les autres langages de programmation.
Ceci marche bien au niveau application :
Code:

1 2 3 Feuil1.Unprotect Feuil1.Range("a1") = 10 Feuil1.Protect
Ce n'est pas une affirmation gratuite.
Il suffit de se rappeler les principes basiques sur les espaces de noms des variables : Une question de priorité qui fait que l'un masque l'autre.
Changer le nom de Feuil1 du projet contenant le code et le tour est joué.

Bien sûr cela ne répond pas à ta question, mais t'apporte un éclairage intéressant à savoir n'est ce pas? ;)
29/09/2017, 19h01
NVCfrm

J'ai testé maintenant. Et tout ce que j'ai affirmé ci-haut concernant la possible visibilité de la variable codename au niveau appli est tout à fait faux .
L'espace de noms propres au projet n'est pas partagé.