Panduan Lengkap Otomatisasi Pekerjaan Menggunakan Macro Excel

Sanksi Pelanggaran Pasal 113 Undang-Undang Nomor 28 Tahun 2014 tentang Hak Cipta 1. Setiap Orang yang dengan tanpa hak melakukan pelanggaran hak ekonomi

sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf i untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 1 (satu) tahun dan/atau pidana denda paling banyak Rp100.000.000 (seratus juta rupiah).

2. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf c, huruf d, huruf f, dan/atau huruf h untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 3 (tiga) tahun dan/atau pidana denda paling banyak Rp500.000.000,00 (lima ratus juta rupiah).

3. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf a, huruf b, huruf e, dan/atau huruf g untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 4 (empat) tahun dan/atau pidana denda paling banyak Rp1.000.000.000,00 (satu miliar rupiah).

4. Setiap Orang yang memenuhi unsur sebagaimana dimaksud pada ayat (3) yang dilakukan dalam bentuk pembajakan, dipidana dengan pidana penjara paling lama 10 (sepuluh) tahun dan/atau pidana denda paling banyak Rp4.000.000.000,00 (empat miliar rupiah).

Panduan Lengkap Otomatisasi Pekerjaan Menggunakan Macro Excel

Yudhy Wicaksono & Solusi Kantor

PENERBIT PT ELEX MEDIA KOMPUTINDO

Panduan Lengkap Otomatisasi Pekerjaan Menggunakan Macro Excel

Yudhy Wicaksono & Solusi Kantor © 2017, PT Elex Media Komputindo, Jakarta Hak cipta dilindungi undang-undang Diterbitkan pertama kali oleh Penerbit PT Elex Media Komputindo Kelompok Gramedia, Anggota IKAPI, Jakarta 2017

717051285 ISBN: 978-602-04-3635-7

[eEp]

Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian atau seluruh isi buku ini tanpa izin tertulis dari penerbit. Dicetak oleh Percetakan PT Gramedia, Jakarta Isi di luar tanggung jawab percetakan

v

KATA PENGANTAR

Buku Panduan Lengkap Otomatisasi Pekerjaan Menggunakan Macro Excel ini menyajikan materi teori pemrograman Macro yang didukung contoh penerapan untuk kasus yang sering dihadapi sehari-hari. Penyajian materi dalam bentuk teori dan praktek diharapkan dapat mempercepat dan mempermudah pemahaman Anda dalam mempelajari Macro secara utuh.

Penulis menyadari bahwa tidak ada sebuah karya yang sempurna. Oleh karena itu, penulis mengharapkan adanya masukan, saran ataupun kritikan dari pembaca yang bersifat membangun. Apabila Anda berniat memberi masukan, saran ataupun kritikan silahkan menghubungi saya melalui Facebook atau e-mail: yudhy_wicaksono2000@yahoo. com

Kepada PT Elex Media Komputindo saya mengucapkan terima kasih karena bersedia menerbitkan buku karya saya yang ke-67. Terima kasih juga saya ucapkan untuk Bapak AS Pratisto (Eep) dan Tim Elex Media Komputindo yang telah banyak membantu hingga buku ini bisa terbit.

Salatiga, Juni 2017

Yudhy Wicaksono yudhy_wicaksono2000@yahoo. com

www. solusi-kantor. com

vii

DAFTAR ISI

KATA PENGANTAR................................................................................ V DAFTAR ISI ........................................................................................VII BAB 1 MENGOPTIMALKAN FITUR MACRO EXCEL.................................... 1

1.1 Penggunaan Fitur Macro ...........................................................2 1.2 Format File ...............................................................................3 1.3 Menonaktifkan Penyimpanan Workbook Otomatis ....................5 1.4 Keamanan Macro ......................................................................5 1.5 Visual Basic Editor.....................................................................8

1.5.1 Project Explorer....................................................................9 1.5.2 Window Properties .............................................................10 1.5.3 Window Code.....................................................................11 1.5.4 Window Object...................................................................12 1.5.5 Object Browser ..................................................................12

1.6 Kode Macro.............................................................................14 1.6.1 Komentar...........................................................................15 1.6.2 Memenggal Kode Macro ....................................................16 1.6.3 Fitur Auto List Members .....................................................16 1.6.4 Kesalahan Penulisan Kode Macro.......................................17 1.6.5 Fitur Auto Quick Info..........................................................18 1.6.6 Pengaturan Format Font Kode Macro ................................18

