VBA to find all Named ranges as an Array and paste into a data validation column on a table

Question

Currently I have code (below) that goes through and finds all charts and tables inside my workbook, takes their names as an array and pastes them onto a table as a data validation dropdown so that whomever is using the workbook can decide which tables and graphs to auto generate into a PowerPoint Presentation. Now I am trying to write code that also will do the same for named ranges. So not charts or tables. For some reason this seems to be way harder than I logically thought it would be. If anything I figured getting tables and charts to work would've been more of a headache but that has not been the case

Code and Picture of the aforementioned table are shown below

CODE:

Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim xlTable As ListObject
Dim xlTableColumn As ListColumn
Dim xlChartObject As ChartObject
Dim xlTableObject As ListObject

Dim ObjectArray() As String
Dim ObjectIndexArray As Integer

Dim ExcRng As Range

'set the book
Set xlBook = ThisWorkbook

'loop through each worksheet
For Each xlSheet In xlBook.Worksheets

    'if we have charts
    If xlSheet.ChartObjects.Count > 0 Then
    
        'grab each chart name
        For Each xlChartObject In xlSheet.ChartObjects
        
            'update count
            ObjectArrayIndex = ObjectArrayIndex + 1
            ReDim Preserve ObjectArray(ObjectArrayIndex)
            
                'add the chart object to array
                ObjectArray(ObjectArrayIndex) = xlChartObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlChartObject)
            
            
        Next
    End If
    
    'if we have tables
    If xlSheet.ListObjects.Count > 0 Then
    
        'grab each table name
        For Each xlTableObject In xlSheet.ListObjects
        
            'update count
            ObjectArrayIndex = ObjectArrayIndex + 1
            ReDim Preserve ObjectArray(ObjectArrayIndex)
            
                'add the table object to array
                ObjectArray(ObjectArrayIndex) = xlTableObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlTableObject)
            
            
        
        Next
    End If
Next

'grab sheet
Set xlSheet = xlBook.Worksheets("Export")
    
    'grab table from sheet
    Set xlTable = xlSheet.ListObjects("ExportToPowerPoint")
    
        'grab object column from table
        Set xlTableColumn = xlTable.ListColumns("Object")
        
            'set the validation dropdown
            With xlTableColumn.DataBodyRange.Validation
            
                'delete old
                .Delete
                
                'add new data
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ObjectArray, ",")
                
                'make sure it's a dropdown
                .InCellDropdown = True
                
            End With

PICTURE OF TABLE

As you can see from the picture; column A is where I have the references going. And as you can see the one chart and table that I have named so far for my workbook are showing up fine--so my code so far for Graphs and Tables seems to be working great. I now just need it to also populate named ranges on that same column A

You will want to loop though `Names` I believe. i.e. `For Each nme In ThisWorkbook.Names` ... taken directly from [this post](https://stackoverflow.com/q/43238985/6706419) — urdearboy, Dec 16 '20 at 16:24

score 1 · Accepted Answer · answered Dec 16 '20 at 16:42

ThisWorkbook.Names contains a reference to all the Names in the Workbook including Named Ranges.

I wrote a function to add the Named Ranges to an array.

Code

Function GetNamedRanges(SheetName As String) As Variant()
    Dim Results As Variant
    ReDim Results(1 To ThisWorkbook.Names.Count)
        
    Dim Count As Long
    Dim Name As Name
    Dim Target As Range
    For Each Name In ThisWorkbook.Names
        On Error Resume Next
        Set Target = Name.RefersToRange
        If Err.Number = 0 Then
            If Target.Parent.Name = SheetName Or Len(SheetName) = 0 Then
                Count = Count + 1
                Set Results(Count) = Target
                On Error GoTo 0
            End If
        End If
    Next
    ReDim Preserve Results(1 To Count)
    GetNamedRanges = Results
End Function

Usage

AllNames = GetNamedRanges
Sheet1Names = GetNamedRanges(Sheet1.Name)

So I am pretty unfamiliar when it comes to using Functions in Excel VBA, so how would I take this block of code here and put it into my code above to have the array that function should create populate into my data validation table like I have it doing with my charts and tables? — ajcarrozza, Dec 22 '20 at 15:17
@ajcarrozza Change `Set Results(Count) = Target` to what ever you want to store in the array (e.g. `Results(Count) = Name.Name & "-" &Target.Parent.Name & "-" & Named Range" & `. — TinMan, Dec 22 '20 at 23:21

VBA to find all Named ranges as an Array and paste into a data validation column on a table

1 Answers1

Code

Usage