Tentang Macro Dan VBA

 

Excel VBA yang merupakan singkatan untuk Excel Visual Basic for Applications adalah nama dari bahasa pemprograman Microsoft Excel. Denagn Excel VBA dapat mengotomatisasi tugas di Ecxcel dengan menggunakan marco.

Bab ini menjelaskan dasar-dasar Excel VBA. Bahkan jika anda belum menganal marco dan VBA, bab ini akan membantu anda memahami bagaimana untuk membuat,merekam,dan menjalankan marco di Excel dengan jelas.

   o   Apa itu Marco ?

          Marco merupakan satu rangkaian instruksi  yang mengoptisasi bebrapa aspek dari Excel sehingga anda dapat bekerja dengan lebih efisien dan dengan lebih cepat. Anda dapat menggunakan perekam marco Excel untuk membuat marco. Excel akan merekam tindakan anda dan mengubahnya menjadi marco VBA.

Anda juga dapat menggunakan bahasa Excel VBA ( Visual Basic for Applications) untuk membuat marco, misalnya, untuk mengimpor data dari file teks, lalu memformat, dan menyimpannya sebagai workbook. Setelah marco dibuat anda dapat menjalankan marco tersebut berulang kali untuk melakukan perintahnya secara otomatis anda dapat melakukan banyak hal dengan marco, dan diantaranya ada tiga yang paling sering umum dilakukan oleh marco adalah sebagai berikut.

1.    Mengotomatisai prosedur yang sering anda lakukan.

Misalnya, anda mungkin perlu untuk mempersiapkan laporan keuangan akhir bulan. Jika tugas itu mudah, anda dapat membuat marco agar melakukannya untuk anda.

2.    Mengotomatisa operasi yang berulang.vanda dapat merekam marco ketika anda melakukan tindakan tersebut sekali dan kemudian menjalankan marco untuk mengulangi tindakan anda di workbook lain.

3.    Buat sebuah perintah khsus. Misalnya, anda dapat menggabungkan beberapa perintah Ecel sehingga mereka dapat dijalankan dari satu cara pintas keyboard ( Keyboard Shortcut ) atau dari satu klik mouse.

   o   Merkam Marco

       Cara termudah membuat marco Excel adalah dengan mengggunakan perekam marco untuk merekam          rangkaian tindakan anda lalu memutar kembali urutan tersebut untuk dikenlai sebagai menjalankan marco.

    Untuk membuat marco, lakukan langkah berikut.

1.      Klik pada sel kosong dan kemudian klik tombol Record Marco disisi kiri dari status bar atau pilih tab view > Marcos (grub Marcos) > Record Marco. Anda juga dapat memilih dari tab Developer > Record Marco (grub Code).

Ketika Excel menampilkan kotak dialog Record Marco,anda juga dapat memilih dari tab Developer>Record Marco (grub Marco).

2.      Ketika Excel menampilkan kotak dialog Record Marco, masukkan nama marco yang digunakan untuk mengganti nama default Marco1 (misalnya, MyMarco). Anda dapat member cara pintas keyboard (shortcut) untuk marco tersebut, misalnya, ctrl + shift + N. klik OK untuk menutup kotak dialog dan Record marco mulai merekam tindakan anda.

3.      Ketik beberapa teks dalam sel yang dipilih (misalnya A1) dan tekan Enter.

4.      Klik tombol Stop Recording pada status bar untuk mengakhiri rekaman atau pilih tab View > Marcos (grub Marcos) > Stop Recording. Anda juga dapat memilih tab Developer > Stop Marco ( grub code).

  o   Tiga Tip Ketika Menggunakan Marco Recorder

 Tip 1 : Mengeset Relative References Setting Ke ON secara default, perekam macro mencatat semua tindakan sebagai referensi absolute. Jika anda menavigasi ke baris 11 saat anda merekam marco, marco dijalankan di lain hari. Hal ini mungkin tidak tepat ketika menggunakan jumlah baris fata yang bias berubah. Pilihan yang lebih baik adalah dengan menggunakan referensi relative saat merekam macro.

Marco yang direkam dengan referensi absolut akan memperhatikan alamat yang sebenarnya dari pointer sel, seperti A11. Marco yang direkam dengan referensi relatif akan memperatikan beberapa banyak baris atau kolom petunjuk sel harus pindah dari posisi saat ini. Misalnya, jika penunjuk sel mulai pada sel A1, kode ActiveCell.offset(16, 1).Select akan memindahkan petunjuk sel ke B17, yang merupakan 16 baris ke bawah dan 1 kolom ke kanan.

