Conditional Formatting Using Visual Basic Application

Beberapa hari yang lalu saat melakukan aktivitas ‘blogwalking’, saya mendapati sebuah pertanyaan dari seorang pengunjung di salah satu website terkait Microsoft Excel sebagai berikut: “Dear Admin, what does a good excel report actually look like? Must it visually be good looking?”, kemudian sang Admin menjawab, “Hi, you’re right. Spreadsheets that are going ‘public’ need to have visual interest. Happily, there are plenty of tools in Excel to accomplish that goal..”, begitu kurang lebih tanggapan dari sang Admin. Dikatakan; selain harus valid, simple, mudah dibaca dan dipahami, tampilan sebuah report yang baik juga perlu dibuat menarik. Satu hal yang perlu digarisbawahi bahwa ‘menarik’ itu tidak harus terlihat canggih. Sekeren apapun report jika pada akhirnya membingungkan pembacanya tidak akan dikatakan menarik. Tujuan dikemasnya tampilan sebuah report semenarik mungkin adalah agar memudahkan para pembacanya memahami content report secara lebih baik. Bahkan di kalangan tertentu tampilan visual sebuah report (entah itu dalam bentuk grafik, icon sets, gambar dll) itu dianggap lebih efektif dan lebih cepat dicerna oleh otak daripada tampilan huruf-huruf (baca: kalimat) atau angka-angka yang terkesan rumit.

Menindaklanjuti tema “visual interest report” diatas, hari ini saya akan berbagi trik sederhana mengenai cara menampilkan “icon sets” pada Microsoft Excel agar terlihat lebih menarik i.e menggunakan Visual Basic Application (VBA) tentunya. Icon sets yang saya maksud adalah sebagaimana yang ditunjukkan pada gambar dibawah. Jika kita perhatikan, terdapat symbol silang (x), symbol check (v), dan symbol tanda seru (!) pada kolom D. symbol-symbol itulah salah satu varian dari Icon sets. Sejatinya symbol-symbol tersebut sudah ada di MS. Excel. Kita bisa menemukannya pada menu Conditional Formatting (Home> Conditional Formatting) dengan berbagai macam variannya seperti Arrows, Flags, Traffic Lights, Quarters dll. Icon sets berbentuk symbols saya pilih karena paling sesuai dengan karakter report yang akan saya buat i.e symbol silang (x) mewakili growth negatif, symbol check (v) mewakili growth positif dan symbol tanda seru (!) mewakili ungrowth revenue.

Sebelum kita membahas syntax program lebih jauh, ijinkanlah saya menjelaskan terlebih dahulu deskripsi table diatas. Ada dua kolom revenue (i.e kolom B dan C) yang masing-masing mewakili service type di bulan Januari dan Februari. Kolom Growth dibuat untuk melihat ada tidaknya pertumbuhan revenue di bulan Februari. Untuk mengetahuinya, kita harus mengkomparasikan data revenue di bulan Februari dengan data revenue di bulan Januari (menggunakan formula: [C-B]/B). Jika prosentasenya minus [negative] maka itu artinya revenue mengalami penurunan. Dan symbol silang (x) diatas secara otomatis akan muncul sebagai representasi dari penurunan revenue. Begitupula jika prosentasenya plus [positif] atau zero (nol), maka symbol check (v) atau symbol tanda seru (!) akan muncul sebagai representasi dari peningkatan atau stagnansi revenue. Otomatisasi tersebut bisa dijalankan melalui syntax berikut:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rFormulas As Range

On Error Resume Next

LastRow = ActiveSheet.UsedRange.Rows.Count

Set rFormulas = Range("D2", Range("D" & LastRow)).SpecialCells(xlCellTypeFormulas)

If Not rFormulas Is Nothing Then

Range("D2", Range("D" & LastRow)).Select

Selection.FormatConditions.AddIconSetCondition

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.ReverseOrder = False

.ShowIconOnly = False

.iconSet = ActiveWorkbook.IconSets(xl3Symbols)

End With

With Selection.FormatConditions(1).IconCriteria(2)

.Type = xlConditionValueNumber

.Value = 0

.Operator = 7

End With

With Selection.FormatConditions(1).IconCriteria(3)

.Type = xlConditionValueNumber

.Value = 0.01

.Operator = 7

End With

On Error GoTo 0

Exit Sub

End If

End Sub

Langkah-langkah

[1]. Langkah pertama tentu membuat table sebagaimana contoh diatas, buatlah kolom Service Type, Kolom Revenue (January & February) dan kolom Growth.

[2]. Jika macro (VBA) pada Microsoft Excel anda belum diaktifkan, maka aktifkanlah terlebih dahulu dengan cara; klik disini.

[3]. Buka Visual Basic Editor (VBE) dengan menekan tombol “Alt” dan “F11” secara bersamaan. Akan muncul Microsoft Excel Objects sebagai berikut:

Klik kanan Sheet1 atau Sheet2 (tergantung di lokasi mana anda membuat Tabel) kemudian View Code. Copykan syntax VBA diatas ke dalam “view code” tersebut.

[4]. Kembali ke table di Sheet1 atau Sheet2, kini anda bisa melakukan ujicoba. Pada cell D2, ketikkan rumus (C2-B2)/B2. Jika muncul icon sets symbols maka itu tandanya syntax tersebut sudah berjalan sesuai dengan harapan.

