Trik Mengubah Marker Colors Secara Dinamis Pada Line Chart Using VBA (Bag. II)

(Lanjutan Bagian 2…)

‘Syntax dibawah diawali dengan “With”. Disamping untuk mereduksi syntax yang berulang, juga untuk mempercepat proses running VBA. Semakin singkat syntax, proses running semakin cepat. Begitu menurut para MVP Vba Excel.

With chtChart

.ChartType = xlLine

.ChartStyle = 34

‘Set data source range. Yakni sumber data quantitative (angka, red) yang akan ditampilkan di grafik. Ada 3 series collection i.e .SeriesCollection(1), .SeriesCollection(2), dan .SeriesCollection(3). Series1 berisi data Utilisasi Traffic itu sendiri tanpa mempedulikan besaran niainya apakah berada diatas atau dibawah batas minimal (default warna yang saya set adalah kuning i.e RGB(255, 165, 0) red), Series2 berisi data batas maksimal Traffic i.e 90% sedangkan Series3 berisi data batas minimal Traffic i.e 50%. Keduanya memiliki warna yang sama (merah bergaris dot i.e .RGB = RGB(240, 128, 128))

.SetSourceData Source:=ws.Range(ws.Range("D105"), ws.Range("D105").End(xlToRight).Offset(1)), PlotBy:= _

xlRows

.SeriesCollection(1).XValues = ws.Range("E104", ws.Range("E104").End(xlToRight))

With .SeriesCollection(1)

.MarkerStyle = -4142

.ChartType = xlLineMarkers

.ApplyDataLabels

.DataLabels.Position = xlLabelPositionBelow

.Smooth = True

.MarkerStyle = 8

.MarkerSize = 11

.MarkerBackgroundColor = RGB(255, 165, 0)

.Format.Line.Weight = 1

End With

With .SeriesCollection(2)

.ChartType = xlLine

.Border.LineStyle = xlDot

.Format.Line.Weight = 2

.Format.Line.ForeColor.RGB = RGB(240, 128, 128)

End With

.SeriesCollection.NewSeries

With .SeriesCollection(3)

.Name = "='Source Monthly Traffic'!$D$107"

.Values = ws.Range("E107", ws.Range("D105").End(xlToRight).Offset(2))

.ChartType = xlLine

.Border.LineStyle = xlDot

.Format.Line.Weight = 2

.Format.Line.ForeColor.RGB = RGB(240, 128, 128)

End With

.HasTitle = True

.HasLegend = True

.Legend.Position = xlBottom

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

.DataTable.ShowLegendKey = True

‘Syntax dibawah berfungsi mengubah judul sesuai pilihan item yang kita butuhkan/inginkan. Misal, di item pilihan saya pilih Branch= Bandung, Sub Branch= Cianjur, dan Cluster= Cianjur. Ketika tombol refresh saya tekan, maka judul grafik akan berubah secara otomatis menjadi “Monthly Traffic Utilization Cianjur”.

.ChartTitle.Characters.Text = "Monthly Traffic Utilization" & " " & Sheets("Display All").Range("C272").Value

.Axes(xlValue).MajorGridlines.Delete

'The Parent property is used to set properties of

'the Chart.

With .Parent

.Top = Range("B275").Top

.Left = Range("B275").Left

.Name = "TrafficMonthly"

.RoundedCorners = True

.Width = 457

.Height = 350

End With

‘Syntax dibawah ini merupakan syntax tambahan yang berfungsi mengubah font dan ukuran huruf sumbu koordinat.

.Axes(xlCategory).TickLabels.AutoScaleFont = False

With .Axes(xlCategory).TickLabels.Font

.Name = "Tahoma"

.FontStyle = "Regular"

.Size = 8

End With

.Axes(xlValue).TickLabels.AutoScaleFont = False

With .Axes(xlValue).TickLabels.Font

.Name = "Tahoma"

.FontStyle = "Regular"

.Size = 8

End With

.Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False

With .Axes(xlValue, xlSecondary).TickLabels.Font

.Name = "Tahoma"

