Series XValues, determine if labels or values

[Peter T]

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]

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/tip83.htm)

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]

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

 

[Peter T]

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

 

[Peter T]

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

 

[Jon Peltier]

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