Asp Forum - Series XValues, determine if labels or values

Peter T

12/19/2006 12:31:00 PM

Hi All,

First to pre-empt confusion, there's a potential difference between terms
XValues and x-values in the following.

Typically series XValues are 1st or 2nd Category X-axis labels. However in
some types of charts the XValues are actual x-values, not labels.

In the chart wizard category labels are shown at the bottom of the dialog.
Labels are common to the chart rather than individual series. If a series
does not have its 'own' x-values it's XValues are labels related to its
AxisGroup (xlPrimary or xlSecondary).

X-values are shown in the box between Name & Y-Values, mid right of the
dialog (the box only appears if the series is capable of having its 'own'
x-values). These are unique to the series.

If a series has XValues I want to determine if these are its own x-values as
distinct from common category labels.

Regards,
Peter T

6 Answers

Jon Peltier

12/19/2006 1:13:00 PM

Peter -

It depends on a number of factors.

An XY series has its own numerical X values.

If the chart has a category axis, this variant array contains the category
labels:

vLabels = ActiveChart.Axes(xlCategory, xlPrimary).CategoryNames

I suppose you could compare vLabels to

vXValues = ActiveChart.SeriesCollection(1).XValues

which returns the X values but no information about the range containing
these values. If the series has no specified X values, the above simply
returns the array {1,2,3,...}. To extract the range, or to determine if the
X values are unspecified, you have to parse the series formula (or use John
Walkenbach's Chart Series class module:
http://www.j-walk.com/ss/excel/tips...)

In any case, the first series in the axis group supplies the CategoryNames
array. Series which go with category axes use the category names array, even
if they have different X values. In an XY chart, the X values of the first
series become the CategoryNames array, even though this is decoupled from
the axis scaling, and even though each series can have distinct X values
which are treated independently.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Peter T" <peter_t@discussions> wrote in message
news:%23h7q8m2IHHA.1912@TK2MSFTNGP03.phx.gbl...
> Hi All,
>
> First to pre-empt confusion, there's a potential difference between terms
> XValues and x-values in the following.
>
> Typically series XValues are 1st or 2nd Category X-axis labels. However in
> some types of charts the XValues are actual x-values, not labels.
>
> In the chart wizard category labels are shown at the bottom of the dialog.
> Labels are common to the chart rather than individual series. If a series
> does not have its 'own' x-values it's XValues are labels related to its
> AxisGroup (xlPrimary or xlSecondary).
>
> X-values are shown in the box between Name & Y-Values, mid right of the
> dialog (the box only appears if the series is capable of having its 'own'
> x-values). These are unique to the series.
>
> If a series has XValues I want to determine if these are its own x-values
> as
> distinct from common category labels.
>
> Regards,
> Peter T
>
>

Jon Peltier

12/19/2006 1:31:00 PM

Nope, if my column chart uses numbers as its category labels, the category
name elements will be numeric. If it uses a mixture of numbers and text,
they are all considered strings.

This doesn't sort out which are the X values of a series and which are the
category labels of an axis, either, because series 2 may have different X
values but be coerced to use the category labels from series 1.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Alok" <Alok@discussions.microsoft.com> wrote in message
news:3FBFC2E9-78FF-4F92-BA52-FE6E7EC8404C@microsoft.com...
> Peter
> Just a suggestion. As you are probably aware when the chart displays
> x-values they are always numeric. On the other hand category labels can
> either be numeric or alphanumeric. If in your situation you can either
> ensure
> that Category Labels are always alphanumeric then you can do this test on
> the
> first Category Label
>
> if TypeName(ActiveChart.Axes(xlCategory,
> xlPrimary).CategoryNames(1))="String" then
> Msgbox "Category Labels are actual labels"
> else
> Msgbox "Category Labels are numeric values"
> End if
>
> Alok
>
>
> "Peter T" wrote:
>
>> Hi All,
>>
>> First to pre-empt confusion, there's a potential difference between terms
>> XValues and x-values in the following.
>>
>> Typically series XValues are 1st or 2nd Category X-axis labels. However
>> in
>> some types of charts the XValues are actual x-values, not labels.
>>
>> In the chart wizard category labels are shown at the bottom of the
>> dialog.
>> Labels are common to the chart rather than individual series. If a series
>> does not have its 'own' x-values it's XValues are labels related to its
>> AxisGroup (xlPrimary or xlSecondary).
>>
>> X-values are shown in the box between Name & Y-Values, mid right of the
>> dialog (the box only appears if the series is capable of having its 'own'
>> x-values). These are unique to the series.
>>
>> If a series has XValues I want to determine if these are its own x-values
>> as
>> distinct from common category labels.
>>
>> Regards,
>> Peter T
>>
>>
>>

Peter T

12/19/2006 2:58:00 PM

Hi Jon,

CategoryNames - excellent, I think that's the key.

In a light test this seems to work for my purposes

Sub test()
Dim b As Boolean
Dim i As Long
Dim idx As Long
Dim nAxGp As Long
Dim sRes As String
Dim sr As Series
Dim ch As Chart
Dim ax As Axis
Dim vCat(1 To 2)
Dim vXvals

Set ch = ActiveChart

For i = 1 To 2 ' test assumes a two x-axis chart
Set ax = ch.Axes(xlCategory, i)
vCat(i) = ax.CategoryNames
Next

idx = 0
For Each sr In ch.SeriesCollection
idx = idx + 1
nAxGp = sr.AxisGroup

If idx = 1 Then
' 1st series always returns category labels
sRes = "cat-labels: " & nAxGp

ElseIf UBound(sr.XValues) <> UBound(vCat(nAxGp)) Then
' no need to bother comparing
sRes = "own x-values"
Else
vXvals = sr.XValues
For i = 1 To UBound(vXvals) ' always 1 base
If vXvals(i) <> vCat(nAxGp)(i) Then
b = True
Exit For
End If
Next
If b Then
sRes = "own x-values"
b = False
Else
sRes = "cat-labels " & nAxGp
End If
End If

Debug.Print "S" & idx & " " & sRes

Next

End Sub

Do you see any problems / ommisions

Thanks for the link to j-walk's series class. I will look at this in more
detail though I already have my own routine to parse series-formulas which I
think caters for a few more rare scenarios.

Many thanks,
Peter T

"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:Ov5uk92IHHA.3916@TK2MSFTNGP02.phx.gbl...
> Peter -
>
> It depends on a number of factors.
>
> An XY series has its own numerical X values.
>
> If the chart has a category axis, this variant array contains the category
> labels:
>
> vLabels = ActiveChart.Axes(xlCategory, xlPrimary).CategoryNames
>
> I suppose you could compare vLabels to
>
> vXValues = ActiveChart.SeriesCollection(1).XValues
>
> which returns the X values but no information about the range containing
> these values. If the series has no specified X values, the above simply
> returns the array {1,2,3,...}. To extract the range, or to determine if
the
> X values are unspecified, you have to parse the series formula (or use
John
> Walkenbach's Chart Series class module:
> http://www.j-walk.com/ss/excel/tips...)
>
> In any case, the first series in the axis group supplies the CategoryNames
> array. Series which go with category axes use the category names array,
even
> if they have different X values. In an XY chart, the X values of the first
> series become the CategoryNames array, even though this is decoupled from
> the axis scaling, and even though each series can have distinct X values
> which are treated independently.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://Pelti...
> _______
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23h7q8m2IHHA.1912@TK2MSFTNGP03.phx.gbl...
> > Hi All,
> >
> > First to pre-empt confusion, there's a potential difference between
terms
> > XValues and x-values in the following.
> >
> > Typically series XValues are 1st or 2nd Category X-axis labels. However
in
> > some types of charts the XValues are actual x-values, not labels.
> >
> > In the chart wizard category labels are shown at the bottom of the
dialog.
> > Labels are common to the chart rather than individual series. If a
series
> > does not have its 'own' x-values it's XValues are labels related to its
> > AxisGroup (xlPrimary or xlSecondary).
> >
> > X-values are shown in the box between Name & Y-Values, mid right of the
> > dialog (the box only appears if the series is capable of having its
'own'
> > x-values). These are unique to the series.
> >
> > If a series has XValues I want to determine if these are its own
x-values
> > as
> > distinct from common category labels.
> >
> > Regards,
> > Peter T
> >
> >
>
>

Peter T

12/19/2006 3:00:00 PM

Hi Alok,

Thanks for looking but I think I'm on the way with Jon's tip to compare
arrays of XValues with respective CategoryNames.

Regards,
Peter T

"Alok" <Alok@discussions.microsoft.com> wrote in message
news:3FBFC2E9-78FF-4F92-BA52-FE6E7EC8404C@microsoft.com...
> Peter
> Just a suggestion. As you are probably aware when the chart displays
> x-values they are always numeric. On the other hand category labels can
> either be numeric or alphanumeric. If in your situation you can either
ensure
> that Category Labels are always alphanumeric then you can do this test on
the
> first Category Label
>
> if TypeName(ActiveChart.Axes(xlCategory,
> xlPrimary).CategoryNames(1))="String" then
> Msgbox "Category Labels are actual labels"
> else
> Msgbox "Category Labels are numeric values"
> End if
>
> Alok
>
>
> "Peter T" wrote:
>
> > Hi All,
> >
> > First to pre-empt confusion, there's a potential difference between
terms
> > XValues and x-values in the following.
> >
> > Typically series XValues are 1st or 2nd Category X-axis labels. However
in
> > some types of charts the XValues are actual x-values, not labels.
> >
> > In the chart wizard category labels are shown at the bottom of the
dialog.
> > Labels are common to the chart rather than individual series. If a
series
> > does not have its 'own' x-values it's XValues are labels related to its
> > AxisGroup (xlPrimary or xlSecondary).
> >
> > X-values are shown in the box between Name & Y-Values, mid right of the
> > dialog (the box only appears if the series is capable of having its
'own'
> > x-values). These are unique to the series.
> >
> > If a series has XValues I want to determine if these are its own
x-values as
> > distinct from common category labels.
> >
> > Regards,
> > Peter T
> >
> >
> >

Jon Peltier

12/19/2006 3:16:00 PM

> Do you see any problems / ommisions

At first glance, it looks okay.

> Thanks for the link to j-walk's series class. I will look at this in more
> detail though I already have my own routine to parse series-formulas which
> I
> think caters for a few more rare scenarios.

He's actually come up with a much shorter version that he showed me once,
but I can't find it and I forget how it works, and AFAIK he's never
published it. Some day when I have all day to kill, I'll figure it out.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Peter T" <peter_t@discussions> wrote in message
news:OL9X%2343IHHA.2232@TK2MSFTNGP02.phx.gbl...
> Hi Jon,
>
> CategoryNames - excellent, I think that's the key.
>
> In a light test this seems to work for my purposes
>
> Sub test()
> Dim b As Boolean
> Dim i As Long
> Dim idx As Long
> Dim nAxGp As Long
> Dim sRes As String
> Dim sr As Series
> Dim ch As Chart
> Dim ax As Axis
> Dim vCat(1 To 2)
> Dim vXvals
>
> Set ch = ActiveChart
>
> For i = 1 To 2 ' test assumes a two x-axis chart
> Set ax = ch.Axes(xlCategory, i)
> vCat(i) = ax.CategoryNames
> Next
>
> idx = 0
> For Each sr In ch.SeriesCollection
> idx = idx + 1
> nAxGp = sr.AxisGroup
>
> If idx = 1 Then
> ' 1st series always returns category labels
> sRes = "cat-labels: " & nAxGp
>
> ElseIf UBound(sr.XValues) <> UBound(vCat(nAxGp)) Then
> ' no need to bother comparing
> sRes = "own x-values"
> Else
> vXvals = sr.XValues
> For i = 1 To UBound(vXvals) ' always 1 base
> If vXvals(i) <> vCat(nAxGp)(i) Then
> b = True
> Exit For
> End If
> Next
> If b Then
> sRes = "own x-values"
> b = False
> Else
> sRes = "cat-labels " & nAxGp
> End If
> End If
>
> Debug.Print "S" & idx & " " & sRes
>
> Next
>
> End Sub
>
> Do you see any problems / ommisions
>
> Thanks for the link to j-walk's series class. I will look at this in more
> detail though I already have my own routine to parse series-formulas which
> I
> think caters for a few more rare scenarios.
>
> Many thanks,
> Peter T
>
>
> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
> news:Ov5uk92IHHA.3916@TK2MSFTNGP02.phx.gbl...
>> Peter -
>>
>> It depends on a number of factors.
>>
>> An XY series has its own numerical X values.
>>
>> If the chart has a category axis, this variant array contains the
>> category
>> labels:
>>
>> vLabels = ActiveChart.Axes(xlCategory, xlPrimary).CategoryNames
>>
>> I suppose you could compare vLabels to
>>
>> vXValues = ActiveChart.SeriesCollection(1).XValues
>>
>> which returns the X values but no information about the range containing
>> these values. If the series has no specified X values, the above simply
>> returns the array {1,2,3,...}. To extract the range, or to determine if
> the
>> X values are unspecified, you have to parse the series formula (or use
> John
>> Walkenbach's Chart Series class module:
>> http://www.j-walk.com/ss/excel/tips...)
>>
>> In any case, the first series in the axis group supplies the
>> CategoryNames
>> array. Series which go with category axes use the category names array,
> even
>> if they have different X values. In an XY chart, the X values of the
>> first
>> series become the CategoryNames array, even though this is decoupled from
>> the axis scaling, and even though each series can have distinct X values
>> which are treated independently.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://Pelti...
>> _______
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%23h7q8m2IHHA.1912@TK2MSFTNGP03.phx.gbl...
>> > Hi All,
>> >
>> > First to pre-empt confusion, there's a potential difference between
> terms
>> > XValues and x-values in the following.
>> >
>> > Typically series XValues are 1st or 2nd Category X-axis labels. However
> in
>> > some types of charts the XValues are actual x-values, not labels.
>> >
>> > In the chart wizard category labels are shown at the bottom of the
> dialog.
>> > Labels are common to the chart rather than individual series. If a
> series
>> > does not have its 'own' x-values it's XValues are labels related to its
>> > AxisGroup (xlPrimary or xlSecondary).
>> >
>> > X-values are shown in the box between Name & Y-Values, mid right of the
>> > dialog (the box only appears if the series is capable of having its
> 'own'
>> > x-values). These are unique to the series.
>> >
>> > If a series has XValues I want to determine if these are its own
> x-values
>> > as
>> > distinct from common category labels.
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>>
>>
>
>

alok

12/19/2006 4:01:00 PM

Jon,
I agree that if the column chart uses numbers as Category labels then the
method I suggested will fail. If it always uses alphanumeric values then the
test will work since charts such as XY Scatter chart will, on the other hand,
always have numeric Category labels.
Alok

"Jon Peltier" wrote:

> Nope, if my column chart uses numbers as its category labels, the category
> name elements will be numeric. If it uses a mixture of numbers and text,
> they are all considered strings.
>
> This doesn't sort out which are the X values of a series and which are the
> category labels of an axis, either, because series 2 may have different X
> values but be coerced to use the category labels from series 1.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://Pelti...
> _______
>
>
> "Alok" <Alok@discussions.microsoft.com> wrote in message
> news:3FBFC2E9-78FF-4F92-BA52-FE6E7EC8404C@microsoft.com...
> > Peter
> > Just a suggestion. As you are probably aware when the chart displays
> > x-values they are always numeric. On the other hand category labels can
> > either be numeric or alphanumeric. If in your situation you can either
> > ensure
> > that Category Labels are always alphanumeric then you can do this test on
> > the
> > first Category Label
> >
> > if TypeName(ActiveChart.Axes(xlCategory,
> > xlPrimary).CategoryNames(1))="String" then
> > Msgbox "Category Labels are actual labels"
> > else
> > Msgbox "Category Labels are numeric values"
> > End if
> >
> > Alok
> >
> >
> > "Peter T" wrote:
> >
> >> Hi All,
> >>
> >> First to pre-empt confusion, there's a potential difference between terms
> >> XValues and x-values in the following.
> >>
> >> Typically series XValues are 1st or 2nd Category X-axis labels. However
> >> in
> >> some types of charts the XValues are actual x-values, not labels.
> >>
> >> In the chart wizard category labels are shown at the bottom of the
> >> dialog.
> >> Labels are common to the chart rather than individual series. If a series
> >> does not have its 'own' x-values it's XValues are labels related to its
> >> AxisGroup (xlPrimary or xlSecondary).
> >>
> >> X-values are shown in the box between Name & Y-Values, mid right of the
> >> dialog (the box only appears if the series is capable of having its 'own'
> >> x-values). These are unique to the series.
> >>
> >> If a series has XValues I want to determine if these are its own x-values
> >> as
> >> distinct from common category labels.
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >>
>
>
>

microsoft.public.excel.programming

Series XValues, determine if labels or values

Peter T

Jon Peltier

Jon Peltier

Peter T

Peter T

Jon Peltier

alok

x Login to ForumsZone