Accéder à une plage nommée en VBA sans spécifier l'onglet

Bonjour,

J'ai 500 feuilles (ah non, commencez pas à me dire que c'est trop, qu'il faut revoir l'organisation des données ... c'est pas vrai bien sûr, juste un cas d'école et pour vous faire réagir)

et un nom d'une plage

  • LaZone1 définie classiquement
  • LaZone2 définie dynamiquement

En VBA, je peux les trouver par ThisWorkbook.Names("LaZone1") ou ThisWorkbook.Names("LaZone2"), donc je sais qu'elles existent dans le classeur sans spécifier le nom de l'onglet.

Ma question est : comment appeler la valeur sans avoir à spécifier le nom de l'onglet (parmi les 500 )dans laquelle elle se trouve ? car je ne peux pas utiliser Range("LaZone1") sans y mettre l'onglet ... je suis perplexe !

Bonjour Steelson,

J'ai peux-être une piste pour toi:

ThisWorkbook.Names("LaZone1").RefersToRange, ça te renvera l'objet Range de la zone

J'ai fait un exemple rapide pour colorer toutes les cellules d'une zone nommée, que j'appelle via referstorange:

49test-zone.xlsm (13.32 Ko)

Tout ce dont tu as besoin se trouve dans les membres de l'objet d'un objet qui s'appelle "Name", tu trouveras toutes les infos sur l'aide Office, tu peux avoir les références, la valeur, les cellules...

Merci Ausecour,

comment j'ai pu passer à côté de cela

en effet ThisWorkbook.Names("test").RefersToRange.Value me donne la valeur

(j'ai réduit test une cellule pour tester !)

je vais l'intégrer à ma liste de choix avant de clore le sujet !

De rien

Tu peux peut-être même mettre directement .Value au lieu de referstorange.value, à essayer, je n'ai pas testé...

Quand tu veux dire liste de choix tu veux parler de la liste des sujets en favoris? Je ne savais même pas qu'on avait cette option! Je vais peut-être mettre quelques sujets dedans, ça peut servir...

non, je veux dire par exemple :

Dim liste as Range
Set liste = Sheets("paramètres").Range("test")
ListBox1.List = liste.Value

mais je voudrais ne pas spécifier "paramètres"

Ah d'accord,

Du coup en mettant:

Dim liste as Range
Set liste = ThisWorkbook.Names("test").RefersToRange
ListBox1.List = liste.Value

ça fonctionnera

Par liste de choix je pensais que tu disais liste de choix de sujet... pas liste de choix de plage de cellules...

je viens d'essayer avec

ListBox1.List = ThisWorkbook.Names("MaListe").RefersToRange.Value

c'est ok

il faut mettre Value

merci beaucoup

23test-zone.xlsm (17.93 Ko)

Ah oui en effet, sinon ça plante si on ne met pas value, ou qu'on met juste Value sans mettre RefersToRange, c'est bon à savoir

Curieusement, il me reste une difficulté en intégrant dans un fichier plus large ... je vais investiguer !

bonjour à vous

essaye aussi Power Query (dont c'est le job après tout)

amitiés et meilleurs voeux

Bonjour,

@ jmd,

Une nouvelle fois tu interviens dans un sujet et tes propos sont hors-sujet.

Il faut que tu lises l'ensemble des messages.

Quel est le job de Power query dans ce cadre ?

Quand tu interviens dans un sujet, apporte des solutions concrètes, plutôt que d'envoyer vers des liens (que tu ne maîtrises pas).

Cdlt.

Curieusement, il me reste une difficulté en intégrant dans un fichier plus large ... je vais investiguer !

et voici pourquoi je n'ai pas réussi l'intégrer dans une application plus large

https://docs.microsoft.com/fr-fr/office/vba/api/excel.name.referstorange

Si l'objet Name ne se réfère pas à une plage (par exemple, s'il se réfère à une constante ou une formule), cette propriété échoue.

Bonjour Steelson,

Ceci explique cela! J'espère que tu t'en sortiras quand même...

Oui,

merci de ton aide du reste

et je comprends pourquoi j'étais "passé à côté" car ma zone était nommée dynamiquement

je n'ai pas de besoin en tant que tel pour moi, cela fait partie de mes "investissements" en excel et des modules que je mets en réserve

comme Jean-Eric le suggère, je recopie ici le message privé que je lui ai envoyé :

salut Jean-Eric

mes interventions : j'interviens avec du bon sens.

dans le fil de Steelson, je réponds donc un peu à côté, normal puisque la question n'existe pas (de l'aveu de Steelson)

quant à fournir des solutions toutes mâchées, je le fais si j'en ai le temps

aucune règle dans aucun forum n'oblige à répondre uniquement avec des solutions "exactes"

j'ai une autre qualité : j'ouvre le champ d'application d'Excel, au travers de PQuery, PBI, et j'espère bientôt Micorsoft Flow et les PowerApps.

Excel évolue, bien plus vite qu'on ne pense.

voili, voilou

amitiés excelliennes et meilleurs vœux

note : je m'abstiendrai également de t'envoyer un MP non sollicité. Je n'avais pas vu l'inscription en pied de ta signature, Mille excuses.

Bon travail, bon apprentissage et amitiés à tous

Bonjour,

Tu peux même simplifier :

ListBox1.List = Names("LaZone1").RefersToRange.Value

Bonjour

peux être plus simple

    ListBox1.List = Range("MaListe").Value

A+

Maurice

Tu peux même simplifier :

ListBox1.List = Names("LaZone1").RefersToRange.Value

Bien sûr ... mais cela ne fonctionne pas sur un nom de zone dynamique =DECALER(__________)

@jmd

la question n'existe pas (de l'aveu de Steelson)

Non, la question était bien réelle et notée en gras :

Ma question est : comment appeler la valeur sans avoir à spécifier le nom de l'onglet (parmi les 500 )dans laquelle elle se trouve ?

elle ne portait pas sur les 500 feuilles qui était une boutade ...

Ausecour, Theze et Archer on bien pris le sujet !

j'ouvre le champ d'application d'Excel, au travers de PQuery,

C'est en effet un plus, et j'espère qu'il évoluera car j'ai rencontré quelques limitations dans son potentiel ... essaie juste d'en tirer des informations sur cette page ! ce que j'ai fait pour un ami du reste ... par des méthodes classiques.

peux être plus simple

    ListBox1.List = Range("MaListe").Value
en effet, merci Maurice, à force de tourner en rond on oublie de faire simple

mais ... cela dépend d'où est appelée cette Range

dans un userform c'est ok

dans une procédure événementielle cela ne fonctionne pas, et c'est là qu'il faut passer par Names ... à condition que la zone ne soit pas dynamiquement définie !

en tous cas merci pour ton intervention

Rechercher des sujets similaires à "acceder plage nommee vba specifier onglet"