Discussion :

[rundish]

Bonjour,
Je suis devant un petit casse tête et je souhaiterai savoir si il est résoluble.
J'ai un classeur excel avec deux feuilles.via par exemple le gestionnaire de nom, j'ai défini un nom ("toto") dans la feuille 1 et "tata" dans la feuille 2.
Quand j'utilise un sub dans un module, je peut aussi bien faire référence à toto qu'à tata via Range("toto") ou [toto].
Par contre quand j'utilise un sub mais rattaché à une feuille cela ne fonctionne qu'avec [toto] et [tata].
Vous me direz, il est ou le problème. Déjà j'aimerai savoir pourquoi Range("toto") ne marche plus. Ensuite, comme j'ai beaucoup de nom, j'incrémente, je compose, du genre toto1, toto2,... . Du coup si je veux par exemple faire une sub dans la feuille de tata (feuil2), les totoi ayant été déclaré dans la feuille 1 :

Code :

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

1
2
3
4
5
6
7
8
sub titi()
Dim num as integer
for num = 1 to 5
     cells(1,num).value=[toto & num]
'ou cells(1,num).value =range("toto" & num)
next num
 
end sub

ca ne fontionne pas.
Vous avez une solution?
Merci

[Marc-L]

Bonjour,

un exemple : Cells(1, num).Value = Feuil1.Range("toto" & num).Value …

__________________________________________________________________________________________

Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion …

[Docmarti]

Bonjour rundish; Marc;

Pour que ca fonctionne à partir de tous les modules de feuilles, le nom doit être défini avec l'étendue Classeur. (section Zone dans Définir un nom)


Voir cet échange
http://www.developpez.net/forums/d82...ible-classeur/

L'avantage de définir un nom au niveau feuille, c'est que tu peux définir un nom dans les autres feuilles en utilisant toujours le même nom. Ce qui ne semble pas ce que tu veux faire pour l'instant.

[rundish]

Bonjour à vous et merci pour vos réponses

Bonjour rundish; Marc;

Pour que ca fonctionne à partir de tous les modules de feuilles, le nom doit être défini avec l'étendue Classeur. (section Zone dans Définir un nom)

Tout mes noms sont définis au niveau classeur justement. C'est pour ça que je ne comprend.
Ce que je sais c'est que pour une macro dans un module associé au classeur tout se passe bien, alors que pour une macro directement dans une feuille (via bouton) ca ne marche plus...

Bonjour,

un exemple : Cells(1, num).Value = Feuil1.Range("toto" & num).Value …

Je vais essayer. On verra bien.

Merci

Bonne journée

[Docmarti]

Tout mes noms sont définis au niveau classeur justement. C'est pour ça que je ne comprend.
Ce que je sais c'est que pour une macro dans un module associé au classeur tout se passe bien, alors que pour une macro directement dans une feuille (via bouton) ca ne marche plus...

Tu as raison. Il semble que les noms définis avec une étendue classeur se comportent comme les variables définies Public dans un module feuille.
C'est-à-dire qu'il faut spécifier l'objet feuille pour s'y référer à partir de l'extérieur de la feuille:

Code :

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

r = Feuil1.a

Une différence cependant. À partir d'un module standard, on peut se référer à un nom défini avec étendue classeur sans spécifier l'objet feuille.

Dans un module standard :

Code :

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

1
2
3
4
5
6
Sub titi()
Dim num As Integer
For num = 1 To 5
  Cells(1, num).Value = Range("toto" & num).Value
Next num
End Sub

Dans un autre module feuille:

Code :

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

1
2
3
4
5
6
7
Sub titi()
Dim num As Integer
For num = 1 To 5
  Cells(1, num).Value = Feuil2.Range("toto" & num).Value
Next num
 
End Sub

[Marc-L]

Si par exemple un nom est défini sur une cellule de la feuille 1 avec la portée classeur, à partir du module de la feuille 2
en version 2003 & 2007 je n'ai pas besoin de préciser la feuille avec l'écriture directe entre crochets …

[Docmarti]

Bonjour Marc.

Merci de l'info. C'est intéressant. Ca m'a conduit à une autre découverte qui fonctionne aussi : Evaluate.

Code :

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

1
2
3
4
5
6
7
8
9
Sub titi()
Dim num As Integer
For num = 1 To 5
 
  Cells(1, num).Value = Evaluate("toto" & num).Value
 
Next num
 
End Sub

[Philippe Tulliez]

Bonjour,
Il y a une autre option c'est d'utiliser l'objet ThisWorkBook où une procédure utilisant Range("nom") fonctionne parfaitement.

[Marc-L]

