Dynamic Charts

[alis88]

Dear all

Based on original thread at:

http://www.ozgrid.com/forum/showthread.php?t=20695

Could someone please explain, line by line, what each line of code is doing?

Thanks

Alis

[MickG]

Hi, If you enter this code in a sheet with a Chart, based on a simple range of Data, then run the code by increasing the data source range, you will see each step in a msgbox.
Basically it reduces the Data source (By Parsing) to a Range address, then adds the Extra Range (Current Region) you've selected to increase the Data source, and hence alters the Chart.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c, n
    On Error Resume Next
    For Each c In ChartObjects
        MsgBox c.Chart.SeriesCollection(1).Formula
        n = c.Chart.SeriesCollection(1).Formula
        
        MsgBox Left(n, InStrRev(n, ",") - 1)
        n = Left(n, InStrRev(n, ",") - 1)
       
       MsgBox Right(n, (Len(n) - InStrRev(n, ",")))
        n = Right(n, (Len(n) - InStrRev(n, ",")))
        
        MsgBox Range(n).CurrentRegion.Address
        c.Chart.SetSourceData Source:=Range(n).CurrentRegion
    Next c

Regards Mick

[alis88]

Sorry Mick, I don't understand what you mean by seeing each step in a message box.

Can you elaborate?

Thanks

Alis

[MickG]

Hi, In the code I have places some message boxes, if you place a Break point in the code by viewing the code window (Alt +F11) and clicking in the left hand Margin somewhere at the top of the code, (This will place a brown dot in the Margin) and then running the code, when it gets to the break point the code window will be displayed at the break point and then you can step through the code by clicking "F8".

Above some lines of code are Msgboxes, the Msgbox will give you the value of the next line of code.

The Code uses Varieties of the "Left" function to Parse the Data.
You will see that the basic purpose of the code is to reduce the source Data Range text at the start of the code. In My Case:- "SERIES(,,Sheet1!$A$1:$A$14,1) to "$A$1:$A$14" it then extends the data source Range by the bit you've added when you changed the selection (Increased Data) by using the "Current Range" property. This increases the range of Data shown on the Chart.
Regards Mick