Macro: Perintah SELECT CASE

Perintah SELECT CASE adalah salah satu syntax yang digunakan dalam bahasa pemrograman Visual Basic for Applications (VBA) atau lebih banyak dikenal dengan sebutan Macro. Perintah atau syntax ini sebenarnya memiliki peran yang hampir sama dengan perintah If-Then, namun dalam beberapa kasus sedikit berbeda atau kadang tergantung selera dari programmer lebih memilih menggunakan Select Case atau perintah If-Then. Oh iya, seperti biasa bila anda tiba-tiba berada dihalaman ini dan belum tahu apa yang disebut dengan Macro atau VBA, sebaiknya terlebih dahulu membaca posting tentang "Membuat Aplikasi pada Excel".

Bentuk dasar penulisan perintah Select Case adalah:

Select Case Variable

     Case i

         'perintah kondisi i

     Case j

         'perintah kondisi j

      Case Else

         'perintah kondisi else

End Select

Dari contoh kode diatas, macro akan menjalankan perintah sesuai kondisi dariVariable, bila nilai Variable sama dengan i, maka perintah kondisi i yang akan dijalankan, begitu juga bila Variable memiliki nilai j, maka akan dijalankan perintah kondisi j.

Bila diperlihatkan dalam bentuk contoh real:

Sub Say_Hello()

Nama = "Bagus"

Select Case Nama

  Case "Andi"

     MsgBox "Halo Andi!"

  Case "Bagus"

    MsgBox "Halo Bagus!"

  Case Else

    MsgBox "Tidak Ada Nama!"

End Select

End Sub

Bila dijalankan, seharusnya menghasilnya MsgBox yang bertuliskan "Halo Bagus!"

Yup, perintah ini sangat simple dan sangat banyak kesamaannya dengan perintah If-Then,  jadi tidak akan kita bahas secara panjang lebar. Mudah-mudahan bermanfaat bagi anda yang sedang belajar Macro.

Read more »

Memberi Warna Border Cells dalam Excel

Border Cells yang saya maksud adalah garis pembatas antara masing-masing cell, dan untuk mempercantik dan membuat tampilan Excel lebih rapi, salah satu cara yang bisa digunakan adalah memberi border cell, dan border ini bisa dipilih warna yang kita inginkan, karena warna standar atau default dari border ini adalah hitam.

Pertama-tama, kita akan membahas cara membuat border dalam sheet Excel.

 Untuk membuat border dalam cells Excel, pertama blok area yang akan diberi border, lalu cari menu seperti gambar disamping, dan pilih jenis border yang akan dibuat. Bila memilih sesuai menu disamping, maka hasilnya akan sama seperti yang ditunjukkan gambar pertama diatas, tapi tentunya warna border-nya masih default atau hitam.

Untuk mengubah warna border ini, blok kembali area tersebut, dan klik kanan pada mouse anda, atau klik menu Format - Cells (atau tekan tombol Ctrl+1), maka akan muncul menu baru seperti gambar dibawah ini.

Dari menu ini, klik pilihan warna, pada gambar diberi tanda "1".

Pilih jenis border pada tanda "2"

Dan pada gambar yang diberi tanda "3", klik pada bagian mana border ini akan dibuat, apakah border top (bagian atas cell), border bottom (bagian bawah cell), dan lain-lain.

Klik OK untuk mengakhiri, dan lihat hasilnya. Selamat mencoba

Read more »

Rumus Perhitungan dalam Excel

Berikut kita akan membahas beberapa rumus Excel yang digunakan dalam perhitungan angka. Beberapa rumus yang dibahas ini biasanya banyak digunakan dalam pengolahan database, dan untuk melihat semua jenis rumus perhitungan angka yang tersedia dalam Excel, lihat pada menu Insert - Function, dan akan muncul listing semua function atau rumus yang tersedia dalam Excel. Pilih category Math & Trig untuk memilih jenis-jenis function yang dapat digunakan untuk perhitungan.

Sebelum menggunaan rumus atau function yang diperlihatkan menu diatas, ada beberapa operator standar yang perlu kita bahas.

Tanda "+" untuk perhitungan Tambah
Contoh: =B1+C2 atau =45+40

