Kedahsyatan Microsoft Excel (“MS Excel”) Bag. II

Kembali ke topic “Dahsyatnya Microsoft Excel” bab: Visual Basic Application (“VBA”) atau Macros. Pada artikel sebelumnya, kita sudah membahas mengenai kedahsyatan Microsoft Excel berikut manfaatnya dalam aktivitas keseharian kita. Pada kesempatan kali ini, saya akan menshare salah satu contoh syntax VBA yang sempat saya gunakan untuk menampilkan record data dengan menggunakan teknik looping (pengulangan). Bagi rekan-rekan yang mahir dalam bidang pemrograman, mungkin sample syntax berikut tidaklah sulit. Namun bagi seorang pemula seperti saya yang tidak memiliki basic pemrograman sama sekali, perlu waktu berjam-jam untuk memahami, mempelajari, dan memodifikasi syntax tersebut agar bisa terbaca dan terdeteksi oleh Microsoft Excel. Nah jika suatu saat rekan-rekan diminta atasan, teman atau rekan sejawat untuk menyiapkan data serupa dengan karakteristik data yang berulang, rekan-rekan bisa mengadopsi syntax berikut dan memodifikasinya sesuai dengan kebutuhan. Sebagai catatan, syntax ini saya peroleh dari sebuah vba-forum yang dikelola oleh para MVP VBA Excel professional dari berbagai negara. Bagi rekan-rekan yang masih belum memahami apa dan bagaimana VBA diaplikasikan, jangan khawatir, rekan-rekan bisa mempelajari dasar-dasarnya terlebih dahulu dengan mengakses url yang memuat referensi-referensi tentang VBA yang banyak tersebar di internet seperti www.excel-vba.com, www.ozgrid.com/VBA, http://www.vbtutor.net/VBA/vba_tutorial.html dan lain-lain atau melalui buku-buku seperti Microsoft Excel 2007: Pemrograman VBA, Membuat Aplikasi Penjualan menggunakan Macro Excel dan lain-lain. Kunci utamanya hanya ada dua; yakni mau belajar dan banyak mencoba, jangan patah arang ketika gagal dalam bereksplorasi. OK, langsung saja ke topic pembahasan,..

'Option Explicit

Sub RepeatAgain()

Dim rw, i, a, rCount As Long

Dim aRange, iRange, CellA, MSISDN As Range

Syntax VBA biasanya diawali dengan “Sub” dan diikuti oleh nama program yang akan kita buat. “RepeatAgain” adalah contoh nama yang saya pilih karena program tersebut berisi syntax-syntax looping. Coba perhatikan, pada syntax diatas terdapat “Dim” yang berfungsi untuk mendeklarasikan variable [Microsoft Excel 2007: Pemrograman VBA, Penerbit ANDI, Yogyakarta]. Rw, i, a, rCount, iRange dan seterusnya adalah nama-nama variable yang saya gunakan dalam program tersebut dimana masing-masing memiliki type yang merupakan pengganti dari variable itu sendiri (e.g Long dan Range)

Set iRange = Sheets("Result").Range("C11")

Set MSISDN = Range("C11", Range("C" & Rows.Count).End(xlUp))

Range pada dasarnya identik dengan cell, baik itu single maupun multiple. “A1” misalnya, tetap disebut sebagai range meskipun tunggal. Agar syntax yang kita tulis tidak terlalu panjang, maka pada syntax-syntax tertentu yang digunakan secara berulang (lebih dari sekali pemakaian, red) bisa kita setting di awal dengan menggunakan kode “Set” seperti contoh diatas.

Ketika Syntax **Range("C11", Range("C" & Rows.Count).End(xlUp))** dirunning (dijalankan), maka system secara otomatis akan memblok data mulai dari cell “C11” sampai cell “C[number]”. Number ini menunjukkan jumlah row (baris) dimana record terakhir pada kolom “C” itu berada. Misal posisi record berada pada row ke 22, maka number pada “C[number]” adalah 22 (i.e “C22”). Barangkali ada pertanyaan, apa sih bedanya ** .End(xlUp))** dengan**.End(xlDown)**?. Sejauh yang saya tahu, perbedaannya hanya terletak pada proses/cara system melakukan pemblokingan dan pada hasil blokingannya saja. ** .End(xlUp))** >> akan memblocking data dari record terakhir ke record awal sedangkan **.End(xlDown)** >> akan memblocking data dari record awal ke record terakhir. Sampai disini masih mudah bukan?

