+ Reply to Thread
Results 1 to 6 of 6

Create a Chart with Values from Array AND NOT from Ranges

  1. #1
    syrhus
    Guest

    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

  2. #2
    Jon Peltier
    Guest

    Re: Create a Chart with Values from Array AND NOT from Ranges

    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


  3. #3
    syrhus
    Guest

    Re: Create a Chart with Values from Array AND NOT from Ranges

    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

    >


  4. #4
    Jon Peltier
    Guest

    Re: Create a Chart with Values from Array AND NOT from Ranges

    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

    >>


  5. #5
    Tushar Mehta
    Guest

    Re: Create a Chart with Values from Array AND NOT from Ranges

    In article <4C7FA31B-811D-4FB8-AB6D-47402F952839@microsoft.com>,
    syrhus@discussions.microsoft.com 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 <4C7FA31B-811D-4FB8-AB6D-47402F952839@microsoft.com>,
    syrhus@discussions.microsoft.com 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

    > >

    >


  6. #6
    Jon Peltier
    Guest

    Re: Create a Chart with Values from Array AND NOT from Ranges

    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 <4C7FA31B-811D-4FB8-AB6D-47402F952839@microsoft.com>,
    > syrhus@discussions.microsoft.com 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? ;-)
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1