Displaying series information for embedded charts
 

I have the following procedure to extract information on the chart series in
embedded charts. I get the following error:

Unable to get the formula property of the series class.

The error appears to occur when there is no data to graph for the series in
question. Is there something I can change within the code to get series
information when there is no data to graph?

Thanks

Sub ChartSeriesListEmbedded()
Dim sht As Worksheet
Dim objCht As ChartObject
Dim xVal, yVal, seriesformula
Dim k, i
Dim lastrow
Dim CurBook

CurBook = Application.ActiveWorkbook.Name
ActiveWorkbook.Unprotect
Worksheets.Add.Name = "SeriesList"
'lastrow = Worksheets("SeriesList").Cells(Rows.Count, "a").End(xlUp).row
lastrow = 0

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
Debug.Print sht.Name
For Each objCht In sht.ChartObjects
Debug.Print objCht.Name
With objCht.Chart
For k = 1 To .SeriesCollection.Count
'xVal = .SeriesCollection(k).XValues
'yVal = .SeriesCollection(k).Values
seriesformula = .SeriesCollection(k).Formula
'Debug.Print sht.Name; " has chart; "; .Parent.Name; _
".; Series"; k; "; formula"; seriesformula
Workbooks(CurBook).Worksheets("SeriesList").range( "a" & lastrow + 1).Value =
sht.Name
Workbooks(CurBook).Worksheets("SeriesList").range( "b" & lastrow + 1).Value =
..Parent.Name
Workbooks(CurBook).Worksheets("SeriesList").range( "c" & lastrow + 1).Value = k
Workbooks(CurBook).Worksheets("SeriesList").range( "d" & lastrow + 1).Value =
"'" & seriesformula
lastrow = lastrow + 1
Next k
End With
Next
Next sht
End Sub