Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series XValues, determine if labels or values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series XValues, determine if labels or values
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, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series XValues, determine if labels or values
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" wrote in message ... 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, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series XValues, determine if labels or values
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://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to Set the XValues Property of the Series | Charts and Charting in Excel | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
how to determine if a series of numbers contain odd or even values | Excel Worksheet Functions | |||
Unable to set the xvalues (or values) property of the series class | Excel Programming | |||
Unable to set the XValues property of the Series class | Excel Programming |