如何用VBA设置图表坐标轴限值？现有代码失效排查

我尝试用VBA设置图表的Y轴最小/最大刻度值，但代码始终无法生效，试过多种语法都没用。以下是我创建"Jigs-Fe%"图表的VBA代码，请帮忙排查原因：

Sub JigsFeStat():
Dim wb As Workbook
    Dim ws As Worksheet
    Dim xData As Range
    Dim yData As Range
    Dim xData2 As Range
    Dim yData2 As Range
    Dim xData3 As Range
    Dim yData3 As Range
    Dim xData4 As Range
    Dim yData4 As Range
    Dim xData5 As Range
    Dim yData5 As Range
    Dim xData6 As Range
    Dim yData6 As Range
    Dim xData7 As Range
    Dim yData7 As Range
    Dim serName As Range
    Dim InitialDate As Variant
    Dim FinalDate As Variant
    
     Set xData = Range("Dates")
     Set yData = Range("JigsFe")
     
     Set xData2 = Range("Dates")
     Set yData2 = Range("JigsFe1")
     
     Set xData3 = Range("Dates")
     Set yData3 = Range("JigsFe2")
     
     Set xData4 = Range("Dates")
     Set yData4 = Range("JigsFe3")
     
     Set xData5 = Range("Dates")
     Set yData5 = Range("JigsFe4")
     
     Set xData6 = Range("Dates")
     Set yData6 = Range("JigsFe5")
     
     Set xData7 = Range("Dates")
     Set yData7 = Range("JigsFe6")
     
    Set wb = ActiveWorkbook
    Set ws = Sheets("Report")
    
    Dim chtObj As ChartObject
    Set chtObj = ws.ChartObjects.Add(Width:=470, Height:=182, Left:=Range("A156").Left, Top:=Range("A156").Top)
    
    With chtObj.Chart.Axes(xlValue)
        .MinimumScale = 20
        .MaximumScale = 80
    End With
    
    
    ' chtObj.Chart.ChartStyle = 23
    
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    
    cht.HasLegend = True
    cht.HasTitle = True
    cht.ChartTitle.Text = "Jigs - Fe%"
    
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy"
    
    ser.Values = yData
    ser.XValues = xData
    ser.Name = "Jigs-Fe%"
    ser.ChartType = xlXYScatter
   
    Dim ser2 As Series
    Set ser2 = cht.SeriesCollection.NewSeries
    ser2.Values = yData2
    ser2.XValues = xData2
    ser2.ChartType = xlLine
    ser2.Name = "UCL"
    
    Dim ser3 As Series
    Set ser3 = cht.SeriesCollection.NewSeries
    ser3.Values = yData3
    ser3.XValues = xData3
    ser3.ChartType = xlLine
    ser3.Name = "LCL"
    
    Dim ser4 As Series
    Set ser4 = cht.SeriesCollection.NewSeries
    ser4.Values = yData4
    ser4.XValues = xData4
    ser4.ChartType = xlLine
    ser4.Name = "2s"
    
    
End Sub

问题原因 #

核心问题是轴限值设置的代码执行顺序错误：你在添加任何数据系列之前就设置了Y轴的最小/最大值，但Excel在每次添加新的数据系列时，会自动触发轴刻度的自适应调整，覆盖你之前手动设置的限值。

另外，你使用的xlXYScatter图表类型会更频繁地触发自动轴刻度调整，进一步加剧这个问题。

修复方案 #

把设置轴限值的代码移到所有数据系列添加完成之后，并且显式关闭轴的自动缩放功能，确保手动设置的限值不会被覆盖。修改后的代码如下：

Sub JigsFeStat()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim xData As Range
    Dim yData As Range
    Dim yData2 As Range
    Dim yData3 As Range
    Dim yData4 As Range
    Dim yData5 As Range
    Dim yData6 As Range
    Dim yData7 As Range
    
    ' 简化重复变量，所有系列共用同一个日期范围
    Set xData = Range("Dates")
    Set yData = Range("JigsFe")
    Set yData2 = Range("JigsFe1")
    Set yData3 = Range("JigsFe2")
    Set yData4 = Range("JigsFe3")
    Set yData5 = Range("JigsFe4")
    Set yData6 = Range("JigsFe5")
    Set yData7 = Range("JigsFe6")
    
    Set wb = ActiveWorkbook
    Set ws = Sheets("Report")
    
    Dim chtObj As ChartObject
    Set chtObj = ws.ChartObjects.Add(Width:=470, Height:=182, Left:=Range("A156").Left, Top:=Range("A156").Top)
    
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    ' 先添加所有数据系列
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    ser.Values = yData
    ser.XValues = xData
    ser.Name = "Jigs-Fe%"
    ser.ChartType = xlXYScatter
    
    Dim ser2 As Series
    Set ser2 = cht.SeriesCollection.NewSeries
    ser2.Values = yData2
    ser2.XValues = xData
    ser2.ChartType = xlLine
    ser2.Name = "UCL"
    
    Dim ser3 As Series
    Set ser3 = cht.SeriesCollection.NewSeries
    ser3.Values = yData3
    ser3.XValues = xData
    ser3.ChartType = xlLine
    ser3.Name = "LCL"
    
    Dim ser4 As Series
    Set ser4 = cht.SeriesCollection.NewSeries
    ser4.Values = yData4
    ser4.XValues = xData
    ser4.ChartType = xlLine
    ser4.Name = "2s"
    
    ' 设置图表基础属性
    cht.HasLegend = True
    cht.HasTitle = True
    cht.ChartTitle.Text = "Jigs - Fe%"
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy"
    
    ' 最后设置Y轴限值，强制关闭自动缩放
    With cht.Axes(xlValue)
        .MinimumScaleIsAuto = False
        .MinimumScale = 20
        .MaximumScaleIsAuto = False
        .MaximumScale = 80
    End With
End Sub

额外优化说明 #

• 去掉了重复的xData2到xData7变量，因为所有系列都使用同一个Dates范围，简化代码结构
• 调整执行顺序，确保所有数据加载完成后再设置轴参数，避免被自动调整覆盖
• 显式关闭轴的自动缩放功能，确保手动设置的刻度值生效

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