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!


23 Respones to "Trik Membuat Grafik Menggunakan VBA Excel"

hendito mengatakan...

muanteb puoll .. aku wis lali excel je ... wakakakaka


15 Februari 2011 pukul 18.13
Old Nakula mengatakan...

Hehe,..bahasan standar koq Cak, everybody can do it!


15 Februari 2011 pukul 18.52
Weby mengatakan...

Bro bisa kasih contoh Excelnya g'..??
Biar lebih ngerti..
Thanks For your Post...


14 Maret 2011 pukul 14.14
Old Nakula mengatakan...

Simpel sebenarnya koq bro,...Coba sampeyan ikuti tips berikut;

[1]. Buka excel baru. Pastikan marco excel anda sudah diaktifkan. Jika belum, ikuti langkah berikut; Di pojok kiri atas ms excel (tepatnya di sebelah kiri menu home) terdapat simbol Office. Klik simbol tsb, di sebelah kanan bawah ada button "excel options" (tepatnya di sebelah kiri exit excel), klik button tsb. Kemudian klik trust center>trust center setting<macro settings. Pilih all enable macros dan centang option trust access to the VBA, kemudian OK, kemudian OK lagi. (continued..)


15 Maret 2011 pukul 19.17
Old Nakula mengatakan...

[2]. Ganti sheet1 dengan nama TSC & TMC (lihat gambar diatas)
[3]. Buatlah tabel seperti gambar diatas, yakni block cell D16:K18 dan buatlah borders. Isi informasi sbb; Cell D16 (Clasification), D17 (MEMBER) dan D18 (REVENUE). E16 sampai K16 isi dengan bulan, i.e Jan-Jul. E17:K18 disi dengan nominal angka. Usahakan angka di cell E17:K17 lebih kecil dari angka di cell E18:K18. Jangan lupa, supaya program tidak error, isikan value pada D10; misal "TOTAL - BANDUNG". D10 sendiri merupakan option kategori (lihat gambar diatas). (continued..)


15 Maret 2011 pukul 19.18
Old Nakula mengatakan...

[4]. Klik sheet TSC & TMC, klik kanan dan klik view code. Klik kanan pada VBAProject>Insert>Module. Nah copylah syntax diatas secara komplet ke modul bro. Setelah itu coba sampeyan "running" (klik tombol run dibawah menu Debug).

Selamat mencoba.

Note: Mohon maaf tidak bisa saya tampilkan file excelnya karena didalamnya terdapat data-data confidential perusahaan. Mudah-mudahan bisa dimaklumi.. :)


15 Maret 2011 pukul 19.18
Old Nakula mengatakan...

Well,..mungkin sudah ada yg mencoba mengcopy syntax diatas namun belum berhasil, seperti yg terjadi pada salah satu rekan kita Kang Aris. Saya coba copy paste persis syntax diatas, ada beberapa hal yg perlu dan wajib diperhatikan (supaya syntax berhasil dirunning, red).

[1]. Penamaan sheet
Ketika pertama kali membuka Excel, sheet name default yang muncul adalah Sheet1, Sheet2, Sheet3 dst. Dalam hal ini kita hanya butuh 1 sheet saja (misal sheet1, red). Gantilah nama Sheet1 tsb dengan "TSC & TMC", atau jika mau ribet, ganti setiap nama "TSC & TMC" pd syntax dengan Sheet1. (Continued)


24 April 2011 pukul 18.51
Old Nakula mengatakan...

[2]. Tempatkan tabel, category options (Branch, Type, Revenue) Sesuai dengan POSISI CELL yg dicontohkan di gambar. Cth; category "Branch" ada di cell D6, "Type" D8 dst, tabel pada range D16:P18 dst. Jika tidak, maka syntax akan salah MEMBACA RANGE. Kalau kita sudah mulai memahami macro, maka kita bisa bereksplorasi dgn mengubah-ubah posisi cell sesuai kehendak kita, kemudian MENYESUAIKAN posisi cell pada SYNTAX VBA berdasarkan posisi cell di workbook (dlm hal ini Sheet1, red)...(Continued)


24 April 2011 pukul 19.00
Old Nakula mengatakan...

Jika hal-hal prinsip diatas sudah dilakukan, perhatikan point berikut:
[3]. Ketika kita mencopy syntax diatas kemudian muncul highlight (dengan font) merah, itu artinya ada yg perlu DIEDIT. Syntax yang saya temukan demikian antara lain:
.SetSourceData Source:=Range(Range("D17"), Range("D18").End(xlToRight)), PlotBy:= _ xlRows

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

Ada 2 cara yg bisa kita lakukan; pertama Hapus tanda _ setelah syntax PlotBy:= atau kedua Letakkan kursor anda setelah tanda _ kemudian ENTER..

Nah silahkan dicoba lagi. Kalau masih ada syntax yg error saat dicompile, kita diskusikan kembali..OK... :)


24 April 2011 pukul 19.12
hendrik thio mengatakan...

kak maaf nie newbie,
artikelnya bgs bgt salut deh bwt kaka adminnya :)
tapi sy mau nanya nih kak ?
bsa ga bikin program VBA untuk menginsert grapik di kolom excel ?
klo bsa gmna caranya ya kak ?
thanks b4 !!!


19 Juni 2011 pukul 17.34
Old Nakula mengatakan...

To Hendrik.
Nuhun sudah mampir kang, kebetulan saya juga newbie nih, baru belajar kemaren.. :).

