Contoh Macro - Menyembunyikan / Menampilkan Baris dengan kondisi

File berikut (disini) ini adalah jawaban pertanyaan seorang member di grup Facebook Excel VBA Usergroup. Pertanyaan nya :

apabila cell C1=1 maka yg tampil hanya Form1 saja (row 10-15 diHide), dan bila cell C1=2 maka yg tampil hanya Form2 saja (row 3-8 yg diHide)... kira-kira seperti apa codenya???

Skrip nya dibuat di Event Worksheet_Change dan skrip lengkap nya seperti ini :

Penjelasan :

Skrip ini hanya mengenal nilai 1 dan 2, yang dimasukan di cell C1. Dalam skrip terlihat menggunakan perintah Select Case. Terlihat apabila nilai pada cell C1 dimasukan 1 maka baris 4 sampai 8 akan di Hide.

Begitu juga apabila di C1 dimasukan nilai 2 maka baris 11 sampai dengan 15 akan di Hide. Diluar itu baris 4 sampai 15 tidak akan di hide dengan cara meng set nilai property Hidden nya menjadi False.

Semoga dapat dipahami.

Opini - Excel sebagai Aplikasi Database ?

Ide untuk menulis hal ini sebetul sudah lama. Tapi kebetulan kemarin malam, ada seseorang yang bertanya grup Facebook Komunitas Excel VBA, dan inti pertanyaan nya adalah kalau mau buat aplikasi, kenapa nggak langsung aja pake MS Access ?.

Dengan segala keterbatasan, yang saya paham tentang konsep sebuah Database singkatnya adalah sekumpulan Informasi sejenis yang terdiri dari satu atau beberapa Tabel yang saling berhubungan. Dan dalam Tabel tersebut paling tidak mempunyai satu atau beberapa Field (kolom) dan Record (Baris). Dan Excel dalam hal ini sudah mewakili bahwa dengan sebuah Sheet kita dapat menciptakan sebuah Tabel. Ada Kolom dan Baris. Tapi walaupun begitu, Excel bukanlah sebuah aplikasi Database yang terintegrasi. Excel adalah sebuah aplikasi Spreadsheet. Itulah sebabnya Microsoft menciptakan MS Access yang dibuat memang untuk Aplikasi Database.

Maka berikut jawaban saya :

1. Apabila kasus nya tidak butuh Database yang saling berkaitan / Relational Database
2. Apabila kasus nya tidak butuh banyak Tabel
3. Volume Datanya <= 5000 an baris / Rows per bulan

Maka kalau kondisi tersebut diatas terpenuhi, maka menggunakan Excel VBA untuk pilihan menciptakan sebuah aplikasi sih sah-sah aja :) walaupun mau tetap bersikeras menggunakan Excel VBA untuk menciptakan sebuah aplikasi Database, ya monggo..

Atau mungkin, sebagai alternatif pilihan, gunakan Excel VBA sebagai perantara / interface nya, dan tabel-tabel Database nya gunakan Aplikasi MS Access atau MySQL. Walaupun secara Ilmu saya belum kesampaian :)

Pesan saya, kalau mau buat sebuah aplikasi yang betul-betul sebuah Database, artinya Tabel-tabel nya saling berhubungan dan terintegrasi, maka akan sangat bijak sana untuk memilih aplikasi yang betul-betul aplikasi Database semacam MySQL, MS Access, SQL Server dan yang lain nya.

Akhir kata, ini hanya sebuah Opini. Silahkan utarakan pendapat anda :)

Dasar Macro - Autofilter (3) Menggunakan kriteria 2 tanggal

Sesuai posting saya sebelumnya mengenai Autofilter yang kedua, dan yang pertama, pada bahasan kali ini akan saya bahas cara untuk mem filter data mengunakan Autofilter dengan menggunakan 2 tanggal sebagai kriteria.

Download file bahasan ini disini.

Yang perlu diketahui apabila menggunakan 2 tanggal pada Autofiter sebagai kriteria, nilai yang kita gunakan sebagai kriteria harus selalu di konversi ke format tanggal US atau dd/mm/yyyy. Karena Excel hanya mengenal sistem format US untuk tanggal.

Terlihat pada screenshot Prosedur diatas :

1. Variabel yang akan digunakan sebagai kriteria selalu di deklarasikan sebagai variabel yang bertipe Date / Tanggal
2. Nilai yang diberikan kepada 2 variabel Date1 dan Date2 sebagai kriteria, masih menggunakan format sesuai data yang ada (dd/mm/yyyy)
3. Disinilah terlihat proses merubah / menkonversikan tipe Variabel Date1 dan Date2 menjadi format US dengan metode Format(Date1, "mm/dd/yyyy")

