Mengenal Macro (VBA) di Microsoft Excel

1.1.   Macro dan Formula a. Macro
Sahabat tekno-g.blogspot.co.id ingin tahu apa itu macro? Tentu ini pertanyaan yang akan muncul ketika anda membaca buku judul ini, bahkan mungkin anda telah bisa membandingkan dengan buku yang lain bahwa macro tersebut bisa di exploitasi menjadi virus komputer, lho kok bisa?!. Coba lihat defenisi di paragraf berikut.
Sebuah program yang ditulis atau direkam yang menyimpan serangkaian perintah-perintah excel kemudian nanti dapat digunakan dengan hanya menggunakan satu perintah saja. Macro bisa secara otomatis melaksanakan perintah yang kompleks dan dapat mengurangi langkah-langkah yang digunakan dalam melaksanakan perintah tersebut. Macro di rekam kedalam VBA (Visual Basic for Application) atau anda bisa sendiri membuatnya pada VBA Editor.

Misalnya untuk mem format suatu sel menjadi huruf tebal dan miring sekaligus di garis bawahi. Paling tidak anda akan melakukan tiga langkah penekanan keyboard namun dengan macro anda bisa membuat semua itu hanya dengan menekan satu tombol kombinasi shortcut atau mengklik pada icon yang telah di hubungkan dengan macro.

Sekarang jelas bahwa Macro selain bisa digunakan untuk mempermudah pekerjaan namun juga bisa di exploitasi untuk dijadikan virus komputer. Karena macro menggunakan bahasa basic maka secara tidak langsung anda juga dapat mengasah kemampuan pemograman anda yang biasa bergelut dengan bahasa basic.

b. Formula

Formula di dalam Excel adalah serangkaian perintah yang terdiri dari angka, operator matematika, sekumpulan data dan fungsi atau kombinasi dari kesemuanya itu yang akan menghasilkan nilai tertentu sesuai dengan hasil yang dinginkan.

Formula di dalam Excel di awali dengan tanda = (sama dengan)  dan diikuti dan fungsi tertentu yang telah disediakan oleh excel atau kombinasi formula menurut keinginan pengguna.

1.2.   Menjadi Kreatif dengan Excel

Begitu banyak fungsi yang disediakan oleh Excel dan tidak semua yang bisa dikenal oleh pengguna excel, untuk itu diperlukan kreatifitas yang tinggi dalam mengolah suatu formula jika kita menemukan kasus yang harus dipecahkan dengan formula yang rumit maka kita dapat menggunakan kombinasi fungsi-fungsi sederhana sehingga menghasilkan suatu formula yang bagus. Selain formula kita juga harus kreatif dalam menggunakan fitur-fitur lain excel, misalnya dengan fasilitas scalling, kita bisa memprint out sebuah denah yang dibuat dengan fasilitas drawing  pada berbagai media kertas yang kita inginkan.

Penggabungan macro dan formula dan menginteraksikan dengan cell-cell yang ada dalam worksheet juga merupakan cara yang sangat bagus untuk menghasilkan suatu aplikasi yang handal.

1.3.   Aktifasi dan spesifikasi sistem untuk penggunaan VBA

a. Mengaktifkan VBA

• Klik Tools, Macro, Visual Basic Editor, atau
• Tekan tombol (Alt+F11)

b. Spesifikasi Sistem

Untuk mengoperasikan VBA tidak diperlukan spesifikasi komputer yang terlalu canggih, namun jika anda memerlukan hasil yang maksimal tentu saja semakin tinggi spesifikasi komputer yang anda miliki akan memberi hasil yang semakin baik, komputer ber processor sekelas Pentium I pun telah mencukupi, syarat mutlak yang dibutuhkan untuk mendapatkan hasil yang terbaik dari tip dan trik yang akan kita laksanakan dalam buku ini adalah :

• Komputer ber processor sekelas Pentium I atau lebih tinggi
• Microsoft Office, Word dan Excel versi 97 atau lebih tinggi (2000, 2003-XP)
• Memori komputer yang memadai

