VBA - How can I get a list of all the named ranges from a workbook using vba

Question

I am trying to create a presentation that will automatically look at all the named ranges and charts from one workbook and paste it into a powerpoint presentation.

My first step is to create all the objects I want to export to the presentation. This is done by running the UpdateDropdownColumn() module to create a dropdown of all the objects in the Excel workbook (charts, list tables and ranges). I then have a separate code that will run through each of these objects, find it in Excel and copies the selected object into the powerpoint presentation. This part appears to be working for now.

So far my first sub-module (UpdateDropdownColumn) works for selecting all charts and list tables but it does not work for selecting all range names within a workbook.

Here is the code for the first piece:

Sub UpdateDropdownColumn()
    
    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 xlRangeObject As Range
    
    Dim ObjectArray() As String
    Dim ObjectArrayIndex As Integer
    
    '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 the count
                ObjectArrayIndex = ObjectArrayIndex + 1
                ReDim Preserve ObjectArray(ObjectArrayIndex)
                
                'add the chart object to the array
                ObjectArray(ObjectArrayIndex) = xlChartObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlChartObject) 'can make the delimiter @@ if '_' is used
            
            Next
        End If
        
        'If we have tables
        If xlSheet.ListObjects.Count > 0 Then
        
            'Grab each ListTable Name
            For Each xlTableObject In xlSheet.ListObjects
            
                'Update the count
                ObjectArrayIndex = ObjectArrayIndex + 1
                ReDim Preserve ObjectArray(ObjectArrayIndex)
                
                'add the list table object to the array
                ObjectArray(ObjectArrayIndex) = xlTableObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlTableObject)
            
            Next
        End If
        
        ''*** Set ExcTbl = Worksheets("G.W").Range("Print_Area1")
        
        'If we have range names
        If xlSheet.Names.Count > 0 Then
        
            'Grab each Range Name
            For Each xlRangeObject In xlSheet.Names.RefersToRange
            'xlSheet.Names.Item
            
                'Update the count
                ObjectArrayIndex = ObjectArrayIndex + 1
                ReDim Preserve ObjectArray(ObjectArrayIndex)
                
                'add the range object to the array
                ObjectArray(ObjectArrayIndex) = xlRangeObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlRangeObject)
            
            Next
        End If
        
    Next
    
    'Grab the sheet
    Set xlSheet = xlBook.Worksheets("Export")
    
    'grab the table from the sheet
    Set xlTable = xlSheet.ListObjects("ExportToPowerPoint") 'this is the table name
    
    'grab the object column from my table
    Set xlTableColumn = xlTable.ListColumns("object")
    
    'set the validation dropdown
    With xlTableColumn.DataBodyRange.Validation
    
        'delete the old one
        .Delete
        
        'add the new data to the dropdown
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ObjectArray, ",")
        
        'make sure it's a dropdown
        .InCellDropdown = True
        
    End With

End Sub

Can anyone assist with how I can modify the code so that I can get all the named ranges from a particular worksheet into my dropbox?

Your help is much appreciated.

Thanks