Excel-VBA：能否在For-To循环内创建指定X/Y轴的图表？

Absolutely! You don’t even need to create the chart inside the loop—once you’ve processed all your data and written the calculated npointfactor values to column E, you can generate a single chart that plots all your points at once. Here’s a modified version of your calculate_data sub that includes the chart creation logic:

Sub calculate_data()
    Dim npoint As Variant
    Dim ncumulative As Variant
    Dim nfactor As Variant
    Dim npointfactor() As Double
    Dim lastrowdata As Long
    Dim n As Long
    Dim chartObj As ChartObject
    Dim dataRange As Range
    
    ' Get the number of data points
    lastrowdata = Sheets("Sheet1").Range("A2").End(xlDown).Row - 2
    
    ' Resize arrays to match data count
    ReDim npoint(lastrowdata)
    ReDim ncumulative(lastrowdata)
    ReDim nfactor(lastrowdata)
    ReDim npointfactor(lastrowdata)
    
    ' Read data from sheet and calculate npointfactor
    For n = 1 To lastrowdata
        npoint(n) = Sheets("Sheet1").Cells(2 + n, 2).Value
        ncumulative(n) = Sheets("Sheet1").Cells(2 + n, 3).Value
        nfactor(n) = Sheets("Sheet1").Cells(2 + n, 4).Value
        npointfactor(n) = npoint(n) / nfactor(n)
    Next n
    
    ' Write calculated values back to column E
    For n = 1 To lastrowdata
        Sheets("Sheet1").Cells(2 + n, 5).Value = npointfactor(n)
    Next n
    
    ' Create the chart object on Sheet1
    Set chartObj = Sheets("Sheet1").ChartObjects.Add( _
        Left:=Sheets("Sheet1").Range("G2").Left, _
        Width:=500, _
        Top:=Sheets("Sheet1").Range("G2").Top, _
        Height:=300)
    
    ' Set chart type to XY Scatter (ideal for numerical X/Y pairs)
    chartObj.Chart.ChartType = xlXYScatter
    
    ' Define data ranges: X = column C (ncumulative), Y = column E (npointfactor)
    Set dataRange = Sheets("Sheet1").Range("C3:C" & (lastrowdata + 2) & ", E3:E" & (lastrowdata + 2))
    
    ' Assign data to the chart
    chartObj.Chart.SetSourceData Source:=dataRange
    
    ' Add labels and basic formatting
    With chartObj.Chart
        .HasTitle = True
        .ChartTitle.Text = "npointfactor vs ncumulative"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "ncumulative"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "npointfactor"
        
        ' Optional: Improve marker visibility
        .SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
        .SeriesCollection(1).MarkerSize = 6
    End With
End Sub

Key Notes: #

• Chart Placement: The chart is positioned starting at cell G2 to avoid overlapping your raw data. Adjust the Left and Top parameters if you want to move it to a different spot.
• Chart Type: xlXYScatter is used here because it’s designed for plotting paired numerical values. If you prefer a line chart, replace it with xlLine.
• Data Source: We reference the columns directly (C for ncumulative, E for npointfactor) since you’ve already written the calculated values to the sheet. This is simpler than using the arrays directly for chart data.
• Customization: Feel free to tweak formatting—change marker colors, add gridlines, or adjust chart dimensions to fit your needs.

If you ever needed to create multiple charts (one per loop iteration), that’s also feasible, but plotting all points in a single chart aligns best with your request.

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