Excel VBA: Workbook-scoped, feuille de travail dépendante nommée formula / named range (les changements de résultat dépendent de la feuille de travail active))

modifier: Titre modifié pour plus de clarté.

résumé rapide: je veux savoir si le comportement des formules nommées dépendantes du classeur et de la feuille de travail (que je décrirai ci-dessous) est une caractéristique documentée dans Excel. Si c'est le cas, s'il vous plaît pointez - moi dans la direction d'une certaine documentation quelque part-je ne semble pas trouver quoi que ce soit à ce sujet en ligne (peut-être que j'utilise de mauvais termes de recherche..?) et ne veulent pas utiliser quelque chose qui est en fait un bug et pourraient disparaître dans une version ultérieure!

Strictement parlant, ce n'est pas vraiment une question à propos de VBA; cependant, nommé formules sont quelque chose de moi et les autres utiliser dans un code VBA tout le temps , de sorte qu'il est encore applicables en la matière, je pense.

EDIT: notez que le code VBA ci - dessous peut ne pas être exactement correct-Je ne l'ai pas testé.

Méthode Régulière

Pour les sciences ou en génie calculs j'ai souvent dû utiliser la même formule ou la même fourchette à plusieurs reprises dans le même cahier de travail, mais sur des feuilles de travail différentes. Comme un exemple simplifié, je pourrais mettre en œuvre quelque chose comme ceci pour la zone d'un cercle:

Dim sht as Worksheet
For Each sht In ThisWorkbook
    Call sht.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*'" & _
            sht.Name & "'!Radius^2")
Next sht

, ce qui donne l'ensemble suivant de fourchettes/formules nommées (portées sur chaque feuille de travail):

=PI()*Sheet1!Radius^2        <--- scoped to Sheet1
=PI()*Sheet2!Radius^2        <--- scoped to Sheet2
etc. etc.

cela fonctionne bien sûr, mais il a l'inconvénient majeur d'être difficile à faire futur changement. Ainsi, par exemple, si la formule change (la zone d'un cercle ne va pas changer de cours! Mais les formules dans le code de conception de l'autoroute LRFD D'AASHTO, par exemple, changent presque toutes les éditions!), J'ai modifier chaque seule instance de chaque Nom unique Formule. C'est fastidieux, même si j'écris une procédure VBA pour le faire pour moi.

Méthode Alternative

j'ai découvert ci-dessous sur accident dans Excel 2013 l'autre jour, et n'ont pas été en mesure pour trouver quoi que ce soit à ce sujet n'importe où en ligne. Ce qui me fait hésiter à commencer à l'utiliser.

disons que je lance la ligne de code suivante à la place:

Call ThisWorkbook.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*!Radius^2")

, ce qui donne la gamme/formule simple suivante (portée dans le classeur):

=PI()*!Radius^2 <--- la formule est inscrite dans le cahier D'exercices; la note !Radius , et non Radius .

noter que ce N'est pas la même chose que la suivante (il n'y a pas de point d'exclamation):

=PI()*Radius^2 <--- notez que ici, Radius est scoped au cahier d'exercices.

maintenant, AreaCircle produira le même comportement que la première méthode ci-dessus: il produira un résultat basé sur la valeur locale, définie par feuille de travail de rayon. Donc s'il y a deux plages nommées appelées Radius (une pour Sheet1 et une pour Sheet2 ), AreaCircle sera calculé en fonction de la valeur de Radius dans la feuille, dans lequel il est utilisé. Et avec l'avantage supplémentaire que je n'ai plus d'ajouter une nouvelle version de ce (et tous les autres!) formule chaque fois que j'ajoute une nouvelle feuille de travail (C'est énorme!).

c'est un comportement difficile à décrire; si vous êtes confus par ma description, Vous pouvez faire les étapes suivantes pour recréer ce comportement:

1. dans un classeur, créez deux ou plusieurs feuilles de travail et inscrivez " 1 "dans la case A1 de Sheet1 , "2" dans la cellule A1 Feuil2 "3" dans la cellule A1 de Sheet3, etc etc.
2. créer une gamme nommée appelée CellA1 (avec Champ du classeur) et entrer ce qui suit pour la formule: =!$A
3. entrer =CellA1 dans n'importe quelle cellule donnera" 1 "sur Sheet1 , donnera" 2 "sur Sheet2 , etc.

de la Documentation?

Hé, vous avez fait - merci pour coller avec moi ici!

donc, comme je l'ai dit plus haut, quelqu'un peut-il me diriger vers la documentation pour cette"fonctionnalité"? J'aimerais commencer à mettre en œuvre ceci dans certains de mes projets les plus compliqués; si rien d'autre, il fera juste le Gestionnaire de noms environ 20 fois plus facile à naviguer (sans tous les noms dupliqués).