Tanda "-" untuk perhitungan Kurang
Contoh: =B1-C2 atau =45-40

Tanda "*" untuk perhitungan Perkalian
Contoh: =B1*C2 atau =45*40

Tanda "/" untuk perhitungan Pembagian
Contoh: =B1/C2 atau =45/40

Tanda "^" untuk perhitungan Pangkat
Contoh: =B1^2 atau =45^2

Berikutnya kita membahas rumus atau function yang bisa digunakan dalam perhitungan
Rumus Sum()
Digunakan untuk menghitung summary atau penjumlahan data yang disebut dalam rumus

Format: =Sum(nilai1, nilai2, nilai3,...) atau =Sum(range)
Contoh: =Sum(1, 2, 5, 45) atau =Sum(B1:B20)

Rumus Average()
Berguna untuk menghasilkan nilai rata-rata dari data yang disebutkan dalam rumus 
Format: =Average(nilai1, nilai2, ...) atau =Average(range)
Contoh; =Average(1, 4, 56, 34) atau =Average(B1:B34)

Rumus Min()
Berguna untuk mendapatkan nilai minimum dari data yang disebutkan dalam rumus
Format: =Min(nilai1, nilai2, nilai3, ...) atau =Min(range)
Contoh: =Min(23, 45, 234, 12, 46), atau =Min(A1:B34)

Rumus Max()
Sama halnya dengan rumus Min, tapi Max memberikan angka terbesar / maksimum
Format: =Max(nilai1, nilai2, nilai3, ...) atau =Max(range)
Contoh: =Max(23, 45, 234, 12, 46), atau =Max(A1:B34)

Rumus SQRT()
Berguna untuk mendapatkan angka akar pangkat,
Format: =SQRT(nilai)
Contoh: =SQRT(64), hasilnya 8, karena 8 adalah akar pangkat 2 dari 64

Read more »

Membuat Aplikasi pada Excel

Membuat Aplikasi pada Excel

Sebagai pengguna maniak Excel, salah keunggulan yang sangat menonjol yang saya rasakan dari Microsoft Excel adalah kemampuannya untuk dipadukan dengan Visual Basic For Applications (VBA), atau biasa disebut Macro. Dengan VBA kita bisa membuat sebuah aplikasi yang bisa dijalankan pada sebuah file Excel, tanpa harus dicompile. Kita bisa membuat pengolah database otomatis, bisa membuat aplikasi kalkulator, dan banyak lagi yang lain. Kalau saya menyebutnya versi mini dari Visual Basic.

Disini kita hanya akan membahas bagaimana proses membuat aplikasi pada Excel, dan saya berusaha meyakinkan anda bahwa pemrogroman dengan VBA (Macro) pada excel sangat simple dan mudah. Penulisan Macro (saya lebih suka menyebut macro) bisa dengan 2 cara, yaitu menulis langsung pada window editor, atau dengan cara Record Macro.

Record Macro

Kita akan melakukan salah satu contoh membuat macro dengan cara merecord. Buka file baru dalam aplikasi Excel, klik menu Tools - Macro - Record New Macro.


Muncul menu "Record macro", kita bisa memberi nama sendiri macro yang akan direcord, tapi defaultnya Macro1, kita biarkan saja namanya. Klik Ok.


Mulai dari sini, semua proses yang akan dilakukan terhadap file excel tersebut akan direcord oleh Macro Recorder, sampai nanti kita menekan tombol stop. Sekarang kita akan merubah Page Setup dari Portrait menjadi Lanscape.

Klik menu File - Page Setup - dan pilih Lanscape - Ok. Klik tombol Menu pada menu tambahan yang muncul pada layar, atau klik menu Tools - Macro - Stop Recording. Nah kita sudah selesai membuat macro dengan menggunakan Macro Recorder, sekarang mari kita lihat source codenya.

Pilih menu Tools - Macro - Visual Basic Editor, maka akan muncul layar baru yang berisi source code dari macro yang baru saja kita record, yang bernama Sub Macro1(). Anda mungkin terkejut, bagaimana perintah yang simple itu menghasilkan source yang sangat panjang. Tapi tenang, inilah memang kelemahan bila menulis macro menggunakan Macro Recorder, perintah yang seharusnya tidak perlu juga disertakan pada saat merecord macro.