1.7 Objek ......................................................................................19 1.8 Properti ...................................................................................20

1.8.1 Mengubah Properti Melalui Window Properties ...................20 1.8.2 Mengubah Properti Saat Runtime.......................................23 1.8.3 Properti yang Sering Digunakan.........................................23

1.9 Method....................................................................................28 1.10 Operator VBA..........................................................................28

viii

1.10.1 Operator Aritmatika............................................................28 1.10.2 Operator Perbandingan......................................................29 1.10.3 Operator Teks ....................................................................29 1.10.4 Operator Referensi .............................................................30 1.10.5 Operator Logika.................................................................30

1.11 Variabel ...................................................................................30 1.12 Konstanta................................................................................31 1.13 Array .......................................................................................32 1.14 Tipe Data ................................................................................33 1.15 Pengaturan Setting Regional Komputer...................................34

BAB 2 EVENT OBJEK....................................................................... 39 2.1 Workbook Event......................................................................39 2.2 Worksheet Event .....................................................................42 2.3 Chart Event .............................................................................44 2.4 UserForm Event ......................................................................45 2.5 Kontrol dan ActiveX Controls Event .........................................47

BAB 3 SUB PROCEDURE .................................................................. 51 3.1 Merekam Macro ......................................................................51 3.2 Menjalankan Macro .................................................................54 3.3 Edit Kode Macro .....................................................................55 3.4 Edit Shortcut Key dan Deskripsi Macro....................................56 3.5 Menghapus Macro...................................................................57 3.6 Membuat Sub Procedure Sendiri .............................................58 3.7 Menjalankan Sub Procedure ....................................................60

3.7.1 Membuat Shortcut Key.......................................................61 3.7.2 Menggunakan Objek Excel .................................................64 3.7.3 Membuat Menu Klik Kanan ................................................67 3.7.4 Menambahkan Tombol Quick Access Toolbar ...................71 3.7.5 Menambahkan Tombol Ribbon Excel 2010 ........................74 3.7.6 Perintah Call.......................................................................77

BAB 4 FUNCTION PROCEDURE .......................................................... 81 4.1 Membuat Fungsi Tanpa Argumen ...........................................83 4.2 Membuat Fungsi dengan Satu Argumen .................................84 4.3 Membuat Fungsi dengan Dua Argumen..................................87 4.4 Membuat Fungsi dengan Argumen Opsional ..........................89 4.5 Fungsi dengan Argumen Array I ..............................................91 4.6 Fungsi dengan Argumen Array II .............................................94 4.7 Menggunakan Fungsi untuk Fungsi yang Lain ........................97 4.8 Menggunakan Fungsi dalam Sub Procedure .........................100 4.9 Menggunakan Worksheet Function .......................................102

ix

4.10 Menggunakan VBA Functions ...............................................105 4.11 Menggunakan MacroOptions.................................................107

BAB 5 MENAMBAH INTERAKTIVITAS MACRO......................................111 5.1 Kotak Pesan (Message Box)...................................................111

5.1.1 Membuat Kotak Pesan .....................................................113 5.1.2 Nilai Kotak Pesan .............................................................115

5.2 Kotak Input (Input Box) .........................................................117 5.2.1 Membuat Kotak Input.......................................................118 5.2.2 Kotak Input dengan Tipe Range.......................................120

5.3 Built In Kotak Dialog .............................................................122 5.4 Kotak Dialog Open................................................................123 5.5 Kotak Dialog Save As ............................................................126 5.6 Kotak Dialog Browse for Folder.............................................128 5.7 UserForm..............................................................................130

5.7.1 ToolBox ...........................................................................131 5.7.2 Menambahkan Kontrol.....................................................132 5.7.3 Mengubah Ukuran dan Posisi Kontrol...............................133 5.7.4 Menyalin Kontrol ..............................................................135 5.7.5 Menghapus Kontrol..........................................................136 5.7.6 Membuat Kotak Dialog.....................................................136 5.7.7 Ekspor Kotak Dialog ........................................................142 5.7.8 Impor Kotak Dialog..........................................................144

