Excel VBA : Reference a named range, 就像一个会引用图表或表格

Question

我有:

    Dim xlBook as Workbook
    Dim xlSheet as Worksheet
    Dim xlTable as ListObject
    Dim xlTable Column as ListColumn
    Dim xlChartObject as ChartObject
    Dim xlTableObject as ListObject
    
    Dim ObjectArray() as String
    Dim ObjectIndexArray 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 name
    For each xlChartObject in xlSheet.ChartObjects
    
    'update count
    ObjectArrayIndex = ObjectArrayIndex + 1
    ReDim Preserve ObjectArray(ObjectArrayIndex)
    
    'add chart object to array
    ObjectArray(ObjectArrayIndex) = xlChartObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlChartObject)

'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 validation dropdown
    With xlTableColumn.DataBodyRange.Validation
'delete old
    .delete
'add new data
    .add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ObjectArray, "-")
'make sure its a dropdown
    .InCellDropdown = true

    end with

    end sub

这段代码非常适合抓取图表，同样我也使用 ListObject 来抓取表格。
我的问题是创建另一个代码块来获取 excel 中的命名范围。 所以不是表格或图表
任何帮助将不胜感激!

Answer 1

Activesheet.names 应该在 activeshee 上给出命名范围的数组。将其添加到每个循环中，您应该完成。