Mungkin hasil kode yang dihasilkan pada komputer anda seperti ini:
Sub Macro1()
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

Bandingkan, kita bisa menyederhanakan kode diatas menjadi :

Sub Macro1()
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With
End Sub

Read more »

Excel: Macro dan Conditional Formatting

Beberapa waktu lalu, saya membuat tulisan tentang macro Excel yang dapat mengubah angka menjadi teks. Salah seorang pengunjung lantasbertanya kegunaan lain dari macro. Saya jawab dengan contoh.

Misalnya kita punya berkas Excel yang berisi ratusan angka di setiap sel. Kita, misalnya, ingin memberi warna merah untuk angka yang nilainya negatif dan warna biru untuk angka yang bernilai lebih dari 100.

Saya katakan bahwa pekerjaan tersebut dapat dilakukan dengan cepat, cermat, dan mudah dengan menggunakan macro.

Seorang pengunjung yang lain kemudian bertanya, “Bagaimana cara membuatnya?”

Ya, bagaimana melakukannya? Dapatkah hal ini dikerjakan dengan macro? Jawabannya tentu saja dapat. Apakah cepat, cermat, dan mudah? Cermat sih jelas. Tetapi, cepat dan mudah itu relatif.

Setidaknya tak secepat dan semudah jika kita melakukannya dengan fitur yang sudah tersedia di Excel: Conditional Formating!

Sebagai contoh, bukalah sebuah berkas Excel yang masih kosong. Lalu isilah sel-sel pada range A1:E20 dengan angka yang bervariasi antara -50 sampai 200. Anda bisa mengetikkan angka-angka tersebut di setiap sel, atau gunakan fungsi =RANDBETWEEN(-50, 200) di salah satu sel, lalu menyalinnya ke sel yang lain. Fungsi ini¹ akan menghasilkan bilangan acak antara -50 dan 200.

Setelah itu, sorotlah semua sel yang berisi angka-angka tersebut, lalu klik menu Format > Conditional Formatting.

Di bagian Condition 1, pilih Cell Value Is di ruas pertama, less than di ruas kedua, dan di ruas ketiga ketikkan 0. Klik tombol Format… lalu pilihlah warna merah di ruas Color.

Lalu kliklah tombol Add >> dan bagian Conditional 2 akan muncul. Isilah dengan Cell Value Is, greater than, dan 100. Klik tombol Format… dan pilihlah warna biru di ruas Color.

Klik tombol OK dan — jreng, semua angka negatif akan berwarna merah dan angka yang lebih besar dari 100 akan berwarna biru.

Cobalah menekan tombol F9 di papan ketik untuk menjalankan kembali fungsiRANDBETWEEN sehingga menghasilkan angka yang berbeda.

DENGAN MACRO

Bagi Anda yang ingin tahu bagaimana melakukannya dengan macro, buatlah sebuah worksheet seperti pada contoh di atas. Anda bisa menggunakan fungsi=RANDBETWEEN(-50, 200).

Tekan tombol Alt + F11 di papan ketik. Di jendela Microsoft Visual Basic bagian VBAProject² klik-ganda ThisWorkbook.

Di bagian sebelah kanan yang terbuka, tulislah kode berikut ini.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    nCol = 1
    nRow = 1
    Cells.Font.ColorIndex = 0
    While Not IsEmpty(Cells(nRow, nCol))
        While Not IsEmpty(Cells(nRow, nCol))
            Cells(nRow, nCol).Select
            If Selection.Value < 0 Then
                Selection.Font.ColorIndex = 3
            End If
            If Selection.Value > 100 Then
                Selection.Font.ColorIndex = 5
            End If
 nCol = nCol + 1
        Wend
        nCol = 1
        nRow = nRow + 1
    Wend
End Sub

Kembalilah ke worksheet Anda untuk melihat hasilnya. Jika Anda menggunakan fungsi RANDBETWEEN, cobalah tekan tombol F9 berulang-ulang untuk menghasilkan angka yang berbeda.

Catatan. Conditional Formatting di Excel hanya menyediakan 3 kondisi. Bila Anda memiliki lebih dari tiga kondisi yang ingin dijalankan, Anda harus memakai macro.

