Add-in Excel untuk Pemula
Sering kita menemukan kondisi dimana formula Excel tidak mencukupi kebutuhan, atau menjadi terlalu sulit dicerna secara logika.
Misal seperti tulisan ini, omong-omong blog tersebut bagus untuk pengguna MS Excel. Jika kita ingin mendapatkan kata paling kiri, formulanya masih relatif sederhana, namun untuk mendapatkan kata kedua sudah mulai bikin kepala sakit, itupun baru sebatas tiga kata, bagaimana jika lebih dari itu?
Dan jangan lupakan fakta bahwa makin panjang formula maka makin sulit untuk dicerna yang mengakibatkan makin rawan salah.
Memprogram untuk mengolah data, seperti kebanyakan tulisan di blog ini, mungkin terlalu rumit, dan akan sangat merepotkan jika saat akan mengolah data yang serupa itu harus menulis kode terlebih dahulu.
Sayangnya lagi di pasaran tidak ada, setidaknya belum ketemu, solusi yang sesuai dengan masalah dan kondisi kita.
Jika kondisi serupa sering kita temukan, tidak ada salahnya menginvestasikan waktu sebentar untuk membuat Add-in Excel.
Add-in dapat berupa sebuah tombol yang dapat diklik atau sebuah custom formula yang dapat dipanggil pada tiap cell. Untuk tipe kedua ini, yang populer di Indonesia adalah formula terbilang yang mengonversi angka menjadi kata (99 menjadi sembilan puluh sembilan).
Oke, mari kita wujudkan. Dalam tulisan ini akan dibagi menjadi tiga langkah pembuatan Add-in, pertama pengenalan mengakses VBA dan menyimpan kode sebagai Add-in. Bagian kedua membuat solusi persoalan di atas dengan Add-in berupa tombol. Dan terakhir Add-in berupa formula yang dapat diketik pada cell.
Pengenalan Akses VBA
Kita akan mencontek metode di sini. Tujuannya adalah membuat sebuah tombol sederhana yang akan memberi warna merah pada cell yang mengandung kesalahan formula.
Buka VBA, bisa dengan ALT+F11.
Di sisi kiri terdapat Project Explorer. Klik kanan pada Sheet, pada kasus penulis Sheet2, lalu Insert dan Module.
Lalu akan ditampilkan formulir untuk menulis kode pada Module1.
Jika formulir untuk menulis kode tersebut tidak muncul, cukup klik kanan pada module dan pilih View Code.
Kemudian paste kode di bawah ini.
Option Explicit
Sub HihglightErrors()
Selection.SpecialCells(xlCellTypeFormulas, xlErrors).Select
Selection.Interior.Color = vbRed
End Sub
Lalu simpan dengan ekstensi xlam. Dalam kasus ini penulis menyimpan dengan nama BuatAddIn1.xlam.
Tombol Akses Add-in
Untuk mencoba Add-in, buka file Excel baru. Kemudian aktifkan Add-in melalui menu File > Options > Add Ins.
Pada bagian Inactive Application Add-ins, pilih Add-in yang sebelumnya dibuat, BuatAddin1. Kemudian klik tombol Go.
Centang pada Buataddin1 kemudian klik OK.
Sampai saat ini, Add-in yang dibuat telah dapat digunakan, namun untuk memudahkan penggunaan, kita dapat membuat sebuah tombol yang akan men-trigger (menjalankan) Add-in tersebut.
Klik File > Options > Quick Access Toolbar. Pada Choose commands from: pilih Macros.
Klik HihglightErrors (awalnya memang saltik namun pada akhirnya berguna, setidaknya membedakan dengan Add-in yang menjadi contoh). Kemudian Add, lalu OK.
Di pojok kiri atas Excel terdapat tombol tambahan untuk Add-in yang dibuat.
Ikon yang digunakan memang tidak terlalu menarik, kapan-kapan kita perbaiki. Kembali ke bagian worksheet, penulis sudah membuat beberapa baris dengan formula di atas, di mana formula ketiga error. Begini kondisi sebelum dan setelah klik pada tombol Add-in.
Awesome!
Kita berhasil membuat Add-in pertama. Sampai sekarang kita telah dapat:
- Membuka VBA.
- Mengetik kode pada module, kode inilah yang mengerjakan hal terberat.
- Menyimpan Add-in.
- Menambahkan Add-in.
- Menggunakan Add-in.
Beberapa konsep yang patut menjadi perhatian adalah, kita dapat membuat kode sepanjang apapun, melakukan pekerjaan apapun, batasannya hanya langit. Selepas itu, kita tinggal memanggil kode tersebut dan tidak perlu memikirkan bagaimana cara kerjanya. Memanggil kode, atau memerintahkan kode untuk bekerja, dapat menggunakan tombol seperti di atas, atau dengan menambahkan sebuah custom (khusus) formula seperti yang akan diterangkan di bawah ini.
Akses Add-in melalui Formula
Di bagian ini kita akan membuat sebuah Add-in yang mengambil kata ke-n dari sebuah cell lalu memanggil melalui formula. Dari bagian di atas kita sudah mengetahui bahwa yang perlu diubah adalah kode VBA, proses yang lain seperti membuat module, menyimpan dan mengaksesnya relatif sama.
Tak ada yang baru di bawah matahari.
Di luar sana, biasanya, telah ada yang mengalami masalah yang sama dengan kita dan sudah ada yang membuat kode untuk itu. Sebab itukita bisa googling, misalnya dengan keyword “VBA Code get n word“.
Hasil pencarian tersebut cukup meyakinkan dan penulis menemukan bahwa link kedua sepertinya patut dicoba.
Kita melakukan proses yang sama seperti sebelumnya dari membuka VBA, menambah module, kemudian paste code (ya, penulis semalas itu) di module.
Function Find_nth_word(Phrase As String, n As Integer) As String
Dim Current_Pos As Long
Dim Length_of_String As Integer
Dim Current_Word_No As Integer
Find_nth_word = ""
Current_Word_No = 1
'Remove Leading Spaces
Phrase = Trim(Phrase)
Length_of_String = Len(Phrase)
For Current_Pos = 1 To Length_of_String
If (Current_Word_No = n) Then
Find_nth_word = Find_nth_word & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = " ") Then
Current_Word_No = Current_Word_No + 1
End If
Next Current_Pos
'Remove the rightmost space
Find_nth_word = Trim(Find_nth_word)
End Function
Kemudian simpan sebagai file xlam (BuatAddIn2DapatKata.xlam).
Langkah selanjutnya adalah menambahkan Add-in tersebut.
Add-in telah siap digunakan, yaitu dengan formula Find_nth_word(teks_yang_ingin_diproses;no_kata_yang_akan_diambil).
Cell B2 diisi dengan sebuah lorem ipsum, sedang cell F2 diisi formula mengambil kata ke-2 dari B2, hasilnya mengedjoetkan. Bandingkan dengan cell G2 yang menggunakan formula bawaan Excel untuk mendapatkan kata ke-2, formula yang diketikkan jauh lebih singkat dan mudah dipikirkan.
Hal tersebut dapat diwujudkan karena kerja keras telah dilakukan oleh code VBA, yang tidak bisa disebut kerja keras juga karena cukup copas. Kita akan bahas secara umum kode tersebut.
Function Find_nth_word(Phrase As String, n As Integer) As StringBaris pertama ini adalah nama fungsi yang dibuat, nama fungsi ini yang akan menjadi custom formula yang kita panggil pada cell. Fungsi ini membutuhkan dua variabel, yaitu Phrase (nama ini boleh diganti) dengan tipe data String dan variabel n dengan tipe data Integer. Fungsi Find_nth_word sendiri bertipe data String, artinya keluaran (output) dari fungsi ini adalah string. Kode sebuah fungsi harus ditutup, karena itu di bagian akhir kode terdapat kode End Function.
Pengetahuan atas tipe data adalah keharusan, selengkapnya tipe data yang dapat digunakan pada VBA dapat dilihat di sini.
Dim Current_Pos As Long
Dim Length_of_String As Integer
Dim Current_Word_No As Integer
Find_nth_word = ""
Current_Word_No = 1
'Remove Leading Spaces
Phrase = Trim(Phrase)
Length_of_String = Len(Phrase)Baris-baris kode di atas adalah inisialisasi, pengenalan, variabel yang akan digunakan. Tiga baris Dim berupa variabel dan tipe data yang digunakan yaitu Long (dapat memuat angka banyak-banyak banget) dan Integer (angka banyak). Variabel Find_nth_word diberi nilai teks kosong dan akan digunakan untuk menampung kata ke-n. Variabel Current_word_No diberi nilai awal 1.
Sedang variabel Phrase diisi dengan variabel Phrase (yang telah diisi saat menggunakan fungsi) yang telah dihapuskan spasi di awal dan akhir (Trim). Lebih lanjut tentang fungsi Trim pada VBA dapat dilihat di sini.
Variabel Length_of_String diisi panjang (Len) variabel Phrase.
For Current_Pos = 1 To Length_of_String
If (Current_Word_No = n) Then
Find_nth_word = Find_nth_word & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = " ") Then
Current_Word_No = Current_Word_No + 1
End If
Next Current_PosBaris-baris kode ini adalah sebuah perulangan (Loop), karakter/huruf per huruf dari variabel Phrase akan diuji.
Jika variabel Current_Word_No (yang sebelumnya telah diisi dengan 1) sama dengan n (yang diisi saat pemanggilan fungsi, dalam contoh di atas diisi 2) maka lakukan: Tambahkan variabel Find_nth_word (yang sebelumnya diisi teks kosong) dengan satu huruf hasil fungsi Mid dari variabel Phrase.
Jika hasil fungsi Mid dari Phrase adalah spasi maka: variabel Current_Word_No diganti dengan Current_Word_No + 1.
Next Current_Pos berarti lakukan untuk variabel Current_Pos selanjutnya.
'Remove the rightmost space
Find_nth_word = Trim(Find_nth_word)
End FunctionDi bagian akhir, variabel Find_nth_word yang ditemukan dibersihkan dulu dari spasi di awal dan akhir kata. Lalu tutup fungsi.
Setelah cukup pede dengan hasilnya, hapal dengan proses pembuatan, kita dapat mulai memodifikasi Add-in yang telah dibuat sebelumnya. Penulis sendiri merasa kode di atas masih dapat disederhanakan.
Buat sebuah Add-in lalu ketik fungsi Cari_Kata_ke seperti di bawah ini.
Function Cari_Kata_ke(Phrase As String, n As Integer) As String
Dim Phrase_Arr() As String
Phrase_Arr() = Split(Phrase)
Cari_Kata_ke = Phrase_Arr(n - 1)
End FunctionPada dasarnya kode ini melakukan hal yang sama, mendapatkan kata ke-n dari sebuah teks. Bedanya dibuat sebuah variabel Array bernama Phrase_Arr dengan tipe data String. Kemudian variabel tersebut diisi dengan hasil pemisahan teks (Split) pada variabel Phrase.
Fungsi Cari_Kata_ke kemudian diisi dengan elemen ke-n minus 1 dari array Phrase_Arr. Kenapa harus dikurangi 1?
Karena fungsi Cari_Kata_ke pada formula diisi dengan no_kata_yang_akan_diambil yang dimulai dari 1 (urutan 1 sampai n lebih alami untuk kita, manusia).
Sedangkan Array dalam VBA dimulai dengan 0.
Sehingga untuk mendapatkan kata “Ipsum” dalam formula kita mengetikkan 2, sedang VBA menggunakan 1.
Akhirnya setelah prosedur standar seperti penyimpanan Add-in, menambahkannya dan lain-lain, penggunaan formula yang baru kurang lebih adalah Cari_Kata_ke(B2;2).
Dari pengalaman menulis Add-in tadi penulis mendapatkan beberapa hal yang perlu diperhatikan saat menulis kode VBA.
- Pentingnya pengetahuan tentang tipe data.
- Fungsi bawaan VBA perlu menjadi perhatian agar tidak perlu reinventing the wheel.
Salam.
3 Replies to “Add-in Excel untuk Pemula”