Sebagai tambahan wawasan anda dapat mengakses file help VBA, secara default help VBA tidak diaktifkan langsung ketika menginstall aplikasi Microsoft Office, anda dapat menambahkannya ketika help VBA meminta anda untuk menginstallkan kembali file tersebut.

Konsekuensi yang harus anda terima, jika komputer yang anda gunakan memiliki spesifikasi yang pas-pasan adalah semakin banyak macro yang anda buat maka akan semakin lambat aplikasi yang mengandung macro tersebut prosesnya. Juga ada beberapa macro yang kita buat hanya akan mulus jalannya jika menggunakan Office 2000 atau lebih.

1.4.   Berkenalan dengan VBA (Visual Basic For Application)

Untuk lebih mudah menjalankan tip dan trik berikutnya, ada baiknya kita mengetahui sekilas tentang VBA Window.

VBA Window adalah tampilan bidang kerja yang digunakan untuk membuat program-program macro secara manual, disini kita akan mengakses berbagai bagian dari VBA mulai dari Menu, Toolbar, Project Window, Code Window, Properties Window, UserForm, Module, dan lain-lain.

Gambar 1.1. Tampilan VBA (Visual Basic for Application) Window

Beberapa bagian yang paling penting untuk diketahui :

1. Project Window

Pada jendela ini seluruh object yang berhubungan dengan macro kita diakses, diantaranya Sheet, Document (di dalam Word), UserForm, Module. Untuk mengakses suatu object dapat dilakukan dengan double klik pada object tersebut.

Gambar 1.2. Project Window

2. Menu dan Toolbar

Bagian ini digunakan untuk mengakses perintah-perintah yang berhubungan dengan object, khusus Toolbar ada tiga icon yang penting untuk menjalankan macro yang anda buat, yaitu ; icon run, break dan reset.

Gambar 1.3. Menu dan Toolbar

3. Code Window

Jendela yang digunakan untuk menulis kode program macro, jendela ini akan muncul jika anda ingin mengaitkan kode pada object tertentu, seperti didalam modul, Userform, kontrol dan object lainnya. Dropdown list dibagian kanan merupakan object, disebelah kiri procedure.


Gambar 1.4. Code Window

4. Properties Windows

Merupakan tempat mengakses properti yang dimiliki oleh suatu object, bagian ini akan berubah dengan sendirinya jika kita mengklik atau mengakses objek tertentu.

Gambar 1.5. Properties Window


5. UserForm

Merupakan interface yang dapat digunakan untuk mengentri data, informasi dan lain-lain. Dibagian ini terdapat berbagai macam kontrol yang ditambahkan dari Toolbox

Gambar 1.6 UserForm

6. Toolbox

Toolbox digunakan untuk mengakses kontrol yang akan dipakai dalam Userform, misalnya label, texbox, image dan lain-lain.

Gambar 1.7. Toolbox

1.5. Melindungi dari Macro Jahat (Virus Macro)

Seperti yang telah disinggung sebelumnya macro dapat dimanfaatkan untuk membuat virus komputer, untuk melindungi diri dari macro jenis ini microsoft office memberi kebebasan kepada pengguna office untuk menentukan tingkat keamanan dari aplikasi office yang digunakan, jika suatu file office (excel, word dan lain-lain) mengandung macro didalamnya maka ketika file tersebut diaktifkan maka aplikasi akan memunculkan sebuah kotak dialog yang menanyakan apakah  macro dijalankan (dengan segala resiko yang akan ditanggung), sebaliknya jika anda khawatir maka anda bisa menghalangi macro tersebut untuk tidak jalan pada saat file diaktifkan. Bagaimana caranya ikuti langkah berikut :

Untuk Excel 97, aktifkan Excel Klik Tools, Option, pilih tab General, perhatikan Kotak  (CheckBox) Macro virus protection, jika tanda cek diaktifkan maka proteksi akan diaktifkan, demikian sebaliknya.

·      
Untuk Excel 2000, aktifkan Excel Klik, Macros, Security, Pilih Option High Level untuk memproteksi, pilih low level untuk menghilangkan proteksi


Gambar 1.8. Perlindungan Virus Macro Excel 2000

