Hello,

I am starting to play with macro features in Excel. For testing purposes, I
have created a table of random values and want to creat a macro that will
plot a line chart of selected values. I've encountered an error and am
asking if someone can explain why it is happening and how to correct the
code. Sample code is provided below. The last line of the sample code (i.e.
myChart.ChartType = XlChartType.xlLine) generates the error:
run-time error '-2147221080 (800401a8): Automation error

The code runs without problems if I replace the problem line with:
ActiveChart.ChartType = XlChartType.xlLine

My question is why does 'ActiveChart.ChartType ' work but not
'myChart.ChartType '?

Thanks,

Ian







Public Sub TestChart()

Dim myWorkBook As Workbook
Set myWorkBook = ActiveWorkbook

Dim strWorkSheet As String
strWorkSheet = "Sheet3"
Dim myWorkSheet As Worksheet
Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)

Dim ctGroups As Integer
ctGroups = 2

Dim ctGroupRows As Integer
ctGroupRows = 10

Dim myChart As Chart
Set myChart = myWorkBook.Charts.Add
myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
myChart.ChartType = XlChartType.xlLine


End Sub

As soon as you run the myChart.Location statement, myChart is no longer
defined, because the chart sheet no longer exists. You would have to use
something like (untested)

Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Sheet3")

or better yet, simply start by creating the chart object:

Set myChart = Worksheets("Sheet3").ChartObjects.Add({left, top, width,
height}).Chart

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ian" <(E-Mail Removed)> wrote in message
news:FDFA73C5-8EBE-48A0-A825-(E-Mail Removed)...
> Hello,
>
> I am starting to play with macro features in Excel. For testing purposes,
> I
> have created a table of random values and want to creat a macro that will
> plot a line chart of selected values. I've encountered an error and am
> asking if someone can explain why it is happening and how to correct the
> code. Sample code is provided below. The last line of the sample code
> (i.e.
> myChart.ChartType = XlChartType.xlLine) generates the error:
> run-time error '-2147221080 (800401a8): Automation error
>
> The code runs without problems if I replace the problem line with:
> ActiveChart.ChartType = XlChartType.xlLine
>
> My question is why does 'ActiveChart.ChartType ' work but not
> 'myChart.ChartType '?
>
> Thanks,
>
> Ian
>
>
>
>
>
>
>
> Public Sub TestChart()
>
> Dim myWorkBook As Workbook
> Set myWorkBook = ActiveWorkbook
>
> Dim strWorkSheet As String
> strWorkSheet = "Sheet3"
> Dim myWorkSheet As Worksheet
> Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)
>
> Dim ctGroups As Integer
> ctGroups = 2
>
> Dim ctGroupRows As Integer
> ctGroupRows = 10
>
> Dim myChart As Chart
> Set myChart = myWorkBook.Charts.Add
> myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
> myChart.ChartType = XlChartType.xlLine
>
>
> End Sub
>

Hello Jon,

I do not understand why 'myChart' is defined on one line and then, is not
defined after execution of the following line. Could you explain what is
happening? Why is the scope of 'myChart' not the subroutine (i.e. it is only
freed/released at the end of the subroutine)?

Thanks,

Ian

"Jon Peltier" wrote:

> As soon as you run the myChart.Location statement, myChart is no longer
> defined, because the chart sheet no longer exists. You would have to use
> something like (untested)
>
> Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Sheet3")
>
> or better yet, simply start by creating the chart object:
>
> Set myChart = Worksheets("Sheet3").ChartObjects.Add({left, top, width,
> height}).Chart
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Ian" <(E-Mail Removed)> wrote in message
> news:FDFA73C5-8EBE-48A0-A825-(E-Mail Removed)...
> > Hello,
> >
> > I am starting to play with macro features in Excel. For testing purposes,
> > I
> > have created a table of random values and want to creat a macro that will
> > plot a line chart of selected values. I've encountered an error and am
> > asking if someone can explain why it is happening and how to correct the
> > code. Sample code is provided below. The last line of the sample code
> > (i.e.
> > myChart.ChartType = XlChartType.xlLine) generates the error:
> > run-time error '-2147221080 (800401a8): Automation error
> >
> > The code runs without problems if I replace the problem line with:
> > ActiveChart.ChartType = XlChartType.xlLine
> >
> > My question is why does 'ActiveChart.ChartType ' work but not
> > 'myChart.ChartType '?
> >
> > Thanks,
> >
> > Ian
> >
> >
> >
> >
> >
> >
> >
> > Public Sub TestChart()
> >
> > Dim myWorkBook As Workbook
> > Set myWorkBook = ActiveWorkbook
> >
> > Dim strWorkSheet As String
> > strWorkSheet = "Sheet3"
> > Dim myWorkSheet As Worksheet
> > Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)
> >
> > Dim ctGroups As Integer
> > ctGroups = 2
> >
> > Dim ctGroupRows As Integer
> > ctGroupRows = 10
> >
> > Dim myChart As Chart
> > Set myChart = myWorkBook.Charts.Add
> > myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
> > myChart.ChartType = XlChartType.xlLine
> >
> >
> > End Sub
> >
>
>
>

Ian -

myChart was at first defined as a standalone chart sheet. When you changed
the location, a new chart with the data and formatting of myChart was
created on the parent sheet, and the original chart sheet (myChart) was
destroyed. The variable myChart no longer pointed to any object.

Instead of questioning the logic of this (and it is logical when you
understand it), simply know that you have to redefine a chart variable when
the chart changes its location. Or bypass this two-step protocol and use
ChartObjects.Add.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ian" <(E-Mail Removed)> wrote in message
news:F0E02088-FD92-4F07-9B01-(E-Mail Removed)...
> Hello Jon,
>
> I do not understand why 'myChart' is defined on one line and then, is not
> defined after execution of the following line. Could you explain what is
> happening? Why is the scope of 'myChart' not the subroutine (i.e. it is
> only
> freed/released at the end of the subroutine)?
>
> Thanks,
>
> Ian
>
> "Jon Peltier" wrote:
>
>> As soon as you run the myChart.Location statement, myChart is no longer
>> defined, because the chart sheet no longer exists. You would have to use
>> something like (untested)
>>
>> Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Sheet3")
>>
>> or better yet, simply start by creating the chart object:
>>
>> Set myChart = Worksheets("Sheet3").ChartObjects.Add({left, top, width,
>> height}).Chart
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Ian" <(E-Mail Removed)> wrote in message
>> news:FDFA73C5-8EBE-48A0-A825-(E-Mail Removed)...
>> > Hello,
>> >
>> > I am starting to play with macro features in Excel. For testing
>> > purposes,
>> > I
>> > have created a table of random values and want to creat a macro that
>> > will
>> > plot a line chart of selected values. I've encountered an error and
>> > am
>> > asking if someone can explain why it is happening and how to correct
>> > the
>> > code. Sample code is provided below. The last line of the sample code
>> > (i.e.
>> > myChart.ChartType = XlChartType.xlLine) generates the error:
>> > run-time error '-2147221080 (800401a8): Automation error
>> >
>> > The code runs without problems if I replace the problem line with:
>> > ActiveChart.ChartType = XlChartType.xlLine
>> >
>> > My question is why does 'ActiveChart.ChartType ' work but not
>> > 'myChart.ChartType '?
>> >
>> > Thanks,
>> >
>> > Ian
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Public Sub TestChart()
>> >
>> > Dim myWorkBook As Workbook
>> > Set myWorkBook = ActiveWorkbook
>> >
>> > Dim strWorkSheet As String
>> > strWorkSheet = "Sheet3"
>> > Dim myWorkSheet As Worksheet
>> > Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)
>> >
>> > Dim ctGroups As Integer
>> > ctGroups = 2
>> >
>> > Dim ctGroupRows As Integer
>> > ctGroupRows = 10
>> >
>> > Dim myChart As Chart
>> > Set myChart = myWorkBook.Charts.Add
>> > myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
>> > myChart.ChartType = XlChartType.xlLine
>> >
>> >
>> > End Sub
>> >
>>
>>
>>