'On Error GoTo MyExit

rw = Range("C" & (Rows.Count)).End(xlDown).Row

a = WorksheetFunction.CountA(Range("C11", "C" & rw))

**On Error GoTo MyExit** sebenarnya berfungsi untuk “mengabaikan error” yang mungkin muncul pada saat program dirunning. Ketika benar bahwa error tersebut ditemukan, maka action otomatis yang dilakukan oleh system adalah “Exit” dari program. Namun jika kita ingin mengetahui letak errornya, sebaiknya syntax tersebut dimatikan dengan menambahkan kode [‘] di awal syntax.

**Range("C" & (Rows.Count)).End(xlDown).Row** merupakan salah satu syntax dari sekian model syntax VBA yang menunjukkan jumlah baris pada suatu kolom tanpa kita “batasi”, kecuali oleh “keterbatasan” jumlah baris pada Excel itu sendiri. Bingung ya? Hehe, sekali-kali boleh kan pakai bahasa majasi. Contoh simpelnya begini; di MS Excel 2003 jumlah maksimum data yang mampu ditampung adalah 65.536 record, artinya jumlah baris maksimal pada MS Excel 2003 adalah 65.536. Nah ketika syntax diatas dirunning system akan menunjukkan hasil **rw = 65.536**. Berbeda ceritanya jika syntax tersebut kita jalankan di MS Excel 2007, karena hasilnya akan menunjukkan **rw = 1.048.576**. Kenapa? Karena jumlah maksimum data yang mampu ditampung di MS Excel 2007 adalah 1.048.576 record, jauh lebih besar dari kapasitas maksimum record di MS Excel 2003. Nah itulah kurang lebih makna kalimat “tidak dibatasi kecuali oleh keterbatasan jumlah baris pada Excel itu sendiri”

**WorksheetFunction.CountA(Range("C11", "C" & rw)) ** merupakan fungsi numerical yang akan menghitung jumlah record pada suatu kolom secara otomatis (dalam hal ini adalah kolom C). Kurang lebih cara kerjanya sama dengan fungsi “CountA” di (sheet) MS Excel. Syntax tersebut merupakah hasil eksplorasi saya pribadi dengan pertimbangan bahwa data yang akan saya olah sifatnya dinamis (i.e record bisa bertambah sewaktu-waktu, red) sehingga perlu adanya fungsi otomatis yang mampu menghitung setiap perubahan record yang terjadi dalam kolom tersebut.

If a = 0 Then

MsgBox "Punten pangisikeun heula datana Kang .. ^_^. Nuhun", _

vbCritical + vbOKOnly, "Wartos!"

Exit Sub

End If

Perlu kreatifitas dan imajinasi dalam pemrograman. Ibarat masakan, kita tidak selalu harus patuh mengikuti pakem/petunjuk cara memasak atau cara memilih bahan/bumbu. Kita bisa menambah atau mengurangi bahan/bumbu tersebut sesuai dengan kebutuhan dengan cara bereksplorasi. Salah satunya adalah syntax sederhana diatas. Syntax tersebut digunakan untuk mendeteksi apakah record yang akan kita looping tersebut eksis/ada/tersedia. Jika tidak, maka system akan menampilkan pesan "Punten pangisikeun heula datana Kang .. ^_^. Nuhun". System tidak akan mengeksekusi program hingga data tersebut tersedia.

For Each CellA In MSISDN

For i = 0 To Range("D" & (CellA.Row)).Value - 1

If Not IsEmpty(Range("C" & (CellA.Row)).Value) Then

iRange.Offset(i).Value = Range("C" & (CellA.Row)).Value

End If

Next i

