How to determine if a chart is embedded or not

[deltaquattro]

Hi guys,

a question for you Excel VBA experts. I wrote the following function
which applies formatting to a chart:

Function FormatAxes(Optional SheetName As String, Optional ChartName
As String, _
Optional IsEmbedded As Boolean, _
Optional Xmin, Optional Xmax, Optional Ymin, Optional Ymax, Optional
XMinorUnit, _
Optional XMajorUnit, Optional YMinorUnit, Optional YMajorUnit)

Dim xlChart As Chart


If SheetName = vbNullString Then SheetName = ActiveSheet.Name

If ChartName = vbNullString Then ChartName = ActiveChart.Name

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

....

End Function

As you can see, i tried to wrote it in such a way that the code would
work also if the sheet name and chart name are not given in input: in
that case, the code usese the active sheet and active chart. Then I
realized that such a code would work only on embedded charts, so I
added the IsEmbedded optional parameter, which tells the code if the
chart is embedded or not. However, I'd really prefer the code to
determine by itself if the chart is embedded or not, rather than
relying on the user to supply the correct value for the IsEmbedded
optional parameter. Could you please help me do this? Thank you very
much,

Best Regards

Sergio Rossi

ps if anybody is interested in the full code of the function, and/or
its goal, feel free to ask

 

[Peter T]

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
Set cht = ActiveWorkbook.Charts(SheetName)
IsChartSheet = Not cht Is Nothing
End Function

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

Regards,
Peter T

 

[Barb Reinhardt]

This might help you see where ChtObj are used

Sub Test()
Dim myChtObj As ChartObject
Dim myCht As Chart
Dim myWS As Excel.Worksheet

For Each myWS In ThisWorkbook.Worksheets
For Each myChtObj In myWS.ChartObjects
Set myCht = myChtObj.Chart
Next myChtObj
Next myWS

For Each myCht In ThisWorkbook.Charts

Next myCht

End Sub

 

[Andrea]

Hello Peter,

thanks very much to you and Barb for the prompt reply! My VBA skills
are somewhat limited, so I'd like to ask you a couple of questions:

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.

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

I don't understand this piece of code...

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?

Regards,
Peter T

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

Best Regards

Sergio

 

[Peter T]

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 -

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).Chart
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

 

[deltaquattro]

Comments in line

Q1> Several ways, one way would be to check if SheetName refers to a chart [..]

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.

Q1
Maybe a typo here? Since cht is a Chart object, the instruction

Set cht = ActiveWorkbook.Charts(SheetName)

will give an error will occur if the name is *not* that of a chart
sheet. Correct?

'On Error Goto 0' resets normal error handling.

Great, now I understand all those pieces of code! Thanks.

Q2.


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.

Wow, that's clever!

Q3



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 -

The scenario is this: I call FormatAxes from other subroutines, and I
want it to work either on the active chart, or on a chart which is not
the active one. If I need it to work on the active chart, then I just
call FormatAxes without passing the chart name: thanks to the line

If ChartName = vbNullString Then ChartName = ActiveChart.Name

it will work on the active chart. Instead, if I need it to work on
another chart, it must be called passing the Optional argument
ChartName. If the inactive chart is not in the active sheet, then you
need to pass also the Optional SheetName.

perhaps you can do something like this

If IsChartSheet(ChartName) Then
Set xlChart = Charts(ChartName)
ElseIf Len(SheetName) then

Q2
I'm not sure why Len would work: from Excel help, "[..] returns a Long
containing the number of characters in a string or the number of bytes
required to store a variable.", so it doesn't return a Boolean.
I already test before if SheetName has been passed as Optional
argument or not, and if it hasn't, I assign it the value
Activesheet.Name. So I think the following should work just fine:

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

Do you agree?

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

Excellent! I found two great books, and I already ordered one of them.
This discussion is really interesting, Peter, you're helping me learn
a lot! In meantime, I received also some other replies to my original
query, so I'll post it here: in order to check whether ChartName
corresponds to an embedded chart or a chart sheet, I've been told that
you can use the following command:

If typename(activechart.parent) = "Workbook" Then
' it's a chart sheet
Else
' it's embedded
End If

Regards,
Peter T

Best Regards,

Sergio

 

[Peter T]

On 21 Gen, 15:28, "Peter T" <peter_t@discussions> wrote:

Sorry about the typo in A1, but glad you got it

The scenario is this: I call FormatAxes from other subroutines, and I
want it to work either on the active chart, or on a chart which is not
the active one.

First of all I'd make an additional procedure purely to return a chart, lets
call it GetChart, then pass the chart to the routine that formats the axes.
Function FormatAxes() is only then concerned with formatting axes, so you'd
remove all arguments other than those pertaining to the axes, but add one,
namely an object reference to the chart you are concerned with. The first
argument will be say 'cht as Chart'

Now lets look at GetChart


Function GetChart(cht As Chart, Optional SheetName As String, _
Optional ChartName As String) As Boolean
On Error GoTo errH
If Len(SheetName) And Len(ChartName) Then
' both strings passed, obviously an embedded chart
Set cht = Sheets(SheetName).ChartObjects(ChartName).Chart
ElseIf Len(SheetName) Then
' ChartName missing so probably a chart sheet wanted
' attempt to reference it
Set cht = Charts(SheetName)
ElseIf Len(ChartName) Then
' SheetName unspecified but got a ChartName
' want an embedded chart on the activesheet
Set cht = ActiveSheet.ChartObjects(ChartName).Chart
Else
' neither sheetName nor ChartName passed,
Set cht = ActiveChart
End If

' let the calling function know we've got a chart
GetChart = Not cht Is Nothing

Exit Function
errH:

MsgBox Err.Description, , "Error in GetChart"

End Function


Best to test it -

Sub test()
Dim cht As Chart

' put a chartobject named "Chart 1" on Sheet1 and select it
' put a chartobject on sheet2 named "Chart 1"
' add a chart sheet named Chart1, simply press F11
' start with Sheet1 active with the chart selected

With Worksheets("Sheet1")
.Activate
.ChartObjects("Chart 1").Activate
End With
ActiveChart.ChartArea.Select


MsgBox GetChart(cht, "Sheet2", "Chart 1"), , cht.Name
Set cht = Nothing
MsgBox GetChart(cht, "Chart1", ""), , cht.Name
Set cht = Nothing
MsgBox GetChart(cht, "", ""), , cht.Name
Set cht = Nothing
MsgBox GetChart(cht, "Sheet3", "Chart 1")
' false because there are no charts on Sheet3

End Sub


If I've missed some of your scenarios maybe you can adapt


In your calling function you might do somehting like this
Dim cht as Chart

If GetChart(cht, other-arg's) Then
FormatAxes cht, other-arg's
Else
' msgbox "failed to get a chart"
End if

I'm not sure why Len would work, from Excel help, "[..] returns a Long
containing the number of characters in a string or the number of bytes
required to store a variable.", so it doesn't return a Boolean.

Len(myString) returns a Long. It's an effective way to test a string, though
not the only way. A number can be coerced to a Boolean to return True (not
zero) or False (zero). So -

If Len(myString) then
' we have a non zero length string
Else
' the string is ""
End If

' could also be written
If Len(myString) = True Then
or
If Len(myString) > 0 then


Regards,
Peter T

 

[deltaquattro]

First of all I'd make an additional procedure purely to return a chart, lets
call it GetChart, then pass the chart to the routine that formats the axes.
Function FormatAxes() is only then concerned with formatting axes, so you'd
remove all arguments other than those pertaining to the axes, but add one,
namely an object reference to the chart you are concerned with. The first
argument will be say 'cht as Chart'

[..]

You're right, of course. It occured to me yesterday, after thinking
over this thread, that maybe passing just the object reference to
FormatAxes would have been better, and I also got the same suggestion
from other sources. A good lesson in correct programming style

I'm not sure why Len would work, from Excel help, "[..] returns a Long
containing the number of characters in a string or the number of bytes
required to store a variable.",  so it doesn't return a Boolean.

Len(myString) returns a Long. It's an effective way to test a string, though
not the only way. A number can be coerced to a Boolean to return True (not
zero) or False (zero). So -

If Len(myString) then
    ' we have a non zero length string
Else
 ' the string is ""
End If

' could also be written
If Len(myString) = True Then
or
If Len(myString) > 0 then

Ok, nice: I use IsMissing to check for Optional arguments, didn't know
that Len would work as well for string arguments.

Regards,
Peter T

Thanks for all your support,

Best Regards

Andrea