Sebagai catatan, sekali virus macro anda beri keleluasaan untuk aktif tanpa melaksanakan langkah-langkah diatas, maka anda telah memberi akses untuk virus itu mendekam bahkan menyebar ke komputer lain.

Untuk mencoba file-file latihan yang disertakan bersama buku ini agar tidak mengganggu maka sebaiknya anda menonaktifkan perlindungan macro.



2.1.   Merekam Macro

Kasus :

Memformat Cell, Teks ditebalkan (Bold), dimiringkan (Italic) sekaligus digarisbawahi (Underline).

Pemecahan :

-          Aktifkan Excel, lalu simpan file dengan nama Latihan01.xls

-          Klik Tools, Macro, Record New Macro, akan muncul kotak dialog Record Macro.

 

Gambar 2.1 Dialog Record Macro

-          Masukan nama macro di Macro Name (misalnya FormatSel), Shortcut Key (misalnya Ctrl+k), OK

Toolbar Record macro akan muncul


Gambar 2.2 Toolbar Record Macro

-          Sorot sembarang Sel, Klik Icon Bold, Klik Icon Italic, Klik Icon Underline

-          Klik Icon Stop pada Toolbar Record Macro

Penerapan :

-          Sekarang setiap kali anda ingin memformat sel dengan Bold, Italic dan Underline. Anda tinggal menyorot sel dan tekan Shortcut Ctrl+k.


2.2. Mengaitkan Macro dengan Menu, Toolbar dan Object

Kasus :

Bagaimana mengaitkan macro dengan dengan Menu, Toolbar dan Object.

Pemecahan :

Asumsi anda sudah mempunyai macro FormatSel

Mengaitkan Macro dengan Menu

Sebelum mengaitkan macro dengan Menu anda harus terlebih dahulu membuat menu sendiri yang anda inginkan ikuti langkah-langkah berikut:

-  Membuat menu baru

Ø  Tools, Customize, Klik Tab Command, Pilih New Menu pada Categories, Klik tahan dan seret New Menu dari dalam Command ke barisan Menu bar.


Gambar 2.3  Menu buatan sendiri

Ø  Setelah New Menu terbentuk, Klik kanan New Menu tersebut untuk membuat Caption Pribadi, dalam kotak Name, ketik Latihan Macro untuk Menu Utama.

Gambar 2.4 Mengganti Caption Menu

- Mengaitkan Macro dengan Menu

Setelah menu utama dibuat kemudian macro yang akan kita kaitkan pada menu tersebut akan menjadi Sub menu dibawah Menu Latihan Macro, ikuti langkah berikut.

Ø  Dalam keadaan Kotak Dialog Customize masih terbuka, klik Macros di dalam Categories, di bagian Command di sebelah kanan klik tahan Custom Menu dan seret ke menu Latihan Macro kemudian letakan dibawah menu tersebut, ubah namanya dengan mengganti Caption &Format Sel.

Gambar 2.5 Menambahkan Sub Menu Format Sel

Ø  Klik kanan lalu pilih Assign Macro, Pilih Macro FormatSel, OK

Gambar 2.6 Mengaitkan Macro dengan Menu

Ø  Tutup Customize, Klik Close.

Mengaitkan Macro dengan Toolbar (Icon)

- Membuat Icon baru

Ø  Tools, Customize, Klik Tab Command, Macros  pada Categories, Klik tahan dan seret Custom Button dari dalam Command ke barisan Toolbar.

Ø  Setelah Custom Button terbentuk, Klik kanan Custom Button tersebut, Pastikan Default Style dalam keadaan terpilih agar yang ditampilkan hanya icon saja.

Ø  Sebagai tambahan untuk merubah bentuk icon anda dapat mengklik Edit Button Image, disini nanti akan dapat di atur bentuk icon yang anda suka.


Gambar 2.7 Membuat Icon sendiri pada Toolbar

-          Mengaitkan Macro dengan Toolbar (icon)

Ø  Dalam keadaan Dialog Customize masih aktif, klik kanan Icon baru tersebut, Klik Assign Macro, Pilih Macro FormatSel, OK

Ø  Tutup Customize, Klik Close.

Mengaitkan Macro Dengan Object

Object yang dimaksud disini adalah shape yang bisa dibuat melalui Toolbar Drawing.

