如何设置图表的X轴值？附现有图表创建VBA代码

Hey there! Let's fix up that X-axis for your clustered column chart. Right now, your code only pulls data from column D, so Excel defaults to using sequential numbers (1, 2, 3...) for the X-axis. Here are a couple of straightforward ways to set custom X-axis values:

Option 1: Include X-Axis Labels in Your Data Source (Recommended) #

If your desired X-axis labels are stored in another column (say, column A, rows 1 to 6), you can expand your data source to include both the labels and your data. This lets Excel automatically use the labels as your X-axis categories.

Here's the modified code with key changes marked:

Private Sub CommandButton3_Click()
    Dim aChart As Chart
    Dim aRange As Range
    
    ' Updated: Combine label column (A1:A6) and data column (D1:D6)
    Set aRange = Union(Range(Cells(1, 1), Cells(6, 1)), Range(Cells(1, 4), Cells(6, 4)))
    
    Set aChart = Charts.Add
    aChart.HasLegend = False
    aChart.ChartType = xlColumnClustered
    aChart.SetSourceData aRange
    aChart.PlotBy = xlColumns ' Ensures first column (A) is used as X-axis labels
    aChart.Axes(xlCategory).HasMajorGridlines = True
    aChart.Location xlLocationAsObject, Sheet1.Name
    
    Sheet1.ChartObjects.Left = 125
    Sheet1.ChartObjects.Top = 250
    Cells(1, 1).Select
End Sub

Note: Adjust the column numbers in Cells(1, 1) to match where your actual X-axis labels are stored.

Option 2: Manually Assign X-Axis Labels #

If you don't want to modify your data source, or your labels come from a separate range, you can directly set the X-axis category names after creating the chart.

Add this line right after setting up your chart's basic properties:

' Replace A1:A6 with your actual label range
aChart.Axes(xlCategory).CategoryNames = Range("A1:A6").Value

Here's how the full code looks with this addition:

Private Sub CommandButton3_Click()
    Dim aChart As Chart
    Dim aRange As Range
    
    Set aRange = Range(Cells(1, 4), Cells(6, 4))
    Set aChart = Charts.Add
    
    aChart.HasLegend = False
    aChart.ChartType = xlColumnClustered
    aChart.SetSourceData aRange
    aChart.PlotBy = xlColumns
    aChart.Axes(xlCategory).HasMajorGridlines = True
    
    ' New line: Assign custom X-axis labels
    aChart.Axes(xlCategory).CategoryNames = Range("A1:A6").Value
    
    aChart.Location xlLocationAsObject, Sheet1.Name
    
    Sheet1.ChartObjects.Left = 125
    Sheet1.ChartObjects.Top = 250
    Cells(1, 1).Select
End Sub

Option 3: Modify an Existing Chart #

If you need to update the X-axis of a chart that's already on your sheet, use this snippet:

Dim targetChart As ChartObject
' Replace "Chart 1" with your chart's name, or use index (e.g., ChartObjects(1))
Set targetChart = Sheet1.ChartObjects("Chart 1")

' Assign new labels
targetChart.Chart.Axes(xlCategory).CategoryNames = Range("A1:A6").Value

Quick Tips #

• Make sure the number of rows in your label range matches the number of data points (6 rows in your example) — mismatched ranges will cause missing or repeated labels.
• If your labels are dates or formatted text, Excel will preserve their formatting automatically.
• Want to add an X-axis title? Add these lines:

  aChart.Axes(xlCategory).HasTitle = True
  aChart.Axes(xlCategory).AxisTitle.Text = "Your Custom X-Axis Title"
  

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