Hello Jon,

I have one last question for you. I am working with MS Office 2002 and the
information your provided is certainly not included in the online help.
Would you mind telling me where I can find such information as I question
whether is will be included in all books.

Thank you

Ian

"Jon Peltier" wrote:

> Ian -
>
> myChart was at first defined as a standalone chart sheet. When you changed
> the location, a new chart with the data and formatting of myChart was
> created on the parent sheet, and the original chart sheet (myChart) was
> destroyed. The variable myChart no longer pointed to any object.
>
> Instead of questioning the logic of this (and it is logical when you
> understand it), simply know that you have to redefine a chart variable when
> the chart changes its location. Or bypass this two-step protocol and use
> ChartObjects.Add.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Ian" <(E-Mail Removed)> wrote in message
> news:F0E02088-FD92-4F07-9B01-(E-Mail Removed)...
> > Hello Jon,
> >
> > I do not understand why 'myChart' is defined on one line and then, is not
> > defined after execution of the following line. Could you explain what is
> > happening? Why is the scope of 'myChart' not the subroutine (i.e. it is
> > only
> > freed/released at the end of the subroutine)?
> >
> > Thanks,
> >
> > Ian
> >
> > "Jon Peltier" wrote:
> >
> >> As soon as you run the myChart.Location statement, myChart is no longer
> >> defined, because the chart sheet no longer exists. You would have to use
> >> something like (untested)
> >>
> >> Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Sheet3")
> >>
> >> or better yet, simply start by creating the chart object:
> >>
> >> Set myChart = Worksheets("Sheet3").ChartObjects.Add({left, top, width,
> >> height}).Chart
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Ian" <(E-Mail Removed)> wrote in message
> >> news:FDFA73C5-8EBE-48A0-A825-(E-Mail Removed)...
> >> > Hello,
> >> >
> >> > I am starting to play with macro features in Excel. For testing
> >> > purposes,
> >> > I
> >> > have created a table of random values and want to creat a macro that
> >> > will
> >> > plot a line chart of selected values. I've encountered an error and
> >> > am
> >> > asking if someone can explain why it is happening and how to correct
> >> > the
> >> > code. Sample code is provided below. The last line of the sample code
> >> > (i.e.
> >> > myChart.ChartType = XlChartType.xlLine) generates the error:
> >> > run-time error '-2147221080 (800401a8): Automation error
> >> >
> >> > The code runs without problems if I replace the problem line with:
> >> > ActiveChart.ChartType = XlChartType.xlLine
> >> >
> >> > My question is why does 'ActiveChart.ChartType ' work but not
> >> > 'myChart.ChartType '?
> >> >
> >> > Thanks,
> >> >
> >> > Ian
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Public Sub TestChart()
> >> >
> >> > Dim myWorkBook As Workbook
> >> > Set myWorkBook = ActiveWorkbook
> >> >
> >> > Dim strWorkSheet As String
> >> > strWorkSheet = "Sheet3"
> >> > Dim myWorkSheet As Worksheet
> >> > Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)
> >> >
> >> > Dim ctGroups As Integer
> >> > ctGroups = 2
> >> >
> >> > Dim ctGroupRows As Integer
> >> > ctGroupRows = 10
> >> >
> >> > Dim myChart As Chart
> >> > Set myChart = myWorkBook.Charts.Add
> >> > myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
> >> > myChart.ChartType = XlChartType.xlLine
> >> >
> >> >
> >> > End Sub
> >> >
> >>
> >>
> >>
>
>
>