- Masukan sebuah object, misalnya oval

- Klik kanan Oval tersebut, Klik Assign Macro, Pilih Macro FormatSel, OK

- Sekarang setiap kali anda mengklik object tersebut maka makro yang telah anda rekam akan di jalankan

Gambar 2.8 Mengaitkan Macro dengan Object

2.3.   Membuat Msgbox

Msgbox adalah sebentuk pesan yang muncul dalam kondisi tertentu misalnya jika ada error atau hanya sekedar informasi.

Kasus :

· Bagaimana memunculkan pesan ketika sebuah file Excel di buka

· Memunculkan Pesan pada tanggal tertentu.

Pemecahan :

· Membuat Pesan ketika Excel di buka

- Aktifkan Excel, aktifkan VBA

- Didalam VBA, Double Klik ThisWorkbook (didalam Project Explorer)

Gambar 2.9 Object This Workbook

- Dalam Code Window pada Dropdown Object pilih Workbook, pada Procedure pilih Open


Gambar 2.10 Object Workbook dan Procedure Open

-  Ketik  baris program berikut ini :

Private Sub Workbook_Open()

      MsgBox "Selamat Menggunakan Excel",_

      vbInformation, "Pesanku"

End Sub

-  Tutup VBA, simpan file Excel dengan nama Latihan02.xls

-  Sekarang Coba Buka file Latihan02.xls, maka akan muncul pesan :

Gambar 2.11 Msgbox saat file dibuka

Memunculkan Pesan pada tanggal tertentu

- Ikuti langkah-langkah untuk memunculkan pesan ketika sebuah file excel di buka.

- Dalam Dropdown Object pilih Workbook, pada Procedure pilih Open

- Ketik  baris program berikut ini :

Private Sub Workbook_Open()

      Dim TANGGAL, BULAN As String

      TANGGAL = Format(Now(), "dd")

      BULAN = Format(Now(), "mmmm")

      If TANGGAL = 1 Then

MsgBox "Sekarang udah tanggal " & TANGGAL & " Bulan " & BULAN & ", waktunya kita gajian", vbInformation, "Info Terkini"

      End If

End Sub

-  Simpan File dengan nama Latihan03.xls, lalu coba buka lagi, perhatikan hasilnya.

Gambar 2.12 Pesan tanggal tertentu

Penerapan :

• Trik di atas dapat digunakan jika anda mempunyai file yang formatnya sama dan anda akan mengcopy file tersebut setiap bulannya atau anda ingin kejutan dengan memberikan pesan selamat ulang tahun kepada seseorang.
• Mengcopy file yang sama setiap bulan dengan nama yang berbeda pada  tanggal tertentu, misalnya setiap tanggal 1 bulan berikutnya secara otomatis akan muncul pesan yang meminta anda untuk mengcopy file tersebut  (save as) dengan nama bulan berikutnya.

Caranya, ikuti langkah ketika Excel di buka, kemudian ketik program berikut :

Private Sub Workbook_Open()

   On Error Resume Next

   Dim TANGGAL, NAMAFILE, BULAN As String

   TANGGAL = Format(Now(), "dd")

   BULAN = Format(Now(), "mmmm")

NAMAFILE = "Laporan Bulan " & Format(Now(), "MMMM YYYY")‘Ketik satu baris

If TANGGAL = 1 Then

JAWABAN = MsgBox ("Sekarang udah tanggal " & TANGGAL & " Bulan " & BULAN & " apakah anda ingin mengcopy file ini ?", vbYesNo, "konfirmasi")‘Ketik satu baris

If JAWABAN = vbYes Then

ChDir "C:\My Documents"     

ActiveWorkbook.SaveAs FileName:=NAMAFILE, FileFormat:=xlNormal ‘Ketik satu baris

         End If

Else

Exit Sub

End If

End Sub


Simpan File dengan nama Latihan04.xls, lalu coba buka lagi, maka setiap tanggal 1 bulan berikutnya akan muncul pesan, untuk mencoba program ini anda bisa mengubah tanggal menjadi tanggal hari ini, kemudian klik run pada toolbar.

