datalabels collection crashes Excel 2003
 

I would be more inclined to think your error handling might be the source of
the problem

Try something like this:

Sub testdatalabels()
Dim ch As Chart
Set ch = ActiveChart
Dim sc As Variant, i As Integer, j As Integer
Set sc = ch.SeriesCollection
Debug.Print "Type of sc is "; TypeName(sc)
Debug.Print "sc.count "; sc.Count
Dim datalabels As Variant, dl As Variant
Dim caption As String
on Error goto ErrHandler
For i = 1 To sc.Count
Set dls = sc(i).datalabels
Debug.Print "i = "; i; " type of dls is "; TypeName(dls); " dls.count = ";
dls.Count

For j = 1 To dls.Count
Set dl = dls(j)
Debug.Print j, TypeName(dl)
caption = "Null"
On Error Resume Next
caption = dl.caption
On Error goto ErrHandler
Debug.Print caption
Lbl1:
Next j
Next i
Exit Sub
ErrHandler:
Debug.Print "error "; Err.Description; " at i="; i; " j = "; j
Resume Lbl1
End Sub

--
Regards,
Tom Ogilvy

"news.microsoft.com" <Peter_Quirk at hotmail dot com wrote in message
...
I have constructed a scatter chart that has one series per point so that I
can control the symbol, color, font, font size of each point.

In my VBA code I want to change the font size of each datalabel. I was
having extreme difficulty with the datalabels collection, so I wrote the
following code to explore what was going on. I didn't trust anything, so I
assigned collections to variants and tested their type and their .counts.

Sub testdatalabels()
Dim ch As Chart
Set ch = ActiveChart
Dim sc As Variant, i As Integer, j As Integer
Set sc = ch.SeriesCollection
Debug.Print "Type of sc is "; TypeName(sc)
Debug.Print "sc.count "; sc.Count
Dim datalabels As Variant, dl As Variant
Dim caption As String

For i = 1 To sc.Count
Set dls = sc(i).datalabels
Debug.Print "i = "; i; " type of dls is "; TypeName(dls); " dls.count = ";
dls.Count
For j = 1 To dls.Count
On Error GoTo lbl1
Set dl = dls(j)
Debug.Print j, TypeName(dl)
caption = "Null"
On Error Resume Next
caption = dl.caption
On Error GoTo 0
Debug.Print caption
GoTo lbl2:
lbl1:
Debug.Print "error "; Err.Description; " at i="; i; " j = "; j
On Error GoTo 0
lbl2:
Next j
Next i
End Sub

When I run this with the active chart, which has five series, each with
one data point, I see the following in the intermediate window before Excel
hangs in the inner loop with i=1 and j=3 at the line "set dl = dls(j)".

Type of sc is SeriesCollection
sc.count 5
i = 1 type of dls is DataLabels dls.count = 3
1 DataLabel
Caption1text
2 DataLabel
Null

If you attempt to examine dl in the Locals Window just before executing
this statement, Excel will also crash. Seems to me like a cut and dried bug.

datalabels collection crashes Excel 2003
 

Watch the word wrap:

Sub testdatalabels()
Dim ch As Chart
Set ch = ActiveChart
Dim sc As Variant, i As Integer, j As Integer
Set sc = ch.SeriesCollection
Debug.Print "Type of sc is "; TypeName(sc)
Debug.Print "sc.count "; sc.Count
Dim datalabels As Variant, dl As Variant
Dim caption As String
on Error goto ErrHandler
For i = 1 To sc.Count
Set dls = sc(i).datalabels
Debug.Print "i = "; i; " type of dls is "; _
TypeName(dls); " dls.count = "; dls.Count

For j = 1 To dls.Count
Set dl = dls(j)
Debug.Print j, TypeName(dl)
caption = "Null"
On Error Resume Next
caption = dl.caption
On Error goto ErrHandler
Debug.Print caption
Lbl1:
Next j
Next i
Exit Sub
ErrHandler:
Debug.Print "error "; Err.Description; _
" at i="; i; " j = "; j
Resume Lbl1
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
I would be more inclined to think your error handling might be the source
of
the problem

Try something like this:

Sub testdatalabels()
Dim ch As Chart
Set ch = ActiveChart
Dim sc As Variant, i As Integer, j As Integer
Set sc = ch.SeriesCollection
Debug.Print "Type of sc is "; TypeName(sc)
Debug.Print "sc.count "; sc.Count
Dim datalabels As Variant, dl As Variant
Dim caption As String
on Error goto ErrHandler
For i = 1 To sc.Count
Set dls = sc(i).datalabels
Debug.Print "i = "; i; " type of dls is "; TypeName(dls); " dls.count = ";
dls.Count

For j = 1 To dls.Count
Set dl = dls(j)
Debug.Print j, TypeName(dl)
caption = "Null"
On Error Resume Next
caption = dl.caption
On Error goto ErrHandler
Debug.Print caption
Lbl1:
Next j
Next i
Exit Sub
ErrHandler:
Debug.Print "error "; Err.Description; " at i="; i; " j = "; j
Resume Lbl1
End Sub

--
Regards,
Tom Ogilvy

"news.microsoft.com" <Peter_Quirk at hotmail dot com wrote in message
...
I have constructed a scatter chart that has one series per point so that
I
can control the symbol, color, font, font size of each point.

In my VBA code I want to change the font size of each datalabel. I was
having extreme difficulty with the datalabels collection, so I wrote the
following code to explore what was going on. I didn't trust anything, so I
assigned collections to variants and tested their type and their .counts.

Sub testdatalabels()
Dim ch As Chart
Set ch = ActiveChart
Dim sc As Variant, i As Integer, j As Integer
Set sc = ch.SeriesCollection
Debug.Print "Type of sc is "; TypeName(sc)
Debug.Print "sc.count "; sc.Count
Dim datalabels As Variant, dl As Variant
Dim caption As String

For i = 1 To sc.Count
Set dls = sc(i).datalabels
Debug.Print "i = "; i; " type of dls is "; TypeName(dls); " dls.count =
";
dls.Count
For j = 1 To dls.Count
On Error GoTo lbl1
Set dl = dls(j)
Debug.Print j, TypeName(dl)
caption = "Null"
On Error Resume Next
caption = dl.caption
On Error GoTo 0
Debug.Print caption
GoTo lbl2:
lbl1:
Debug.Print "error "; Err.Description; " at i="; i; " j = "; j
On Error GoTo 0
lbl2:
Next j
Next i
End Sub

When I run this with the active chart, which has five series, each with
one data point, I see the following in the intermediate window before
Excel
hangs in the inner loop with i=1 and j=3 at the line "set dl = dls(j)".

Type of sc is SeriesCollection
sc.count 5
i = 1 type of dls is DataLabels dls.count = 3
1 DataLabel
Caption1text
2 DataLabel
Null

If you attempt to examine dl in the Locals Window just before executing
this statement, Excel will also crash. Seems to me like a cut and dried
bug.