VBA代码问题：如何让簇状柱形图SetSourceData引用命名范围

现有VBA子过程changeSourceData用于在工作簿其他工作表的下拉列表触发变更事件时，更新Project Dashboard工作表上簇状柱形图的数据源（通过SetSourceData方法）。目前硬编码单元格引用的部分可正常运行，但使用命名范围的最后一个If语句的Else分支无法生效，需修改代码以正确引用表格数据源区域。

原代码 #

Sub changeSourceData()

    Dim currentMonth As String
    Dim projectDashboardWorksheet As Object
    Dim matchRange As String
    Dim fyMonths As Variant
    Dim monthIndex As Integer
    Dim expandRange As Integer
    Dim chartRangeMonths As Range
    Dim chartRangeValues As Range
        
        
    currentMonth = Evaluate(ThisWorkbook.Names("CoverWorksheet_Current_Month_DropDown").Value)
    Set projectDashboardWorksheet = Sheet13.ChartObjects("Chart 3")
    matchRange = Range("CoverWorksheet_Current_Month_DropDown").Value
    

    fyMonths = Array("Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar")
    monthIndex = Application.Match(matchRange, fyMonths, 0)
    

    If Range("CoverWorksheet_Current_Month_DropDown") = "Apr" Then
        expandRange = 1
    ElseIf Range("CoverWorksheet_Current_Month_DropDown") = "May" Then
        expandRange = 2
    ElseIf Range("CoverWorksheet_Current_Month_DropDown") = "Jun" Then
        expandRange = 3
    ElseIf Range("CoverWorksheet_Current_Month_DropDown") = "Jul" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Aug" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Sep" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Oct" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Nov" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Dec" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Jan" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Feb" _
        Or Range("CoverWorksheet_Current_Month_DropDown") = "Mar" _
    Then
        expandRange = Evaluate(ThisWorkbook.Names("Profile_Of_Income_Data_Source_Range").Value)
    End If


    If currentMonth = "Apr" _
        Or currentMonth = "May" _
        Or currentMonth = "Jun" Then
        Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(Range("Profile_Of_Income_Months_archived_HEADER").Rows.Count + expandRange - 1).Offset(1, 0)
        Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(Range("Profile_Of_Income_MonthsRANGE").Rows.Count + expandRange - 1).Offset(1, 0)
    ElseIf currentMonth = "Jul" _
        Or currentMonth = "Aug" _
        Or currentMonth = "Sep" _
        Or currentMonth = "Oct" _
        Or currentMonth = "Nov" _
        Or currentMonth = "Dec" _
        Or currentMonth = "Jan" _
        Or currentMonth = "Feb" _
        Or currentMonth = "Mar" Then
            If Profile_Of_Income_Data_Source_Range = 1 Then
                Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(Range("Profile_Of_Income_Months_archived_HEADER").Rows.Count + expandRange - 1).Offset(fyMonths, 0)
                Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(Range("Profile_Of_Income_MonthsRANGE").Rows.Count + expandRange - 1).Offset(fyMonths, 0)
            ElseIf Profile_Of_Income_Data_Source_Range = 2 Then
                Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(Range("Profile_Of_Income_Months_archived_HEADER").Rows.Count + expandRange - 1).Offset(fyMonths - 1, 0)
                Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(Range("Profile_Of_Income_MonthsRANGE").Rows.Count + expandRange - 1).Offset(fyMonths - 1, 0)
            ElseIf Profile_Of_Income_Data_Source_Range = 3 Then
                Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(Range("Profile_Of_Income_Months_archived_HEADER").Rows.Count + expandRange - 1).Offset(fyMonths - 2, 0)
                Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(Range("Profile_Of_Income_MonthsRANGE").Rows.Count + expandRange - 1).Offset(fyMonths - 2, 0)
            ElseIf Profile_Of_Income_Data_Source_Range = 4 Then
                Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(Range("Profile_Of_Income_Months_archived_HEADER").Rows.Count + expandRange - 1).Offset(fyMonths - 3, 0)
                Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(Range("Profile_Of_Income_MonthsRANGE").Rows.Count + expandRange - 1).Offset(fyMonths - 3, 0)
            End If
    End If
    

    If currentMonth = "Apr" Then
        projectDashboardWorksheet.Chart.SetSourceData _
        Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57,'Project Dashboard'!$H$57:$S$57")
    ElseIf currentMonth = "May" Then
        projectDashboardWorksheet.Chart.SetSourceData _
        Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57:$F$58,'Project Dashboard'!$H$57:$S$58")
    ElseIf currentMonth = "Jun" Then
        projectDashboardWorksheet.Chart.SetSourceData _
        Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57:$F$59,'Project Dashboard'!$H$57:$S$59")
    Else
        projectDashboardWorksheet.Chart.SetSourceData _
        Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'! & chartRangeMonths.Address & ,'Project Dashboard'! & chartRangeMonths.Address & ")
    End If


