Ini adalah nota rancangan saya dari penampilan saya di Episod 33 dari Call for Help di TechTV Canada.
Perakam Makro VBA
Setiap salinan Excel yang dijual sejak 1995 merangkumi Visual Basic for Applications (VBA) yang sangat kuat yang bersembunyi di sebalik sel. Sekiranya anda mempunyai Excel, anda mempunyai VBA.
VBA membolehkan anda mengautomasikan apa sahaja yang boleh anda lakukan di Excel, dan melakukan lebih banyak perkara yang tidak mungkin dilakukan secara umum di Excel.
Nasib baik bagi kami, Microsoft menyertakan perakam makro dengan Excel. Ini membolehkan anda menulis kod VBA tanpa menjadi pengaturcara. Malangnya, Perakam Makro cacat. Ia tidak akan menghasilkan kod yang akan berfungsi setiap masa. Perakam makro akan membuat anda hampir, tetapi dalam banyak kes, anda perlu sedikit memperbaiki kodnya agar dapat berfungsi dengan baik. Dalam kes lain, anda perlu mengetahui kapan menggunakan Rakaman Relatif untuk membuat makro berfungsi.
Tracy Syrstad dan saya menulis VBA dan Makro Microsoft Excel 2016 untuk membantu anda memahami batasan kod yang direkodkan dan untuk mengajar anda cara memperbaiki kod yang dirakam menjadi sesuatu yang akan berfungsi setiap masa.
Dalam rancangan itu, saya menunjukkan proses merakam makro yang akan berjalan dengan sempurna jika anda tahu bagaimana menggunakan butang Rujukan Relatif. Seseorang telah memberi saya lembaran kerja Excel dengan beratus nama dan alamat. Mereka mahukan label surat. Sekiranya anda pernah menggunakan ciri gabungan surat Microsoft Word, anda tahu bahawa anda memerlukan setiap bidang dalam lajur baru di lembaran kerja. Sebaliknya, lembaran kerja khusus ini mempunyai data yang turun pada lajur A.
Proses membetulkan satu label agak membosankan.
- Mulakan dengan penunjuk sel pada nama di lajur A.
- Tekan anak panah ke bawah untuk beralih ke alamat
- Ctrl + x untuk dipotong
- Anak panah ke atas, anak panah kanan untuk beralih ke lajur B di sebelah namanya
- Ctrl + v untuk ditampal
- Anak panah ke bawah dua kali, anak panah kiri sekali untuk berpindah ke bandar
- Ctrl + x untuk dipotong
- Anak panah ke atas dua kali, panah kanan tiga kali
- Ctrl + v untuk ditampal
- Anak panah kiri dua kali, anak panah ke bawah sekali untuk beralih ke baris kosong sekarang.
- Tahan kekunci shift semasa anda menekan anak panah ke bawah dua kali
- Alt + edr untuk memadam baris kosong
- Anak panah ke atas & bawah untuk memilih nama sahaja.
Berikut adalah animasi yang menunjukkan langkah-langkah berikut:
Menyiapkan Excel untuk Membolehkan Makro
Walaupun setiap salinan Excel mengandungi VBA, secara lalai, Microsoft mematikan kemampuan menjalankan makro. Anda perlu melakukan penyesuaian satu kali untuk membolehkan makro berjalan. Buka Excel. Dari menu, pilih Alat> Makro> Keselamatan. Sekiranya tahap keselamatan makro anda sekarang ditetapkan ke tinggi, ubah menjadi Medium.
Bersedia untuk Merakam Makro
Fikirkan langkah-langkah yang diperlukan untuk mencapai tugas. Anda ingin memastikan bahawa anda bermula dengan penunjuk sel pada nama dan diakhiri dengan nama seterusnya. Ini akan membolehkan anda menjalankan makro berulang kali berkali-kali. Apabila anda sudah siap, hidupkan perakam makro. Dari menu, pilih Alat> Makro> Rakam Makro Baru.
Sekiranya anda menjalankan makro ini selama beberapa hari, anda harus memberikan makro nama yang berguna. Sekiranya makro satu kali untuk mengotomatisasi tugas satu kali, maka meninggalkan nama Makro sebagai Macro1 mungkin baik. Bidang penting di sini adalah bidang pintasan. Sekiranya anda membuat makro untuk satu kali penggunaan, tetapkan makro ke kekunci pintasan seperti Ctrl + a - cukup mudah untuk memukul Ctrl + berulang kali kerana kunci saling berdekatan. Sekiranya anda akan membuat makro yang akan anda gunakan setiap hari, maka anda ingin menetapkan makro kepada kunci yang belum menjadi kombinasi kunci pintasan yang penting. Ctrl + j atau Ctrl + k adalah pilihan yang baik.
Makro sekali pakai harus disimpan dalam Buku Ini. Sekiranya anda perlu menggunakan makro berulang kali pada banyak buku kerja yang berbeza, maka anda boleh menyimpan makro tersebut di Buku Kerja Makro Peribadi.
Anda kini dibentangkan dengan bar alat terkecil di seluruh Excel; Bar Alat Rakaman Berhenti. Ia hanya mempunyai dua ikon dan kelihatan seperti ini:
Sekiranya bar alat ini menghalangi anda, jangan tekan X untuk menutupnya. Sebaliknya, ambil bar biru dan seret bar alat ke lokasi baru. Tindakan menggerakkan bar alat tidak direkodkan dalam makro. Sekiranya anda menutup bar alat secara tidak sengaja, dapatkannya kembali dengan Paparan> Bar Alat> Berhenti Merakam. Tindakan ini, bagaimanapun, akan dirakam.
Butang pertama pada bar alat adalah butang "Berhenti Merakam". Ini jelas. Setelah selesai merakam makro, tekan bar alat rakaman berhenti.
Butang yang lebih penting (dan jarang difahami) ialah butang "Relative Reference".
Dalam contoh semasa kami, anda mesti menekan butang Rujukan Relatif sebelum anda memulakan. Sekiranya anda merakam makro dengan Rujukan Relatif dihidupkan, Excel akan merakam langkah seperti ini:
- Gerakkan satu sel ke bawah
- Potong sel semasa
- Gerakkan satu sel ke atas
- Gerakkan satu sel ke kanan
- Tampal
- dan lain-lain.
Sekiranya anda merakam makro tanpa rujukan relatif, makro akan merakam langkah-langkah berikut:
- Pindah ke sel A4
- Potong sel A4
- Pindah ke sel A3
- Pindah ke sel B3
- Tampal ke sel B3
- dan lain-lain.
Ini sangat salah - kita memerlukan makro untuk mengusahakan sel yang sel 1 dan 2 dari titik permulaan makro. Semasa anda menjalankan makro berulang-ulang, titik permulaan akan menjadi baris 4, baris 5, baris 6, dll. Merakam makro tanpa rujukan relatif dihidupkan, makro akan selalu berjalan pada baris 3 sebagai titik permulaan.
Ikut langkah-langkah ini:
- Pilih Butang Rujukan Relatif pada Bar Alat Rakaman Berhenti
- Penunjuk sel mesti ada pada nama di lajur A.
- Tekan anak panah ke bawah untuk beralih ke alamat
- Ctrl + x untuk dipotong
- Anak panah ke atas, anak panah kanan untuk beralih ke lajur B di sebelah namanya
- Ctrl + v untuk ditampal
- Anak panah ke bawah dua kali, anak panah kiri sekali untuk berpindah ke bandar
- Ctrl + x untuk dipotong
- Anak panah ke atas dua kali, panah kanan tiga kali
- Ctrl + v untuk ditampal
- Anak panah kiri dua kali, anak panah ke bawah sekali untuk beralih ke baris kosong sekarang.
- Tahan kekunci shift semasa anda menekan anak panah ke bawah dua kali
- Alt + edr untuk memadam baris kosong
- Anak panah ke atas & bawah untuk memilih nama seterusnya dalam senarai.
- Tekan bar alat Berhenti Merakam
- Simpan buku kerja
- Uji makro dengan menekan kekunci pintasan yang diberikan di atas. Ia mesti betulkan nama seterusnya dalam senarai
Setelah anda melihat bahawa makro berfungsi seperti yang diinginkan, anda dapat menahan Ctrl + a untuk memperbaiki beberapa nama sesaat dengan cepat. Keseluruhan senarai 500 nama dapat diperbaiki dalam satu atau dua minit.
Petua Bonus - Tidak ditunjukkan
Anda boleh membungkus makro dengan mudah dalam gelung sederhana untuk memperbaikinya 500 nama tanpa anda perlu menekan Ctrl + beberapa ratus kali.
Tekan kekunci Alt + F11 untuk membuka editor makro.
Sekiranya anda tidak melihat panel Project - VBAProject, tekan Ctrl + r.
Klik kanan Modul1 dan pilih Lihat Kod. Anda akan melihat kod yang kelihatan seperti ini:
Sekarang, anda tidak perlu memahami apa yang dilakukan oleh kod ini, tetapi anda tahu bahawa kami ingin menjalankan semua perkara dalam makro itu sekali untuk setiap nama yang kami ada. Sekiranya anda mengetahui bahawa terdapat 462 nama, maka anda boleh menambahkan 2 baris untuk menjalankan kod 462 kali. Di bahagian atas makro, masukkan baris baru dengan perkataan " For i = 1 to 462". Di bahagian bawah makro, masukkan baris yang bertuliskan " Next i". Ini memberitahu VBA untuk menjalankan kod dalam 462 kali.
Kesimpulannya
Selepas makro sederhana ini, saya menunjukkan contoh pada pertunjukan makro yang sangat kompleks. Makro ini membuat jadual pangsi dan carta untuk 46 jabatan syarikat. Laporan ini memakan masa 40 jam setiap bulan. Makro VBA kini berjalan dan membuat semua 46 laporan dalam masa kurang dari 2 minit.
Buku VBA dan Makro Microsoft Excel 2016 akan memberi petunjuk kepada anda untuk mendapatkan dari merakam makro mudah seperti ini hingga menjalankan laporan yang sangat rumit yang dapat menjimatkan ratusan jam setiap tahun. Sudah tentu, jika anda tidak mahu belajar VBA, ambil seorang Perunding Excel untuk merancang laporan untuk anda.
