Attribution d'un nom à une plage ou même à un objet

Une première manière de procéder

- Sélectionner l'objet ou le tableau (en entier) à nommer,
- Tapez le nom dans la Zone Nom (située à l'extrémité gauche de la barre de formule),
 puis appuyez sur ENTRéE pour attribuer rapidement un nom à une cellule ou une plage sélectionnée,
- Cliquez dans la Zone Nom sur le nom de l'objet pour l'atteindre.
(Excel se déplace  sur la feuille où figure l'élément choisi). Une seconde manière : Insertion -> Nom -> Définir

Instructions sur l'attribution d'un nom aux cellules, formules et constantes dans Microsoft Excel (d'isabelle)

-Le premier caractère d'un nom doit être une lettre ou un caractère de soulignement.
-Les autres caractères du nom peuvent être des lettres, des nombres, des points et des caractères de soulignement.
-Les noms ne peuvent être identiques à des références de cellules, telles que Z$100 ou L1C1.
-Les espaces sont interdits. Vous pouvez utiliser des caractères de soulignement ou des points comme séparateurs de mots, par exemple Premier.Trimestre ou Taxe_Ventes.
-Un nom peut compter jusqu'à 255 caractères.
-Les noms peuvent contenir des majuscules et des minuscules. Microsoft Excel ne fait pas de distinction entre les majuscules et les minuscules des noms.
Par exemple, si vous avez créé le nom Ventes puis créé l'autre nom VENTES dans le même classeur, le second nom remplace le premier.

!!!! Utiliser un nom pour référence à une valeur constante dans une formule

     - Insertion -> Nom -> Définir un nom
     - Nom : par exemple, vitessautoroute
     - Ce nom fait référence à : ="130"
     - Valider par "Ajouter"
     - Dans une cellule, pour calculer le nombre d'heures de déplacement pour 450 kms, entre Paris & Lyon :
        =>     formule =450/vitessautoroute

Ajouter un nom par VBA: activeworkbook.names.add name:="hello", ReferstoR1C1:="=Feuil1!R5C5"
Zone de noms cachés : très bonne page ici : http://longre.free.fr
Commande Définir un nom dans le menu contextuel : news, par Frédéric S.
Conseils pour créer des noms : news

Manipulation d'une plage nommée

Si vous voulez redéfinir un champ nommé :

1) Déroulez le menu Insertion ;
2) Pointez sur Nom et cliquez sur Définir ;
3) Sélectionnez un des noms ;
4) Dans la zone Fait référence à, changez le champ ;
5) Cliquez sur le bouton Ajouter ;
6) Changez d'autres références au choix ;
7) Cliquez sur le bouton OK.

Atteindre une cellule nommée/objet : application.goto reference:="hello"

Masquer un nom : Names("VotreNom").Visible = True / False

!!!!! Définir une plage de cellules variables/dynamique, par Bernie Deitrick (dynamic named range)
=DECALER(Feuil1!$B$1;0;0;NBVAL(Feuil1!$B:$B);1)
=OFFSET(Sheet1!$B$1,0,0,counta(Sheet1!$B:$B),1)

Supprimer les noms d'un classeur : news
Supprimer ce nom : activeworkbook.names("toto").delete