Untuk mengaktifkan Relative References Settings, pilih tab Developer > Use Relative References (grup Code).

Tip 2 : Gunakan Tombol Navigasi Khusus Untuk Pindah Ke Bawah Dataset

Jika anda berada di dalam satu dataset dan perlu pindah ke baris (Sel) terakhir yang memiliki data, anda dapat menekan Ctrl + E.

Dengan menggunakan tombol navigasi, anda dapat melompat akhir dataset – tidak perduli beberapa banyak baris atau kolom yang anda miliki.

Tips 3 : jangan menggunakan ikon Autosum ketika merekam macro

Ketika anda menggunkan tombol Autosum, Excl akan menghasilkan rumus sebagai referensi mutlak sehingga rumus ini tidak akan bekerja dengan tepat jika anda memiliki lebih banyak atau lebih sedikit record dalam dataset dikemudian hari.

Sebaliknya, ketik formula dengan tanda dolar tunggal ($), seperti SUM(E$2:E10). Bila ini dilakukan, perekam macro akan mencatat E$2 sebagai referensi campuran (Mixed referencing) dan fungsi SUM akan mulai langsung di bawah judul baris 1. Jika sel aktif adala E11, perekam macro akan menganggap E10 sebagai referensi campuran (Mixed referencing) dan fungsi SUM akan mulai langsunng dibawah judul baris 1. Jika sel aktif adalah E11, perekam macro akan menganggap E10 sebagai referensi relative yang menunjuk langsung diatas sel saat ini.

  o   Melihat kode marco

    Marco direkam dalam sebuah modul VBA baru yang bernama Module1, untuk melihat kode dalam modul ini tkan Alt + F11 untuk beralih antara jendela Excel dan VBE (Visual Basic Editor). Dalam VBE, anda akan melihat tiga jendela utama yaitu, jendela Project Explorer, properties, dan Code.

1.      Jendela project explorer

              Menampilkan semua worksheet, add-in, modules, user forms yang terbuka. Daftar ini ditampilkan sebagai sebuah diagram pohon yang anda dapat memperluas atau memperkecil dengan mengeklik tanda plus (+) dan tanda minus (-).

Untuk menampilkan jendela project Explorer, pilih menu View > Projrct Explorer atau tekan Ctrl + R.

Arau klik ikon roject explorer pada toolbar.

2.      Jendela properties

              Memungkinkan anda untuk mengedit property dari berbagai komponen seperti worksheet, workbook, modul dan form control. Daftar property akan tampil berbeda sesuai dengan komponen yang dipilih.

Untuk menampilkan jendela ini, pilih menu View > properties windows atau tekan F4.

Atau klik ikon Project properties pada toolbar.

3.      Jendela code / programming

              Kode yang anda direkam disimpan dalam module1 (secara default) dalam workbook saat ini. Ketika anda klik ganda pada module1, kode dalam modul akan ditampilkan dalam jendela code.

Excel akan memasukkan beberapa komentar di bagaian atas prosedur. Komentar ini (yang dimulai dengan tanda apostrof) tidak diperlukan dan jika dihapus tidak mempengaruhi bagaimana macro berjalan.

   o   Menjalankan Marco

       Sebelum anda merekam marco, anda mungkin ingin member cara pintas (Shortcut) keyboard untuk macro tersebut, misalnya, Ctrl + Shift + N. untuk menguji macro, kembali ke Excel dengan menekan Alt + F11.

         Klik pada satu sel dan tekan Ctrl + Shift + N. marco tersebut akan menyisipkan teks ke dalam sel dan kembali ke sel A2 – sel anda berada ketika anda tekan tombol Enter setelah selesai mengetik teks, “ PINTAR MARKO EXCEL”.

Apabila anda selesai membuat macro, anda dapat menjalankan macro tersebut dalam beberapa cara seperti berikut.

       Gunakan Kotak Dialog Macro

         Untuk menjalankan satu macro, workbook yang berisi macro tersebut harus terbuka. Tekan tombol Alt + F8 atau pilih tab View > macros (grup Macros).

         Anda juga dapat pilih tab Developer > View Macros (grup  Code) kotak dialog Marco yang muncul akan menampilkan semua macro yang tersedia. Pilih macro yang anda inginkan dan klik Run.

              Gunakan Jendela Visual Nasic Editor

       Anda juga dapat menjalankan macro langsung dari VBE. Langkah sebagai berikut :

Tekan Alt + F11 untuk beralih ke VBE.

