Search code examples
excelvbacharts

Charting Sub no longer working and now crashes without changing code

sheet layout

I created the following sub as part of an overall set of VBA code. It used to work without error and gave me exactly what I was looking for. Then at some point it still worked but the final formatting would be off. I would manually need to flip the data source, and the formatting of the bars (namely a black trim around bars) was not performed. Then finally the code for the chart now throws an error.

enter image description here

What I can't figure out is why the change has happened since the code in the sub has not changed.

For error checking I have done the following.

  1. Reduced the supplied data to a point where everything works as intended
  2. Running the the sub with and without autocalc, screen updating, etc turned off and on
  3. Changing the location of the chart from default location to a specified location approximately CW35

SUB CODE:

Sub AddSleepFrequencyChart()

Dim ws As Worksheet
Dim rng As Range
Dim CounterX As Long

    Set ws = ThisWorkbook.Worksheets("Sleep Chart")
    
    ws.Shapes.AddChart2(201, xlColumnClustered).Select
    With ActiveChart
        .Parent.Top = 551
        .Parent.Left = 876
        .SetSourceData Source:=Range("'Sleep Chart'!$DC$10:$DD$33")

 '**********************************************************************
 'The following line SOMETIMES throws an error
 '**********************************************************************

        .ChartTitle.Text = "Sleep Duration Frequency"
        .ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "Sleep Duration Frequency"
    End With

    With ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Characters(1, 24).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Characters(1, 24).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartGroups(1).GapWidth = 0
    ActiveChart.ChartGroups(1).Overlap = 0
    
    With ActiveChart.PlotArea.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    With ActiveChart.PlotArea.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    
    With ActiveChart.Axes(xlCategory).Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    
    With ActiveChart.FullSeriesCollection(1).Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
   
End Sub

For Step 1 of error testing, I reduced the number of days of data to April 8th and the last time I tested that it worked without a problem. I tried to see what connection adding extra rows to the table above the chart had to do with the chart itself. The only thing I could come up with was the table was getting close to the default position of the graph. The other reason I find it odd is that the chart is based on the highlighted columns in the screen shot and the chart sub is only called after all the rest of the text, formulas, formatting have been applied on a new worksheet. The highlighted cells are static and constant in size. I am totally not sure why extra rows in the table above would make a difference.

For Error checking step 2, I ran the overall program and let it crash before it got to the point of turning on calcs, updating and whatnot. I deleted the chart, then stepped through the sub above to insert the chart. I then ran the overall program again and dragged the yellow arrow down immediately to the last steps where autocalc and updating and whatnot gets turned back on. I got the error in both cases.

For Error checking step 3, this is what the small section of code was modified from in order to reposition the chart.

 ActiveChart.SetSourceData Source:=Range("'Sleep Chart'!$DC$10:$DD$33")
 '**********************************************************************
 'The following line SOMETIMES throws an error
 '**********************************************************************
 ActiveChart.ChartTitle.Text = "Sleep Duration Frequency"
 ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "Sleep Duration Frequency"

What the overall program does in case it is of any importance.

On sheet "Sleep Diary" I have four columns that record date, time, action, and type. There is a header row and the data starts in row 2 and is continuous to the bottom of the data with new entries being added at the bottom. While my current list happens to be in chronological order, I was trying to write the overall program as if it was not. I don't think I got there yet on that part.

A           B       C           D
date        Time    action      Sleep Type
2024/04/03  02:11   Laid Down   CPAP
2024/04/03  02:45   Fell Asleep CPAP
2024/04/03  03:10   Woke Up     CPAP
2024/04/03  03:25   Laid Down   CPAP
2024/04/03  04:02   Laid Down   CPAP
2024/04/03  05:25   Laid Down   CPAP
2024/04/03  05:30   Fell Asleep CPAP
2024/04/03  12:25   Woke up     CPAP
2024/04/04  02:05   Laid Down   CPAP
2024/04/04  02:15   Fell Asleep CPAP
2024/04/04  08:05   Woke Up     CPAP
2024/04/04  15:30   Laid Down   CPAP
2024/04/04  15:35   Fell Asleep CPAP
2024/04/04  17:15   Woke Up     CPAP
2024/04/04  17:15   Laid Down   CPAP
2024/04/04  17:17   Fell Asleep CPAP
2024/04/04  17:45   Woke Up     CPAP
2024/04/04  17:45   Laid Down   CPAP
2024/04/04  17:47   Fell Asleep CPAP
2024/04/04  18:15   Woke Up     CPAP

The Overall Program

  1. Set Option Explicit
  2. Turns off autocalc, Screen Upate, Status Bar display, and Enable Events
  3. Determines the first and last date of data
  4. Deletes the worksheet "Sleep Chart" if it exists
  5. Creates the worksheet "Sleep Chart"
  6. Sizes and formats the table above the chart, providing all header text, cells sizes and borders, and providing conditional formatting inside the table.
  7. Provides all the headers and formulas to the right of the table.
  8. Fills in the table with all the Red Bars via a sub.
  9. Fills in cells inside the table with either c or s to indicate 15 minutes of time corresponding to entries in the sleep diary via a sub
  10. Sets the print Area via a sub
  11. Adds and formats a bar chart. (when it doesn't crash)
  12. Turns on autocalc, Screen Upate, Status Bar display, and Enable Events

Question

Why is my sub throwing an error at me and what can I do to correct it?

I am running Excel 2013. Please be gentle as I am purely self taught on VBA. Please let me know if more information is needed, I tried to be thorough without copy and pasting everything.

Updated Code 1

This is a snippit and summary of the update I made as per Axel Richter's initial answer.

Sub AddSleepFrequencyChart()

Dim ws As Worksheet
Dim rng As Range
Dim CounterX As Long
Dim FreqChart As Chart


    Set ws = ThisWorkbook.Worksheets("Sleep Chart")
    ws.Activate
    
    Set FreqChart = ws.Shapes.AddChart2(201, xlColumnClustered).Chart
    With FreqChart
        .Parent.Top = 551
        .Parent.Left = 876
        .SetSourceData Source:=Range("'Sleep Chart'!$DC$10:$DD$33")

'**********************************************************************
'The following line SOMETIMES throws an error
'**********************************************************************
        .ChartTitle.Text = "Sleep Duration Frequency"
        .ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "Sleep Duration Frequency"
    End With

In the rest of the code all references to ActiveChart have been replaced with FreqChart.

Solution

  • So it turns out the solution to the problem wound up being rather simple. The problem was caused by more numbers being on the worksheet as the data grew. The proximity of these numbers to the source data for the chart seems to cause the default layout of the chart to change from what was recorded in the macro I had recorded and was editing. Both issues I was having were solved by the change to one line of code.

    First issue was the error being thrown at the indicated line of code when attempting to give the chart a title. The original default layout had title text visible so changing that element of the chart via my line of code was not a problem ORIGINALLY. Later when more numbers were added to the worksheet, the title element of the chart was no longer visible as seen in the layout screen shot in the question. I believe trying to change an element that was not visible, or conversely did not exist, caused the error.

    The second issue I was having, depending on how many numbers had been added to the worksheet was the row and column information got swapped at some point. This meant all my lines of chart formatting for the series were essentially lost when I manually had to swap row/column under select data for the chart.

    Both these problems were fixed by the solution for this other question.

    All that needed to be done was to change the following line of code from:

    .SetSourceData Source:=Range("'Sleep Chart'!$DC$10:$DD$33")

    to:

    .SetSourceData ws.Range("'Sleep Chart'!$DC$10:$DD$33"), xlColumns

    and voila! Expected results and behaviour are achieved

    POC