"Andrea" wrote in message


Comments in line

Q1
Several ways, one way would be to check if SheetName refers to a chart
sheet, eg

Function IsChartSheet(SheetName) As Boolean
Dim cht As Chart
On Error Resume Next
^^^^^^^^^^^^^^^^^^^^^^^^^
What's this? I've seen this construct some other times, usually in the
form

On Error Resume Next
(do something)
On Error GoTo 0

and then there's no label 0 in all the code(!). Is this VBA way of
handling errors? Does it have something to do with OO coding? I come
from a Fortran background and I'm not sure what's going on here.

A1
In this example any errors following Resume Next will be ignored, rather
than passed to another Line to be handled. In the posted example we expect
an occur while attempting to assign the reference if the name is that of a
chart sheet.

'On Error Goto 0' resets normal error handling.


Q2.
Set cht = ActiveWorkbook.Charts(SheetName)
IsChartSheet = Not cht Is Nothing

A2
Either we succeeded or failed to assign an object reference (a chart in this
case) to the object variable 'cht'. If it failed, the state of 'cht' will be
'Nothing'. We can test that with the keyword 'Is'

objVariable Is Nothing = True ' or False

If True means it's nothing/unassigned. The keyword 'Not' reverses the
boolean true/false. So to return the result of our test to the function
(does a chart-sheet named chartName exist in the active workbook) we negate
the result of the Is Nothing test.


Q3

End Function

Note also, although unusual it's possible for a chartsheet (ie a chart) to
include one or more embedded charts.


Does this mean that my code + your code will not be able to tell if
ChartName corresponds to an embedded chart or a chart, in the case of
a chart embedded in another chart? Or will it work anyway?

A3
You would need to adapt the use of my function into your code. As it stands
you don't quite conclusively have the whole picture. All the IsChartSheet
function prooves is whether or not a chart-sheet named ChartName exists. If
it does, you can probably infer the user wants to process that chart sheet.
I' not sure as I don't know everything about your secnario, but maybe
instead of this -

If IsEmbedded Then
Set xlChart = Worksheets(SheetName).ChartObjects
(ChartName).Chart
Else
Set xlChart = Charts(ChartName)
End If

perhaps you can do something like this

If IsChartSheet(ChartName) Then
Set xlChart = Charts(ChartName)
ElseIf Len(SheetName) then
Set xlChart = Worksheets(SheetName).ChartObjects(ChartName).Char t
Else
Set xlChart = ActiveSheet.ChartObjects(ChartName).Chart


Q4
Thanks again, and one last question: can you suggest a good Excel/VBA
book to study VBA programming in Excel?

A4
I don't have a list of suggested books to hand, but search this ng as others
have given recommendations

Regards,
Peter T