🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Named Range dans Excel — Names.Add, RefersTo, et pourquoi votre référence dérive

|

VBA Named Range dans Excel — Names.Add, RefersTo, et pourquoi votre référence dérive

TL;DR — Un named range est une formule stockée, pas une étiquette. Range("B2:B50").Name = "SalesData" est la façon d'en créer un en une ligne ; Names.Add Name:="TaxRate", RefersTo:="=Sheet1!$B$1" est la forme longue. Les deux choses qui piègent tout le monde : RefersTo a besoin d'un = en tête et de signes $ absolus (retirez le $ et le nom dérive silencieusement vers une autre cellule à chaque usage), et un nom a une portée — le classeur entier ou une seule feuille. Lisez un nom avec Range("TaxRate").Value ou le raccourci [TaxRate].

' Une ligne : portee classeur, absolu - le cas courant
Range("B2:B50").Name = "SalesData"

' Forme longue, avec une portee explicite et une formule RefersTo
ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"

Debug.Print Range("TaxRate").Value          ' lit la valeur pointee par le nom
Range("SalesData").Interior.Color = vbYellow ' utilise le nom comme n'importe quel Range

Tout le monde se tourne vers les named ranges pour arrêter de disséminer Range("$B$2") dans une macro, puis se heurte à une série de petits mystères : le nom renvoie à la mauvaise cellule, ou à du texte littéral, ou le même nom signifie deux choses différentes sur deux feuilles, ou un nom survit à une colonne supprimée et empoisonne chaque formule qui l'utilisait. Tous viennent d'une seule idée à garder en tête : un nom est une formule RefersTo stockée qu'Excel réévalue à chaque usage — donc les règles qui gouvernent les formules (le = en tête, les $ absolus, le qualificateur de feuille) sont exactement les règles qui gouvernent les noms.

Ce que vous allez apprendre

  • Le modèle mental — un nom est une formule stockée, pas un surnom
  • Créer un nom — le Range.Name en une ligne contre Names.Add avec RefersTo
  • Pourquoi $ décide entre un nom stable et un nom qui dérive avec la cellule active
  • La portée — le classeur entier contre une seule feuille, et la collision qu'elle provoque
  • Lire et utiliser un nom, et les noms constantes qui n'ont aucune cellule
  • Nettoyer les noms #REF! et le gonflement de noms cachés qu'ils laissent derrière eux

Le modèle mental : un nom est une formule stockée

Quand vous créez un named range, Excel n'étiquette pas la cellule. Il stocke une formule RefersTo et la résout chaque fois que le nom apparaît. SalesData n'est pas « la cellule B2:B50 » figée sur place — c'est la formule =Sheet1!$B$2:$B$50, évaluée à la demande. Lisez Range("SalesData") et Excel exécute cette formule et vous rend ce qu'elle résout maintenant.

C'est pourquoi tout ce qui suit revient en réalité à écrire correctement la formule RefersTo :

ThisWorkbook.Names.Add Name:="SalesData", RefersTo:="=Sheet1!$B$2:$B$50"
Debug.Print ThisWorkbook.Names("SalesData").RefersTo   ' =Sheet1!$B$2:$B$50

RefersTo est une chaîne qui doit commencer par =, exactement comme une saisie dans le Gestionnaire de noms. Omettez le = — RefersTo:="Sheet1!$B$2" — et vous n'obtenez pas d'erreur ; vous obtenez un nom qui renvoie au texte littéral Sheet1!$B$2, ce qui n'est presque jamais ce que vous vouliez. Gardez en tête « c'est une formule » et le = cesse d'être un mystère.

Créer un nom : la version en une ligne et la forme longue

Pour le cas courant — un nom absolu, porté sur le classeur — affectez .Name à une plage et c'est terminé :

Range("B2:B50").Name = "SalesData"      ' portee classeur, absolu, une ligne

Tournez-vous vers Names.Add quand vous devez définir la portée explicitement, référencer un nom par formule, ou stocker une constante plutôt que des cellules :

ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"   ' portee classeur
Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9" ' portee feuille
ThisWorkbook.Names.Add Name:="VAT", RefersTo:="=0.2"              ' une constante, sans cellules