Penjelasan Syntax

Well,..kita coba kupas beberapa syntax VBA diatas (CMIIW a.k.a jika salah mohon diluruskan, red).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rFormulas As Range

On Error Resume Next

LastRow = ActiveSheet.UsedRange.Rows.Count

Set rFormulas = Range("D2", Range("D" & LastRow)).SpecialCells(xlCellTypeFormulas)

Sebenarnya kita bisa menuliskan syntax vba tersebut di Module/ Class Module (seperti yang biasa kita lakukan). Namun kali ini saya sengaja menggunakan Worksheet Event Change (mengenai Worksheet Event Change bisa klik disini.) agar setiap kali saya melakukan perubahan pada sheet tersebut, syntax bekerja secara otomatis mengikuti perubahan. Hasil dari syntax LastRow = ActiveSheet.UsedRange.Rows.Count adalah berupa value. Syntax tersebut akan menghitung secara otomatis jumlah record yang ada pada table (misal; ada 7 record pada table diatas, maka LastRow = 7, red). Karena icon sets symbols nantinya akan kita munculkan di setiap record di kolom D, maka pada setting rFormulas dituliskan syntax berikut: Range("D2", Range("D" & LastRow)). Adapun fungsi dari syntax .SpecialCells(xlCellTypeFormulas) pada rFormulas adalah untuk mengidentifikasi apakah cell yang berada pada specified/selected range (pada kolom D) mengandung formula. Jika iya, maka icon sets symbol akan dimunculkan oleh system secara otomatis, sebaliknya jika tidak, icon sets symbol tidak akan dimunculkan. Oleh karenanya, pada syntax selanjutnya tertulis;

If Not rFormulas Is Nothing Then

Range("D2", Range("D" & LastRow)).Select

Selection.FormatConditions.AddIconSetCondition

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.ReverseOrder = False

.ShowIconOnly = False

.iconSet = ActiveWorkbook.IconSets(xl3Symbols)

End With

Syntax If Not rFormulas Is Nothing Then berfungsi melakukan verifikasi apakah cell pada specified/ selected range mengadung formula atau tidak. Jika iya, maka syntax berikutnya akan dieksekusi oleh system.

Selection.FormatConditions.AddIconSetCondition

(Returns a new IconSetCondition object which represents an icon set conditional formatting rule for the specified range. Intinya, syntax ini berfungsi menambahkan Icon set baru pada specified/selected range)

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

(Selection.FormatConditions.Count: Returns a Long value that represents the number of objects in the collection)

With Selection.FormatConditions(1)

.ReverseOrder = False

.ShowIconOnly = False

(Jika .ShowIconOnly kita set “True”, maka angka [i.e prosentase] yang tercatat di kolom G tidak ditampilkan. Yang muncul hanya Icon sets-nya saja)

.iconSet = ActiveWorkbook.IconSets(xl3Symbols)

(Icon sets yang saya pilih adalah type symbols .IconSets(xl3Symbols) karena sesuai dengan karakteristik data yang saya buat [i.e ingin melihat prosentase growth])

End With

Adapun syntax selanjutnya merupakan penjabaran dari “conditional formatting rule” yang berlaku pada specified/selected range, dimana specified range dalam hal ini adalah selected range pada kolom D.

With Selection.FormatConditions(1).IconCriteria(2)

.Type = xlConditionValueNumber

.Value = 0

.Operator = 7

End With

With Selection.FormatConditions(1).IconCriteria(3)

.Type = xlConditionValueNumber

.Value = 0.01

.Operator = 7

End With

On Error GoTo 0

Exit Sub

End If

End Sub

Jika kita perhatikan secara seksama, tidak kita temukan .IconCriteria(1) pada syntax diatas (yang ada hanyalah .IconCriteria(2) dan .IconCriteria(3)). Mengapa demikian?, jawabannya bisa kita temukan pada sebuah buku yang berjudul; Excel 2007 VBA Programming with XML and ASP, ditulis oleh seorang MVP Excel, Julitta Korol** sebagai berikut: (Baca scan text yang saya garis bawahi, red).

Jadi Excel secara otomatis akan mensetting threshold value-nya sendiri pada IconCriteria(1). Jika kita set threshold valuenya ke dalam syntax (misal; With Selection.FormatConditions(1).IconCriteria(1)), maka yang terjadi adalah akan muncul message box “a run-time error”.

Angka 7 pada syntax .Operator = 7 maksudnya adalah “Greater than or equal to”. Atau bisa juga kita tulis .Operator = xlGreaterEqual sebagai ganti dari syntax .Operator = 7. Sedangkan value pada masing-masing IconCriteria saya set “0”, dan “0.01” yang artinya: Jika value (salah satu cell di kolom D) <0, maka icon sets bersimbol silang (x), jika value antara 0<0.01 maka icon sets bersimbol tanda seru (!) dan jika value >0 maka icon sets bersimbol check (v).

Itu saja, mudah-mudahan bermanfaat.

_________________
**JULITTA KOROL is a software consultant, technical writer, and software trainer who delivers custom-tailored hands-on training and develops advanced database and spreadsheet applications. She is a best-selling author of nine computer books published by Mikom Publishing in Warsaw, Poland, and the author of the forthcoming Learn Word 2000 VBA by Example (1-55622-748-5). She lives on Long Island, New York.