Trik Membuat Grafik Menggunakan VBA Excel

Well, beberapa waktu yang lalu kita pernah membahas dua buah topic sederhana mengenai kedahsyatan Microsoft Excel (klik disini), Visual Basic Application (VBA) atau yang familiar dengan istilah “Macros” dan contoh aplikasinya (i.e looping undian) dalam pekerjaan/aktivitas sehari-hari kita (klik disini). Kali ini saya masih akan menshare trik-trik simple lainnya yang berkaitan dengan VBA, yakni mengenai bagaimana membuat grafik secara otomatis di Microsoft Excel. Sebagai catatan, Microsoft Office yang terinstall di computer saya adalah Office 2007 sehingga syntax VBA yang ada didalamnya pun secara otomatis akan menyesuaikan dengan versinya. Jika anda mencreate syntax VBA di Microsoft Excel 2003, anda tidak perlu khawatir (kalau) program anda tidak bisa dirunning/dijalankan di Microsoft Excel 2007, karena secara umum, setiap kali Microsoft melaunching software versi terbaru, syntax-syntax lama yang terdapat pada software versi sebelumnya masih tetap terbaca oleh system terbaru. Namun hal ini tidak berlaku sebaliknya, jika anda membuat syntax VBA di Microsoft office 2007, anda harus bersiap-siap gigit jari (i.e ketika anda menjalankannya di Microsoft Excel versi sebelumnya). Mengapa? karena banyak syntax di Microsoft Excel 2007 tidak terdapat di versi sebelumnya (sebagian diantaranya merupakan bentuk penyederhanaan dari versi terdahulu, red). –CMIIW-

OK lanjut, berikut adalah syntax grafik sederhana yang akan saya share pada kesempatan kali ini (beberapa diantaranya sudah pernah kita bahas pada kesempatan sebelumnya, red);

Sub ChartTSELCommunity()

Dim chtChart As Chart

Dim oc As ChartObject

Merujuk kepada pembahasan terdahulu, “Sub” berfungsi sebagai pembuka suatu program, diikuti oleh judul program yang akan kita buat [ChartTSELCommunity() adalah nama yang saya pilih, red]. Selanjutnya ada dua variable yang harus kita declare dalam program ini yakni “chtChart” sebagai “Chart” dan “oc” sebagai ChartObject. Mungkin ada yang bertanya, “ChartObject” itu yang seperti apa sih bro? Tampilan grafik yang biasa kita buat di Microsoft Excel dalam berbagai macam bentuk/varian (e.g Batang, Pie, Garis dkk) itulah yang biasa kita sebut sebagai ChartObject. Mengapa kita harus mendeklarasikan variable ini?. Jawabannya ada pada penjelasan selanjutnya.

On Error Resume Next

Set oc = ActiveSheet.ChartObjects("Community")

For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects("Community")

oc.Delete

Next oc

Dulu ketika pertama kali membuat program ini, saya berpikir, “Bagaimana caranya ya, agar setiap kali tombol ‘Refresh’ diklik, grafik eksisting yang sebelumnya saya buat menghilang/terhapus dari tampilan excel dan ter-replace/tergantikan oleh grafik baru yang sesuai dengan pilihan menu kita”. Nah syntax inilah jawabannya. Lalu apa hubungannya dengan deklarasi variable “oc” diatas?. Jawabannya sederhana, i.e jika variable tersebut tidak dideklarasikan, syntax ini tidak akan berjalan. Selanjutnya kita set terlebih dahulu grafik apa saja yang termasuk dalam cakupan variable “oc”, i.e grafik yang berada di dalam Sheets(“TSC & TMC”) pada workbook Microsoft Excel yang sedang kita bahas. Karena hanya ada satu sheet aktif, maka cukup kita tuliskan “ActiveSheet.ChartObjects("Community")”. Community sendiri merupakan nama dari object/grafik tersebut.

“For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects("Community")” dibaca; Setiap kali program ini dijalankan atau setiap kali kita mengklik tombol refresh, maka secara otomatis pula system akan menghapus grafik eksisting i.e melalui eksekusi syntax “oc.Delete”. Jika tidak ada (grafik eksisting), maka system akan menjalankan syntax selanjutnya.

'Create a new chart.

Set chtChart = Charts.Add

Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="TSC & TMC")

With chtChart

.ChartType = xlLine

.ChartStyle = 34

