CODEWITHSUNDEEP
excelchartsexcel-2007vba
JazZeus
JazZeus

Reputation: 180

Why does formatting the secondary axis result in an error?

I've created a function that creates charts from data arrays. Sometimes it works fine, sometimes it doesn't, for unclear reasons. If it doesn't, it gives the following error:

Run-time error '-2147467259 (80004005)'

I've tried to reduce the code as much as possible while still 'conserving' the problem. The reduced code is below. It errors on the third line from below. If the DoEvents statement is used on the indicated position, it errors much less often, but still sometimes. I'm using Excel 2007.

My questions are:

  1. What is the problem here?
  2. What to do about it, as adding DoEvents is not a complete solution. Also, I've read that DoEvents is often considered quite evil. 
Option Explicit

Sub test()

Dim a(1 To 3) As Double
Dim b(1 To 3) As Double
Dim c(1 To 3) As Double
Dim d(1 To 3) As Double

a(1) = 1.1
a(2) = 1.3
a(3) = 0.8

b(1) = 1.1
b(2) = 1.3
b(3) = 0.8

c(1) = 1.1
c(2) = 1.3
c(3) = 0.8

d(1) = 1.1
d(2) = 1.3
d(3) = 0.8

Call PrintSimChart(a, b, c, d)

End Sub

Function PrintSimChart(a() As Double, b() As Double, c() As Double, d() As Double)
'Prints given simulation result to chart

Dim SimChart As Chart
'Create a new chart.
Set SimChart = Charts.Add

With SimChart

    With .SeriesCollection.NewSeries

        .Values = a
        .XValues = b
        .ChartType = xlColumnStacked
    End With

    With .SeriesCollection.NewSeries

        .Values = c
        .XValues = d
'        DoEvents 'Not adding this line causes a crash on line:   .Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "dd/mm/yyyy"
        .ChartType = xlXYScatterLinesNoMarkers
        .Format.Line.Weight = 1
    End With

    .Axes(xlCategory).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd/mm/yyyy"

    .Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "dd/mm/yyyy"

End With
End Function

Upvotes: 1

Views: 1900

Answers (1)

Cool Blue
Cool Blue

Reputation: 6476

This is the minimal solution that I found that works reliably. Two extra lines were required to fix the problem.

Function PrintSimChart(a() As Double, b() As Double, c() As Double, d() As Double)
'Prints given simulation result to chart

Dim SimChart As Chart
'Create a new chart.
Set SimChart = Charts.Add

With SimChart

    With .SeriesCollection.NewSeries

        .ChartType = xlColumnStacked
        .Values = a
        .XValues = b
    End With

    With .SeriesCollection.NewSeries

        .ChartType = xlXYScatterLinesNoMarkers
        .Values = c
        .XValues = d
        .AxisGroup = xlSecondary   '**Required Edit**
        .Format.Line.Weight = 1
    End With
    With .Axes(xlCategory, xlPrimary)
        .CategoryType = xlTimeScale
        .TickLabels.NumberFormat = "dd/mm/yyyy"
    End With

    .HasAxis(xlCategory, xlSecondary) = True   '**Required Edit**
    .Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "dd/mm/yyyy"
End With

End Function

Upvotes: 1

Related Questions