Problem with Charts in VBA for Excel

  • Thread starter Thread starter Edward Ulle
  • Start date Start date
E

Edward Ulle

What is wrong with the following code. I some times get the message
"method 'HasTitle' of object '_Chart' failed"

========================================================
Public Sub CompareSpectra()
CompareSpectraListDialog.Show
End Sub

Public Function CompareCurves(cChart As Chart, strWorksheetName As
String, dblDamping As Double) As Chart

Dim wsWorksheet As Worksheet
Dim iNumFreq As Integer
Dim iNumDamp As Integer
Dim strRange As String
Dim sSeries As Series

Set wsWorksheet = Worksheets(strWorksheetName)

If cChart Is Nothing Then

Set cChart = Charts.Add(after:=Sheets(Sheets.Count))

With cChart
.Name = strWorksheetName + "_Compare"
.HasTitle = True
.....
Intervening code removed for brevity
.....

End If

CompareCurves = cChart

End Function
 
Edward -

If your chart has no data charted, it cannot have a chart title, so the
..HasTitle is meaningless. Either select some data before running the
code, or insert code before the .HasTitle line that creates a new series
and populates it with data.

- Jon
 
The error does not occur all the time. I have successfully created new
charts using this routine. I create the chart sheet first with all the
parameters set. Then populate it with Series. I have examples of both
working and non-working cases.

By the way the cChart variable passed into the function comes from the
code for the CompareSpectraListDialog Form.

Attached is the entire code for this routine.

==========================================================
Option Explicit

Public Sub CompareSpectra()
CompareSpectraListDialog.Show
End Sub

Public Function CompareCurves(cChart As Chart, strWorksheetName As
String, dblDamping As Double) As Chart

Dim wsWorksheet As Worksheet
Dim iNumFreq As Integer
Dim iNumDamp As Integer
Dim strRange As String
Dim sSeries As Series

Set wsWorksheet = Worksheets(strWorksheetName)

If cChart.Name Is Nothing Then

Set cChart = Charts.Add(after:=Sheets(Sheets.Count))

With cChart
.Name = strWorksheetName + "_Compare"
.HasTitle = True
.ChartTitle.Text = wsWorksheet.Range("A1").Offset(0, 1) +
vbCr + wsWorksheet.Range("A1").Offset(0, 2)
.ChartTitle.Font.Size = 12
.HasLegend = True
.ChartType = xlXYScatterLinesNoMarkers
With .PlotArea
.Border.Color = RGB(0, 0, 0)
.Border.Weight = xlThin
.Interior.Pattern = xlPatternNone
.Width = 680
.Height = 420
End With
With .PageSetup
.Orientation = xlLandscape
.TopMargin = 20
.BottomMargin = 20
.LeftMargin = 36
.RightMargin = 54
.HeaderMargin = 10
.FooterMargin = 10
End With
End With
' X Axis
With cChart.Axes(xlCategory)
.HasTitle = True
.HasMajorGridlines = True
.HasMinorGridlines = True
.AxisTitle.Text = wsWorksheet.Range("A1").Offset(0, 3)
.TickLabels.NumberFormat = "0.0"
.ScaleType = xlScaleLogarithmic
.Crosses = xlCustom
.CrossesAt = 0.01
End With
' Y Axis
With cChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = wsWorksheet.Range("A1").Offset(0, 4)
.TickLabels.NumberFormat = "0.00"
.Crosses = xlCustom
.CrossesAt = -xlMinimum
End With

iNumDamp = 1
Do While Not IsEmpty(wsWorksheet.Range("A1").Offset(1,
iNumDamp))
If wsWorksheet.Range("A1").Offset(1, iNumDamp) = dblDamping
Then
iNumFreq = 3
Do While Not
IsEmpty(wsWorksheet.Range("A1").Offset(iNumFreq, iNumDamp))
iNumFreq = iNumFreq + 1
Loop
strRange = Chr(64 + iNumDamp) + "4:" + Chr(65 +
iNumDamp) + Trim(Str(iNumFreq))
cChart.SetSourceData
Source:=wsWorksheet.Range(strRange), PlotBy:=xlColumns
Set sSeries = cChart.SeriesCollection(1)
sSeries.Name = Str(100 * dblDamping) + "% (" +
strWorksheetName + ")"
Exit Do
End If
iNumDamp = iNumDamp + 2
Loop

Else

iNumDamp = 1
Do While Not IsEmpty(wsWorksheet.Range("A1").Offset(1,
iNumDamp))
If wsWorksheet.Range("A1").Offset(1, iNumDamp) = dblDamping
Then
iNumFreq = 3
Do While Not
IsEmpty(wsWorksheet.Range("A1").Offset(iNumFreq, iNumDamp))
iNumFreq = iNumFreq + 1
Loop
Set sSeries = cChart.SeriesCollection.newSeries
strRange = Chr(64 + iNumDamp) + "4:" + Chr(64 +
iNumDamp) + Trim(Str(iNumFreq))
sSeries.XValues = wsWorksheet.Range(strRange)
strRange = Chr(65 + iNumDamp) + "4:" + Chr(65 +
iNumDamp) + Trim(Str(iNumFreq))
sSeries.Values = wsWorksheet.Range(strRange)
sSeries.Name = Str(100 * dblDamping) + "% (" +
strWorksheetName + ")"
Exit Do
End If
iNumDamp = iNumDamp + 2
Loop

' Relocate legend
With cChart.Legend
.Top = 110
.Left = 78
.Border.Weight = xlThin
End With

End If

Set CompareCurves = cChart

End Function
 
Edward -

Yes, it sometimes works. I've never bothered figuring out why, but
sometimes Excel starts a chart with a one-point series even without
specifying data. Since you specify the data range in the same macro, why
not do that earlier in the macro and set your preferences later?

- Jon
 
You are correct. When a new chart is created a new series is also
automatically created.

I reversed the order of assigning the first series data and then the
chart parameters and it is working.

It is these idiosyncrasies that can be frustrating to development but
once you get past them, Excel and VBA become a powerful tool.

Thanks again.
 
Edward -

After a while, you begin to know the object model well enough that you
can anticipate the problems. You still make the mistakes, but they are
more easily fixed.

- Jon
 
Back
Top