5.8 ActiveX Controls ....................................................................145 5.8.1 Pengaturan Keamanan ActiveX Controls ..........................146 5.8.2 Menambahkan Kontrol.....................................................148 5.8.3 Kotak Dialog Format Control ...........................................149 5.8.4 Memasukkan Nilai SpinButton ke TextBox .......................152

5.9 Form Control dan Shape.......................................................153 BAB 6 KONTROL PROGRAM............................................................157

6.1 Pencabangan On Error..........................................................157 6.1.1 On Error GoTo <label>...................................................157 6.1.2 On Error Resume Next .....................................................159 6.1.3 On Error GoTo 0..............................................................160

6.2 Struktur Kontrol Keputusan...................................................161 6.2.1 If…Then...........................................................................161 6.2.2 If…Then…Else.................................................................162 6.2.3 Select Case ......................................................................165

6.3 Struktur Pengulangan............................................................167 6.3.1 Do…Loop........................................................................167 6.3.2 For…Next ........................................................................170

x

6.3.3 While…Wend ...................................................................173 6.3.4 For Each…Next ...............................................................174

6.4 End .......................................................................................176 6.4.1 End Sub...........................................................................176 6.4.2 End Function ...................................................................176 6.4.3 End If ...............................................................................177 6.4.4 End With ..........................................................................177

BAB 7 WORKBOOK DAN WORKSHEET..............................................179 7.1 Membuat Workbook Baru .....................................................179 7.2 Menyimpan Workbook ..........................................................180 7.3 Menutup Workbook...............................................................183 7.4 Membuka Workbook .............................................................185 7.5 Proteksi Workbook ................................................................187 7.6 Mengaktifkan Workbook........................................................189 7.7 Seleksi Worksheet .................................................................190 7.8 Menambahkan Worksheet .....................................................190 7.9 Group Worksheet ..................................................................192 7.10 Mengubah Nama Worksheet .................................................193 7.11 Menyembunyikan Worksheet.................................................193 7.12 Menghapus Worksheet ..........................................................195 7.13 Memindah Worksheet............................................................196 7.14 Menyalin Worksheet ..............................................................197 7.15 Proteksi Worksheet................................................................198 7.16 Mengatur Tampilan Ukuran Worksheet..................................199

BAB 8 SEL DAN RANGE.................................................................. 203 8.1 Membuat Nama Sel dan Range .............................................203 8.2 Menyisipkan Sel atau Range ..................................................205 8.3 Menghapus Sel atau Range ...................................................206 8.4 Menyisipkan Baris atau Kolom...............................................207 8.5 Menghapus Baris atau Kolom................................................208 8.6 Menyembunyikan Baris atau Kolom ......................................210 8.7 Mengubah Lebar Kolom........................................................212 8.8 Mengubah Tinggi Baris .........................................................213 8.9 Seleksi Sel dan Range ...........................................................214 8.10 Seleksi Nama Range .............................................................217 8.11 Seleksi Sel dengan Properti Offset.........................................218 8.12 Seleksi Sel dan Range dengan Properti End ..........................219 8.13 Seleksi Tipe Sel .....................................................................221 8.14 Seleksi Baris dan Kolom........................................................224 8.15 Seleksi Seluruh Sel................................................................226

xi

BAB 9 INPUT DAN EDIT DATA..........................................................229 9.1 Input Data .............................................................................229 9.2 Edit dan Hapus Data .............................................................230 9.3 Menyalin Data .......................................................................231 9.4 Memindahkan Data ...............................................................235 9.5 Input Formula dan Fungsi.....................................................236 9.6 Membuat Seri Data Dengan AutoFill .....................................237 9.7 Membuat Daftar Seri Data .....................................................239 9.8 Pencarian Data......................................................................241 9.9 Edit Data Menggunakan Fitur Replace...................................243

BAB 10 FORMAT SEL....................................................................... 245 10.1 Number Format ....................................................................245 10.2 Format Alignment .................................................................247 10.3 Format Font..........................................................................248 10.4 Mengubah Warna Font dan Warna Sel ..................................250 10.5 Border...................................................................................252 10.6 Fitur Wrap Text .....................................................................254 10.7 Menyalin Format Sel..............................................................255 10.8 Proteksi Sel ...........................................................................256 10.9 Menggabungkan Sel..............................................................259

