Type Mismatch Error VBA: Array referencing named ranges in Workbook

Question

SCOPE

Code below is attempting to grab all table objects, chart objects and range names on my workbook as an array, then the code creates a data validation column on a table which these arrays can then be referenced--so later I can use this table to auto generate a PowerPoint presentation.

PROBLEM

Code for grabbing tables and charts works great--getting type mismatch error when developing the portion of code for named ranges (please see further down for entire block of code/variables):

 'if we have named ranges'
    If ThisWorkbook.Names.Count > 0 Then
    
        'grab each range
        **For Each ExcRng In ThisWorkbook.Names**  **'PROBLEM OCCURS HERE'**
        
            'update count
            ObjectArrayIndex = ObjectArrayIndex + 1
            ReDim Preserve ObjectArray(ObjectArrayIndex)
            
                'add the named range to array
                ObjectArray(ObjectArrayIndex) = ExcRng.Name & "-" & xlSheet.Name & "-" & TypeName(ExcRng)

CODE

Sub GetTablesAndChartToExportTable()

    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
        
        'if we have named ranges'
        If ThisWorkbook.Names.Count > 0 Then
        
            'grab each range
            For Each ExcRng In ThisWorkbook.Names
            
                'update count
                ObjectArrayIndex = ObjectArrayIndex + 1
                ReDim Preserve ObjectArray(ObjectArrayIndex)
                
                    'add the named range to array
                    ObjectArray(ObjectArrayIndex) = ExcRng.Name & "-" & xlSheet.Name & "-" & TypeName(ExcRng)
                    
        
        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
            
    End Sub

NOTICE

I am by no means very experienced in VBA, please, in your answer, include as much detail as possible

@Vityata For Each ExcRng In ThisWorkbook.Names -- I wrote next to it — ajcarrozza
– ajcarrozza, Commented Dec 22, 2020 at 17:59
Try Dim ExcRng As Variant instead of Dim ExcRng As Range — Vityata
– Vityata, Commented Dec 22, 2020 at 18:00
Cool! Try to rewrite the code in a separate question, using as few lines as possible in order to replicate the error. Really, instead of 20+, try only validation and the assignment of the ObjectArray. The code should be able to compile. Good luck! — Vityata
– Vityata, Commented Dec 22, 2020 at 18:05
Join(ObjectArray, ",") does not add leading or trailing " to the joined string. You'll need to add those — chris neilsen
– chris neilsen, Commented Dec 22, 2020 at 18:13

chris neilsen · Accepted Answer · 2020-12-22 18:09:19Z

1

The For Each ExcRng In ThisWorkbook.Names is iterating through the collection ThisWorkbook.Names. Thus, the ExcRng should be of type Name.

To stay on the sure site, whenever iterating with For Each [x] In [y], declare the [x] as a variant. This will make sure it works, but it will take away intellisense and will be a bit "heavier".

edited Dec 22, 2020 at 18:09

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

answered Dec 22, 2020 at 18:07

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

3 Comments

chris neilsen Over a year ago

Names is a collection of Name objects. (Edited to correct)

ajcarrozza Over a year ago

Cool! This got the block for named ranges to go through which is good. Now weirdly it caused an application defined / object defined error later on in my code which I find odd... If only Microsofts error messages could be better detailed lol

Tim Williams Over a year ago

stackoverflow.com/questions/43450523/…

Collectives™ on Stack Overflow

Type Mismatch Error VBA: Array referencing named ranges in Workbook

SCOPE

PROBLEM

CODE

NOTICE

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

SCOPE

PROBLEM

CODE

NOTICE

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related