Semoga bermanfaat.

Tips - menghasilkan banyak Report dari Pivot Table sekali klik

Untuk yang sering bekerja dengan Pivot Table, ada sebuah fasilitas yang mungkin saja terlupakan. Atau mungkin banyak yang tidak tau. Fasilitas ini berfungsi untuk menghasilkan banyak report dari sebuah Pivot Table, yang secara otomatis dibuat menjadi masing-masing Sheet. Tanpa macro. Fasilitas ini sudah ada sejak Excel 2007.

File contoh nya silahkan download disini.

Langkah nya seperti berikut :

1. Buat lah sebuah Pivot Table dari sebuah database / tabel data
2. Setelah Pivot Table siap, pastikan Pivot Table tersebut mempunya Report Filter - dalam file contoh, Report Filter nya adalah kolom Agent ID
3. Klik sembarang Cell didalam Pivot Table
4. Pada Excel 2010, klik tab Options



Pilihan Show Report Pages pada tab Options

5. Pada grup PIvotTable, klik Options, dan pilih "Show Report Filter Pages"
6. Excel akan menampilkan pesan Show Report Filter Pages, klik OK
7. Lihat Excel akan bekerja sendiri menghasilkan Report yang sudah dipisahkan per Sheet sesuai dengan Report Filter yang kita buat :)

Dalam file contoh, data Agent ID yang dijadikan Report Filter mungkin tidak banyak. Coba bayangkan apabila data nya ada puluhan ? atau Ratusan ? 

Fasilitas ini jelas sangat menghemat waktu.

Happy Exceling :)

Dasar Macro - AutoFilter (2)

Setelah sebelumnya dibahas cara me-filter 1 kolom dengan 1 kriteria saja (disini), dalam bahasan ini akan dibahas bagaimana caranya me filter 2 kolom dan salah satu kolom nya menggunakan 2 kriteria.

Sedikit catatan saja untuk bahasan yang pertama, mungkin ada yang bertanya mengenai fungsi dan perbedaan AutoFilterMode dan FilterMode ? kalau nggak ada yang bertanya, biar saya jelasin aja :)..

Fungsi AutofilterMode (dipadukan dengan logika True / False) adalah untuk mengetahui apakah Autofilter sedang aktif. Argumen nya True / False. Kalau True, berarti sedang Aktif. Dan False Tidak Aktif. Penulisan lengkap skrip nya :

AutoFilterMode = False

Fasilitas Autofilter dalam kondisi tidak di aktifkan

Sedangkan FilterMode (juga dipadukan dengan logika True / False) adalah untuk mengetahui apakah data sedang terfilter atau tidak. Argumen yang digunakan juga True / False. Kalau True, berarti data sedang dalam kondisi Terfilter, dan False berarti data tidak dalam kondisi terfilter. Sudah kelihatan beda nya kan ? apabila data dalam kondisi terfilter, terlihat nomer baris sebelah kiri menjadi biru.

Tabel data sedang dalam kondisi Terfilter

Untuk pembahasan kali ini, silakan download file contoh nya disini.

Seperti yang sudah dijelaskan diatas, kali ini akan dicontohkan bagaimana caranya memfilter 2 kolom, dan 1 kolom menggunakan lebih dari 1 kriteria.

Untuk lebih jelas nya coba tekan Alt + F11 untuk menampilkan VBE dan buka Module nya.

Berikut adalah skrip untuk prosedur Button2 yang berfungsi untuk memfilter data :

Sub Button2_Click()

    Range("A1:L1").AutoFilter

    

    Range("A1:L1").AutoFilter field:=6, Criteria1:=Array("Alabama", "Arizona", "Arkansas"), _

    Operator:=xlFilterValues

    

    Range("A1:L1").AutoFilter field:=4, Criteria1:="High"

End Sub

Untuk menggunakan banyak kriteria, disini digunakan metode Array, kemudian diikuti dengan kriteria nya. Kriteria yang digunakan adalah Alabama, Arizona dan Arkansas. Jangan lupa setelah itu ikuti dengan argumen Operator:=xlFilterValues.

Sebenarnya, untuk kriteria yang banyak kita bisa menggunakan operator xlOr atau xlAnd. Hanya nggak tau kenapa cara itu nggak pernah bisa. Paling tidak di Excel 2010 yang saya gunakan. Apapun alasan nya, ternyata di luar sana, orang-orang banyak menyarankan dengan metode Array seperti diatas.

Kemudian menyusul dibagian bawah nya terihat bagaimana caranya untuk memfilter field Order Priority yang jatuh pada kolom ke 4, dengan kriteria High.

