Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Names - Where are they referenced?

Tags:

excel

vba

I have defined a large number of named cells on one worksheet of a workbook. Each name may be used multiple times (or never) on that worksheet or others in the workbook. Is there a way to get a list of the where else in the workbook the names are used?

For example, lets say I define the name Fred for a cell on Sheet1. There is an equation on sheet2, cell B6 that is =Fred*6. How do I get a function to return Sheet2!B6 and any other places that Fred is used?

I hope this is clear enough. :)

like image 239
user3352364 Avatar asked Feb 25 '14 17:02

user3352364


People also ask

How do you reference a name in VBA?

Create a Name Range using VBA Define the workbook where you want to create the named range. Use the names property and then further add method. Specify the name in the “Name” argument. Refer to the range using the “ReferTo” argument.

How do I find references in Excel VBA?

The VBA reference list (figure 1) can be accessed by the VBE menu Tools > References sequence. The Available References list displays each reference item by its description property, in priority order.

How do I find named references in Excel?

You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard. In the Go to box, double-click the named range you want to find.


1 Answers

The free version of JKP and my Name Manager Addin can show you where a particular name is used.
You can download this addin from http://www.decisionmodels.com/downloads.htm
When I wrote the FastExcel extended version of Name Manager I added a Name Map facility that shows you a count of where the names are used (or not) by Worksheet and Other Names.
This is done by parsing out the Names from all the formulas on all the worksheets and the formulas in the Names Refers-tos (recursively), using a special-purpose parser and evaluating cases like the use of INDIRECT.

like image 88
Charles Williams Avatar answered Oct 01 '22 07:10

Charles Williams