Set iRange = iRange.Offset(i)

ActiveCell.Offset(1).Select

Next CellA

Syntax diatas merupakan inti dari program yang saya buat dan merupakan bagian yang paling rumit, yakni looping record. Butuh waktu beberapa jam untuk memastikan bahwa syntax berjalan sesuai dengan harapan melalui metode “try and error”. Barangkali kita pernah belajar atau mendengar logika looping seperti For…Next, For Each…Next, Do While…Loop dll. Nah kita bisa menggunakan salah satunya. Dalam case diatas saya memilih menggunakan “For Each…Next”. Dasar pertimbangannya hanya satu yakni karena logikanya paling mudah dipahami (bagi saya), hehe. Di bagian sebelumnya, saya sudah menset variable MSISDN sebagai Range, maka syntax **For Each CellA In MSISDN** bisa dibaca sbb: “Untuk setiap data yang terecord di dalam range MSISDN”, dan **Next CellA** berfungsi melakukan looping sebanyak jumlah record yang ada di range MSISDN. Kenapa variable (i) dimulai dari 0, tidak dari 1?, Sebab kita ingin record hasil looping pertama ditempatkan pada **Sheets("Result").Range("C11")**. Jika nilai (i) diset diangka 1, maka posisi record pertama berada di cell (“C12”), bukan (“C11”) pada Sheets(“Result”). Itulah mengapa saya tulis (-1) pada syntax **Range("D" & (CellA.Row)).Value – 1**

**Range("D" & (CellA.Row)).Value – 1** sendiri pada dasarnya berupa value. Sumber rujukannya adalah record di cellA dalam range MSISDN. Jika cellA berada pada posisi record cell (“C11”) di Sheets(“Raw Data”) misalnya, maka Range("D" & (CellA.Row)).Value menunjukkan nilai numerical record di cell sebelahnya, yakni cell (“D11”). Pada case diatas, cell (“C11”) dan seterusnya berisi data MSISDN [e.g +62811222333, +62811223344 dll], sedangkan cell (“D11”) dan seterusnya berisi data jumlah poin [e.g 5, 10, 6 dll]. Clear?..Lanjut kalau begitu..

**If Not IsEmpty(Range("C" & (CellA.Row)).Value)** digunakan sebagai prasyarat bagi syntax selanjutnya, yakni jika value pada Range("C" & (CellA.Row)) tidak kosong (terisi), maka **iRange.Offset(i).Value = Range("C" & (CellA.Row)).Value** bisa dijalankan. Sebaliknya jika value tersebut kosong, maka looping tidak bisa dilakukan.**Offset(i)** berfungsi untuk menggeser posisi cell aktif. Ketika nilai i = 0, maka posisi cell aktif tetap di tempat semula. Cell aktif baru akan bergeser ke row selanjutnya setelah nilai i > 0. Jadi logikanya adalah jika record padaRange("C" & (CellA.Row) tidak kosong, maka system akan melooping Range("C" & (CellA.Row)).Value sebanyakRange("D" & (CellA.Row)).Value kali, dimana hasilnya akan ditempatkan di iRange.Offset(i).

Sejauh yang saya ketahui syntax **Set iRange = iRange.Offset(i)** berfungsi mengurutkan record hasil looping danmemastikan bahwa jumlah record tersebut sudah sesuai dengan nilai i-nya (jumlah poinnya, red). Jika syntaxtersebut dimatikan, maka hasilnya akan jauh berbeda.

Sheets("Raw Data").Range("E8").Select

End Sub

**Sheets("Raw Data").Range("E8").Select** Merupakan syntax yang berguna untuk menggeser posisi cell aktifterakhir ke posisi range (“E8”) di Sheets(“Raw Data”). Jadi setelah proses looping selesai, saya ingin memposisikandefault cell aktif ke range (“E8”). Sebagai penutup, program “RepeatAgain” saya akhiri dengan kode **End Sub**yang menunjukkan bahwa program looping telah sempurna. Selamat mencoba Guys dan jangan pernah lelah untuk bereksplorasi.. ^_^