vba - VBA Excel系列中的Y轴对称

Question

我已经使用 Chart 对象和 SeriesCollection.NewSeries 绘制了一些图
部分代码是这样的

Private Function AddSeriesAndFormats(PPSChart As Chart, shInfo As Worksheet, tests() As PPS_Test, RowCount As Integer, col As Integer, smoothLine As Boolean, lineStyle As String, transparency As Integer, lineWidth As Single, ByRef position As Integer) As Series

Dim mySeries As Series

Set mySeries = PPSChart.SeriesCollection.NewSeries
With mySeries
    .Name = tests(0).GetString()
    .XValues = "='" & shInfo.Name & "'!R" & RowCount - UBound(tests) - 1 & "C" & CInt(4 * (col + 1) - 2) & ":R" & RowCount - 1 & "C" & CInt(4 * (col + 1) - 2)
    .Values = "='" & shInfo.Name & "'!R" & RowCount - UBound(tests) - 1 & "C" & CInt(4 * (col + 1) - 1) & ":R" & RowCount - 1 & "C" & CInt(4 * (col + 1) - 1)
    .Smooth = smoothLine
    .Format.line.Weight = lineWidth
    .Format.line.DashStyle = GetLineStyle(lineStyle)
    .Format.line.transparency = CSng(transparency / 100)
    .MarkerStyle = SetMarkerStyle(position)
    .MarkerSize = 9
    .MarkerForegroundColorIndex = xlColorIndexNone
End With

Set AddSeriesAndFormats = mySeries

End Function

并且 PPSChart 是这样创建的

Private Function AddChartAndFormatting(chartName As String, chartTitle As String, integralBuffer As Integer, algoPropertyName As String) As Chart

Dim PPSChart As Chart, mySeries As Series

Set PPSChart = Charts.Add
With PPSChart
    .Name = chartName
    .HasTitle = True
    .chartTitle.Characters.Text = chartTitle
    .ChartType = xlXYScatterLines
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).HasTitle = True
    If algoPropertyName <> "" Then 'case for Generic PPS plots
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = algoPropertyName
    Else
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PL/PR max(avg_" & integralBuffer & "ms) [mbar]" 'case for the bumper obsolate algorithm
    End If
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Bumper Position [mm]"
End With

' delete random series that might be generated
For Each mySeries In PPSChart.SeriesCollection
    mySeries.Delete
Next mySeries

Set AddChartAndFormatting = PPSChart

End Function

结果示例如下图所示

我想要的是让 X 轴从 -350 开始，即使我在 Y 轴的左侧(负侧)没有值。实际上，我想要的是中间的 Y 轴，即使绘制的值是正的(最大 X 值和最小 X 值对 Y 轴对称)。
你能告诉我是否可能并给我一些例子吗？

Answer 1

嗨，你可以尝试这样的事情:

Dim dMinValue as Double, dMaxValue as Double

With PPSChart
    dMinValue = application.WorksheetFunction.Min("='" & _
                    shInfo.Name & "'!R" & RowCount - UBound(tests) - 1 _ 
                    & "C" & CInt(4 * (col + 1) - 2) & ":R" & RowCount - 1  _
                    & "C" & CInt(4 * (col + 1) - 2))

    dMaxValue = application.WorksheetFunction.Max("='" & _
                    shInfo.Name & "'!R" & RowCount - UBound(tests) - 1 _ 
                    & "C" & CInt(4 * (col + 1) - 2) & ":R" & RowCount - 1  _
                    & "C" & CInt(4 * (col + 1) - 2))
    ....
    .Axes(xlValue).MinimumScale = dMinValue
    .Axes(xlValue).MaximumScale = dMaxValue
    ....
End With

我的建议，您应该将您的系列值分配给一个对象以便于使用

Dim rSerie as Range
Set rSerie = Range("='" & _
                        shInfo.Name & "'!R" & RowCount - UBound(tests) - 1 _ 
                        & "C" & CInt(4 * (col + 1) - 2) & ":R" & RowCount - 1  _
                        & "C" & CInt(4 * (col + 1) - 2)))

With ....
    dMinValue = Application.WorksheetFunction.Min(rSerie)
    ....
End with