Ini adalah catatan acara saya dari penampilan saya di Episode 33 dari Panggilan untuk Bantuan di TechTV Kanada.
Perekam Makro VBA
Setiap salinan Excel yang dijual sejak 1995 menyertakan Visual Basic for Applications (VBA) yang sangat kuat yang bersembunyi di balik sel. Jika Anda memiliki Excel, Anda memiliki VBA.
VBA memungkinkan Anda mengotomatiskan apa pun yang dapat Anda lakukan di Excel, serta melakukan lebih banyak hal yang umumnya tidak mungkin dilakukan di Excel.
Beruntung bagi kami, Microsoft menyertakan perekam makro dengan Excel. Ini memungkinkan Anda menulis kode VBA tanpa menjadi programmer. Sayangnya, Perekam Makro cacat. Itu tidak akan menghasilkan kode yang akan berfungsi setiap saat. Perekam makro akan membantu Anda, tetapi dalam banyak kasus Anda perlu memperbaiki kode sedikit agar dapat berfungsi dengan baik. Dalam kasus lain, Anda perlu mengetahui kapan menggunakan Perekaman Relatif untuk membuat makro berfungsi.
Tracy Syrstad dan saya menulis VBA dan Macros Microsoft Excel 2016 untuk membantu Anda memahami batasan kode yang direkam dan untuk mengajari Anda cara memperbaiki kode yang direkam menjadi sesuatu yang akan berfungsi setiap saat.
Di acara itu, saya mendemonstrasikan proses merekam makro yang akan bekerja dengan sempurna jika Anda tahu cara menggunakan tombol Referensi Relatif. Seseorang telah memberi saya lembar kerja Excel dengan ratusan nama dan alamat. Mereka menginginkan label surat. Jika Anda pernah menggunakan fitur gabungan surat Microsoft Word, Anda tahu bahwa Anda membutuhkan setiap bidang di kolom baru di lembar kerja. Sebaliknya, lembar kerja khusus ini memiliki data di kolom A.
Proses untuk memperbaiki satu label cukup melelahkan.
- Mulailah dengan penunjuk sel pada nama di kolom A.
- Tekan panah bawah untuk pindah ke alamat
- Ctrl + x untuk memotong
- Panah atas, panah kanan untuk pindah ke kolom B di sebelah nama
- Ctrl + v untuk menempel
- Panah bawah dua kali, panah kiri sekali untuk pindah ke kota
- Ctrl + x untuk memotong
- Panah atas dua kali, panah kanan tiga kali
- Ctrl + v untuk menempel
- Panah kiri dua kali, panah bawah sekali untuk pindah ke baris yang sekarang kosong.
- Tahan tombol shift saat Anda menekan panah bawah dua kali
- Alt + edr untuk menghapus baris kosong
- Panah atas & panah bawah untuk memilih nama lagi.
Berikut animasi yang menunjukkan langkah-langkah ini:
Menyiapkan Excel untuk Mengizinkan Makro
Meskipun setiap salinan Excel berisi VBA, secara default, Microsoft menonaktifkan kemampuan untuk menjalankan makro. Anda perlu melakukan penyesuaian satu kali untuk memungkinkan makro berjalan. Buka Excel. Dari menu, pilih Alat> Makro> Keamanan. Jika tingkat keamanan makro Anda saat ini disetel ke tinggi, ubah ke Sedang.
Bersiap untuk Merekam Makro
Pikirkan tentang langkah-langkah yang diperlukan untuk mencapai tugas tersebut. Anda ingin memastikan bahwa Anda memulai dengan penunjuk sel pada nama dan diakhiri dengan nama berikutnya. Ini akan memungkinkan Anda untuk menjalankan makro berkali-kali. Saat Anda memiliki langkah-langkah yang siap untuk dilakukan, nyalakan perekam makro. Dari menu, pilih Alat> Makro> Rekam Makro Baru.
Jika Anda akan menjalankan makro ini selama beberapa hari, Anda harus memberikan nama yang berguna pada makro. Jika ini adalah makro satu kali untuk mengotomatiskan tugas satu kali, maka membiarkan nama Makro sebagai Macro1 mungkin baik-baik saja. Bidang penting di sini adalah bidang tombol pintas. Jika Anda membuat makro untuk penggunaan satu kali, tetapkan makro ke tombol pintasan seperti Ctrl + a - cukup mudah untuk menekan Ctrl + a berulang kali karena tombol tersebut berdekatan satu sama lain. Jika Anda akan membuat makro yang akan Anda gunakan setiap hari, maka Anda ingin menetapkan makro ke tombol yang belum merupakan kombinasi tombol pintasan penting. Ctrl + j atau Ctrl + k adalah pilihan yang bagus.
Makro sekali pakai harus disimpan di Buku Kerja Ini. Jika Anda perlu menggunakan makro berulang kali di banyak buku kerja yang berbeda, maka Anda bisa menyimpan makro di Buku Kerja Makro Pribadi.
Anda sekarang disajikan dengan toolbar terkecil di seluruh Excel; Stop Recording Toolbar. Ini hanya memiliki dua ikon dan terlihat seperti ini:
Jika toolbar ini menghalangi Anda, jangan tekan X untuk menutupnya. Sebagai gantinya, ambil bilah biru dan seret bilah alat ke lokasi baru. Tindakan memindahkan toolbar tidak direkam di makro. Jika Anda menutup toolbar secara tidak sengaja, dapatkan kembali dengan View> Toolbars> Stop Recording. Tindakan ini, bagaimanapun, akan direkam.
Tombol pertama pada toolbar adalah tombol "Berhenti Merekam". Ini sudah cukup jelas. Saat Anda selesai merekam makro, tekan toolbar berhenti merekam.
Tombol yang lebih penting (dan jarang dipahami) adalah tombol "Referensi Relatif".
Dalam contoh kami saat ini, Anda harus menekan tombol Referensi Relatif sebelum Anda mulai. Jika Anda merekam makro dengan Referensi Relatif diaktifkan, Excel akan merekam langkah-langkah seperti ini:
- Pindahkan satu sel ke bawah
- Potong sel saat ini
- Pindah satu sel ke atas
- Pindah satu sel ke kanan
- Tempel
- dll.
Jika Anda malah akan merekam makro tanpa referensi relatif, makro akan merekam langkah-langkah berikut:
- Pindah ke sel A4
- Potong sel A4
- Pindah ke sel A3
- Pindah ke sel B3
- Tempel ke sel B3
- dll.
Ini akan sangat salah - kita membutuhkan makro untuk bekerja pada sel yang berukuran 1 dan 2 sel dari titik awal makro. Saat Anda menjalankan makro berulang kali, titik awalnya adalah baris 4, baris 5, baris 6, dll. Merekam makro tanpa referensi relatif yang diaktifkan akan membuat makro selalu berjalan di baris 3 sebagai titik awal.
Ikuti langkah ini:
- Pilih Tombol Referensi Relatif di Stop Recording Toolbar
- Penunjuk sel harus sudah ada di nama di kolom A.
- Tekan panah bawah untuk pindah ke alamat
- Ctrl + x untuk memotong
- Panah atas, panah kanan untuk pindah ke kolom B di sebelah nama
- Ctrl + v untuk menempel
- Panah bawah dua kali, panah kiri sekali untuk pindah ke kota
- Ctrl + x untuk memotong
- Panah atas dua kali, panah kanan tiga kali
- Ctrl + v untuk menempel
- Panah kiri dua kali, panah bawah sekali untuk pindah ke baris yang sekarang kosong.
- Tahan tombol shift saat Anda menekan panah bawah dua kali
- Alt + edr untuk menghapus baris kosong
- Panah atas & panah bawah untuk memilih nama berikutnya dalam daftar.
- Tekan toolbar Stop Recording
- Simpan buku kerja
- Uji makro dengan menekan tombol pintas yang ditetapkan di atas. Itu harus dengan sempurna memperbaiki nama berikutnya dalam daftar
Setelah Anda melihat bahwa makro berfungsi seperti yang diinginkan, Anda dapat menahan Ctrl + a untuk memperbaiki beberapa nama per detik dengan cepat. Seluruh daftar 500 nama bisa diperbaiki dalam satu atau dua menit.
Tip Bonus - Bukan Pertunjukan
Anda dapat dengan mudah membungkus makro dalam loop sederhana untuk memperbaikinya semua 500 nama tanpa Anda harus menekan Ctrl + beberapa ratus kali.
Tekan tombol Alt + F11 untuk membuka editor makro.
Jika Anda tidak melihat panel Project - VBAProject, tekan Ctrl + r.
Klik kanan Module1 dan pilih View Code. Anda akan melihat kode yang terlihat seperti ini:
Sekarang, Anda tidak perlu memahami apa yang dilakukan kode ini, tetapi Anda tahu bahwa kami ingin menjalankan semua yang ada di makro itu sekali untuk setiap nama yang kami miliki. Jika Anda mengetahui bahwa ada 462 nama, maka Anda dapat menambahkan 2 baris untuk menjalankan kode sebanyak 462 kali. Di bagian atas makro, sisipkan baris baru dengan kata " For i = 1 to 462". Di bagian bawah makro, sisipkan baris yang bertuliskan " Next i". Ini memberitahu VBA untuk menjalankan kode di dalam 462 kali.
Kesimpulan
Setelah makro sederhana ini, saya menunjukkan contoh pada tampilan makro yang sangat kompleks. Makro ini membuat tabel dan bagan pivot untuk 46 departemen perusahaan. Laporan ini biasanya memakan waktu 40 jam setiap bulan. Makro VBA sekarang berjalan dan membuat semua 46 laporan dalam waktu kurang dari 2 menit.
Buku VBA dan Makro Microsoft Excel 2016 akan memandu Anda dalam kurva pembelajaran sehingga Anda bisa mulai merekam makro sederhana seperti ini hingga menjalankan laporan yang sangat kompleks yang dapat menghemat ratusan jam per tahun. Tentu saja, Jika Anda tidak ingin mempelajari VBA, sewa Konsultan Excel untuk merancang laporan untuk Anda.