Cari proyek dan modul yang berisi macro yang diinginkan, tempatkan kursor dimanapun dalam kode macro tersebut (dalam jendela Code).

Pilih menu Run > Run Sub/UserForm atau tekan tombol F5.

                Gunakan Tombol Pintas (Shortcut)

         Ketika anda mulai merekam macro, kotak dialog Record Marco member anda pilihan untuk member cara pintas keyboard untuk macro tersebut.

Untuk mengubah cara pintas atau membuat cara pintas baru untuk macro tersebut, langkah sebagai berikut :

1)      Tekan Alt + F8 untuk menampilkan kotak dialog marco.

2)      Pada kotak dialog macro, pilih nama macro dari daftar.

3)      Klik tombol Options untuk menampilkan kotak dialog Macro kembali ke kotak dialog Macro.

4)      Tentukan tombol shortcut yang diinginkan dan klik ok untuk kembali ke kotak dialog macro

Menjalankan Macro Lewat Satu Tombol

Anda dapat menambahkan tombol ke worksheet dan kemudian menjalankan macro dengan mengklik tombol tersebut. Caranya sebagai berikut :

1)      Pilih tab Developer > Insert (grup Control).

2)      Klik pada tombol control yang anda inginkan dibagian Form Controls.

3)      Gambar tombol tersebuut pada worksheetAnda.

4)      Pada kotak dialog Macro Assign yang muncul, pilih macro dari daftar yang tampil dan klik Ok untuk menutup kotak dialog Assign Marco.

5)      Jika anda ingin mengubah teks yang muncul pada tombol, klik kanan pada tombol > Edit Teks dari menu shortcut yang muncul.

6)      Setelah selesai, klik tombol untuk menjalankan.

Tambahkan Tombol Ke Toolbar Quick Access Anda

Cara lain untuk menjalankan macro adalah membuat sebuah tombol pada Quick Access toolbar. Caranya sebagai berikut :

1)      Klik kanan pada Quick Access toolbar > Customize Quick Access Toolbar untuk menampilkan kotak dialog Excel Options.

2)      Dalam daftar dropdown disebelah kiri, pilih Macros, daftar macro yang tersedia akan ditampilkan.

3)      Pilih macro dari daftar tersebut dan klik Add. Klik Ok untuk melanjutkan.

    o   Memahami File Format Excel 2007

              Excel 2007 mendukung banyak format fie yang berada. Macro tidak dapat disimpan dalam jenis default (xlsx). Anda harus menggunkan perintah save as untuk menyimpan semua workbook yang berisi macro atau anda dapat mengubah tie defalt file yang digunkan oleh Excel 2007. Berbagai format file baru yang digunakan dalam Ecel 2007 adalah sebagai berikut :

ü  XLSX   : sebuah workbook yang tidak mengandung macro.

ü  XLSM  : sebuah workbook yang berisi macro.

ü  XLTX  : sebuah template workbook yang tidak mengandung macro.

ü  XLTM  : sebuah template workbook yang berisi macro.

ü  XLSA   : sebuah file Add-in.

ü  XLSB   : sebuah file biner yang mirip dengan format XLS lama tetapi mampu mengakomodasi fitur baru.

ü  XLSK   : sebuah file backup.

Kecuali XLSB, semua format file tersebut merupakan format file XML terbuka, yang berarti bahwa applikasi lain dapat membaca dan menulis jenis file tersebut.

   o   Menggunakan Tab Developer

            Secara default, Microsoft menyembunyikan VBA tools. Untuk menampilkan tab Developer, pilih Office button > Excel Options > Popular. Dibawah Top Options For working with excel, beri tanda centang / ceklis untuk show developer tab in ribbon dan klik Ok untuk melanjutkan.

Excel sekarang akan menampilkan tab Developer.grup code pada tab developer berisi ikon yang digunakan untuk merekam dan memutar uklang macro VBA.

·         Visual Basic       : membuka visual basic editor

·         Macro                 : menampilkan kotak dialog macro dimana anda dapat memilih untuk menjalankan atau mengedit macro dari daftar macro.

·         Record macro    : memulai proses perekaman macro.

·         Use Relative References            : beralih antara menggunakan alamat relative atau absolute ketika merekam macro. Dengan alamat relative, excel akan mencatat bahwa anda bergerak turun tiga sel. Dengan alamat absolute, excel akan mencatat anda pilih sel A4.