1. Fungsi RANDBETWEEN bukan fungsi bawaan dari Excel. Anda harus mengaktifkannya dengan pergi ke Tools > Add-Ins. Centang Analysis ToolPak lalu klik tombol OK. CD instalasi MS Office mungkin diperlukan. [↩]
2. Bila bagian ini tidak ada, pilihlah View > Project Explorer atau tekan tombol Ctrl + R. [↩]

Tags: excel, macro, tips, tutorial

Read more »

Macro Excel Pengganti VLookup

Untuk mencari data di sebuah tabel di dalam worksheet, Excel menyediakan beberapa fungsi. Kita dapat menggunakan fungsi VLookup, HLookup, atau fungsi Index yang sering kali dikombinasikan dengan fungsi Match.

Misalnya, Anda memiliki sebuah tabel yang berisi informasi siswa di sebuah sekolah. Ada 6 kolom di sana. Kolom pertama berisi NISN, kolom kedua berisi nomor induk, selanjutnya adalah nama, kemudian tempat lahir, tanggal lahir, dan alamat.

Lalu di sheet lain pada file yang sama, sel A4 sudah berisi nomor induk salah seorang siswa. Anda ingin mengisi sel B4 dengan nama siswanya, sel C4 berisi tanggal lahir, dan sel D4 berisi NISN. Bagaimana caranya? (Perhatikan bahwa Nomor Induk pada tabel pertama tidak terurut.)

Untuk memudahkan, Anda dapat memberi beberapa label pada tabel tersebut. Pada sheet pertama, sorotlah range A2:F11, lalu klik Insert > Name >Define…. Ketikkan “tabel_siswa” (tanpa tanda petik) pada ruas Names in workbook, lalu klik tombol OK. Dengan demikian, sekarang “tabel_siswa” akan mengacu pada range A2:F11. Ulangi hal yang sama untuk range B2:F11 (beri nama “data_siswa”), dan “nomor_induk” untuk range B2:B11. Catatan:dari tiga label yang kita buat, tidak semuanya kita perlukan secara bersamaan. Tergantung metode mana di bawah ini yang akan Anda pakai.

FUNGSI VLOOKUP, INDEX, DAN MATCH

Kembali ke sheet berikutnya (gambar kedua), kita akan mengisi nilai di sel B4, C4, dan D4 dengan mengacu pada data yang telah tersedia di sel A4. Dengan fungsi standar Excel, setidaknya ada dua cara yang dapat kita gunakan.

Cara pertama adalah dengan menggunakan fungsi VLookup. Tulislah di sel B4 formula ini: =VLOOKUP(A4,data_siswa,2,FALSE). Argumen FALSE kita pakai karena kolom Nomor Induk tidak terurut. Sementara itu, nilai sel C4 dapat diisi dengan menulis formula =VLOOKUP(A4,data_siswa,4,FALSE).

Sekarang, bagaimana mengisi sel D4? Fungsi VLookup tidak dapat melakukan ini karena data acuan kita adalah kolom Nomor Induk, dan VLookup hanya bisa mengindeks data pada kolom di sebelah kanan kolom acuan. Padahal data NISN yang kita butuhkan berada di sebelah kiri kolom acuan kita.

Cara kedua adalah dengan fungsi Index yang akan kita kombinasikan denganMatch. Di sel B4, Anda bisa menulis ini:=INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),3). Nilai sel C4 adalah=INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),5). Nilai sel D4? Tentu saja ini: =INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),1).

Dengan fungsi Index, Anda bisa mengisi semua kolom.

MENULIS MAKRO

Anda bisa juga membuat fungsi sendiri dengan membuat makro. Misalnya, dengan hanya mengetikkan =Siswa(n,m), Anda dapat memperoleh data yang diinginkan.

