Read Named Range Names into VBA array

Question

I'm trying to figure out how I can get the names of ranges in a given worksheet into an array. I've got as far as the listnames function but that seems to list them all in one go. Any ideas?

Gary's Student · Accepted Answer · 2017-06-07 11:43:54Z

1

Something like:

Sub listum()
    Dim ary(1 To 3) As String
    With ActiveWorkbook
        If .Names.Count > 0 Then
            For i = 1 To .Names.Count
                MsgBox (i & "  " & .Names(i).Name & "  " & Range(.Names(i)).Address)
                ary(i) = .Names(i).Name
            Next
        End If
    End With
End Sub

will display them and put them into an array.

Size the array to suit your needs.
Insure that there are no non-Range Names in the workbook.

answered Jun 7, 2017 at 11:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JamesMcS Over a year ago

Thanks very much, I'll give this a go

JamesMcS Over a year ago

Have just tried this, I din't know about "names" - but it's taking the workbook name, print area etc into the array, which is causing problems down the line. Any thoughts?

Gary's Student Over a year ago

@JamesMcS You need to test for them......after all, PrintArea is a valid group of cells, just like any other user-defined Named Range !

Christopher Oezbek Over a year ago

@JamesMcS: Did you find any way to exclude them well? I am building a small template exporter for word using Named Ranges and it would be good to exclude built-in fields github.com/coezbek/…

Wils Mils · Accepted Answer · 2017-06-07 13:13:46Z

0

My version is I used dynamic array:

    Sub TestNamedRange()
    Dim i As Long
    Dim intCount As Long
    Dim NameArray() As String
    'count number of named ranges
    intCount = ThisWorkbook.Names.Count
    ReDim NameArray(intCount)  'for dynamic number of named ranges
    'assign all names to each array element
    For i = 1 To intCount
      NameArray(i) = ThisWorkbook.Names(i).Name 'remove .Name if you want address range only
    Next
   'check array elements
   For Each ele In NameArray
     Debug.Print ele
   Next
  'manipulate your array here
  End Sub

edited Jun 7, 2017 at 13:13

answered Jun 7, 2017 at 11:54

Wils Mils

6334 silver badges9 bronze badges

3 Comments

jkpieterse Over a year ago

Though rare I have seen workbooks with more than 32767 names so best to use a Long rather than an integer. Since use of an Integer do not offer any advantage over Long I'd advise to always use Long to avoid problems like this.

JamesMcS Over a year ago

Thanks very much! I'll give this a go

Wils Mils Over a year ago

@JamesMcS please give it an up. I am just starting :-) thank you.

Collectives™ on Stack Overflow

Read Named Range Names into VBA array

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related