Les deux conviennent ; la différence, c'est le contrôle. Range.Name = est le plus rapide et correct pour une plage simple ; Names.Add est ce que vous utilisez dès que la portée ou une cible autre qu'une plage compte.

Pourquoi $ décide entre un nom stable et un nom qui dérive

Voici le bug de named range que personne n'arrive à expliquer : « mon nom pointe vers une cellule différente à chaque exécution de la macro ». La cause est un nom relatif — un RefersTo sans signes $.

' DERIVE - reference relative, resolue par rapport a la cellule ACTIVE
ThisWorkbook.Names.Add Name:="Prev", RefersTo:="=Sheet1!A1"

' STABLE - reference absolue, toujours la meme cellule
ThisWorkbook.Names.Add Name:="Anchor", RefersTo:="=Sheet1!$A$1"

Un nom relatif est stocké par rapport à l'endroit où se trouvait la cellule active au moment de sa création, et Excel le réancre sur la cellule active à chaque usage — si bien que Prev peut résoudre en A1, puis D5, puis Z99, selon la sélection. Les noms relatifs sont une vraie fonctionnalité, parfois utile (un nom signifiant « la cellule juste à gauche »), mais si vous ne l'avez pas fait exprès, cela ressemble à un phénomène de hantise. Mettez $ sur la colonne comme sur la ligne sauf si vous voulez précisément que le nom se déplace. Avec Range.Name =, vous obtenez de l'absolu automatiquement — une raison de plus d'en faire le choix par défaut le plus sûr.

La portée : le classeur entier contre une seule feuille

Chaque nom vit dans une portée. ThisWorkbook.Names.Add (et Range.Name =) crée un nom porté sur le classeur, visible de partout. Worksheets("Jan").Names.Add crée un nom porté sur la feuille, visible uniquement sur cette feuille — ce qui permet à Jan et Feb d'avoir chacune leur propre nom Region pointant vers leurs propres données.

Le piège, c'est de les confondre :

Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9"
' Depuis un module standard, ceci ne voit PAS le nom local a la feuille de facon fiable :
' Debug.Print Range("Region").Address   ' peut echouer ou toucher un autre Region
Debug.Print Worksheets("Jan").Range("Region").Address   ' qualifiez-le -> fonctionne

Un nom porté sur la feuille doit être atteint via sa feuille — Worksheets("Jan").Range("Region") — et non avec un Range("Region") nu depuis un module. Décidez délibérément : une constante unique pour tout le fichier (TaxRate) relève de la portée classeur ; une région qui se répète par feuille (Region sur chaque onglet mensuel) relève de la portée feuille, et vous la qualifiez à chaque fois. Voir VBA Worksheet pour l'adressage des feuilles.

Lire, utiliser, et le piège du nom constante

Un nom de plage se comporte comme n'importe quel Range. Lisez-le, écrivez-le, formatez-le :

Range("TaxRate").Value = 0.19            ' ecrit dans la cellule nommee
Debug.Print Range("SalesData").Cells.Count
Set rng = ThisWorkbook.Names("SalesData").RefersToRange  ' l'objet Range

