How to Modify Excel Chart Axes with VBA Code

This tutorial explains how to modify and format the axes of chart in Excel using VBA code.

You can download the following workbook to practice.

  Download Excel File

.HasAxis(xlCategory, xlPrimary) = True adds X and Y axis categories in a chart.

.HasAxis(xlValue, xlPrimary) = True adds X and Y axis values in a chart.

Let’s take the given sample dataset to understand how it works.

Sub AddAxes()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.chart
        
        .SetSourceData Source:=Range("A1:C14")
        
        .ChartType = xlLine
        
     .HasAxis(xlCategory, xlPrimary) = True
     .HasAxis(xlValue, xlPrimary) = True
        End With
    
End Sub

Press Run or F5 to run the above code.

.Axes(xlCategory).AxisTitle.Text = "President" returns “President” title to X-axis.

.Axes(xlValue).AxisTitle.Text = "Percentage Vote" returns “Percentage Vote” title to the Y-axis

Let’s take the given sample dataset to understand how it works.

Sub AddAxesTitle()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        .SetSourceData Source:=Range("A1:C14")
        .ChartType = xlLine
        
        
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "President"
        
       
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Percentage Vote"
    End With
End Sub 

Press Run or F5 to run the above code

.Axes(xlCategory).TickLabels.Font.Color = RGB(255, 0, 0) returns Red color to the labels at X-axis.

.Axes(xlValue).TickLabels.Font.Color = RGB(0, 0, 255) returns Blue color to the labels at Y-axis.

Let’s take the given sample dataset to understand how it works.

Sub ChangeAxisLabelColor()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
       
        .SetSourceData Source:=Range("A1:C14")
               
        .ChartType = xlLine
        
        With .Axes(xlCategory).TickLabels
            .Font.Color = RGB(255, 0, 0)
            End With
              
        With .Axes(xlValue).TickLabels
            .Font.Color = RGB(0, 0, 255)
            End With
    End With
End Sub 

Press Run or F5 to run the above code.

.Axes(xlCategory).TickLabels.Font.Size = 14 set label size to 14 in X-axis.

.Axes(xlValue).TickLabels.Font.Size = 14 set label size to 14 in Y-axis.

Let’s take the given sample dataset and set the Font size of axes to 14.

Sub ChangeAxisFontSize()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        .SetSourceData Source:=Range("A1:C14")
        
        .ChartType = xlLine
        
        With .Axes(xlCategory).TickLabels
            .Font.Size = 14
        End With
        
        With .Axes(xlValue).TickLabels
            .Font.Size = 14
            End With
    End With
End Sub   

Press Run or F5 to run the above code.

.Axes(xlCategory).Delete deletes the X-axis.

.Axes(xlValue).Delete deletes the Y-axis.

Let’s take the given sample dataset to understand how it works.

Sub RemoveAxes()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        
        .SetSourceData Source:=Range("A1:C14")
        
       
        .ChartType = xlLine
        
        
        On Error Resume Next
        .Axes(xlCategory).Delete
        
        
        .Axes(xlValue).Delete
        On Error GoTo 0
    End With
End Sub 

Press Run or F5 to run the above code.

The following code can be used to apply a particular number format.

• .NumberFormat = "0%" Format as Percentage (Example: 50%)
• .NumberFormat = "0.00" Format with 2 Decimal Places (Example: 12.34)
• .NumberFormat = "#,##0.00" Format as Number with Thousand Separator and 2 Decimal Places (Example: 1,234.56)
• .NumberFormat = "$#,##0.00" Format as Currency (Example: $1,234.56)

Let’s take the same sample dataset and change the values on Y-axis from percentage to decimal (upto two decimal places).

Sub NumberFormat()
    Dim chartObj As ChartObject
    Dim chart As chart
    
    
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    Set chart = chartObj.chart
    
    chart.SetSourceData Source:=Range("A1:C14")
    
    chart.ChartType = xlLine
     
    With chart.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Text = "Category Axis"
    End With
    
   
    With chart.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Text = "Value Axis"
        .TickLabels.NumberFormat = "0.00"
    End With 

Press Run or F5 to run the above code

.Axes(xlCategory/xlValue).MajorTickMark = xlNone removes the major ticks from the selected axis.

.Axes(xlCategory/xlValue).MinorTickMark = xlNone removes the minor ticks from the selected axis.

Let’s take the following Sample data to understand how it works

Sub HideChartTicks()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As chart

    Set ws = ThisWorkbook.Sheets("Sheet1") 
    Set chartObj = ws.ChartObjects("Chart 1") 
    Set chart = chartObj.chart

    
    With chart.Axes(xlCategory)
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
    End With

    With chart.Axes(xlValue)
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
    End With

End Sub

Press Run or F5 to run the above code

Read MoreListenData