Please run the following code.

    Public Sub Macro1()
    
    'Delete all charts
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        If ActiveSheet.Shapes(i).Type = msoChart Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next i
    
    'Add a chart.
    With ActiveSheet.ChartObjects.Add(Left:=10, Top:=10, Width:=400, Height:=200)
        .Name = "myChart"
    End With
    
    'Add a serie.
    With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
        .ChartType = xlLine
        .XValues = Array(45658, 45689, 45717, 45748, 45778, 45809)
        .Values = Array(1, 2, 3, 4, 5, 6)
    End With
    
    'Format the xlCategory 
    With ActiveSheet.ChartObjects("myChart").Chart.Axes(xlCategory)
        .CategoryType = xlTimeScale
        .TickLabels.NumberFormat = "MMM/YY"
    End With
    
    End Sub

How to make Uppercase Month Names in Y axes of the Chart via vba?

The following picture shows desired output.

Please run the following code.

    Public Sub Macro1()
    
    'Delete all charts
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        If ActiveSheet.Shapes(i).Type = msoChart Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next i
    
    'Add a chart.
    With ActiveSheet.ChartObjects.Add(Left:=10, Top:=10, Width:=400, Height:=200)
        .Name = "myChart"
    End With
    
    'Add a serie.
    With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
        .ChartType = xlLine
        .XValues = Array(45658, 45689, 45717, 45748, 45778, 45809)
        .Values = Array(1, 2, 3, 4, 5, 6)
    End With
    
    'Format the xlCategory 
    With ActiveSheet.ChartObjects("myChart").Chart.Axes(xlCategory)
        .CategoryType = xlTimeScale
        .TickLabels.NumberFormat = "MMM/YY"
    End With
    
    End Sub

How to make Uppercase Month Names in Y axes of the Chart via vba?

The following picture shows desired output.

https://prnt.sc/g2dXINXpMV97

• excel
• vba
• date
• charts
• uppercase

• I'm afraid the display of months in Excel is fully controlled by your region and language settings of Windows. In many languages, month names as in lower case. But in English, month names start with capitalized letters. I don't think there is a built-in way to get all-caps unless you're OK with text formulas. But those will cause a line chart to not recognize the x-data as dates. – jkpieterse Commented Feb 6 at 13:27
• You could try to do something along the lines what is suggested here: mrexcel.com/board/threads/… – jkpieterse Commented Feb 6 at 15:21

1 Answer 1

Change this block:

'Add a serie.
    Dim xVal As Variant, UpVal As Variant
    With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
        .ChartType = xlLine
        xVal = Array(45658, 45689, 45717, 45748, 45778, 45809)
        UpVal = xVal
        For i = 0 To UBound(xVal)
            UpVal(i) = UCase(WorksheetFunction.Text(xVal(i), "[$-en-us]mmm/yy"))
        Next i
        .XValues = UpVal
        .Values = Array(1, 2, 3, 4, 5, 6)
    End With  

and remove the last one: 'Format the xlCategory.