Range("TaxRate").Value lit la valeur ; [TaxRate] est un raccourci pour la même chose (c'est Evaluate("TaxRate")). Mais attention à la dernière ligne ci-dessus : .RefersToRange ne fonctionne que lorsque le nom renvoie à des cellules. Si vous avez stocké une constante — Names.Add Name:="VAT", RefersTo:="=0.2" — il n'y a aucune plage derrière, donc Range("VAT") et .RefersToRange déclenchent une erreur, tandis que [VAT] et Evaluate("VAT") renvoient correctement 0.2. Sachez de quel type de nom vous disposez avant de le traiter comme des cellules. Pour la façon dont un nom se résout en formule, voir VBA Formula.

Nettoyer les noms #REF! et le gonflement de noms cachés

Supprimez les lignes ou colonnes qu'un nom couvre et le nom ne meurt pas — son RefersTo devient =#REF!, une mine active qui casse chaque formule référençant le nom. Les noms voyagent aussi quand vous copiez une feuille, s'accumulant discrètement jusqu'à ce qu'un classeur transporte des milliers de noms cachés et cassés, et rame. Voilà pourquoi auditer les noms est une tâche de maintenance, pas une configuration ponctuelle :

Dim nm As Name
For Each nm In ThisWorkbook.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then
        Debug.Print "broken: " & nm.Name & " -> " & nm.RefersTo
        nm.Delete                      ' retire la mine
    End If
Next nm

Parcourez ThisWorkbook.Names, repérez ceux dont le RefersTo contient #REF!, et faites Delete. Lancez la même boucle avec nm.Visible = False dans la condition pour trouver les noms cachés qu'une feuille collée a entraînés avec elle. Un nom est peu coûteux à créer et facile à oublier — traitez la liste des noms comme quelque chose que vous nettoyez, pas seulement que vous remplissez.

Comment ExcelMaster aide

Les erreurs de named range qui coûtent vraiment du temps ne sont pas des fautes de frappe — c'est le nom relatif qui dérive parce qu'un $ manquait, le nom porté sur la feuille qu'un module ne voit pas, le .RefersToRange qui explose sur un nom constante, et le nom #REF! que personne n'a remarqué jusqu'à ce qu'un rapport parte faux. Chacune s'exécute ; elle se résout simplement au mauvais endroit.

ExcelMaster écrit les noms comme le ferait un développeur soigneux. Demandez-lui de « nommer la cellule du taux de taxe et de l'utiliser dans le calcul », et il crée un nom absolu, porté sur le classeur, le référence partout au lieu de coder en dur $B$1, et ne choisit la portée feuille que lorsque la région se répète réellement par onglet. Il lit les valeurs nommées avec l'appel adapté aux noms de plage contre les noms constantes, et il peut auditer et effacer les noms #REF! cassés qu'un classeur a accumulés. Vous nommez ce que vous voulez dire ; il câble la référence pour qu'une ligne insérée ne la casse jamais en silence.

Questions fréquentes

Comment créer un named range en VBA ?

Le plus court est d'affecter la propriété Name d'une plage : Range("B2:B50").Name = "SalesData", ce qui crée un nom absolu porté sur le classeur. Pour plus de contrôle, utilisez Names.Add : ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1". La chaîne RefersTo doit commencer par =, et vous voulez presque toujours des références $ absolues pour que le nom ne dérive pas.

Quelle est la différence entre la portée classeur et la portée feuille pour un nom ?

Un nom porté sur le classeur (ThisWorkbook.Names.Add ou Range.Name =) est visible depuis chaque feuille et chaque module. Un nom porté sur la feuille (Worksheets("Jan").Names.Add) n'est visible que sur cette feuille, si bien que des feuilles différentes peuvent réutiliser le même nom pour leurs propres données. Atteignez un nom porté sur la feuille via sa feuille : Worksheets("Jan").Range("Region"), pas un Range("Region") nu.

Comment obtenir la plage à laquelle un nom fait référence en VBA ?

Utilisez ThisWorkbook.Names("SalesData").RefersToRange pour obtenir l'objet Range, ou simplement Range("SalesData") pour un nom porté sur le classeur. Pour lire sa valeur, Range("SalesData").Value ou le raccourci [SalesData]. .RefersToRange ne fonctionne que pour les noms qui pointent vers des cellules — un nom constante comme =0.2 n'a aucune plage et doit être lu avec Evaluate ou [Name].

Pourquoi mon named range pointe-t-il vers #REF! en VBA ?

Parce que les cellules auxquelles il faisait référence ont été supprimées. Supprimer les lignes ou colonnes qu'un nom couvre ne supprime pas le nom ; à la place, son RefersTo devient =#REF!, et chaque formule utilisant le nom se casse. Auditez-les en parcourant ThisWorkbook.Names et en vérifiant si nm.RefersTo contient #REF!, puis appelez nm.Delete sur les noms cassés.

Comment supprimer un named range en VBA ?

Appelez .Delete sur le nom : ThisWorkbook.Names("SalesData").Delete, ou parcourez ThisWorkbook.Names et supprimez selon une condition (par exemple, tous ceux dont le RefersTo contient #REF!). Supprimer le nom ne touche pas les cellules ; cela ne retire que le nom défini, et c'est ainsi que vous éliminez les noms cassés et cachés qui s'accumulent quand des feuilles sont copiées.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1 — vérifié le 01/09/2026.

Guides connexes : VBA Range · VBA Cell Value · VBA Formula · VBA Worksheet · VBA Offset