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

Question

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"
        'The following code needs to be repaired.
        '.TickLabels.NumberFormat = UPPERCASE("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

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.