Tulislah kode makro berikut ini di bagian Module. (Anda yang belum tahu bagaimana caranya, bisa membaca petunjuk singkatnya pada tulisan lain di blog ini.

Function Siswa(ByVal NIS, Order)
 
    Check = WorksheetFunction.CountIf(Range("nomor_induk"), NIS)
 
    If Check = 0 Then
        Siswa = "Tidak ada"
    ElseIf Check = 1 Then
        With Range("nomor_induk")
            Siswa = .Find(What:=NIS, LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, Order)
        End With
    Else: Siswa = "Data lebih dari satu"
    End If
 
End Function

Dengan fungsi yang kita buat ini, kita dapat mengisi sel B4 dengan=Siswa(A4,1). Sel C4 berisi =Siswa(A4,3), dan sel D4 berisi =Siswa(A4,-1).

Dari beberapa metode di atas, Anda bisa memilih salah satu yang sesuai dengan kebutuhan Anda.

Read more »

Macro Excel: Mengubah Angka Menjadi Teks

Tiga hari yang lalu, seorang teman menghubungi saya lewat telepon, bertanya tentang formula untuk mengubah sebuah angka menjadi teks. Dia ingin mengubah, misalnya, angka 145 menjadi seratus empat puluh lima. Seperti tulisan “terbilang” yang ada di kuitansi-kuitansi.

Saya ingat, dulu saya pernah membuat sebuah macro di Excel yang berfungsi seperti ini. Sebenarnya, macro ini aslinya untuk teks berbahasa Inggris (saya lupa dapat dari mana¹), kemudian saya mengubahnya menjadi berbahasa Indonesia. Karena adanya perbedaan struktur bahasa, tentu saja perubahan ini menjadi tidak sekadar hanya menerjemahkan.

Silakan unduh kodenya—hasil ubekan dari tumpukan file. Anda tinggal memasukkannya ke bagian module di MS Visual Basic pada berkas Excel Anda. Di worksheet, Anda bisa memanggil fungsi ini dengan menulis =terbilang(x). Angka x dapat Anda isikan langsung, atau dapat pula merupakan referensi ke sel lain.

Terbilang (1.23 KB)

Tambahan (23-04-2007). Bagi Anda yang kesulitan menerapkan macro ini, berikut adalah petunjuk singkatnya.

1. Setelah berkas (file) di atas Anda unduh, bukalah dengan aplikasi pembaca teks. WordPad, misalnya.
2. Bukalah berkas Excel yang baru, atau berkas milik Anda yang ingin ditambahi kode ini.
3. Pergilah ke Tools > Macro > Visual Basic Editor. Langkah ini dapat pula Anda tempuh dengan menekan Alt + F11. Langkah ini akan membuka satu jendela khusus untuk Visual Basic Editor.
4. Di bagian VBAProject di panel sebelah kiri, klik-kanan ThisWorkbook > Insert > Module.
5. Di bagian kosong yang terbuka di bagian kanan, tuliskan kode macro yang tadi dibuka di WordPad. Silakan salin-dan-tempel.
6. Kembalilah ke jendela Excel Anda, dan cobalah tuliskan formula terbilang di salah satu sel. Misalnya, tulislah =terbilang(123) maka hasilnya akan berupa tulisan seratus dua puluh tiga rupiah. Anda juga dapat menulis formula dengan =terbilang(B3), misalnya, dan sel B3 berisi angka yang ingin dibuat teks.

Perhatian. Bila macro di aplikasi Excel Anda tidak dapat dijalankan, periksalah bagian Macro security. Pergilah ke Tools > Options…, klik tabSecurity lalu klik tombol Macro Security di bagian bawah. Ubah pilihanSecurity level ke Medium (disarankan) atau Low.

Pembaruan. Pengecekan bilangan negatif ditambahkan. Bilangan negatif akan dibaca minus sekian sekian.

19-12-2007. Berkas add-ins untuk fungsi terbilang tersedia untuk diunduh. Terima kasih agus untuk sarannya.

Add-Ins untuk Fungsi Terbilang

Untuk mengaktifkan add-ins ini, simpan berkas .xla yang sudah Anda unduh di komputer Anda. Di Excel, pergilah ke menu Tools > Add-Ins…. Di kotak Add-Ins yang terbuka, klik tombol Browse… dan arahkan ke berkas .xla yang Anda miliki. Add-ins untuk fungsi terbilang kini tampak dalam daftar. Centang kotaknya lalu klik tombol OK. Anda sekarang dapat menggunakan fungsi ini di semua dokumen Excel yang Anda buka.

Read more »