BAB 11 MENGELOLA DATABASE .......................................................261 11.1 Mengurutkan Data.................................................................261 11.2 Mengaktifkan atau Menonaktifkan Drop Down Filter..............265 11.3 Penyaringan Data Menggunakan Macro ................................267

11.3.1 Filter Data Teks................................................................269 11.3.2 Filter Data Tanggal ..........................................................276 11.3.3 Filter Data Angka .............................................................281

11.4 Advanced Filter .....................................................................288 11.4.1 Advanced Filter 1 .............................................................292 11.4.2 Advanced Filter 2 .............................................................293 11.4.3 Advanced Filter 3 .............................................................294 11.4.4 Advanced Filter 4 .............................................................295

11.5 Mendeteksi Penyaringan Data................................................297 11.6 Menghapus Penyaringan Data ...............................................298

BAB 12 GRAFIK (CHART) ................................................................. 301 12.1 Membuat Grafik.....................................................................301 12.2 Mengaktifkan Grafik...............................................................302 12.3 Mengubah Nama Grafik ........................................................304 12.4 Edit Sumber Data..................................................................305 12.5 Mengubah Tipe Grafik ...........................................................306

xii

12.6 Pengaturan Layout Grafik ......................................................308 12.6.1 Judul Grafik .....................................................................308 12.6.2 Legenda Grafik.................................................................309 12.6.3 Data Table .......................................................................311 12.6.4 Axis Title ..........................................................................312

12.7 Pengaturan Gridlines .............................................................314 12.8 Memindahkan Grafik .............................................................315

BAB 13 PIVOTTABLE DAN PIVOTCHART .............................................319 13.1 Membuat PivotTable..............................................................319 13.2 Menghapus dan Mengganti Field dalam PivotTable ...............322 13.3 Mengubah Nama Field dalam PivotTable ..............................323 13.4 Mengubah Perhitungan Ringkasan ........................................324 13.5 Mengubah Penempatan PivotTable .......................................325 13.6 Membuat PivotChart..............................................................326

BAB 14 MENCETAK WORKSHEET DAN GRAFIK....................................331 14.1 Tampilan Page Layout dan Page Break Preview.....................331 14.2 Menentukan Area Cetak Worksheet .......................................333 14.3 Menentukan Baris dan Kolom Sebagai Judul Cetak...............334 14.4 Mengatur Urutan Pencetakan ................................................336 14.5 Menentukan Orientasi Cetak..................................................338 14.6 Melihat Tampilan Worksheet yang Akan Dicetak ...................338 14.7 Mengatur Skala Hasil Pencetakan..........................................340 14.8 Mengatur Warna Cetak..........................................................340 14.9 Hasil Cetak di Bagian Tengah Halaman ................................341 14.10 Mencetak Worksheet .............................................................343 14.11 Mencetak Grafik ....................................................................343

TENTANG PENULIS ........................................................................... 345

1

MENGOPTIMALKAN FITUR

MACRO EXCEL

Visual Basic for Application (VBA) atau biasa dikenal dengan istilah Macro, merupakan pengembangan bahasa pemrograman Visual Basic yang diterapkan dalam aplikasi Excel. Berbeda dengan program pengembang Visual Basic, pemrograman yang dibuat menggunakan Macro hanya dapat dibangun dan digunakan pada aplikasi Excel. Program yang dibuat menggunakan Macro tidak dapat berjalan, sebelum Anda menjalankan Excel terlebih dahulu. Pemrograman Macro Excel mempunyai beberapa keuntungan sebagai berikut:

Menghemat waktu. Penyelesaian pekerjaan menggunakan Macro lebih cepat dibandingkan dengan cara manual, karena prosesnya dikerjakan secara otomatis.

Menghemat tenaga. Selain menghemat waktu, penyelesaian pekerjaan menggunakan Macro juga dapat menghemat tenaga.

Mengurangi tingkat kesalahan. Kemungkinan adanya kesalahan dalam menyelesaikan pekerjaan secara manual dapat saja terjadi, meskipun Anda seorang yang sangat ahli dalam menggunakan Excel. Penyelesaian pekerjaan menggunakan Macro secara konsisten akan menyelesaikan suatu pekerjaan berdasarkan perintah yang tertulis dalam kode Macro sehingga tingkat kesalahan yang mungkin timbul sangat kecil. Kesalahan hanya dapat terjadi jika ada kesalahan perintah pada kode Macro.

