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 |
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 |
Series XValues, determine if labels or values
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://PeltierTech.com _______ "Alok" wrote in message ... 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 |
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 |
Series XValues, determine if labels or values
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" wrote in message ... 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 |
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 |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com