Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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(Whe=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" wrote in message ... 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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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(Whe=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" wrote in message ... 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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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" wrote in message ... 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(Whe=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" wrote in message ... 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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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" wrote in message ... 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(Whe=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" wrote in message ... 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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart and automation error
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" wrote in message ... 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" wrote in message ... 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(Whe=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" wrote in message ... 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 Whe=xlLocationAsObject, Name:="Sheet3" myChart.ChartType = XlChartType.xlLine End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bay chart error bars automation - constrained to averages? | Charts and Charting in Excel | |||
Run-Time Error'-2147221080(800401a8)': Automation Error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
vc++ automation: opening chart as chart window and setting scale | Excel Programming | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |