Bad chart when building using VBA

Question

Here is the code I have written to regenerate a chart each time a button is pressed:

Sub MakeChart()
    ActiveWorkbook.Charts("cht_hgn_hgn").Delete
    Dim s As Series
    Dim sh As Worksheet
    Dim cs As Chart
    Set sh = Worksheets("clc_hgn_hgn")
    Set cs = ActiveWorkbook.Charts.Add
    cs.Name = "cht_hgn_hgn"
    cs.ChartType = xlLine
    For iCount = 3 To 3
        Debug.Print (iCount)
        Set s = cs.SeriesCollection.NewSeries
        s.Name = sh.Cells(1, iCount).Value
        s.values = sh.Range(sh.Cells(3, iCount), sh.Cells(41, iCount))
        s.XValues = sh.Range(sh.Cells(3, 1), sh.Cells(41, 1))
    Next iCount
End Sub

Here is a screenshot of the source data:

https://i.sstatic.net/OrZHi.jpg

Here is a screenshot of the chart:

https://i.sstatic.net/jfFno.jpg

The problem is that the labels (at the very least) in the legend and along the x axis appear to be really messed up. What have I done wrong in my code?

Thanks!!

If you can fix the chart manually, then Record Macro and check the generated code. The problem is with the empty cells in your source ranges. — Slai
– Slai, Commented Aug 14, 2016 at 2:13

Axel Richter · Accepted Answer · 2016-08-14 07:27:38Z

1

There are mainly two issues with your data and creating a chart from it.

First: You need setting the way that blank cells are plotted on the chart. For this Chart.DisplayBlanksAs Property can be used.

Second: While ActiveWorkbook.Charts.Add "default" series where added from the data of the active worksheet. Those series you probably not need an therefore should be deleted.

So probably:

Sub MakeChart()
    On Error Resume Next
    ActiveWorkbook.Charts("cht_hgn_hgn").Delete
    On Error GoTo 0

    Dim s As Series
    Dim sh As Worksheet
    Dim cs As Chart
    Dim lLastRow As Long, lColumnCount As Long, lLastColumn As Long

    Set sh = Worksheets("clc_hgn_hgn")
    lLastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    lLastColumn = 5

    Set cs = ActiveWorkbook.Charts.Add
    cs.Name = "cht_hgn_hgn"
    cs.ChartType = xlLine

    'set the way that blank cells are plotted on the chart
    cs.DisplayBlanksAs = xlInterpolated
    'delete the series which are created while ActiveWorkbook.Charts.Add
    'since we want creating our own series now
    For Each s In cs.SeriesCollection
     s.Delete
    Next

    For lColumnCount = 3 To lLastColumn
        Debug.Print (lColumnCount)
        Set s = cs.SeriesCollection.NewSeries
        s.Name = sh.Cells(1, lColumnCount).Value
        s.Values = sh.Range(sh.Cells(3, lColumnCount), sh.Cells(lLastRow, lColumnCount))
        s.XValues = sh.Range(sh.Cells(3, 1), sh.Cells(lLastRow, 1))
    Next lColumnCount

End Sub

answered Aug 14, 2016 at 7:27

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

posfan12 Over a year ago

Good answer. I did not use xlInterpolated however. If zero values are a problem, then maybe I am using the wrong chart type.

Collectives™ on Stack Overflow

Bad chart when building using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related