Jujur saya masih belum paham maksud pertanyaan sampeyan. Yang saya tangkap adalah sbb (mohon diluruskan jika salah menafsirkan, red):

Kalau cara pembuatan grafik secara otomatis menggunakan syntax vba sendiri sudah kita bahas di artikel sederhana di atas. Nah sekarang barangkali terkait dengan insert kolom. Yang saya pahami dari pertanyaan sampeyan diatas adalah bagaimana cara memasukkan grafik ke dalam kolom tertentu sesuai yang kita inginkan. Sebenarnya sudah tercapure pada syntax diatas.

With .Parent

.Top = Range("D21").Top

.Left = Range("D21").Left

End With

Nah kita bisa mengutak-atik posisi cell yang ada di syntax ".Top" dan ".Left" diatas. Jika kita ingin memasukkan (baca: memindahkan) grafik ke kolom F, baris ke 3 misalnya, maka range D21 diatas harus kita ganti dengan F3. Mohon diluruskan jika salah memahami pertanyaan Cak.. :)


19 Juni 2011 pukul 18.57
Unknown mengatakan...

gak da yah langkah2 nya dlm bntuk gambar .
saya ada tugas:(


21 Juni 2012 pukul 01.09
Old Nakula mengatakan...

Untuk sementara belum saya buat mas, karena asumsi awal saya adalah bahwa pembaca artikel ini sudah memahami dasar-dasar vba. Nuhun.


30 Juni 2012 pukul 23.14
Anonim mengatakan...

gan klo udah jadi macro bisa di import ke java gak??


9 Desember 2012 pukul 22.20
Old Nakula mengatakan...

Sejauh pengetahuan saya bisa mas, tapi saya pribadi belum pernah mencobanya, wallaahu a'lam.


22 Desember 2012 pukul 00.06
Unknown mengatakan...

sangat membantu artikelnya :)
kalau boleh nanya, misal saya punya 12 workbook yg isinya laporan tiap cabang perusahaan, tugasnya, mau bikin laporan tersebut jd 1, plus analisa chart nya jg.. kalo bikin nya via macro, apa bisa menggunakan coding diatas?

*maksudnya, jika 12 workbook tsb dijadikan 1 workbook sehingga didlmnya ada beberapa sheet, dan jika salah satu sheet dibuatkan laporan/chartnya, maka sheet lain pun akan secara otomatis muncul laporan/chart juga, namun sesuai dgn data masing2 sheet


bisakah membantu?
maklum belom pernah gunakan macro sebelumnya, jd bingung :d

makasih bro :)


24 April 2013 pukul 21.02
Unknown mengatakan...

Dear Admin,,
sebelumnya thanks utk artikel yg sangat berguna ini :)

saya mau nanya,,
kalau misal saya punya 12 workbook yg masing2nya berisi data laporan/bulan.
data tsb mau saya buatkan jd 1laporan dan dibuatkan juga chart dan analisisnya ke masing2 workbook.

pertanyaannya ::
1.bisakah saya jadikan 12workbook tsb jd 1 workbook yg isinya jd beberapa sheet?
2. bisakah kalau saya sudah membuat 1 chart di slaah satu sheet sbg analisis saya, maka secara otomatis di sheet lain jg muncul chart namun hasilnya sesuai dgn isi data masing2 sheet tsb?
3. bisa gunain coding diataskah?

mohon bantuan dan infonya,, belom pernah berkutat dgn macro sebelumnya :(


Thanks :)


24 April 2013 pukul 21.10
Old Nakula mengatakan...

Secara teoritis bisa mba Devin,... Bedanya, Untuk case chart di atas, reference/source datanya masih terletak di sheet yang lain yang berada dalam satu workbook yang sama, sedangkan casenya mba Devin, source datanya ada di workbook yang berbeda kan?... Ini hanya masalah pengambilan source datanya saja (i.e yang menjadi rujukan updating Chart automatically setiap source datanya diupdate, atau dialter, red)..

Namun mohon maaf, berhubung sedikitnya waktu liang, saya belum bisa membantu ya mba.. :D, nuwun sewu lo..


12 Mei 2013 pukul 21.08
Admin mengatakan...

Nice Post..good good good..sukses sih mas, saya aplikasikan di excel macro saya..ini ni...ini niii...ini nii..yang saya cari....cuma bikin tombol refreshnya gmn mas?


11 April 2015 pukul 16.20
Silvya mengatakan...

thanks kak infonya,, tapi klo pengen chart nya bentuk XY scatter itu coding nya apa? trus klo table nya gak horizontal (kayak contoh) tapi vertikal (kebawah) gitu gimana??


24 Oktober 2016 pukul 21.31
Pak Yadi mengatakan...

terimakasih mas, tutorialnya membantu sekali buat sy untuk referensi membuat modul komputer VBA Macro di Microsoft Excel untuk siswa SMA SMK...


24 April 2017 pukul 16.07
Unknown mengatakan...

Maaf min, mau nanya gimana cara masukin rumus plot bila yg diketahui bukan tabelnya, melainkan nilai v, alpha, r, ymax, dan x max?


31 Mei 2017 pukul 08.01
Unknown mengatakan...

Maaf min, mau nanya gimana cara masukin rumus plot bila yg diketahui bukan tabelnya, melainkan nilai v, alpha, r, ymax, dan x max?


31 Mei 2017 pukul 08.01

Posting Komentar

 

Entri Populer

Recent Comments

Blog Statistic

Return to top of page Copyright © 2007 | Old Nakula