Gambar 2.13 Mengcopy File yang Sama Setiap Bulan

Jika Yes di Klik maka file akan di Save As menjadi Laporan Bulan September 2004.xls (jika bulan ini bulan September 2004).

- Membuat Pesan Ulang Tahun

Ketik Listing Program Berikut di dalam Object Workbook dengan Procedure Open

Private Sub Workbook_Open()

     Dim TANGGAL, NAMA, BULAN As String

     TANGGAL = Format(Now(), "dd")

     BULAN = Format(Now(), "mm")

     If TANGGAL = 03 And BULAN = 12 Then

MsgBox "Selamat Ulang Tahun Buat Najwa", vbInformation, "Happy Birthday"

     End If

End Sub

Tutup VBA, kemudian simpan file dengan nama Latihan05.xls. Sekarang coba buka lagi file tersebut jika hari ini Tanggal 03 Desember maka akan muncul pesan sebagai berikut :

Gambar 2.14 Msgbox Ucapan ulang tahun

Untuk mencoba program ini anda bisa mengubah TANGGAL dan BULAN sesuai dengan hari ini, kemudian Klik Icon RUN di dalam VBA, atau Tutup File dan coba Buka Kembali.

2.4. Form VBA

Form VBA merupakan suatu tampilan yang dapat kita gunakan untuk menginput data kedalam lembaran kerja Excel, walaupun sebenarnya Excel sudah memiliki tampilan yang fleksibel terkadang juga perlu menggunakan Form yang dapat kita rancang sendiri.

Kasus :

Bagaimana membuat sebuah Form dan mengaitkannya dengan Macro.

Pemecahan :

Membuat UserForm VBA

Aktifkan VBA, dalam jendela VBA Klik Insert, Userform (Klik Icon Userform), maka dalam jendela project akan ditambah sebuah Userform1, dibagian kanan akan ditampilkan beberapa UserForm1 tersebut dan siap diolah sesuai dengan dengan kebutuhan kita, untuk properti seperti penggantian judul (caption) dan pengaturan lainnya dapat dilakukan melalui jendela properties di bagian kiri bawah jendela VBA. (lihat kembali BAB I bagian 1.5 Berkenalan dengan VBA)

Gambar 2.15 UserForm

Mengaitkan UserForm dengan Macro

Untuk menjalankan form tanpa harus membuka VBA lagi maka kita harus mengaitkan Form dengan Macro, baik itu dengan menggunakan Shortcut (penekanan kombinasi tombol pada keyboard) atau dengan toolbar dan Object.

Langkah-langkah mengaitkan Form dengan Macro.

-          Simpan File dengan nama Latihan06.xls kemudian Klik Userform1, pada bagian properties Name ganti menjadi FrmAwal (tergantung keinginan anda), anda juga dapat menambahkan Control lain ke dalam form misalnya Label, Image dan lain-lain. Demikian juga dengan Properti Caption dapat anda ganti misalnya Form Pertama.

-          Tambahkan sebuah modul kedalam Project Window, Klik Menu Insert, Module, kemudian di dalam project akan muncul sebuah Module, double klik modul tersebut dibagian kanan akan muncul code window editor kemudian ketik :

Sub BukaFrm()

  FrmAwal.Show

End Sub

Gambar 2.16 Menambahkan Module

- Simpan kembali file, tutup tampilan VBA, sekarang anda berada pada jendela Excel, Buatlah sebuah Icon baru di toolbar (lihat kembali trik 2.2), kemudian klik kanan Toolbar, Klik Customize, setelah jendela Customize muncul klik kanan Icon yang baru anda tambahkan tadi.

Ø  Klik Assign Macro, Pilih Macro BukaFrm, OK

Ø  Tutup Customize, Klik Close.

- Sekarang coba klik icon tersebut maka akan muncul sebuah form seperti gambar berikut.

Gambar 2.17 UserForm Form Pertama

- Untuk Mengaitkan dengan dengan shortcut Klik Tools, Macro, Macros, kemudian Pilih FrmAwal lalu Klik Options, dan tekan tombol shortcut yang anda inginkan misalnya Ctrl+f, kemudian klik ok.

