Create a Chart with Values from Array AND NOT from Ranges

I'm trying to manage data's graph from an array but when I try following case

|
|
|
|__________________________
|France | Italy | France | Italie |
| 2003 | 2004 |


I don't succeeded to generate the appropriate array.
Does anyone know how to do that WITHOUT ranges?

Thx

Poser of the day. I spent a half hour trying to insert an array into the
chart series' XValues property, and it wouldn't go.

If you construct a chart using a range to produce this category axis
pattern, then use the F9 key to change the XValues, your category axis
labels are put into this array:

={"2004","France","0","Italy","2005","France","0","Italy"}

which is just one dimensional, and shouldn't work. And it doesn't if you
enter just that into the XValues.

Building 2-D arrays and saying .XValues = MyArray didn't work for any
array I could come up with.

All kinds of text representations of two-dimensional arrays which should
produce the proper array in the worksheet don't work in the chart. Here
are a few examples:

={"France","2004";"Italy","";"France","2005";"Italy",""}
={"2004","","2005","";"France","Italy","France","Italy"}

Even the usual trick of making the chart with a range and using

.XValues = .XValues

fell down.

Probably someone will come along in five minutes with a simple solution,
but I'll go out on a limb here and say, you're out of luck.

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

syrhus wrote:

> I'm trying to manage data's graph from an array but when I try following case
>
> |
> |
> |
> |__________________________
> |France | Italy | France | Italie |
> | 2003 | 2004 |
>
>
> I don't succeeded to generate the appropriate array.
> Does anyone know how to do that WITHOUT ranges?
>
> Thx

Thanks Jon for the precious time you spent for my case!
In fact, I found effectivly than when I press F9 on the SERIES function in
the formula bar, Excel convert it into an array. The array is not exactly as
you wrote:
"France
2003"\"Italy"\"France
2004"\"Italy"

I found that Excel insert the chr$(10) between France and 2003 to generate a
2-D array.
So I succeed to generate the same array with :
..XValues = Array("France" & chr$(10) & "2003", "Italy", "France" & chr$(10)
& "2004","Italy")

BUT, of course there is a BUT, on the excel graph on wich I convert the
range into array, when I unckeck the Chart Options/Axes/Category (x) Axis and
ckeck again, the graph lost the representation of the 2 dimensions
!!!!!!!!!!!!!!!!!!!!!!!!!!!
So, it means (to my mind) that Excel graph doesn't support 2-D array :o(

"Jon Peltier" wrote:

> Poser of the day. I spent a half hour trying to insert an array into the
> chart series' XValues property, and it wouldn't go.
>
> If you construct a chart using a range to produce this category axis
> pattern, then use the F9 key to change the XValues, your category axis
> labels are put into this array:
>
> ={"2004","France","0","Italy","2005","France","0","Italy"}
>
> which is just one dimensional, and shouldn't work. And it doesn't if you
> enter just that into the XValues.
>
> Building 2-D arrays and saying .XValues = MyArray didn't work for any
> array I could come up with.
>
> All kinds of text representations of two-dimensional arrays which should
> produce the proper array in the worksheet don't work in the chart. Here
> are a few examples:
>
> ={"France","2004";"Italy","";"France","2005";"Italy",""}
> ={"2004","","2005","";"France","Italy","France","Italy"}
>
> Even the usual trick of making the chart with a range and using
>
> .XValues = .XValues
>
> fell down.
>
> Probably someone will come along in five minutes with a simple solution,
> but I'll go out on a limb here and say, you're out of luck.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> syrhus wrote:
>
> > I'm trying to manage data's graph from an array but when I try following case
> >
> > |
> > |
> > |
> > |__________________________
> > |France | Italy | France | Italie |
> > | 2003 | 2004 |
> >
> >
> > I don't succeeded to generate the appropriate array.
> > Does anyone know how to do that WITHOUT ranges?
> >
> > Thx
>

Good eyes, I hadn't noticed the chr(10). But unfortunately it didn't
really help, did it?

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

syrhus wrote:

