Getting Chart DataRange

[AccessShell]

I created a line chart based on data from another tab in the spreadsheet. I ran 'Record New Macro' from the tools tab tp get code to expand the DataRange.
That was fairly simple.
But, what I need is to get the value of the DataRange already in the chart. I want to change the datarange from code. The last point on the graph is usually well before the datarange for the chart.
I need to compare the datarange in the chart with the last point in the chart to decide if I need to (in code) expand the datarange.

I can easily convert Excel VBA to VB6

I hope this is clear.

NOTE: This was posted, with different wording, in another forum.

Thanks

 

[Siddharth Rout]

There is no direct way to get the Datarange. If your data is in contigous range then it will be easy. Otherwise it will be slightly complex to retrieve what you want. The logic is to get the top and last cell from the 1st and last series and then construct the range. Let me see if I can quickly cough up a code.. gimme few minutes.

 

[Siddharth Rout]

See if this helps? I have not extensively tested it so please test it thouroughly before using in yuor vb6 application.

Sheet1 (Where the chart is)

Sheet2 (Where the data is)

Code

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Chrt As Chart
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    '~~> Change Chart 1 to the relevant chart name
    Set Chrt = ws.ChartObjects("Chart 1").Chart
    
    '~~> Find the first cell
    Dim FirstCell As Range
    Set FirstCell = Range(Split(Chrt.SeriesCollection(1).Formula, ",")(2))(1)
    
    '~~> Identify total series in the chart
    Dim SeriesCount As Long
    SeriesCount = Chrt.SeriesCollection.Count
    
    '~~> Find last cell
    Dim LastCell As Range
    With Range(Split(Chrt.SeriesCollection(SeriesCount).Formula, ",")(2))
        Set LastCell = .Item(.Count)
    End With
    
    '~~> Construct your data range
    Debug.Print Range(FirstCell.Offset(-1, -1), LastCell).Address(External:=True)
End Sub

Output

 

[Jon Peltier]

Here, try this:

Function GetChartDataRange(MyChart As Chart) As Range
  With MyChart
    Dim srs As Series
    For Each srs In ActiveChart.FullSeriesCollection
      Dim sFmla As String
      sFmla = srs.Formula
      sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
      Dim vFmla As Variant
      vFmla = Split(sFmla, ",")
      Dim iFmla As Long
      For iFmla = 0 To 2
        Dim rFmla As Range
        On Error Resume Next
        Set rFmla = Range(vFmla(iFmla))
        On Error GoTo 0
        Dim DataRange As Range
        Set DataRange = JoinRanges(DataRange, rFmla)
        Set rFmla = Nothing
      Next
    Next
  End With
  Set GetChartDataRange = Intersect(DataRange.EntireColumn, DataRange.EntireRow)
End Function

Function JoinRanges(BaseRange, AddRange) As Range
  If BaseRange Is Nothing Then
    If AddRange Is Nothing Then
      ' nothing
    Else
      Set JoinRanges = AddRange
    End If
  Else
    If AddRange Is Nothing Then
      Set JoinRanges = BaseRange
    Else
      Set JoinRanges = Union(BaseRange, AddRange)
    End If
  End If
End Function

 

[AccessShell]

SR. I cannot get past the statement

Set ws = Sheet1

For my case I changed Sheet1 to the name of the sheet that has the charts. In my case "Charts ==>

Set ws = Charts

I got an error - Type mismatch Run time error '13'

JP. I cannot get past the statement

For Each srs In ActiveChart.FullSeriesCollection

FullSeriesCollection lights up. I got an error - Compile error, Method or data not found. I think there is relation to what I am passing to GetChartDataRange. I tried "Chart 1", it is chart #1 on the Charts sheet

 

[Jon Peltier]

SR. I cannot get past the statement

Set ws = Sheet1

For my case I changed Sheet1 to the name of the sheet that has the charts. In my case "Charts ==>

Set ws = Charts

I got an error - Type mismatch Run time error '13'

JP. I cannot get past the statement

For Each srs In ActiveChart.FullSeriesCollection

FullSeriesCollection lights up. I got an error - Compile error, Method or data not found. I think there is relation to what I am passing to GetChartDataRange. I tried "Chart 1", it is chart #1 on the Charts sheet

If your version if Excel is old, use .SeriesCollection instead of .FullSeriesCollection

Also you have to pass in a Chart, like this:

Dim ChartDataRange As Range
Set ChartDataRange = GetChartDataRange(ActiveChart)

Or

Dim ChartDataRange As Range
Set ChartDataRange = GetChartDataRange(ActiveSheet.ChartObjects("Chart 1").Chart)

 

[AccessShell]

My version of Excel is very, very old.

 

[AccessShell]

JP. I get s type mismatch with your VBA code. It is on the Set statement

 

[Jon Peltier]

JP. I get s type mismatch with your VBA code. It is on the Set statement

I fixed it in my post.

 

[AccessShell]

JP. I can now get your code to work, sort of. I have no idea what Function JoinRanges(BaseRange, AddRange) As Range does.
When I run the code, the statement

sFmla = srs.Formula

gives me
sFmla = =SERIES(,Activity!$A$5:$A$1102,Activity!$L$5:$L$1102,1)

The number 1102 is correct. It is what I am looking for, This chart has only one series (line). Does that mean I don't need the "for Eash srs..."
When I put my curser on the last data point in the chart, I get at the top of the sheet in the Fx area the same data that you gave me in sFmla.

When I changed the range manually, with Record New Macro, I went to the Source Data tab, the DataRange tab and changes the 1102 there.
I thought that's what I would do with code as a starting point. I think, if I use your sFmla with the new 1102, based on my calculations, I should be able to enter this new formula somehow.

Ihope I am not too confusing. Your code is great. Thanks