• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting chart range

N

ninad7

Member
Hi,


I have PLENTY of charts in one workbook which are the same, except that the data ranges are different (same X-axis and same number of series and type).


Since I do not want to physically check each graph for the range (just to make sure there's no error in a wrong range being selected), what I'd like is something below. Any help ?


For each series in each chart in each worksheet of the active workbook, what is the data range. If this can be display in a new worksheet with headings as "Chart_name", "Worksheet_name", "Series", "Chart_type", "Data_Range"


TIA,


Ninad.
 
Ninad

I would be using named ranges for your charts

using named ranges ensures that they are all kept up to date

The named ranges should refer to ranges using a formula instead of just a Range

By doing this they can be made dynamic in terms of changing as the source data changes

Read more about Dynamic Ranges here:

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
 
Thanks of responding Hui. For future graphs I can follow as per the post link you provided. However, for the current set of graphs it's too much of a hassle to redo them.


Hence, my request as in the first post.


I am not well versed with Excel VB, but hte logic is pretty clear to me for scripting.


For each worksheet

For each chart

For each series

Get Range

Goto Worksheet (named as ChartRange)

Goto next blank row

Enter details as wanted in each row (in separate cells)

Next series

Next chart

Next worksheet


If you can point towards the VBA script, then I can try to code myself as well. (Teach a man to fish and he can eat for life).


Thanks for the assistance.


Ninad.
 
Ninad

Copy and paste the following code into a VBA Code Sheet and execute the Chart_Range_List macro


If you run it again delete the Chart Summary sheet which it will add to your workbook.


=====

Sub Chart_Range_List()

Dim ws As Worksheet
Dim ch As ChartObject
Dim s As Series
Dim i As Long

i = 1

Set NewSheet = Worksheets.Add
NewSheet.Name = "Chart Summary"
NewSheet.Select

Cells(i, 1).Value = "Worksheet Name"
Cells(i, 2).Value = "Chart Name"
Cells(i, 3).Value = "Series Number"
Cells(i, 4).Value = "Series Name"
Cells(i, 5).Value = "Series Type"
Cells(i, 6).Value = "Axis"
Cells(i, 7).Value = "Series Name"
Cells(i, 8).Value = "X Range"
Cells(i, 9).Value = "Y Range"

Rows("1:1").Font.Bold = True

For Each ws In Worksheets
For Each ch In ws.ChartObjects
For Each s In ch.Chart.SeriesCollection

i = i + 1
Cells(i, 1).Value = ws.Name 'Worsheet Name
Cells(i, 2).Value = ch.Name 'Chart Name
Cells(i, 3).Value = s.PlotOrder
Cells(i, 4).Value = s.Name
Cells(i, 5).Value = chttype(s.Type)
If s.AxisGroup = xlPrimary Then
Cells(i, 6).Value = "Primary"
Else
Cells(i, 6).Value = "Secondary"
End If
Cells(i, 7).Value = form(s.formula, 1) 'Series Name
Cells(i, 8).Value = form(s.formula, 2) 'Series X Range
Cells(i, 9).Value = form(s.formula, 3) 'Series Y Range

Next
Next
Next

Columns("A:J").EntireColumn.AutoFit

End Sub

Function form(formula As String, i As Long) As Variant

b1 = InStr(1, formula, "(")
c1 = InStr(1, formula, ",")
c2 = InStr(c1 + 1, formula, ",")
c3 = InStr(c2 + 1, formula, ",")

If i = 1 Then
form = Mid(formula, b1 + 1, c1 - b1 - 1)
ElseIf i = 2 Then
form = Mid(formula, c1 + 1, c2 - c1 - 1)
ElseIf i = 3 Then
form = Mid(formula, c2 + 1, c3 - c2 - 1)
Else
form = "undefined"
End If

form = Right(form, Len(form) - InStr(1, form, "!"))

End Function

Function chttype(stype) As String

If stype = -4169 Then
chttype = "Scatter."
ElseIf stype = -4151 Then
chttype = "Radar."
ElseIf stype = -4120 Then
chttype = "Doughnut."
ElseIf stype = -4102 Then
chttype = "3D Pie."
ElseIf stype = -4101 Then
chttype = "3D Line."
ElseIf stype = -4100 Then
chttype = "3D Column."
ElseIf stype = -4098 Then
chttype = "3D Area."
ElseIf stype = 1 Then
chttype = "Area"
ElseIf stype = 4 Then
chttype = "Line."
ElseIf stype = 5 Then
chttype = "Pie."
ElseIf stype = 15 Then
chttype = "Bubble."
ElseIf stype = 51 Then
chttype = "Clustered Column."
ElseIf stype = 52 Then
chttype = "Stacked Column."
ElseIf stype = 53 Then
chttype = "100% Stacked Column."r />ElseIf stype = 54 Then
chttype = "3D Clustered Column."
ElseIf stype = 55 Then
chttype = "3D Stacked Column."
ElseIf stype = 56 Then
chttype = "3D 100% Stacked Column."
ElseIf stype = 57 Then
chttype = "Clustered Bar."
ElseIf stype = 58 Then
chttype = "Stacked Bar."
ElseIf stype = 59 Then
chttype = "100% Stacked Bar."
ElseIf stype = 60 Then
chttype = "3D Clustered Bar."
ElseIf stype = 61 Then
chttype = "3D Stacked Bar."
ElseIf stype = 62 Then
chttype = "3D 100% Stacked Bar."
ElseIf stype = 63 Then
chttype = "Stacked Line."
ElseIf stype = 64 Then
chttype = "100% Stacked Line."
ElseIf stype = 65 Then
chttype = "Line with Markers."
ElseIf stype = 66 Then
chttype = "Stacked Line with Markers."
ElseIf stype = 67 Then
chttype = "100% Stacked Line with Markers."
ElseIf stype = 68 Then
chttype = "Pie of Pie."
ElseIf stype = 69 Then
chttype = "Exploded Pie."
ElseIf stype = 70 Then
chttype = "Exploded 3D Pie."
ElseIf stype = 71 Then
chttype = "Bar of Pie."
ElseIf stype = 72 Then
chttype = "Scatter with Smoothed Lines."
ElseIf stype = 73 Then
chttype = "Scatter with Smoothed Lines and No Data Markers."
ElseIf stype = 74 Then
chttype = "Scatter with Lines."
ElseIf stype = 75 Then
chttype = "Scatter with Lines and No Data Markers."
ElseIf stype = 76 Then
chttype = "Stacked Area."
ElseIf stype = 77 Then
chttype = "100% Stacked Area."
ElseIf stype = 78 Then
chttype = "3D Stacked Area."
ElseIf stype = 79 Then
chttype = "100% Stacked Area."
ElseIf stype = 80 Then
chttype = "Exploded Doughnut."
ElseIf stype = 81 Then
chttype = "Radar with Data Markers."
ElseIf stype = 82 Then
chttype = "Filled Radar."
ElseIf stype = 83 Then
chttype = "3D Surface."
ElseIf stype = 84 Then
chttype = "3D Surface (wireframe)."
ElseIf stype = 85 Then
chttype = "Surface (Top View)."
ElseIf stype = 86 Then
chttype = "Surface (Top View wireframe)."
ElseIf stype = 87 Then
chttype = "Bubble with 3D effects."
ElseIf stype = 88 Then
chttype = "High-Low-Close."
ElseIf stype = 89 Then
chttype = "Open-High-Low-Close."
ElseIf stype = 90 Then
chttype = "Volume-High-Low-Close."
ElseIf stype = 91 Then
chttype = "Volume-Open-High-Low-Close."
ElseIf stype = 92 Then
chttype = "Clustered Cone Column."
ElseIf stype = 93 Then
chttype = "Stacked Cone Column."
ElseIf stype = 94 Then
chttype = "100% Stacked Cylinder Column."
ElseIf stype = 95 Then
chttype = "Clustered Cylinder Bar."
ElseIf stype = 96 Then
chttype = "Stacked Cylinder Bar."
ElseIf stype = 97 Then
chttype = "100% Stacked Cylinder Bar."
ElseIf stype = 98 Then
chttype = "3D Cylinder Column."
ElseIf stype = 99 Then
chttype = "Clustered Cone Column."
ElseIf stype = 100 Then
chttype = "Stacked Cone Column."
ElseIf stype = 101 Then
chttype = "100% Stacked Cone Column."
ElseIf stype = 102 Then
chttype = "Clustered Cone Bar."
ElseIf stype = 103 Then
chttype = "Stacked Cone Bar."
ElseIf stype = 104 Then
chttype = "100% Stacked Cone Bar."
ElseIf stype = 105 Then
chttype = "3D Cone Column."
ElseIf stype = 106 Then
chttype = "Clustered Pyramid Column."
ElseIf stype = 107 Then
chttype = "Stacked Pyramid Column."
ElseIf stype = 108 Then
chttype = "100% Stacked Pyramid Column."
ElseIf stype = 109 Then
chttype = "Clustered Pyramid Bar."
ElseIf stype = 110 Then
chttype = "Stacked Pyramid Bar."
ElseIf stype = 111 Then
chttype = "100% Stacked Pyramid Bar."
ElseIf stype = 112 Then
chttype = "3D Pyramid Column."
Else
chttype = "Unknown."
End If

If Right(chttype, 1) = "." Then chttype = Left(chttype, Len(chttype) - 1)

End Function
 
Hui,


For every ElseIf, I thank you. I shall study this code to help me increase my VBA knowledge.


Regards,


Ninad.
 
You must log in or register to reply here.
Back
Top