> Thanks Jon for the precious time you spent for my case!
> In fact, I found effectivly than when I press F9 on the SERIES function in
> the formula bar, Excel convert it into an array. The array is not exactly as
> you wrote:
> "France
> 2003"\"Italy"\"France
> 2004"\"Italy"
>
> I found that Excel insert the chr$(10) between France and 2003 to generate a
> 2-D array.
> So I succeed to generate the same array with :
> .XValues = Array("France" & chr$(10) & "2003", "Italy", "France" & chr$(10)
> & "2004","Italy")
>
> BUT, of course there is a BUT, on the excel graph on wich I convert the
> range into array, when I unckeck the Chart Options/Axes/Category (x) Axis and
> ckeck again, the graph lost the representation of the 2 dimensions
> !!!!!!!!!!!!!!!!!!!!!!!!!!!
> So, it means (to my mind) that Excel graph doesn't support 2-D array :o(
>
> "Jon Peltier" wrote:
>
>
>>Poser of the day. I spent a half hour trying to insert an array into the
>>chart series' XValues property, and it wouldn't go.
>>
>>If you construct a chart using a range to produce this category axis
>>pattern, then use the F9 key to change the XValues, your category axis
>>labels are put into this array:
>>
>>={"2004","France","0","Italy","2005","France","0","Italy"}
>>
>>which is just one dimensional, and shouldn't work. And it doesn't if you
>>enter just that into the XValues.
>>
>>Building 2-D arrays and saying .XValues = MyArray didn't work for any
>>array I could come up with.
>>
>>All kinds of text representations of two-dimensional arrays which should
>>produce the proper array in the worksheet don't work in the chart. Here
>>are a few examples:
>>
>>={"France","2004";"Italy","";"France","2005";"Italy",""}
>>={"2004","","2005","";"France","Italy","France","Italy"}
>>
>>Even the usual trick of making the chart with a range and using
>>
>> .XValues = .XValues
>>
>>fell down.
>>
>>Probably someone will come along in five minutes with a simple solution,
>>but I'll go out on a limb here and say, you're out of luck.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>syrhus wrote:
>>
>>
>>>I'm trying to manage data's graph from an array but when I try following case
>>>
>>>|
>>>|
>>>|
>>>|__________________________
>>>|France | Italy | France | Italie |
>>>| 2003 | 2004 |
>>>
>>>
>>>I don't succeeded to generate the appropriate array.
>>>Does anyone know how to do that WITHOUT ranges?
>>>
>>>Thx
>>

In article <[email protected]>,
[email protected] says...
> So, it means (to my mind) that Excel graph doesn't support 2-D array :o(
>
Well, it does and it doesn't. {g} Over time MS has done a lot to keep
XL going. Some (many?) things violate XL's own rules. The charting
module is notorious for its own idiosyncrasies. No reason for this to
be any different, eh? ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Thanks Jon for the precious time you spent for my case!
> In fact, I found effectivly than when I press F9 on the SERIES function in
> the formula bar, Excel convert it into an array. The array is not exactly as
> you wrote:
> "France
> 2003"\"Italy"\"France
> 2004"\"Italy"
>
> I found that Excel insert the chr$(10) between France and 2003 to generate a
> 2-D array.
> So I succeed to generate the same array with :
> .XValues = Array("France" & chr$(10) & "2003", "Italy", "France" & chr$(10)
> & "2004","Italy")
>
> BUT, of course there is a BUT, on the excel graph on wich I convert the
> range into array, when I unckeck the Chart Options/Axes/Category (x) Axis and
> ckeck again, the graph lost the representation of the 2 dimensions
> !!!!!!!!!!!!!!!!!!!!!!!!!!!
> So, it means (to my mind) that Excel graph doesn't support 2-D array :o(
>
> "Jon Peltier" wrote:
>
> > Poser of the day. I spent a half hour trying to insert an array into the
> > chart series' XValues property, and it wouldn't go.
> >
> > If you construct a chart using a range to produce this category axis
> > pattern, then use the F9 key to change the XValues, your category axis
> > labels are put into this array:
> >
> > ={"2004","France","0","Italy","2005","France","0","Italy"}
> >
> > which is just one dimensional, and shouldn't work. And it doesn't if you
> > enter just that into the XValues.
> >
> > Building 2-D arrays and saying .XValues = MyArray didn't work for any
> > array I could come up with.
> >
> > All kinds of text representations of two-dimensional arrays which should
> > produce the proper array in the worksheet don't work in the chart. Here
> > are a few examples:
> >
> > ={"France","2004";"Italy","";"France","2005";"Italy",""}
> > ={"2004","","2005","";"France","Italy","France","Italy"}
> >
> > Even the usual trick of making the chart with a range and using
> >
> > .XValues = .XValues
> >
> > fell down.
> >
> > Probably someone will come along in five minutes with a simple solution,
> > but I'll go out on a limb here and say, you're out of luck.
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> > syrhus wrote:
> >
> > > I'm trying to manage data's graph from an array but when I try following case
> > >
> > > |
> > > |
> > > |
> > > |__________________________
> > > |France | Italy | France | Italie |
> > > | 2003 | 2004 |
> > >
> > >
> > > I don't succeeded to generate the appropriate array.
> > > Does anyone know how to do that WITHOUT ranges?
> > >
> > > Thx
> >
>

I've resisted the urge up until now, but I'll say it anyway:

The charting module isn't as smart as a worksheet. Especially with arrays and
defined names.

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

Tushar Mehta wrote:

> In article <[email protected]>,
> [email protected] says...
>
>>So, it means (to my mind) that Excel graph doesn't support 2-D array :o(
>>
>
> Well, it does and it doesn't. {g} Over time MS has done a lot to keep
> XL going. Some (many?) things violate XL's own rules. The charting
> module is notorious for its own idiosyncrasies. No reason for this to
> be any different, eh? ;-)
>