Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
If the SeriesCollection Object on the chart has all values contained within
it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
If the SeriesCollection Object on the chart has all values contained
within it as "#N/A", then the object effectively becomes invisible from the code In what way. What object effectively becomes invisible. If you mean the series, sure it's not immediately visible to the user but it can be selected (arrow keys), and not invisible from the code. Note, Chart Object isn't the same object as the ChartObject Object Are you talking about something like this set chtObj = ActiveSheet.ChartObjects(1) Set cht = chtObj.Chart I just tried various ways to reference the series, its values, and source without any problem, even if all values in the series are #N/A, and then assigning a new source range for the series. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
Hi,
It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
It depends on the chart type.
Ah of course, I should have remembered that! Regards, Peter T "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
This is the line that it fails on:
m_chtProductionRateLineChart.Chart.SeriesCollectio n("1st").Values = m_str1stShiftProductionRateConstants When the series contains the array of: #N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A The Series object is no longer visible within the Watch window and the code for some reason can not see it, but yet, even with the code in Break mode on the line above, I can perform the following steps: Within the Intermediate Window, type in: ?m_str1stShiftProductionRateConstants And when I press the "Enter" key at the end of the line, it returns the values within the string variable. I then go into the DataSource of the chart on the spreadsheet side, and paste the data in between the curly brackets, which then as long as it's not full of the "#N/A" values, the code will move on after I click on the Run button within VB Editor. Also note, if all series within the SeriesCollection has all values as "#N/A", the SeriesCollection collection isn't even visible within the Watch Window. I hate the idea of comparing rather if a variable contains such a string, and if so, then delete the series from the chart (if it's not already), but if it doesn't, check to be sure the series is in the chart, and if it's not, then add the series to the chart. This would mean I would have to set a lot more properties in Run-Time mode rather then depending on this sort of stuff being set in Design-Time mode. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code In what way. What object effectively becomes invisible. If you mean the series, sure it's not immediately visible to the user but it can be selected (arrow keys), and not invisible from the code. Note, Chart Object isn't the same object as the ChartObject Object Are you talking about something like this set chtObj = ActiveSheet.ChartObjects(1) Set cht = chtObj.Chart I just tried various ways to reference the series, its values, and source without any problem, even if all values in the series are #N/A, and then assigning a new source range for the series. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
Bare in mind, I'm not assigning a range, but rather constants to the Values
property. Reason being, the plan is to eventually replicate a copy of the worksheet from the process file to the work center file, but then the code would then remove all external defined names from the work center file. If it contained just a range name, that wouldn't have been an issue, but it's assigning constants. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
So which chart type(s) would it still see such a series?
-- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
Andy gave the example as a column chart. Essentially those charts without
the capability to display values as markers seem to treat #N/A as zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ronald R. Dodge, Jr." wrote in message ... So which chart type(s) would it still see such a series? -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
It doesn't matter if the unplottable values are in a range or in an array.
Have you done the proof of concept with all values loaded into the chart as arrays? If not, check it out before spending too much time on your final approach. There is a limit to how long an array can be to work as the source data for a chart series, and the limit is in the number of characters it takes to represent the array as a string, surrounded with curly braces and separated with commas. The limit is around 250 or so characters. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ronald R. Dodge, Jr." wrote in message ... Bare in mind, I'm not assigning a range, but rather constants to the Values property. Reason being, the plan is to eventually replicate a copy of the worksheet from the process file to the work center file, but then the code would then remove all external defined names from the work center file. If it contained just a range name, that wouldn't have been an issue, but it's assigning constants. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SeriesCollection Object Issue
I'm not close to that limit, as far as the 255 character limit is concerned.
However, cause of the manual work that I was doing, I had been thinking about truncating those values down to the significant 4 digits (an Accounting rule), then this way, I don't have mutliple lines of data to copy from the Intermediate window either. There would be, 49 characters at most for any one data series given 8 points potentially to plot rather than the currently 137 characters potentially to plot. No plottable point would reach 5 digit figures [to the left of the decimal point] given current capacities of work centers. Thank you for those that mentioned about the chart type as that resolved the issue. I ended up using the following snippet of code: m_chtSetupAverageTimeLineChart.Chart.ChartType = xlColumnClustered m_chtSetupAverageTimeLineChart.Chart.SeriesCollect ion("1st").Values = _ m_str1stShiftAverageSetupTimeConstants m_chtSetupAverageTimeLineChart.Chart.SeriesCollect ion("3rd").Values = _ m_str3rdShiftAverageSetupTimeConstants m_chtSetupAverageTimeLineChart.Chart.ChartType = xlLineMarkers While I may not know any one particular area fully inside out, I'm integrating a lot of different areas into each other including those items outside of MS Office. Some of these things came up as a result of other issues that I have been resolving, which I been working on making some of my older codes more dynamic and modulated, thus I also have gotten quite deep into Class Coding too. What brought on me making the adjustments that had worked for years prior? Let's just say corporate did some things that broke my reports and it's was even to the point that I can't really rely on formulas to do certain things, and the only way to carry the dynamicness is to use range names and refer to them within VBA, which then that brought on some other issues along the way, such as having to use VBA coding to manage those names. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Jon Peltier" wrote in message ... It doesn't matter if the unplottable values are in a range or in an array. Have you done the proof of concept with all values loaded into the chart as arrays? If not, check it out before spending too much time on your final approach. There is a limit to how long an array can be to work as the source data for a chart series, and the limit is in the number of characters it takes to represent the array as a string, surrounded with curly braces and separated with commas. The limit is around 250 or so characters. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ronald R. Dodge, Jr." wrote in message ... Bare in mind, I'm not assigning a range, but rather constants to the Values property. Reason being, the plan is to eventually replicate a copy of the worksheet from the process file to the work center file, but then the code would then remove all external defined names from the work center file. If it contained just a range name, that wouldn't have been an issue, but it's assigning constants. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Andy Pope" wrote in message ... Hi, It depends on the chart type. I created this single line by changing the source data for a column chart. ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2" No problem whether the range B2:B6 contains values, #N/As or is empty. Change the chart type to Line and try using all empty or #N/A data and it will raise a error 1004. If your chart is not too complex you can change the type in order to manipulate it and the return the chart type to that you want. Cheers Andy Ronald R. Dodge, Jr. wrote: If the SeriesCollection Object on the chart has all values contained within it as "#N/A", then the object effectively becomes invisible from the code and when attempting to set the values to it again via code, it errors out as the code can't see the object on the Chart Object (Note, Chart Object isn't the same object as the ChartObject Object). However, on the spreadsheet side, one can clearly go into the DataSource of the chart and see it there. What needs to be done to get this issue resolved as I must still be able to print out the charts, even if at least one of the series contains all "#N/A" values, which is needed for interpolated charts to work properly. For the time being, I'm having to do this manually, and I don't like that idea. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method 'SeriesCollection' of object '_Chart'failed | Excel Programming | |||
Add method of SeriesCollection object clears Windows clipboard | Excel Programming | |||
Object variable issue | Excel Programming | |||
Formatting a new seriescollection in a chart object | Excel Programming | |||
Adding to the SeriesCollection of a chart object in VB6 | Excel Programming |