求助：VBA创建Excel动态命名区域失效，求解决方案

Got it, let's tackle this dynamic named ranges issue for your 200 data points. Here's a tailored VBA solution that can create (or update) dynamic named ranges in bulk, using the names and formulas you've already prepared:

Prerequisite Setup #

First, make sure you have a dedicated worksheet (let's call it NameList for this example) where:

• Column A contains the names for each dynamic range (e.g., SalesData_2023, CustomerCount)
• Column B contains the dynamic range formulas you've already written. For stability, I recommend using non-volatile formulas like:

  =INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
  

  instead of volatile OFFSET formulas (they slow down Excel's calculations over time).

VBA Code to Create Dynamic Ranges #

Open the VBA editor (Alt + F11), insert a new module, and paste this code:

Sub CreateDynamicNamedRanges()
    Dim nameSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim rangeName As String
    Dim rangeFormula As String
    
    ' Update this to your actual worksheet name that holds names/formulas
    Set nameSheet = ThisWorkbook.Worksheets("NameList")
    
    ' Find the last row with data in column A (names)
    lastRow = nameSheet.Cells(nameSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row to create the dynamic range
    For i = 2 To lastRow ' Skip row 1 if it's a header
        rangeName = nameSheet.Cells(i, "A").Value
        rangeFormula = nameSheet.Cells(i, "B").Value
        
        ' Skip empty names to avoid errors
        If rangeName <> "" Then
            ' Delete existing range with the same name (if needed)
            On Error Resume Next
            ThisWorkbook.Names(rangeName).Delete
            On Error GoTo 0
            
            ' Create the dynamic named range using your pre-written formula
            ThisWorkbook.Names.Add _
                Name:=rangeName, _
                RefersTo:=rangeFormula, _
                Visible:=True
            
            ' Optional: Print progress to Immediate Window
            Debug.Print "Successfully created: " & rangeName
        End If
    Next i
    
    MsgBox "All 200 dynamic named ranges are ready for your charts!", vbInformation
End Sub

Key Notes #

• Why RefersTo instead of RefersToRange? Dynamic ranges rely on formulas, not fixed cell references. Using RefersTo lets you directly assign your pre-written dynamic formula to the named range.
• Adjust for your setup: If your name/formula list starts at a different row, or uses different columns, tweak the For i = 2 To lastRow and column references (e.g., Cells(i, "C") if formulas are in column C).
• Backup first: If you have existing named ranges, the code will delete duplicates—remove the On Error Resume Next/Delete block if you don't want this behavior.

内容的提问来源于stack exchange，提问作者user31445