Syntax ini tetap akan dijalankan oleh system dalam kondisi apapun i.e dengan atau tanpa adanya grafik pada saat atau sebelum tombol Refresh ditekan. Maka kita set “Charts.Add” diawal syntax yang artinya “penambahan grafik” [jika sebelumnya tidak ada grafik eksisting, red] atau “replace grafik” [jika sebelumnya terdapat grafik eksisting]. Kita set pula lokasi penempatan grafik baru tersebut melalui syntax “chtChart.Location(Where:=xlLocationAsObject, Name:="TSC & TMC")”. Secara default, grafik yang terdisplay nantinya akan berupa grafik garis (line) dengan mode/style bertype 34 [Mengenai mode grafik ini bisa di search di menu “help” atau di http://msdn.microsoft.com, red].

'Set data source range.

.SetSourceData Source:=Range(Range("D17"), Range("D18").End(xlToRight)), PlotBy:= _ xlRows

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

.SeriesCollection(1).MarkerStyle = -4142

.SeriesCollection(1).Smooth = True

.SeriesCollection(2).ChartType = xlColumnClustered

.SeriesCollection(2).AxisGroup = 2

'.SeriesCollection(1).ApplyDataLabels

.HasTitle = True

.HasLegend = True

.Legend.Position = xlBottom

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

.DataTable.ShowLegendKey = True

Selanjutnya kita akan berbicara mengenai proses pengambilan data di table data. “.SetSourceData Source:=Range(Range("D17"), Range("D18").End(xlToRight)), PlotBy:= xlRows” artinya; system akan memblock data secara otomatis mulai dari range/cell D17 hingga range/cell n18. [n18] sendiri diartikan sebagai posisi cell terakhir yang mengandung data sepanjang raw/baris 18 (di Microsoft Excel kita) yang berada (pasti) di sebelah kanan cell D18 (meskipun belum tentu persis satu kolom disebelahnya, red). Artinya jika data terakhir yang kita update berada pada kolom L, maka posisi n18 adalah cell L18, kemudian system akan memblock data dari D17 hingga L18. “PlotBy" menunjukkan type pilihan sumbu koordinat, yang terdiri dari “xlRows” dan “xlColumn”. Jika kita memilih “xlRows” (seperti yang saya pilih, red), maka data yang berada di sumbu (axis) X adalah data primer yang berada pada kolom-kolom tabelnya (dalam hal ini adalah data yang berada pada kolom D, E, hingga n, red)

Ketika syntax “.SeriesCollection(1).XValues = Range("E16", Range("E16").End(xlToRight))” dijalankan, maka system akan memblock data mulai dari range/cell E16 hingga n16. Nilai (n) sendiri disesuaikan dengan posisi kolom terakhir dimana ‘the most updated data’ tersebut berada (dalam case ini, n adalah kolom L, red). Disini saya menset style marker -4142, smooth grafik saya aktifkan, legendkey saya aktifkan, demikian pula dengan data labelnya. Yang perlu kita cermati (secara mendalam) adalah ‘Series’ yang terdapat pada syntax i.e SeriesCollection(1) dan SeriesCollection(2). SeriesCollection(1) berisi data “Member TSC & TMC” sedangkan SeriesCollection(2) berisi data “Revenue TSC & TMC” yang lantas dibreakdown lagi secara lebih detail menjadi Revenue Voice, SMS dan GPRS per Branch/GraPARI. Agar tampilan grafik terlihat lebih menarik, maka salah satu type grafik harus diubah (dalam hal ini SeriesCollection(2) saya ubah menjadi xlColumnClustered, dimana sebelumnya bertype xlLine, red.) dan AxisGroup-nya saya set = 2, sehingga akan menampilkan 2 sumbu koordinat (axis) Y pada grafik.

.ChartTitle.Characters.Text = "Grafik " & Sheets("TSC & TMC").Range("D8").Value & " " & _"(" & Sheets("TSC & TMC").Range("D10").Value & ")"

Sekilas tidak ada yang menonjol dari syntax diatas. Syntax “.ChartTitle.Characters.Text = "Grafik" & Sheets("TSC & TMC").Range("D8").Value & " " & "(" & Sheets("TSC & TMC").Range("D10").Value & ")"” dibuat agar judul grafik yang muncul bisa berubah secara dinamis mengikuti pilihan menu yang kita inginkan. Contoh; ketika Range("D8").Value kita isi dengan “TSC” dan Range("D10").Value kita set dengan “GPRS - BANDUNG”, maka ketika tombol Refresh kita klik, judul grafik akan berubah menjadi, “Grafik TSC (GPRS – BANDUNG)” [lihat gambar grafik diatas, red].

'The Parent property is used to set properties of

'the Chart.

With .Parent

.Top = Range("D21").Top

.Left = Range("D21").Left

.Name = "Community"

.RoundedCorners = True

.Width = 510

.Height = 300

End With

With Selection

.Font.Name = "Trebuchet MS"

.Font.Size = 8

.Border.Weight = 2

End With

Syntax ini hanya berfungsi untuk mengatur ‘accessories’ grafik supaya lebih menarik dan supaya terdisplay di posisi cell yang kita inginkan. Misal syntax; “.Top = Range("D21").Top” dan “.Left = Range("D21").Left” digunakan untuk mengatur posisi sudut atas dan batas kiri grafik, yakni di range D21, “.Width = 510” dan “.Height = 300” digunakan untuk mengatur panjang dan lebar grafik sesuai besaran angka yang kita inginkan. Sisanya tidak terlalu istimewa dan rumit.. J

Range("F6").Select

End With

End Sub

Last but not least, saat tombol Refresh saya klik sebagai tanda bahwa program mulai dieksekusi, maka saya ingin menempatkan posisi cell aktif pasca eksekusi program di range(“F6”) agar menambah kesan “professional”.. (Hubungannya apa ya,..hehe). Dan akhirnya syntax saya tutup dengan “End Sub” sebagai tanda bahwa program telah selesai (paripurna). Nah barangkali itu saja yang bisa saya share hari ini, mudah-mudahan ada waktu luang lain, di jam kerja lain yang bisa saya isi untuk berbagi ilmu dengan rekan-rekan.. J. Selamat mencoba!