Ca m'a conduit à une autre découverte qui fonctionne aussi : Evaluate.

En fait les crochets représentent la notation abrégée d'Evaluate.
Mais parfois pour certaines formules les crochets ne passent pas alors que c'est sans souci via Evaluate …

[rundish]

Bonsoir.
Merci à vous pour votre aide, remarques et suggestions.
L'écriture directe entre crochet est effectivement très pratique. Le truc c'est que ca ne marche pas quand on "reconstruit" le nom.
J'ai donc fait ce que vous avez dit avec la référence à la feuille. Je vais tester avec evaluate, cela peut simplifier la lecture de la macro.
Je vous remercie donc encore. Mes macros fonctionnent.
Je ne sais toujours pas pourquoi :

Il semble que les noms définis avec une étendue classeur se comportent comme les variables définies Public dans un module feuille.

C'est un mystère de microsoft!!!

Si quelqu'un a la réponse, je suis preneur, sinon, le sujet peut être clos.
Encore merci

[Didier Gonard]

Bonjour,

Tu as raison. Il semble que les noms définis avec une étendue classeur se comportent comme les variables définies Public dans un module feuille.

Attention !
Une variable Public en VBA doit toujours et uniquement être déclarée dans le haut (zone de déclaration) d’un module standard !
Les modules de feuilles, de classeur ou de UserForm sont des pseudos modules de classe et se comportent du coup pareils à ce niveau, y déclarer une variable Public revient en fait à définir une propriété dans cette classe… c’est pourquoi, pour avoir un résultat tu dois ajouter devant le nom de l’objet concerné, suivi du point et de nom de la propriété que tu as ainsi accidentellement définie.

C'est un mystère de microsoft!!!

Non, pas vraiment, c’est plus relatif à la compréhension du modèle Objet du VBA Excel. On en revient donc à ce qui vient d’être dit ci-dessus, les modules de feuilles et de classeurs ne sont pas des modules ordinaires, mais des pseudos modules de Classe.

Dans un classeur de base à 3 feuilles avec la 1 active, si tu lances le code suivant dans un module standard ou le module de feuille1:

Code :

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

Range("B5").Select

Dans la même configuration, tu lances ce code dans le module de la feuille 2 ou 3 => Erreur 1004 !

Eh oui, c’est le danger du pouvoir d’interprétation du VBA et de la notation implicite

Code :

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

1
2
3
Range("B5").Select  'revient à écrire en mode explicite
 
Application.ActiveWorkbook.ActiveSheet.Range("B5").Select

Si tu lances ce dernier code à partir de n’importe quel module de feuille, ce sera OK.

Que se passe-t-il ? Pas de mystère, mais une logique dans un module Standard, il n’y a pas d’objet parent, le VBA applique ses règles tels que décrites dans l’aide (cf la propriété ActiveSheet de l’objet Application).
Par contre les modules objets ont eux un objet parent ((«Aide VBA : Référence à l'instance en cours d'un objet : Utilisez le mot clé Me pour faire référence à l'instance en cours de l'objet où le code est exécuté. »)
Et dans les notations vues plus haut, c’est à cet objet parent du module en cours que l’instruction se réfère, si tu lances un

Code :

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

Debug.Print Me.Name

dans un module de feuille, il te retournera le nom de la feuille concernée par ce module.
Dans notre contexte Si tu es dans le module de la feuille3 et que tu invoque la propriété Range("B5"), tu invoques en fait : Me.Range("B5"), si la feuille active n’est pas celle-ci => Select = plantage.

Au niveau des noms, en schématisant, on a le même phénomène, nous n’avons que des noms d’étendue classeur dans le cas présent.

Quand tu es dans un module Standard, le système de code implicite remonte au classeur et pas de souci.
Quand tu es dans un module de feuille, le système de code implicite remonte au niveau feuille, si le nom (d’étendue classeur, on est d’accord) fait référence à cette feuille, il trouve ses petits et c’est bon, si ce n’est pas le cas, il ne trouve pas dans son objet container de cellule sur laquelle pointer et il plante.
A partir du moment où tu deviens explicite et que tu lui dis sur quelle feuille pointer, plus de souci.
Pour ce qui est du Thisworkbook, du coup ça coule de source, un Debug.Print Me.Name chez lui te retourneras le nom du classeur comme objet parent, le classeur est lui-même l’objet parent des toutes les feuilles, il retrouve ce qu’on lui demande sans soucis.

D’où l’avantage d’être explicite pour savoir où l’on est et où l’on vise… Surtout qu’on pourrait s’amuser à se placer dans un cas où l’on ait le même nom en 4 exemplaires (1 portée classeur et 3 portées feuilles)

cordialement,

Didier