Semoga bisa dicerna ya penjelasan nya ? :)

Untuk berikutnya saya akan coba menjelaskan bagaimana menggunakan tanggal sebagai kriteria nya.

Dasar Macro - Memfilter data dengan AutoFilter (1)

Memfilter data dengan fasilitas Auto Filter akan sangat banyak manfaat nya apabila kita ber “urusan” dengan data yang lumayan banyak. Apalagi kalau kita punya rencana untuk buat satu aplikasi dengan Macro / VBA.  

sebuah Tabel Data dengan Autofilter yang sedang Aktif

Daripada menggunakan Loop dan men-cek satu per satu isi cell nya, menggunakan Auto Filter akan menjadikan nya lebih cepat dalam proses seleksi dan evaluasi data yang kita inginkan.

Pada tulisan ini, dan beberapa bahasan berikutnya, akan dibahas dasar-dasar penggunaan fasilitas Auto Filter untuk mem-filter sebuah tabel data dengan menggunakan Macro.

Sebelum mulai membahas Auto Filter, download dulu file nya disini. Buka VBE dengan cara mengklik Alt + F11, dan klik Module nya.

Pada dasarnya penggunaan fasiltas Auto Filter dibagi menjadi 4, yaitu :

1.     Mengaktifkan Autofilter
2.     Memfilter data
3.     Menampilkan semua data
4.     Dan menghapus / meng non aktifkan Autofilter

1.      Mengaktifkan Autofilter dan Memfilter data

Mengaktifkan Auto Filter metode nya dengan syntax : range.AutoFilter (Field, Criteria1, Operator,field, Criteria2 )

Cara mengaktifkan Autofilter pada file contoh dapat dilihat pada prosedur “Apply Filter”. 

If ActiveSheet.AutoFilterMode = False Or ActiveSheet.FilterMode = False Then

        Range("A1:G1").AutoFilter

       

        Range("A1:G1").AutoFilter Field:=3, Criteria1:="Algeria"

             

End If

Penjelasan :

Terlihat pada range A1 sampai G1 sebagai Header akan di filter. Setelah itu kolom / field ketiga, yaitu custCountry sebagai field yang akan di filter, dan kriteria yang digunakan adalah nama “Algeria”.

2.      Menampilkan semua data

Untuk menampilkan semua data, atau kalau dengan cara manual ketika kita memilih “Show All Data” untuk menghapus kriteria filter, skrip nya ada pada prosedur ShowAllData. Skrip nya seperti berikut :

If ActiveSheet.AutoFilterMode = False Then

        MsgBox "Filter tidak Aktif !"

        Exit Sub

       

        Else

            If ActiveSheet.FilterMode = False Then

                MsgBox "Data tidak di filter"

                Exit Sub

            End If

    End If

With ActiveSheet

     .ShowAllData

End With

Penjelasan :

Terlihat ada 2 kondisi diawal prosedur. Apabila Autofilter nya tidak aktif maka akan menampilkan pesan “Filter tidak aktif !”. Dan apabila data tidak terfilter atau sedang dalam kondisi menampilkan semua data maka akan ditampilkan pesan “Data tidak difilter !”.

Sedangkan metode menampilkan semua data ada pada baris .ShowAllData. Yang berarti dengan Sheet yang aktif maka data nya akan ditampilkan semua.

3.   Menghapus / Men non aktifkan filter

Metode menghapus / men non aktifkan Autofilter yang sedang aktif hanya dengan cek terlebih dahulu apakah AutoFilter sedang aktif ? kalau ya (pada Prosedur ClearFilter, baris If ActiveSheet.AutoFilterMode)  maka tinggal menambahkan argumen False karena argumen ini bersifat logika True / False.

Semoga bisa dicerna ya ? :) mohon maaf kalau bahasa nya rada blepotan. Hehehe

Tips - Alternatif menambahkan "catatan" pada Formula / rumus

Selamat sore.

Sudah tau fungsi formula N() ?. Formula N berfungsi merubah nilai argumen nya menjadi data numerik. Dan apabila argumen tersebut adalah sebuah string (Diapit oleh dua tanda "), maka akan menghasilkan angka Nol.

Dengan ini maka formula N dapat kita gunakan sebagai alternatif untuk memberikan  sebuah catatan / keterangan pada formula yang kita buat sebagai penjelasan, selain sebuah cell comment.

Pada contoh berikut, formula N ditambahkan setelah penulisan formula Sumifs :

Hasil nya tetap 693, karena penambahan formula N diujung Sumifs itu tidak menghasilkan apa-apa alias Nol :)

Selamat mencoba.