How does one access a chart Series address in VBA?

[DrJBN]

I'm writing a subroutine to format a chart. I want to place the formatting information in cells above each series. Then, I want to select the chart and execute my routine. The routine will take the formatting information and apply it to the chart.

Here is how I am imagining that it will work. Consider the following information in a sheet-

	1​	2​
	2​	3​
	3​	4​
	Stuff	morestuff
1​	0.4063​	0.2​
2​	0.4564​	0.5​
3​	0.4216​	0.4​
4​	0.432​	0.2​
5​	0.3709​	0.1​
6​	0.4129​	0​

I would select the three columns (numbers 1-6, stuff, and morestuff), make a chart. Then, I would select the chart and apply my subroutine which would look at the numbers above each series and use those to apply various formatting to each series. What I cannot figure out is how to get to my numbers above each series. If you change a value in the series, the chart updates. Change a series name, it updates etc, so it seems to me that the chart knows the location of each series data. Can that location be found in the chart object so that I can use it (e.g., find series1 location, go up 3 cells and retrieve my formatting information)?

Many thanks,
Byron

 

[rlv01]

When I was learning to use VBA to work with charts, the way I would determine how to do things like formatting a series was to use the macro recorder to record myself manually doing the things I wanted to do to the chart or chartobject, then study the macro recorder code to see how to apply the methodology to my own subroutines.

 

[DrJBN]

When I was learning to use VBA to work with charts, the way I would determine how to do things like formatting a series was to use the macro recorder to record myself manually doing the things I wanted to do to the chart or chartobject, then study the macro recorder code to see how to apply the methodology to my own subroutines.

Thanks, I've got the formatting process under control- I just don't know how to get to my formatting data from the subroutine & selected chart without having to open a dialog box to select it.

 

[rlv01]

Thanks, I've got the formatting process under control- I just don't know how to get to my formatting data from the subroutine & selected chart without having to open a dialog box to select it.

That's one of the things you can learn from the macro recorder. Record yourself doing it using the dialog boxes, then study the macro recorder code.

 

[DrJBN]

That's one of the things you can learn from the macro recorder. Record yourself doing it using the dialog boxes, then study the macro recorder code.

Unless I'm missing something, there's not a dialog box for what I want. A dialog box will show how to assign addresses to a chart series, for example. I need the reverse- I need to take an existing chart series, and find the address of the data that were used to populate it.

When you click a chart, it hi-lights the data that it uses (lets call it D). The addresses of D are, I assume, stored somewhere in the chart object. That is what I am looking for. I want to know D's address so that I can use adjacent cells to store data that I will use to control the formatting of the chart. The formatting in VBA isn't an issue for me, its finding the address, from the chart itself, of D that the chart is charting. I'd like to avoid a 3-step process where the user selects a chart, starts the subroutine, then has to select the cells from the sheet that the VBA code will use to set the formats. I want to just select the chart, start the subroutine, have it determine where D is on the sheet, and based on that position grab data from other cells to apply to the formatting

 

[rlv01]

Unless I'm missing something, there's not a dialog box for what I want.

You imply that I have somehow raised the issue of dialog boxes, but I have not. One way to get address information is to use the series .formula property. Assuming an XY Plot:

Sub Button1_Click()
'
' Button1_Click Macro
'
    Dim Msg As String
    Dim FS As String
    Dim SA As Variant
    Dim I As Long
    
    If Not ActiveChart Is Nothing Then
        Msg = "X data location: "
        With ActiveChart
            FS = Replace(Replace(ActiveChart.SeriesCollection(1).Formula, "series(", "", Compare:=vbTextCompare), ")", "", Compare:=vbTextCompare)

            SA = Split(FS, ",")
            For I = 0 To UBound(SA) - 1
                Select Case I
                Case 0
                    Msg = "Series name or location: " & SA(I) & vbCr & vbCr
                Case 1
                    Msg = Msg & "X Series data location: " & SA(I) & vbCr & vbCr
                Case 2
                    Msg = Msg & "Y Series data location: " & SA(I)
                End Select
            Next I
            MsgBox Msg, vbInformation, ActiveWorkbook.Name & ", " & .Name
        End With
    Else
        MsgBox "Please select a chart", vbOKOnly Or vbCritical, Application.Name
    End If
End Sub
Sub Macro9

 

[DrJBN]

Thank you! That is exactly what I needed. I could not find a list of the SeriesCollection propeties with google, and the "intellisense" wasn't popping up anything for SeriesCollection. (Now I see that it is a property of a "series" itself, which is being indexed.) I don't program in VBA often enough for my aged brain to remember little details like that anymore.
" somehow raised the issue of dialog boxes, but I have not." ... I was responding to my attempt to understand how I could get the addresses from .."Record yourself doing it using the dialog boxes, "
Many thanks again,
Byron

 

[rlv01]

... I was responding to my attempt to understand how I could get the addresses from .."Record yourself doing it using the dialog boxes, "

Just to close this out, I think that was a misunderstanding. Just because you use dialog boxes during the macro record process does not mean that dialog boxes will be used in the recorded code. They won't be. For example, I used dialog boxes to change the series name and extend the series data range on a sample chart. Here is the recorded code

Sub Macro7()
'
' Macro7 Macro
'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Name = "=""New Name"""
    ActiveChart.SeriesCollection(1).XValues = "=Sheet2!$A$2:$A$20"
    ActiveChart.SeriesCollection(1).Values = "=Sheet2!$B$2:$B$20"
End Sub

I could not find a list of the SeriesCollection propeties with google, and the "intellisense" wasn't popping up anything for SeriesCollection.

One additional resource that is very powerful is the Object Browser (F2 from the VBE); but it seems to be under utilized in the days of google.

 

[DrJBN]

Thank you for that! I did not know it existed.