·         Macro Security  : membuka trust center di mana anda dapat memilih untuk mengizinkan atau melarang macro untuk berjalan di computer.

  o   Macro Security

            Setelah macro VBA disalahgunakan untuk menyebar virus yang berbahaya, Microsoft telah mencegah macro dari berjalan pada computer anda secara default.

Namun anda dapat membuat pengaturan control macro untuk workbook tertentu atau secara global dengan menyimpan workbook dilokasi terpercaya (trusted locations). Setiap workbook yang disimpan dalam folder yang ditandai sebagai lokasi terpercaya secara otomatis akan dapat menjalankan macronya.

            Anda dapat menemukan pengaturan keamanan macro dibawah ikon macro security pada tab developer. Ketika anda mengeklik ikon ini, kategori macro settings dari trust center akan ditampilkan. Anda dapat menggunakan navigasi kiri dalam kotak dialog ini untuk mengakses daftar Trusted Locatiuns.

      Menambah Lokasi Terpercaya

            Anda dapat memilih untuk menyimoan workbook macro anda dalam folder yang ditandai sebagai lokasi terercaya. Setiap workbook yang disimpan dalam folder terpercaya secara otomatis akan dapat menjalankan macronya.

Untuk menentukan lokasi terpercaya, langkahnya sebagai berikut ?

1)      Klik ikon macro security ditab developer.

2)      Pada jendela Trust Center, klik Trusted Locations. Lokasi yang terpercaya akan ditampilkan.

3)      Klik add new locations untuk menambah folder anda sebagi lokasi yang terpercaya.

4)      Pada kotak dialog Microsoft Office Trusted locarion, pilih folder ang diinginkan dan beri tanda centang untuk subfolders of this locations is are also trusted sehingga subfolder dalam folder anda juga dianggap dapat dipercayai. Klik ok untuk melanjutkan/

5)      Untuk menghapus atau mengubah lokasi yang terpercaya, pilih loasi tersebut > remove untuk menghapus lokasi tersebut atau pilih Modify untuk mengubah lokasi tersebut. Klik Ok apabila selesai.

Mengaktifkan macro di luar lokasi terpercaya

   Untuk semua macro yang tidak disimpan dilokasi yang dipercaya, Excel akan tergantung pada pengaturan macro (Low, Medium, Higt, and very High). Untuk mengakses pengaturan macro, langkahnya sebagai berikut :

1)      Klik macro security di tab developer. Excel menampilakn kategori macro settings dari kotak dialog trust center.

2)      Pada umumnya, anda akan memilih opsi kedua, disable All Macros With Notification namun anda dapat juga memilih peraturan lain.

a)      Disable All Macros without Notification, peraturan ini akan mencegah semua macro berjalan.

b)     Disable All Macros With Notification, ini adalah pengaturan yang direkomendasikan. Excel akan menampilkan kotak peringatan dan meminta anda untuk memilih Enable atau Disable ketika membuka sebuah file yang berisi macro.

c)      Disable All Macros Except Digitally Signed Macros, peraturan ini mengharuskan anda untuk mendapatkan penaandatanganan digital dari VeriSign atau penyesia lain. Pilihan ini ungkin tepat jika anda berniat untuk menjual add-in untuk orang lain.

d)     Enable All Macros (Not Recommended: Potentially Dangerous Code Can Run), peraturan ini akan membuka semua macro secara otomatis yang dapat mengekspos computer anda terhadap serangan dari virus berbahaya. Microsoft menyarankan bahwa anda tidak menggunakan peraturan ini.

                    Menggunakan Disable All Macro With Notification

Dianjurkan agar anda menetapkan peraturan macro anda ke Disable All Macro With Notification. Jika anda menggunakan peraturan ini dan membuka workbook yang berisi macro, anda akan melihat peringatan keamanan diatas formula bar.

Klik Enable Content jika anda yakin menggunakan macro tersebut. Jika anda tidak ingin mengaktifkan macro tersebut, abaikan peringatan keamanan dengan mengeklik tombol Cancel.

Jika anda lupa untuk mengaktifkan macro dan berusaha untuk menjalankan macro, Excel akan memberitahu bahwa anada tidak dapat menjalankan macro karena semua macro telah dinonaktifkan. Jika hal ini terjadi, tutup workbook dan buka kembali untuk mengakses pesan bar lagi.

Setelah anda mengaktifkan macro di workbook lalu menyimpannya, excel akan ingat bahwa anda sebelumnya pernah mengaktifkan macro dalam workbook tersebut.

Ketika excel membuka workbook tersebut dikemudian hari, macro didalamnya akan diaktifkan secara otomatis tanpa peringatan keamanan diatas formula bar.