Search code examples
excelvbacharts

I dont want to extract data of chart from excel cells

The following code works perfectly.

Public Sub Macro1()

'Enter Today's date into A1 and A2 cells.
ActiveSheet.Range("A1").Value = Now()
ActiveSheet.Range("A2").Value = Now()

'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:=100, Top:=50, Width:=400, Height:=200)
    .Name = "myChart"
    .Chart.Axes(xlCategory).CategoryType = xlTimeScale
    .Chart.Axes(xlCategory).TickLabels.NumberFormat = "MMM/YY"
End With

'Add a xlLine serie.
With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
    .ChartType = xlLine
    .Name = "Example"
    .Values = Array(100, 200, 300, 400, 500, 600)
    .XValues = Array(45658, 45689, 45717, 45748, 45778, 45809)
End With

'Add a xlXYScatterLinesNoMarkers serie.
With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
    .ChartType = xlXYScatterLinesNoMarkers
    .Name = "My Birthday"
    .Values = Array(0, 800)
    .XValues = ActiveSheet.Range("A1:A2")
    'Points settings
    .Points(1).ApplyDataLabels
    .Points(1).DataLabel.ShowSeriesName = True
    .Points(1).DataLabel.ShowCategoryName = False
    .Points(1).DataLabel.ShowValue = False
    .Points(1).DataLabel.Position = xlLabelPositionAbove
    .Points(1).DataLabel.Orientation = xlUpward
    .Points(1).DataLabel.Width = 200
    .Points(1).DataLabel.Format.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
End With

End Sub

I dont want to extract data of chart from excel cells.

So I tried to replace this

.XValues = ActiveSheet.Range("A1:A2")

with this

.XValues = Array(Now(), Now())

But this time output of chart is corrupted.

How can I solve that problem?

Solution

  • As already written as comment:

    When writing .XValues = Array(Now(), Now()), the date is implicitly converted into a string (a formatted date). Couldn't find any documentation about it, just observed it using your code:

    enter image description here

    When displaying the data, Excel tries to fit that value onto the x-axis. As this axis contains dates, the string is converted back to a numeric value but fails, so Excel assumes the value "0". It expands the x-Axis so that is starts at 0 (=1.1.1900) and puts the series to the very left.

    Easiest way is to pass the numeric representation values to the chart (as you already for the x-axis).

    .XValues = Array(CLng(Now), CLng(Now))