Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 crashes when open new workbook | Excel Discussion (Misc queries) | |||
Excel 2003 Crashes | Excel Discussion (Misc queries) | |||
Excel 2003 macro collection | Excel Discussion (Misc queries) | |||
Excel 2003 crashes when opening | Excel Discussion (Misc queries) | |||
Excel 2003 crashes when I close any worksheet | Excel Discussion (Misc queries) |