Share via

Is there a vba code or excel function scrolling an embedded line chart along the x-axis

Anonymous
2011-04-02T05:39:33+00:00

I got a vba program streaming data every minute that is presented in an embedded line chart. The problem is that there are too many data points to read the details in the chart. Is it possible to make a vba code or is there any excel function to make it possible to scroll along the x-axis to read only portion of the data points at a time?

I hope

Per Morten

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-06T02:02:02+00:00

    I write a lot of code but I jump at every opportunity to solve a problem w/o it.  And, from what I can tell, this can be solved with named formulas.

    But, it's your work so if you believe you have to go with VBA, go for it... 

    Thanks for the reply but as long as I have a VBA program continually updating the rang from where I make the graphs I think I must do it differently than proposed. I know how to allocate the scrollbar on the graph and I know how to define a range name but as long as the range is continuously enlarged I believe it must be part of the code.

    The chart includes 10 lines and may be the code could be very much simpler, but anyway.

    Below is the part of the code that generate the graph. Do you see how it could be done?

    • SourceDataSheet; the sheet from where the data generating the chart comes from.
    • ChartSheet; name of the sheet where the chart is embedded.
    • ChartName; name of the chart withing the sheet.
    • RowsToGraph; number of row from the bottom of the list in SourceDataSheet to be included in the chart.

     

    Sub UpdateGraph(ByVal SourceDataSheet As String, ByVal ChartSheet As String, _

        ByVal ChartName As String, ByVal RowsToGraph As Integer)

        Dim WS As Worksheet

        Dim StartRow As Long, LastRow As Long

        Dim MinValue As Double, MaxValue As Double

        Dim CheckRange As Range

     

    '    On Error Resume Next

        Set WS = Worksheets(SourceDataSheet)

        LastRow = WS.Range("A5").End(xlDown).Row

        'Check number of rows to graph assumes data row starts at row 6

        If (LastRow > 5) Then 'changed as formula = to lastrow

            If (LastRow - 5) > RowsToGraph Then

                StartRow = (LastRow - RowsToGraph) + 1

            Else

                StartRow = 6

            End If

       

            Set CheckRange = Range(WS.Cells(StartRow, 2), WS.Cells(LastRow, 2))

           

            MaxValue = Application.Max(CheckRange)

            MinValue = Application.Min(CheckRange) - 1

           

            If MaxValue = 0 Or MinValue = 0 Then

                MsgBox "MIN or MAX has not set for " & SourceDataSheet, vbCritical

            End If

       

            With Sheets(ChartSheet).ChartObjects(ChartName).Chart

                .SeriesCollection(1).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(1).Values = WS.Range(WS.Cells(StartRow, 2), WS.Cells(LastRow, 2))

                .SeriesCollection(1).Format.Line.Weight = 1

                .SeriesCollection(1).Border.Color = RGB(0, 0, 0)

               

                .SeriesCollection(2).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(2).Values = WS.Range(WS.Cells(StartRow, 3), WS.Cells(LastRow, 3))

                .SeriesCollection(2).Format.Line.Weight = 1

                .SeriesCollection(2).Border.Color = RGB(0, 0, 255)

               

                .SeriesCollection(3).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(3).Values = WS.Range(WS.Cells(StartRow, 4), WS.Cells(LastRow, 4))

                .SeriesCollection(3).Format.Line.Weight = 1

                .SeriesCollection(3).Border.Color = RGB(0, 191, 255)

               

                .SeriesCollection(4).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(4).Values = WS.Range(WS.Cells(StartRow, 5), WS.Cells(LastRow, 5))

                .SeriesCollection(4).Format.Line.Weight = 1

                .SeriesCollection(4).Border.Color = RGB(0, 100, 0)

               

                .SeriesCollection(5).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(5).Values = WS.Range(WS.Cells(StartRow, 6), WS.Cells(LastRow, 6))

                .SeriesCollection(5).Format.Line.Weight = 1

                .SeriesCollection(5).Border.Color = RGB(0, 255, 127)

               

                .SeriesCollection(6).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(6).Values = WS.Range(WS.Cells(StartRow, 7), WS.Cells(LastRow, 7))

                .SeriesCollection(6).Format.Line.Weight = 1

                .SeriesCollection(6).Border.Color = RGB(0, 0, 255)

               

                .SeriesCollection(7).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(7).Values = WS.Range(WS.Cells(StartRow, 8), WS.Cells(LastRow, 8))

                .SeriesCollection(7).Format.Line.Weight = 1

                .SeriesCollection(7).Border.Color = RGB(0, 191, 255)

               

                .SeriesCollection(8).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(8).Values = WS.Range(WS.Cells(StartRow, 9), WS.Cells(LastRow, 9))

                .SeriesCollection(8).Format.Line.Weight = 1

                .SeriesCollection(8).Border.Color = RGB(0, 100, 0)

               

                .SeriesCollection(9).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(9).Values = WS.Range(WS.Cells(StartRow, 10), WS.Cells(LastRow, 10))

                .SeriesCollection(9).Format.Line.Weight = 1

                .SeriesCollection(9).Border.Color = RGB(0, 255, 127)

               

                .SeriesCollection(10).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(10).Values = WS.Range(WS.Cells(StartRow, 11), WS.Cells(LastRow, 11))

                .SeriesCollection(10).Format.Line.Weight = 1

                .SeriesCollection(10).Border.Color = RGB(255, 0, 0)

     

                .Axes(xlValue).MinimumScale = MinValue

                .Axes(xlValue).MaximumScale = MaxValue

            End With

        Else

            MsgBox ("There is no data to graph")

        End If

    End Sub

     

    Thanks in advance

     

    Per Morten

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-04-05T14:34:37+00:00

    Thanks for the reply but as long as I have a VBA program continually updating the rang from where I make the graphs I think I must do it differently than proposed. I know how to allocate the scrollbar on the graph and I know how to define a range name but as long as the range is continuously enlarged I believe it must be part of the code.

    The chart includes 10 lines and may be the code could be very much simpler, but anyway.

    Below is the part of the code that generate the graph. Do you see how it could be done?

    • SourceDataSheet; the sheet from where the data generating the chart comes from.
    • ChartSheet; name of the sheet where the chart is embedded.
    • ChartName; name of the chart withing the sheet.
    • RowsToGraph; number of row from the bottom of the list in SourceDataSheet to be included in the chart.

    Sub UpdateGraph(ByVal SourceDataSheet As String, ByVal ChartSheet As String, _

        ByVal ChartName As String, ByVal RowsToGraph As Integer)

        Dim WS As Worksheet

        Dim StartRow As Long, LastRow As Long

        Dim MinValue As Double, MaxValue As Double

        Dim CheckRange As Range

    '    On Error Resume Next

        Set WS = Worksheets(SourceDataSheet)

        LastRow = WS.Range("A5").End(xlDown).Row

        'Check number of rows to graph assumes data row starts at row 6

        If (LastRow > 5) Then 'changed as formula = to lastrow

            If (LastRow - 5) > RowsToGraph Then

                StartRow = (LastRow - RowsToGraph) + 1

            Else

                StartRow = 6

            End If

            Set CheckRange = Range(WS.Cells(StartRow, 2), WS.Cells(LastRow, 2))

            MaxValue = Application.Max(CheckRange)

            MinValue = Application.Min(CheckRange) - 1

            If MaxValue = 0 Or MinValue = 0 Then

                MsgBox "MIN or MAX has not set for " & SourceDataSheet, vbCritical

            End If

            With Sheets(ChartSheet).ChartObjects(ChartName).Chart

                .SeriesCollection(1).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(1).Values = WS.Range(WS.Cells(StartRow, 2), WS.Cells(LastRow, 2))

                .SeriesCollection(1).Format.Line.Weight = 1

                .SeriesCollection(1).Border.Color = RGB(0, 0, 0)

                .SeriesCollection(2).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(2).Values = WS.Range(WS.Cells(StartRow, 3), WS.Cells(LastRow, 3))

                .SeriesCollection(2).Format.Line.Weight = 1

                .SeriesCollection(2).Border.Color = RGB(0, 0, 255)

                .SeriesCollection(3).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(3).Values = WS.Range(WS.Cells(StartRow, 4), WS.Cells(LastRow, 4))

                .SeriesCollection(3).Format.Line.Weight = 1

                .SeriesCollection(3).Border.Color = RGB(0, 191, 255)

                .SeriesCollection(4).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(4).Values = WS.Range(WS.Cells(StartRow, 5), WS.Cells(LastRow, 5))

                .SeriesCollection(4).Format.Line.Weight = 1

                .SeriesCollection(4).Border.Color = RGB(0, 100, 0)

                .SeriesCollection(5).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(5).Values = WS.Range(WS.Cells(StartRow, 6), WS.Cells(LastRow, 6))

                .SeriesCollection(5).Format.Line.Weight = 1

                .SeriesCollection(5).Border.Color = RGB(0, 255, 127)

                .SeriesCollection(6).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(6).Values = WS.Range(WS.Cells(StartRow, 7), WS.Cells(LastRow, 7))

                .SeriesCollection(6).Format.Line.Weight = 1

                .SeriesCollection(6).Border.Color = RGB(0, 0, 255)

                .SeriesCollection(7).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(7).Values = WS.Range(WS.Cells(StartRow, 8), WS.Cells(LastRow, 8))

                .SeriesCollection(7).Format.Line.Weight = 1

                .SeriesCollection(7).Border.Color = RGB(0, 191, 255)

                .SeriesCollection(8).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(8).Values = WS.Range(WS.Cells(StartRow, 9), WS.Cells(LastRow, 9))

                .SeriesCollection(8).Format.Line.Weight = 1

                .SeriesCollection(8).Border.Color = RGB(0, 100, 0)

                .SeriesCollection(9).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(9).Values = WS.Range(WS.Cells(StartRow, 10), WS.Cells(LastRow, 10))

                .SeriesCollection(9).Format.Line.Weight = 1

                .SeriesCollection(9).Border.Color = RGB(0, 255, 127)

                .SeriesCollection(10).XValues = WS.Range(WS.Cells(StartRow, 1), WS.Cells(LastRow, 1))

                .SeriesCollection(10).Values = WS.Range(WS.Cells(StartRow, 11), WS.Cells(LastRow, 11))

                .SeriesCollection(10).Format.Line.Weight = 1

                .SeriesCollection(10).Border.Color = RGB(255, 0, 0)

                .Axes(xlValue).MinimumScale = MinValue

                .Axes(xlValue).MaximumScale = MaxValue

            End With

        Else

            MsgBox ("There is no data to graph")

        End If

    End Sub

    Thanks in advance

    Per Morten

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-04-03T00:41:31+00:00

    You should be able to do this w/o VBA.  For various examples that you can adapt to your specific requirement see

    Dynamic Charts

    http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

    I got a vba program streaming data every minute that is presented in an embedded line chart. The problem is that there are too many data points to read the details in the chart. Is it possible to make a vba code or is there any excel function to make it possible to scroll along the x-axis to read only portion of the data points at a time?

    I hope

    Per Morten

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2011-04-02T10:42:12+00:00

    You can use dynamic named ranges and a scrollbar. See http://www.eileenslounge.com/viewtopic.php?f=27&t=5067.

    Was this answer helpful?

    0 comments