Discussion :

[Phil.D]

Bonsoir à tous et toutes,

Utiliser des noms désignant des plages de cellules facilite grandement la lecture des formules: Ex =NB.SI(VILLES;"=Bruxelles").
Problème: si le nom VILLES est supprimé (quelle qu'en soit la raison) toutes les formules utilisant ce nom affichent un message d'erreur #NOM?
Serait-il possible que lors de la suppression de tout nom de plage, celui-ci soit automatiquement remplacé, de manière transparente et dans toutes les formules du classeur,
par l'adresse de la plage correspondante ?

Ceci ne me semble pas possible depuis le menu du gestionnaire de noms; d'où le recours à VBA .

>>> FINALITE Possibilité d'envisager le cas où tous les noms du classeur seraient supprimés ??

Merci d'avance à tous ceux/celles qui voudront bien se pencher sur ce problème

Phil.D

[Invité]

Bonjour,

Regardez l'utilisation des tableaux structurés :

Pièce jointe 573893

Dans cet exemple, le tableau structuré (A1:B5) s'appelle VILLES. Si je n'ai plus VILLES, cela signifie que je n'ai plus de tableau du tout.

[Phil.D]

Salut Eric,

Dans XL 2003, les tableaux structurés n'existent pas; j'ai donc pour habitude de nommer à la fois le tableau, mais aussi les données contenues dans les différentes colonnes. (champs)
Je fais de même pour certaines variables importantes pour mes calculs.

Que faire alors en cas de suppression d'un ou de tous les noms, puisque toutes les cellules dépendantes affichent alors #NOM ! ?

il faut alors vérifier toutes ces cellules via la barre de formules qui contient encore le nom récemment supprimé, mais en cas de classeur un tant soit peu complexe, et de suppressions multiples de noms,
le travail de reconstruction** (** les références des plages sont aussi supprimées dans l'outil Audit des formules / Evaluation) peut s'avérer fastidieux; raison pour laquelle je cherchais à automatiser, au moment de la suppression,
un remplacement automatique, dans chaque cellule, du nom, par la référence de plage ... puisque, à ce moment, tout ceci est stocké dana le gestionnaires de noms

-----
Une solution alternative pourrait résider dans une macro, à utiliser préventivement, qui me permettrait de stocker dans une des feuilles, toutes les plages nommées ainsi que leurs références;
(toutefois le travail de remplacement ou de reconstruction resterait à faire en cas de suppression intempestive de noms...)

L'idée est là; malheureusement mes connaissances en vba sont un peu limitées (doux euphémisme)

Merci

[78chris]

Bonjour

La suppression pure et simple d'un nom me parait difficile à gérer de façon automatique.

Cela ne déclenche pas d'évènement particulier dans Excel, que l'on pourrait annuler

La meilleure protection reste la copie de sauvegarde...

[Invité]

(Bonjour Chris )

Effectivement sous 2003, je n'avais pas fait attention.

Le code ci-dessous liste les zones nommées. Il faut un onglet "Liste des zones nommées" :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
Sub ListerLesZonesNommees()
 
Dim ListeDesNoms As Names
Dim ShNames As Worksheet
Dim I As Long, IndexNoms As Long
 
    If ActiveWorkbook.Names.Count = 0 Then Exit Sub
 
    Set ListeDesNoms = ActiveWorkbook.Names
    Set ShNames = Worksheets("Liste des zones nommées")
 
    With ShNames
         .Range(.Cells(2, 1), .Cells(.Rows.Count, 2)).ClearContents
         I = 2
         For IndexNoms = 1 To ListeDesNoms.Count
             .Cells(I, 1).Value = ListeDesNoms(IndexNoms).Name
             If InStr(1, ListeDesNoms(IndexNoms).Value, "=@", vbTextCompare) > 0 Then
                .Cells(I, 2).Value = Mid(ListeDesNoms(IndexNoms).Value, 3)
             Else
                .Cells(I, 2).Value = Mid(ListeDesNoms(IndexNoms).Value, 2)
             End If
             I = I + 1
         Next IndexNoms
    End With
 
    Set ListeDesNoms = Nothing
    Set ShNames = Nothing
 
End Sub

Je n'ai pas testé tous les cas, mais ce code pose problème lorsqu'il s'agit de reconstruire les zones. Il faut supprimer de la liste, toutes les zones faisant référence à des filtres, zones d'édition, etc.


Le code qui regénère les zones :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
Sub RetablirLesZoneNommees()
 
Dim I As Long, DerniereLigne As Long, IndexNoms As Long
Dim Continuer As Boolean
Dim AireZonesNommees As Range
 
    With ActiveWorkbook
 
         With Sheets("Liste des zones nommées")
              DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
              Set AireZonesNommees = .Range(.Cells(2, 1), .Cells(DerniereLigne, 1))
         End With
 
         For I = AireZonesNommees.Count To 1 Step -1
             Continuer = True
             If .Names.Count > 0 Then
                For IndexNoms = .Names.Count To 1 Step -1
                    If .Names(IndexNoms).Name = AireZonesNommees(I) Then
                       Continuer = False
                       Exit For
                    End If
                Next IndexNoms
             End If
 
             If Continuer = True Then
                'Debug.Print AireZonesNommees(I)
                .Names.Add Name:=AireZonesNommees(I), RefersTo:="=" & AireZonesNommees(I).Offset(0, 1)
             End If
         Next I
 
    End With
 
    Set AireZonesNommees = Nothing
 
End Sub

[Phil.D]

Chris, Eric,

Merci pour vos interventions !
J'essaierai en début de semaine les codes fournis et vous tiendrai au jus ...

[Phil.D]

Re,

Retour comme promis : les 2 procédures ont été testées et validées
Effectivement parfois quelque petit souci avec les zones de filtre sinon tout OK
Merci pour ton aide

Comme j'aimerais mourir un peu moins idiot, pourrais-tu m'éclairer sur cette ligne par laquelle, dans mes tests,
la procédure ne passe jamais ....

Code :

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

1
2
             If InStr(1, ListeDesNoms(IndexNoms).Value, "=@", vbTextCompare) > 0 Then
                .Cells(I, 2).Value = Mid(ListeDesNoms(IndexNoms).Value, 3)

[Invité]

Comme j'aimerais mourir un peu moins idiot, pourrais-tu m'éclairer sur cette ligne par laquelle, dans mes tests,
la procédure ne passe jamais ....

Code :

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

1
2
             If InStr(1, ListeDesNoms(IndexNoms).Value, "=@", vbTextCompare) > 0 Then
                .Cells(I, 2).Value = Mid(ListeDesNoms(IndexNoms).Value, 3)

Bonjour,

Instr indique la position de =@ dans la chaine testée. Si aucune valeur n'est trouvée, Instr = 0
Mid récupère ici la chaine testée à partir du 3ème caractère. Particularité, elle n'est pas bornée ici sur le nombre de caractères à récupérer.