2

1.1 Penggunaan Fitur Macro

Sebelum menggunakan fitur Macro, Anda harus menampilkan tab Developer dalam Ribbon terlebih dahulu. Berikut langkah-langkah untuk menampilkan tab Developer:

1. Untuk pengguna Excel 2007, klik Office Button kemudian pilih Excel Options. Muncul kotak dialog Excel Options.

Gambar 1.1 Menampilkan tab Developer Excel 2007.

2. Pilih opsi Popular. Beri tanda contreng pada pilihan Show Developer tab in the Ribbon kemudian klik tombol OK. Tampilan tab Developer dalam Ribbon terlihat seperti pada Gambar 1.2.

Gambar 1.2 Tab Developer dalam Ribbon.

3. Untuk menampilkan tab Developer pada Ribbon Excel 2010, klik kanan area Ribbon kemudian pilih menu Customize the Ribbon... Muncul kotak dialog Excel Options pada pilihan Customize Ribbon.

3

Gambar 1.3 Menu klik kanan area Ribbon.

4. Anda juga dapat menampilkan kotak dialog Excel Options dengan cara klik tab File kemudian pilih Options. Muncul kotak dialog Excel Options. Pilih opsi Customize Ribbon.

Gambar 1.4 Menampilkan tab Developer Excel 2010.

5. Pilih Main Tabs pada kotak pilihan Customize the Ribbon: Beri tanda contreng tab Developer dalam daftar di sebelah kanan. Klik tombol OK.

1.2 Format File

Workbook standar Excel Excel 2007 dan Excel 2010 dengan format XLSX tidak mendukung Macro. Untuk menyimpan workbook Excel 2007 atau Excel 2010 yang mengandung Macro, Anda harus menggunakan format XLSM atau XLS (Excel 97-2003).

4

1. Klik tombol Office Button kemudian pilih menu Save As untuk menyimpan workbook. Bagi pengguna Excel 2010 klik tab File kemudian pilih menu Save As. Muncul kotak dialog Save As. Jika Anda menyimpan workbook yang mengandung Macro dengan format file XLSX, muncul kotak pesan seperti pada Gambar 1.5.

2. Klik tombol Yes jika workbook akan tetap disimpan dalam format file XLSX tanpa mengaktifkan Macro. Jika opsi ini yang Anda pilih, Macro dalam workbook akan dihapus.

Gambar 1.5 Kotak pesan format penyimpanan workbook.

3. Untuk menyimpan workbook dengan Macro klik tombol No. Muncul kotak dialog Save As. Ketikkan nama file pada kotak isian File name: Pada kotak pilihan Save as type: pilih format file Excel Macro-Enabled Workbook(*. xlsm) atau Excel 97-2003 Workbook(*. xls) kemudian klik tombol Save.

Gambar 1.6 Kotak dialog Save As.

5

1.3 Menonaktifkan Penyimpanan Workbook Otomatis

Excel menyediakan fitur yang digunakan untuk menyimpan workbook secara otomatis setiap interval waktu tertentu. Cara ini memang sangat bermanfaat untuk menghindari kejadian yang tidak diinginkan, misalnya listrik yang tiba-tiba padam. Namun demikian, penyimpanan secara otomatis dalam bentuk workbook backup tidak mendukung fitur Macro sehingga terkadang malah menyebabkan error. Oleh karena itu, untuk sementara fitur tersebut kita non-aktifkan.

1. Bagi pengguna Excel 2007 klik Office Button kemudian pilih Excel Options. Untuk pengguna Excel 2010 klik tab File kemudian pilih menu Options. Muncul kotak dialog Excel Options.

2. Pilih opsi Save. Hilangkan tanda contreng pada pilihan Save AutoRecover information every. Jika sudah, klik tombol OK.

Gambar 1.7 Menon-aktifkan penyimpanan workbook otomatis

1.4 Keamanan Macro

Otomatisasi Macro Excel terkadang disalahgunakan oleh pengguna yang tidak bertanggung jawab untuk menyebarkan kode Macro yang berpotensi menimbulkan kerugian, misalnya kode Macro untuk menghapus data. Untuk mengantisipasi hal tersebut, Excel secara