- Sekarang setiap kali anda menekan ctrl + f maka akan muncul form diatas.

Gambar 2.18 Mengaitkan UserForm dengan Macro

Penerapan :

Untuk contoh penerapan penggunaan form dapat dilihat pada trik 2.6 dan 2.14.

2.5. Form yang tidak bisa di – close

Pada form terdapat sebuah close button yang berfungsi untuk menutup form tersebut. Adakalanya kita tidak ingin form tersebut ditutup dengan menggunakan close button tetapi dengan menggunakan tombol kita sendiri.

Kasus :

Bagaimana membuat tombol exit pribadi dan menonaktifkan tombol close pada form.

Pemecahan :

- Buka kembali file Latihan06.xls dan aktifkan VBA, tambahkan Control Command Button, ganti Properti Name dengan CmdExit dan Properti Caption dengan EXIT.

- Doubel Klik FrmAwal, kemudian Pilih Procedure QueryClose dan ketik;

Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer)

     If CloseMode = vbFormControlMenu Then

        MsgBox "MAAF NGGAK BISA DITUTUP."

        Cancel = True

     End If

End Sub

- Kembali ke Project, pilih (double klik) FrmAwal, kemudian Double Klik CmdExit dan Ketik ;

Private Sub CMDEXIT_Click()

     Unload Me

End Sub

- Sekarang tutup VBA kemudian coba Klik Kembali Icon atau tekan Shortcut yang telah anda buat tadi, setelah muncul Form Pertama coba anda Klik close button maka akan muncul pesan dan form tidak bisa ditutup kecuali anda menekan EXIT.

Gambar 2.19 Menonaktifkan Tombol Close UserForm

Penerapan :

Penggunaan code diatas dapat dilakukan jika anda menginginkan user hanya dapat  meng close form sesuai dengan tombol atau ketentuan  yang anda inginkan atau anda ingin membuat form dengan password.

2.6.   Membuat Splash Screen Pribadi

Selain memunculkan pesan (msgbox) diawal sebuah file Excel di buka anda juga dapat memanfaatkan Form VBA untuk memunculkan pesan pribadi, tampilan akan lebih atraktif karena muncul dalam bentuk splash screen (tampilan yang muncul hanya beberapa detik kemudian akan hilang pada umumnya memberitahukan nama suatu program dan register program tersebut kemudian nama para programmernya)

Kasus :

Bagaimana membuat splash Screen pada sebuah file Excel.

Pemecahan :

- Buka kembali file Latihan5.xls kemudian aktifkan VBA.

- Kita telah mempunyai sebuah Userform, yaitu FrmAwal (jika menggunakan file yang lain tambahkan saja Userform dan tambahkan kontrol label serta image pribadi anda), supaya terlihat lebih bagus untuk propeties Caption pada FrmAwal sebaiknya anda kosongkan.

- Double Klik Object ThisWorkbook dalam Project Explorer (pada bagian kiri jendela VBA dengan icon huruf x dilatari kertas) ketik kode berikut :

Private Sub Workbook_Open()

     FrmAwal.Show

End Sub

-          Double Klik FrmAwal dan pilih procedure Activate, ketik kode berikut :

Private Sub FrmAwal_Activate()

Application.OnTime Now + TimeValue("00:00:05"), _ "TutupFrmAwal" ‘ketik satu baris

End Sub

- Double Klik Module1 (jika belum ada, Klik Insert, Module), lalu ketik kode berikut :

Private Sub TutupFrmAwal()

     Unload FrmAwal

End Sub

- Simpan file, save as, Latihan07.xls,  tutup file, kemudian coba jalankan lagi, hasilnya akan muncul FrmAwal ketika file pertama dibuka, kemudian setelah lima detik FrmAwal akan hilang dengan sendirinya. Anda dapat mengganti lama aktifnya FrmAwal dengan menyesuaikan TimeValue.

Gambar 2.20 Splash Screen Pribadi

Penerapan :

- Splash Screen Pribadi ini dapat berguna sebagai inisial file pribadi yang anda miliki, jika anda sering berkirim file kepada orang lain,  maka trik ini dapat menjadi identitas tersendiri terhadap file tersebut.