End Sub

问题分析 #

1. Range字符串拼接错误：最后Else分支中，直接在字符串里用&连接变量的写法无效，需先拼接成完整的区域引用字符串再传入Range。
2. Offset参数错误：处理非前三个月时，Offset使用了数组fyMonths，应改用monthIndex（月份对应的索引值）作为偏移参数。
3. 命名范围引用错误：直接使用Profile_Of_Income_Data_Source_Range名称而非其实际值，需通过Evaluate获取命名范围的数值。
4. 冗余的月份判断：大量If-Else判断月份的逻辑可通过monthIndex直接简化。

修改后的代码 #

Sub changeSourceData()

    Dim currentMonth As String
    Dim projectDashboardChart As ChartObject
    Dim matchRange As String
    Dim fyMonths As Variant
    Dim monthIndex As Integer
    Dim expandRange As Integer
    Dim chartRangeMonths As Range
    Dim chartRangeValues As Range
    Dim dataSourceRangeVal As Integer
    Dim sourceRangeStr As String
        
    ' 获取当前月份和图表对象
    currentMonth = Evaluate(ThisWorkbook.Names("CoverWorksheet_Current_Month_DropDown").Value)
    Set projectDashboardChart = Sheet13.ChartObjects("Chart 3")
    matchRange = Range("CoverWorksheet_Current_Month_DropDown").Value
    
    ' 定义财年月份数组并获取当前月份索引
    fyMonths = Array("Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar")
    monthIndex = Application.Match(matchRange, fyMonths, 0)
    
    ' 简化expandRange计算：前三个月直接用索引，其余用命名范围值
    If monthIndex <= 3 Then
        expandRange = monthIndex
    Else
        dataSourceRangeVal = Evaluate(ThisWorkbook.Names("Profile_Of_Income_Data_Source_Range").Value)
        expandRange = dataSourceRangeVal
    End If
    
    ' 设置图表数据区域
    If monthIndex <= 3 Then
        ' 前三个月：偏移1行
        Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(, expandRange).Offset(1, 0)
        Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(, expandRange).Offset(1, 0)
    Else
        ' 其余月份：根据dataSourceRangeVal计算偏移量
        Dim offsetRows As Integer
        offsetRows = monthIndex - dataSourceRangeVal
        
        Set chartRangeMonths = Range("Profile_Of_Income_Months_archived_HEADER").Resize(, expandRange).Offset(offsetRows, 0)
        Set chartRangeValues = Range("Profile_Of_Income_MonthsRANGE").Resize(, expandRange).Offset(offsetRows, 0)
    End If
    
    ' 构建数据源引用字符串并设置图表数据源
    If monthIndex <= 3 Then
        ' 硬编码部分保留原逻辑
        Select Case currentMonth
            Case "Apr"
                projectDashboardChart.Chart.SetSourceData _
                    Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57,'Project Dashboard'!$H$57:$S$57")
            Case "May"
                projectDashboardChart.Chart.SetSourceData _
                    Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57:$F$58,'Project Dashboard'!$H$57:$S$58")
            Case "Jun"
                projectDashboardChart.Chart.SetSourceData _
                    Source:=Range("Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE,'Project Dashboard'!$F$57:$F$59,'Project Dashboard'!$H$57:$S$59")
        End Select
    Else
        ' 正确拼接区域引用字符串
        sourceRangeStr = "Profile_Of_Income_Months_archived_HEADER,Profile_Of_Income_MonthsRANGE," & _
                         "'Project Dashboard'!" & chartRangeMonths.Address & "," & _
                         "'Project Dashboard'!" & chartRangeValues.Address
                         
        projectDashboardChart.Chart.SetSourceData Source:=Range(sourceRangeStr)
    End If

End Sub

关键修改说明 #

• 简化expandRange计算：利用monthIndex直接判断前三个月，避免冗余的If-Else。
• 修复Offset参数：使用monthIndex - dataSourceRangeVal计算正确的偏移行数，替换原错误的数组引用。
• 正确引用命名范围：通过dataSourceRangeVal变量存储命名范围的数值，避免直接使用名称导致的错误。
• 修复数据源拼接：先构建完整的sourceRangeStr字符串，再传入Range，确保引用格式正确。
• 变量命名优化：将projectDashboardWorksheet改为projectDashboardChart，更符合实际指向（图表对象而非工作表）。

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