Author: Debra Dalgleish

You probably use defined names in some of your Excel workbooks. We’ll look at a built-in way to list the names in a workbook, and see some Excel VBA code that creates a more detailed list of names.

Using Names in Excel

You can name a group of cells, and use that name as the source for a data validation drop down list.

For example, if you have entered a list of the products that you sell, you could select the list, and name the range reference as ProdList.

Then, that product list could be used in an order form.

Table Names

If you created Excel Tables, in Excel 2007 or Excel 2010, they are automatically named.

Later, you can change the names to something meaningful, such as ProdTable, for a list of products and their prices.

Create a List of Names

If you’re working on a complex Excel workbook, it’s easy to lose track of what you’ve named, and where the named ranges are located.

For reference, you can print out a list of names, using a built-in feature in Excel.

To paste a list of workbook level names in Excel:

• Insert a blank worksheet
• On the Excel Ribbon, click the Formulas tab
• In the Defined Names group, click Use in Formula, and click Paste Names (the keyboard shortcut is F3)

Paste Name Dialog Box

Next, in the Paste Name window, click the Paste List button.

Names List on Worksheet

A list of defined names and their formulas is pasted into the worksheet.

Note: To see worksheet level names, use the Paste List feature on the worksheet where those names are defined.

Create Names List with Excel VBA Macro

The built-in names list feature is helpful, but if you need more details, you can create your own list, by using Excel VBA.

This macro adds a new sheet to the active workbook, with a list of the non-hidden defined names, with details for each name, if available.

• A – Name;
• B – Refers To formula;
• C – Number of cells in the range;
• D – Sheet name where range is located;
• E – Address on worksheet;
• F – Scope (workbook or worksheet)

Sub ListAllNames()
Dim lRow As Long
Dim nm As Name
Dim wb As Workbook
Dim ws As Worksheet
Dim wsL As Worksheet
Dim wsName As String
Dim shName As String
Dim myName As String
Dim nmRef As String
Dim nmAddr As String
Dim nmRng As Range
Dim nmSc As String
Dim lCells As Long
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set wsL = Worksheets.Add

wsName = ws.Name

With wsL
    .Range("A1:F1").Value = Array("Name", _
        "Refers To", "Cells", "Sheet", "Address", "Scope")
    lRow = 2
End With

On Error Resume Next
  For Each nm In wb.Names
    If nm.Visible Then
      Set nmRng = nm.RefersToRange
      myName = nm.Name
      nmRef = "'" & nm.RefersTo
      lCells = nmRng.Cells.Count
      shName = nm.RefersToRange.Parent.Name
      nmAddr = nm.RefersToRange.Address
      If TypeOf nm.Parent Is Workbook Then
        nmSc = "Wb"
      Else
        nmSc = "Ws"
      End If
      wsL.Range(wsL.Cells(lRow, 1), wsL.Cells(lRow, 6)).Value _
         = Array(myName, nmRef, lCells, shName, nmAddr, nmSc)
      lRow = lRow + 1
      Set nmRng = Nothing
      myName = ""
      nmRef = ""
      lCells = 0
      shName = ""
      nmAddr = ""
      nmSc = ""
    End If
  Next nm
         
With wsL
  .Rows("1:1").Font.Bold = True
  .Columns("A:F").EntireColumn.AutoFit
End With

End Sub

Download the Names List Sample File

To get the sample workbook, and the Names List code, go to the Excel Names Macros page on my Contextures site.

The file is zipped, and in Excel xlsm file format, and it contains macros.
___________

This week, I’ve been working in an Excel 2007 file that has several named Excel Tables. After adding a column in one table, I copied the entire worksheet column.

Next, I tried to paste it into another worksheet, where there was a similar table.

That didn’t go too well. After a few minutes of staring at the hourglass, I gave up, and closed Excel in the Task Manager.

Today, I tried to repeat the column copy and paste, forgetting about the previous problem.

Sure enough, Excel crashed again. Well, technically, I guess it’s a hang, rather than a crash, but it’s still annoying.

A Smaller Named Excel Table

In a smaller workbook, with smaller tables, the copy eventually completed, but with strange results. There was a strange message in the Status Bar.

Eventually, the copy completed, but instead of the ten rows from the original table, the paste filled the entire column, so the named Excel Table ended in the last row.

Successful Copy and Paste

Instead of copying and pasting the entire column, you can copy and paste the named Excel table column.

• To select the table column, click, the top of the table heading cell, instead of the column heading button.
• 
• Then, to paste into the other table, right-click the heading cell, and paste.
• 

Or you can copy the cells, and paste them, instead of copying and pasting the column.

How Do You Crash Excel?

Enough about my problems! What’s your favourite way to crash/hang Excel?
________________