.FontStyle = "Regular"

.Size = 8

End With

.ChartTitle.AutoScaleFont = False

With .ChartTitle.Font

.Name = "Tahoma"

.FontStyle = "Bold"

.Size = 11

End With

.ChartArea.Border.Weight = xlMedium

Range("K268").Select

End With

‘Setelah diawal baris ScreenUpdating dan Calculation diset False dan Manual, maka diakhir baris kita ganti menjadi True dan Automatic.

With Application

.ScreenUpdating = True

.Calculation = xlCalculationAutomatic

End With

‘Syntax ini dunakan untuk merelease memory. Tujuannya sama dengan syntax diatasnya, i.e mempercepat proses running VBA

Set chtChart = Nothing

Set oc = Nothing

Set ws = Nothing

End Sub

Jika keseluruhan syntax diatas dijalankan maka grafik yang muncul akan sama persis dengan tampilan grafik pada gambar diatas, hanya saja tidak ada markerstyle atau bulatan berwarna hijau dan merah. Yang ada hanyalah markerstyle atau bulatan berwana kuning (default). Lantas bagaimana mengubah warna markerstyle untuk utilisasi traffic diatas 90% dengan warna hijau dan utilisasi traffic dibawah 50% dengan warna merah secara otomatis?. Inilah inti artikel ini. Berikut syntaxnya:

VBA Syntax to change Markerstyle Color

Sub ChangeColorMarker()

Dim i As Long, sc As Series

‘Karena utilisasi traffic yang warna markerstylenya akan kita ubah terdapat pada Series1, maka Series yang kita set adalah .SeriesCollection(1).

Set sc = ActiveSheet.ChartObjects("TrafficMonthly").Chart.SeriesCollection(1)

With Application

.ScreenUpdating = False

.Calculation = xlCalculationManual

End With

‘Syntax UBound(.XValues) berfungsi menghitung jumlah markerstyle (bulatan) di SeriesCollection(1). Total ada 11 bulatan (sesuai nilai XSeries-nya i.e total bulan dari Jan-11 sampai Nov-11) sehingga UBound(.XValues) = 11.

With sc

For i = 1 To UBound(.XValues)

‘Syntax Application.WorksheetFunction.Index(.Values, i) > 0.9 berfungsi mencari marker value (nilai utilisasi traffic itu sendiri, red) yang nilainya > 90%, jika ditemukan maka warna markerstyle akan berubah secara otomatis menjadi RGB(46, 139, 87). Jika < 50%, maka warnanya akan berubah menjadi RGB(178, 34, 34) dan jika berada diantara keduanya, maka warnanya tidak akan berubah alias tetap.

If Application.WorksheetFunction.Index(.Values, i) > 0.9 Then

sc.Points(i).MarkerBackgroundColor = RGB(46, 139, 87)

ElseIf Application.WorksheetFunction.Index(.Values, i) < 0.5 Then

sc.Points(i).MarkerBackgroundColor = RGB(178, 34, 34)

End If

Next i

End With

With Application

.ScreenUpdating = True

.Calculation = xlCalculationAutomatic

End Tith

Set i = Nothing

Set sc = Nothing

End Sub

Supaya berhasil, maka kedua program diatas i.e Sub ChartUtilisasiTrafficMonthly dan Sub ChangeColorMarker, harus dijalankan bersamaan. Untuk itu syntax berikut harus dimasukkan ke dalam tombol Refresh:

Private Sub CommandButton13_Click()

Call ChartUtilisasiTrafficMonthly

Call ChangeColorMarker

End Sub

Atau bisa juga dengan model lain (i.e mengganti “call” dengan “Run”):

Private Sub CommandButton13_Click()

Run “ChartUtilisasiTrafficMonthly”

Run “ChangeColorMarker”

End Sub

Note: Program diatas tidak akan berjalan jika sourcenya (i.e data quantitative) tidak tersedia dan jika kita tidak menset range/cell-nya dengan tepat. Itu saja, mudah-mudahan bermanfaat.

(Selesai…)