Tag: VBA

Data Engineering

VBA Excel Chart automatic formatting of SeriesCollection / Marker / Line by Series Name / Formula

tonielookLeave a comment

Q. How to start editing Marco / VBA?

A. Right click the chart, “Assign Marco”, “New/Edit”.

Q. How to loop each object (SeriesCollection) within a chart?

A. Below shows 2 simple For Loops to browse ActiveSheet -> .ChartObjects -> .Chart.SeriesCollection; And modified each Marker to Size 10.

Sub Chart1_Click()
Dim myChartObject As ChartObject
Dim mySrs As Series
 With ActiveSheet
        For Each myChartObject In .ChartObjects
            For Each mySrs In myChartObject.Chart.SeriesCollection
                mySrs.Select
                With Selection
                    .MarkerSize = 10
                End With
            Next
        Next
    End With
End Sub

Q. How to get the Series name of each SeriesCollection? How to get the background color of a given cell?

A. Get the formula of each series by .formula then Split() the string; Get the RGB color of a given cell by ActiveSheet.Range().Interior.Color.

Sub Chart1_Click()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim MySrsCell As String
 With ActiveSheet
        For Each myChartObject In .ChartObjects
            For Each mySrs In myChartObject.Chart.SeriesCollection
                mySrs.Select
                With Selection
                    .MarkerSize = 10
                    
'                   Get .Formula and split the series name from its first part
                    MySrsCell = Split(Split(.Formula, ",")(0), "(")(1)
'                   Format the Maker and Line color according Cell's background color
                    .Format.Line.ForeColor.RGB = ActiveSheet.Range(MySrsCell).Interior.Color
                    .MarkerBackgroundColor = ActiveSheet.Range(MySrsCell).Interior.Color
'                   This line means no color for the Marker border
                    .MarkerForegroundColorIndex = -4142
                End With
            Next
        Next
    End With
End Sub

Q. How to modify the format of Maker / Line / Color base on the series name?

A. Use IF and like wildcard matching.

Sub Chart1_Click()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim MySrsCell As String
Dim MySrsName As String
 With ActiveSheet
        For Each myChartObject In .ChartObjects
            For Each mySrs In myChartObject.Chart.SeriesCollection
                mySrs.Select
                With Selection
                    .MarkerSize = 10
                    
'                   Get .Formula and split the series name from its first part
                    MySrsCell = Split(Split(.Formula, ",")(0), "(")(1)
'                   Format the Maker and Line color according Cell's background color
                    .Format.Line.ForeColor.RGB = ActiveSheet.Range(MySrsCell).Interior.Color
                    .MarkerBackgroundColor = ActiveSheet.Range(MySrsCell).Interior.Color
'                   This line means no color for the Marker border
                    .MarkerForegroundColorIndex = -4142
                    
                    MySrsName = ActiveSheet.Range(MySrsCell)
                    If (MySrsName Like "*precision") Then
                        .MarkerStyle = 3
                    ElseIf (MySrsName Like "*recall") Then
                        .MarkerStyle = 8
                    End If
                End With
            Next
        Next
    End With
End Sub

Done. Now every time while clicking the chart, the assigned VBA will format the lines and markers according to Series Name and its background color.