I guess a lot of these things are not documented. I know it because I know
it, or I've figured it out.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ian" <(E-Mail Removed)> wrote in message
news:8BCCFA70-3966-4D5E-AD17-(E-Mail Removed)...
>
> Hello Jon,
>
> I have one last question for you. I am working with MS Office 2002 and
> the
> information your provided is certainly not included in the online help.
> Would you mind telling me where I can find such information as I question
> whether is will be included in all books.
>
> Thank you
>
> Ian
>
> "Jon Peltier" wrote:
>
>> Ian -
>>
>> myChart was at first defined as a standalone chart sheet. When you
>> changed
>> the location, a new chart with the data and formatting of myChart was
>> created on the parent sheet, and the original chart sheet (myChart) was
>> destroyed. The variable myChart no longer pointed to any object.
>>
>> Instead of questioning the logic of this (and it is logical when you
>> understand it), simply know that you have to redefine a chart variable
>> when
>> the chart changes its location. Or bypass this two-step protocol and use
>> ChartObjects.Add.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Ian" <(E-Mail Removed)> wrote in message
>> news:F0E02088-FD92-4F07-9B01-(E-Mail Removed)...
>> > Hello Jon,
>> >
>> > I do not understand why 'myChart' is defined on one line and then, is
>> > not
>> > defined after execution of the following line. Could you explain what
>> > is
>> > happening? Why is the scope of 'myChart' not the subroutine (i.e. it
>> > is
>> > only
>> > freed/released at the end of the subroutine)?
>> >
>> > Thanks,
>> >
>> > Ian
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> As soon as you run the myChart.Location statement, myChart is no
>> >> longer
>> >> defined, because the chart sheet no longer exists. You would have to
>> >> use
>> >> something like (untested)
>> >>
>> >> Set myChart = myChart.Location(Where:=xlLocationAsObject,
>> >> Name:="Sheet3")
>> >>
>> >> or better yet, simply start by creating the chart object:
>> >>
>> >> Set myChart = Worksheets("Sheet3").ChartObjects.Add({left, top, width,
>> >> height}).Chart
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "Ian" <(E-Mail Removed)> wrote in message
>> >> news:FDFA73C5-8EBE-48A0-A825-(E-Mail Removed)...
>> >> > Hello,
>> >> >
>> >> > I am starting to play with macro features in Excel. For testing
>> >> > purposes,
>> >> > I
>> >> > have created a table of random values and want to creat a macro that
>> >> > will
>> >> > plot a line chart of selected values. I've encountered an error
>> >> > and
>> >> > am
>> >> > asking if someone can explain why it is happening and how to correct
>> >> > the
>> >> > code. Sample code is provided below. The last line of the sample
>> >> > code
>> >> > (i.e.
>> >> > myChart.ChartType = XlChartType.xlLine) generates the error:
>> >> > run-time error '-2147221080 (800401a8): Automation error
>> >> >
>> >> > The code runs without problems if I replace the problem line with:
>> >> > ActiveChart.ChartType = XlChartType.xlLine
>> >> >
>> >> > My question is why does 'ActiveChart.ChartType ' work but not
>> >> > 'myChart.ChartType '?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Ian
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Public Sub TestChart()
>> >> >
>> >> > Dim myWorkBook As Workbook
>> >> > Set myWorkBook = ActiveWorkbook
>> >> >
>> >> > Dim strWorkSheet As String
>> >> > strWorkSheet = "Sheet3"
>> >> > Dim myWorkSheet As Worksheet
>> >> > Set myWorkSheet = myWorkBook.Worksheets(strWorkSheet)
>> >> >
>> >> > Dim ctGroups As Integer
>> >> > ctGroups = 2
>> >> >
>> >> > Dim ctGroupRows As Integer
>> >> > ctGroupRows = 10
>> >> >
>> >> > Dim myChart As Chart
>> >> > Set myChart = myWorkBook.Charts.Add
>> >> > myChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
>> >> > myChart.ChartType = XlChartType.xlLine
>> >> >
>> >> >
>> >> > End Sub
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>