none
Excel 2016 Macro only works in debug mode RRS feed

  • Question

  • I have an Excel VBA macro that worked fine in Excel 2010. After saving as an Excel 2016 macro file the code stops running while trying to title a Chart. It give an error "This object has no title" with error code -2147024809 (80070057).  When I stop the code several lines above in debug mode and advance one line at a time with the F8 key it works. It fails on the "ActiveChart.ChartTitle.Text" when running through from the start.  I tried to insert the WAIT for a slight pause before adding the title but that didn't work.

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData _
        Source:=Worksheets(strTabAUTH).Range("$A$1,$A$3:$A$" & lngOMONE - 1 & ",$A$" & lngOMONE + 1 & ":$A$13,$C$1,$C$3:$C$" _
        & lngOMONE - 1 & ",$C$" & lngOMONE + 1 & ":$C$13,$E$1,$E$3:$E$" & lngOMONE - 1 & ",$E$" & lngOMONE + 1 & ":$E$13"), _
        PlotBy:=xlColumns
    ActiveSheet.Shapes("Chart 1").IncrementLeft 375
    ActiveSheet.Shapes("Chart 1").IncrementTop -110
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.4593751094, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.7430555556, msoFalse, _
        msoScaleFromTopLeft
    Application.Wait (Now + TimeValue("0:00:02"))
    ActiveChart.SetElement (msoElementChartTitleAboveChart)

    ActiveChart.ChartTitle.Text = _
            "Top 10 by Cost " & strMO

    Thanks for any help you can offer.

    Tuesday, June 13, 2017 3:31 PM

All replies

  • Hi Bubba9240,

    What do you mean saving as Excel 2016 macro file? I test the code in Excel 2016 and it works well. Which version of Excel are you using now? I suggest you comment out the code titling the chart and check if the new chart have a default chart title. Besides, I also suggest you use Chart.HastTitle to add a title to the chart.

    Here is the example.

    With ActiveChart

    .HasTitle = True

    .ChartTitle.Text = "Top 10 by Cost "

    End With

    Best Regards,

    Terry

    Wednesday, June 14, 2017 9:54 AM
  • Terry:  Thanks for the suggestions.  The chart did not have a default title when I commented out the titling line.  I used the With/End With sequence you suggested and it did not run.  It did again work when I went line-by-line through the code in debug room.
    Thursday, June 15, 2017 6:44 PM
  • * debug mode.
    Thursday, June 15, 2017 6:45 PM
  • Hi Bubba9240,

    Which version of Excel are you using now? Have you tried to repair your Excel? I test your code in Excel 2016 and Excel 2010 and it all works well. It seems that the macro always failed to add the title. I suggest you use the code for a slight pause after adding title. You could select a chart with no title and then try to run the method.

    Sub Test()
    With ActiveChart
    .HasTitle = True
    Application.Wait (Now + TimeValue("0:00:02"))
    .ChartTitle.Text = "Top 10 by Cost "
    End With
    End Sub

    Besides, I also suggest you share your file so we could try to reproduce your issue. You could share it via OneDrive and put link here.

    Best Regards,

    Terry

    Friday, June 16, 2017 9:12 AM