Effacer les noms invalides - où les liens existent plus - (#N/A) : news
(delete all invalid range names - where the links no longer exist)

Comparer deux listes de cellules et mettre à jour une des listes: news

Nommer une plage (la sélection) en VBA : ActiveWorkbook.Names.Add Name:="zaza", RefersTo:=Selection.Address

Définir la zone d'impression : Union(range("range1"), range("range2")).name = "Print_Area"

Nom d'une zone non adjacente / discontinue

Range("A1,C1,E1").Name = "One" :
Debug.Print Range("One").Address 'pour afficher l'adresse dans la fenêtre d'exécution, ce qui donne : $A$1,$C$1,$E$1
Debug.Print Range("One").Offset(0, 1).Address '=> donnerait $B$1,$D$1,$F$1

With Range("A:D")
Range("A1:D" & .Find("*", .Item(1), , , , xlPrevious).Row).Select
ActiveWorkbook.Names.Add Name:="zaza", RefersTo:=Selection.Address
End With

une fonction : news
autre méthode :
Sub Testname()
dim rng As range
for each definedname In thisworkbook.names
On Error Resume next
Set rng = Nothing
Set rng = definedname.Referstorange
if Not rng Is Nothing then
if Not Intersect(rng, activecell) Is Nothing then
msgbox activecell.address & " is in a range named: " & definedname.name
end if
end if
next
end Sub autre méthode :
on error resume next : worksheets("Sheet1").range("A1").name.name 'retourne le nom de la zone s'il existe, une erreur sinon

Sub test()
dim nameInBook As name
dim test As range
for each nameInBook In activeworkbook.names
Set test = application.Intersect(nameInBook.Referstorange,
selection)
if Not test Is Nothing then
msgbox "Part of " & nameInBook.name
end if
next
end Sub

La macro SelectRange ci-dessous permet de sélectionner le cas échéant la plage de cellules nommée auquel appartient la cellule active, elle fait appel à la fonction CellInNamedRange

Public Sub SelectRange()

Dim RngName As String
Dim R As Range
Set R = ActiveCell
Dim Msg As String

Msg = "Active Cell Is Not In A Named Range."
RngName = CellInNamedRange(R)
If RngName <> "" Then
Range(RngName).Select
Msg = "Range: " + RngName + " Selected."
End If

Application.StatusBar = Msg

End Sub

Public Function CellInNamedRange(Rng As Range) As String

Dim N As Name
Dim C As Range
Dim TestRng As Range
On Error Resume Next

For Each N In ActiveWorkbook.Names
Set C = Nothing
Set TestRng = N.RefersToRange
Set C = Application.Intersect(TestRng, Rng)
If Not C Is Nothing Then
CellInNamedRange = N.Name
Exit Function
End If
Next N
CellInNamedRange = ""

End Function

Private Sub Worksheet_Change(ByVal Target As Excel.range)
if Target.address = "$C$4" then
for each cellule In sheets("Feuil2").range("Liste")
if Target.value = cellule.value then
msgbox "Déjà dans Liste"
end if
next
end if
end Sub

Vous voulez incrémenter une valeur et la cacher afin qu'elle ne soit pas facilement accessible ? Alors, regardez l'exemple ci-dessous, constitué de 2 procédures et une fonction. Exemple proposé le 21 Novembre 1999 par Robert Rosenberg, conversation "How to save a variable before close?" du groupe de discussion microsoft.public.excel.programming.

La macro "StoreName" exécute la fonction szStoredName pour créer et cacher le nom appelé "StoredString", la macro "RetrieveName" l'exécute pour en récupérér la valeur.

Sub StoreName()
Const szName As String = "StoredString"
szStoredName szName, "hello" 'When you provide the second argument, the function will store
MsgBox ThisWorkbook.Names(szName).RefersTo
End Sub

'''This routine retrieves the value
Sub RetrieveName()
MsgBox szStoredName("StoredString") 'Note: Not providing the second argument will make the function retrieve the string value
End Sub

'szName : The name of the range name you want to create/retrieve the string from.
szStore : The string you want to store in the range name.
' If you provide this second argument, the function willl create & hide the name using szStore as the value you want to store
' If you do not provide this second argument, the function will retrieve the string value from the name.

Function szStoredName(szName As String, Optional ByVal szStore As String) As String
Dim nm As Name
Dim szRefersTo As String

'''If you provided a value in szStore, create the name & hide it
If Len(szStore) Then

'''Pull out the equal sign if it was included in szStore
szRefersTo = szStore
If Left(szStore, 1) = "=" Then szRefersTo = Right(szStore, Len(szStore) - 1)

Set nm = ThisWorkbook.Names.Add(Name:=szName, RefersTo:="=" & szRefersTo)
nm.Visible = False
Else 'The function will assume the name is already there & you want to retrieve the value

'''Test whether the name exists
On Error Resume Next
Set nm = ThisWorkbook.Names(szName)
On Error GoTo Error

'''If it exists, the function will return the value
If Not nm Is Nothing Then
szStoredName = Right(nm.Value, Len(nm.Value) - 1)
Else
Err.Raise 9999, "Excel name does not exist"
End If
End If
Exit Function
Error: MsgBox Err.Number & vbCrLf & Err.Description
End Function

Lister les noms définis dans un classeur

Pour cela, il suffit d'une instruction - activecell.listnames -ou de passer Insertion -> nom -> Coller -> Liste

Vous pouvez également boucler sur la collection Names

Sub Nom_Liste1()
cells.Listnames
end Sub

Sub Nom_Liste2()
dim nr As name
dim x As Integer
x = 1
for each nr In names
cells(x, 1) = nr.name
cells(x, 2) = nr
x = x + 1
next nr
end Sub

Sub Tester1()
for each nme In thisworkbook.names
Set rng = nme.Referstorange
msgbox nme.name & " refers to sheet " & rng.Parent.name
next
end Sub

Insérer le nom du fichier et de l'onglet

1^ère méthode : écrivez les fonctions ci-dessous suivantes dans une feuille de code, vous pouvez alors sélectionner ces deux fonctions dans le menu "fx" à "fonctions personnalisées".

function nomOnglet()
nomOnglet = activesheet.name
end function

function nomFichier()
nomFichier = activeworkbook.name
end function

Nom du classeur : activeworkbook.name
Nom complet du classeur : activeworkbook.fullname

2^ème méthode : par l'intermédiaire de formules "existantes" toutes très longues et toutes valables !!

1. Chemin d'accès complet : "C:\Temp[Test.xls]Feuil1"
=CELLULE("filename";A1)

2. Répertoire : "C:\Temp"
=GAUCHE(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1))-2)

3. Nom du classeur seul : "Test.xls"
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1))+1;
SOMME(TROUVE({"[";"]"};CELLULE("filename";A1))*{-1;1})-1)

4. Nom de la feuille : "Feuil1"
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
=STXT(CELLULE("filename";A62);TROUVE("]";CELLULE("filename";A62))+1;NBCAR(CELLULE("filename";A62))-TROUVE("]";CELLULE("filename";A62)))

5. Répertoire + classeur : "C:\Temp\Test.xls"
=SUBSTITUE(GAUCHE(CELLULE("filename";A1);TROUVE("]"; CELLULE("filename";A1))-1);"[";"")

6. Nom du classeur et de la feuille : "[Test.xls]Feuil1"
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1));300)

Liste de noms personnalisés

Comment lister les noms contenus dans la liste personnalisé n°4

listarray = application.GetCustomListContents(1)
for i = LBound(listarray, 1) to ubound(listarray, 1)
worksheets("sheet1").cells(i, 1).value = listarray(i)
next i

Connaître le nombre de listes personnalisées : MsgBox "There are currently " & Application.CustomListCount & _ " defined custom lists." '(y compris celles que vous avez vous-même définies)

Ajouter une liste personnalisée : Application.AddCustomList Array("cogs", "sprockets", _ "widgets", "gizmos")
Connaître le numéro d'une liste : n = Application.GetCustomListNum(Array("cogs", "sprockets", _ "widgets", "gizmos")): Application.DeleteCustomList n

Comment partager les listes personnalisées

Soit :

- Accéder et copier le fichier où la configuration de l'interface et les barres d'outils sont stockées (pour excel 95 & supra).
- Ouvrir la base de registre pour lire leur paramétrage à cet endroit (l'un ou l'autre)
=> hkey current user / software /microsoft /office / 8.0 / excel /microsoft excel
=> hkey current user / software /microsoft /office / 9.0 / excel /options
=> exporter un fichier de la base en cochant la branche sélectionnée
supprimer les lignes du fichiers commençant par de sguillements sauf celle contenant le nom du fichier exporté puis réimporter.