Panduan ini akan menunjukkan kepada Anda 4 cara berbeda untuk membuat makro Anda berjalan lebih cepat dan lebih efisien di Excel
Ada 4 hal utama yang dapat Anda lakukan untuk setiap makro agar berjalan lebih cepat
Bagian
Pembaruan Layar
Ini adalah cara nomor satu untuk mempercepat makro
Kontrol Pembaruan Layar jika Excel akan memperbarui lembar kerja setiap kali ada sesuatu yang dilakukan dalam lembar kerja
Jika Anda membiarkan pengaturan ini, yang merupakan nilai default, dan Anda melakukan sesuatu seperti menjalankan pengulangan pada setiap baris data dan melakukan sesuatu dengan data tersebut, Excel akan memperbarui layar setelah setiap perubahan;
Matikan ini
Application.ScreenUpdating = False
Nyalakan ini
Application.ScreenUpdating = True
_Baca lebih lanjut tentang ini di tutorial kami tentang mematikan ScreenUpdating di Excel
Perhitungan
Jika Anda memiliki spreadsheet besar dengan rumus kompleks atau rumus yang ditautkan ke buku kerja terpisah, ini benar-benar dapat memperlambat makro
Penghitungan di Excel mengacu pada Excel yang menghitung ulang semua rumus dan fungsi di lembar kerja setelah setiap kali ada perubahan di lembar kerja tersebut. Dengan lembar kerja kecil dan rumus sederhana, Anda tidak akan melihat apa pun;
Matikan ini
Application.Calculation = xlCalculationManual
Nyalakan ini
Application.Calculation = xlCalculationAutomatic
Baca lebih lanjut tentang ini di tutorial kami tentang menonaktifkan Perhitungan di Excel
Acara
Acara adalah hal-hal di Excel yang dapat diatur untuk memicu makro untuk dijalankan. Anda dapat mengatur acara untuk menjalankan makro dengan pengguna memasukkan data ke dalam sel, memilih sel, berpindah antar lembar kerja, atau bahkan saat mereka menutup buku kerja
Peristiwa dipicu saat pengguna melakukan tindakan tertentu dan juga saat makro melakukan tindakan tersebut. Dengan demikian, saat Anda menjalankan makro, Peristiwa dapat menyebabkan hasil yang tidak dapat diprediksi atau hanya makro yang lambat. Namun, Anda dapat mematikan Acara ini selama durasi makro
Matikan ini
Application.EnableEvents = False
_Nyalakan ini
Application.EnableEvents = True
Baca lebih lanjut tentang ini di tutorial kami tentang mematikan Acara di Excel
Lansiran
Lansiran adalah jendela Excel yang muncul untuk "memperingatkan" Anda akan sesuatu. Ini terjadi ketika, misalnya, Anda mencoba menutup buku kerja tanpa menyimpan dan Excel menghentikan Anda dan menampilkan jendela yang menanyakan apakah Anda ingin menyimpannya atau tidak
Perilaku seperti ini merepotkan saat melakukan hal seperti menjalankan makro yang perlu membuka dan menutup buku kerja. Untuk menyiasatinya, Anda dapat menonaktifkan pesan dan peringatan ini agar tidak muncul
Matikan ini
Application.DisplayAlerts = False
_Nyalakan ini
Application.DisplayAlerts = True
Baca lebih lanjut tentang ini di tutorial kami tentang mematikan Peringatan di Excel
Bersama
Banyak orang hanya menggabungkan semua baris kode di atas dalam makro mereka sehingga mereka tidak perlu khawatir tentang masalah kecepatan dan kegunaan ini
Ini contohnya
Sub your_macro[]
'Turn everything off
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
'Code to run here
'Turn everything back on
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
_Catatan
Tautan di setiap bagian di atas memberikan lebih banyak informasi dan contoh serta dapat membantu Anda lebih memahami apa yang terjadi di makro
Di bawah ini Anda akan menemukan beberapa tip untuk mempercepat kode VBA Anda. Kiat diatur secara longgar berdasarkan kepentingan
Cara termudah untuk meningkatkan kecepatan kode VBA Anda adalah dengan menonaktifkan ScreenUpdating dan menonaktifkan Perhitungan Otomatis. Pengaturan ini harus dinonaktifkan di semua prosedur besar
Nonaktifkan Pembaruan Layar
Secara default, Excel akan menampilkan perubahan pada buku kerja secara real-time saat kode VBA berjalan. Hal ini menyebabkan penurunan besar dalam kecepatan pemrosesan karena Excel paling banyak menginterpretasikan dan menampilkan perubahan untuk setiap baris kode
Untuk mematikan Pembaruan Layar
Application.ScreenUpdating = False
_Di akhir makro Anda, Anda harus mengaktifkan kembali Pembaruan Layar
Application.ScreenUpdating = True
Saat kode Anda berjalan, Anda mungkin perlu "menyegarkan" layar. Tidak ada perintah "segarkan". Sebagai gantinya, Anda harus mengaktifkan Pembaruan Layar kembali dan menonaktifkannya lagi
Tetapkan Perhitungan ke Manual
Setiap kali nilai sel diubah, Excel harus mengikuti "pohon perhitungan" untuk menghitung ulang semua sel yang bergantung. Selain itu, setiap kali rumus diubah, Excel perlu memperbarui "pohon kalkulasi" selain menghitung ulang semua sel dependen. Bergantung pada ukuran buku kerja Anda, penghitungan ulang ini bisa menyebabkan makro Anda berjalan sangat lambat
Untuk mengatur Perhitungan ke Manual
Application.Calculation = xlManual
Untuk menghitung ulang seluruh buku kerja secara manual
Calculate
Perhatikan bahwa Anda juga dapat menghitung hanya satu lembar, rentang, atau sel individual, jika perlu untuk meningkatkan kecepatan
Untuk memulihkan Perhitungan Otomatis [di akhir prosedur Anda]
Application.Calculation = xlAutomatic
Penting. Ini adalah pengaturan Excel. Jika Anda tidak menyetel ulang penghitungan ke otomatis, buku kerja Anda tidak akan menghitung ulang hingga Anda memintanya
Anda akan melihat peningkatan terbesar dari pengaturan di atas, namun ada beberapa pengaturan lain yang dapat membuat perbedaan
Nonaktifkan Acara
Acara adalah "pemicu" yang menyebabkan prosedur acara khusus dijalankan. Contohnya termasuk. saat sel mana pun pada lembar kerja berubah, saat lembar kerja diaktifkan, saat buku kerja dibuka, sebelum buku kerja disimpan, dll.
Menonaktifkan acara bisa menyebabkan sedikit peningkatan kecepatan saat makro dijalankan, namun peningkatan kecepatan bisa jauh lebih besar jika buku kerja Anda menggunakan acara. Dan dalam beberapa kasus menonaktifkan acara diperlukan untuk menghindari pembuatan loop tanpa akhir
Untuk menonaktifkan acara
Application.EnableEvents = False
Untuk menghidupkan kembali acara
Application.EnableEvents = True
Nonaktifkan Hentian Halaman
Menonaktifkan PageBreaks dapat membantu dalam situasi tertentu
- Anda sebelumnya telah menyetel properti PageSetup untuk lembar kerja yang relevan dan prosedur VBA Anda mengubah properti dari banyak baris atau kolom
- ATAU Prosedur VBA Anda memaksa Excel untuk menghitung jeda halaman [menampilkan Pratinjau Cetak atau mengubah properti apa pun dari PageSetup]
Untuk menonaktifkan Hentian Halaman
ActiveSheet.DisplayPageBreaks = False
Untuk mengaktifkan kembali PageBreaks
ActiveSheet.DisplayPageBreaks = True
Praktik Terbaik untuk Meningkatkan Kecepatan VBA
Hindari Mengaktifkan dan Memilih
Saat Anda merekam Makro, Anda akan melihat banyak metode Aktifkan dan Pilih
Sub Slow_Example[]
Sheets["Sheet2"].Select
Range["D9"].Select
ActiveCell.FormulaR1C1 = "example"
Range["D12"].Select
ActiveCell.FormulaR1C1 = "demo"
Range["D13"].Select
End Sub
Mengaktifkan dan memilih objek biasanya tidak diperlukan, mereka menambah kekacauan pada kode Anda, dan sangat memakan waktu. Anda harus menghindari metode ini jika memungkinkan
Contoh yang Ditingkatkan
Application.ScreenUpdating = True
_0Hindari Menyalin dan Menempel
Menyalin membutuhkan memori yang signifikan. Sayangnya, Anda tidak dapat memberi tahu VBA untuk mengosongkan memori internal. Sebaliknya, Excel akan menghapus memori internalnya pada [tampaknya] interval tertentu. Jadi, jika Anda melakukan banyak operasi salin dan tempel, Anda berisiko memonopoli terlalu banyak memori, yang dapat memperlambat kode Anda secara drastis atau bahkan membuat Excel crash.
Alih-alih menyalin dan menempel, pertimbangkan untuk menyetel properti nilai sel
Application.ScreenUpdating = True
_1Gunakan Untuk Setiap loop, bukan Untuk Loops
Saat melakukan perulangan melalui objek, perulangan Untuk Setiap lebih cepat daripada perulangan Untuk. Contoh
Ini Untuk Loop
Application.ScreenUpdating = True
_2Lebih lambat dari ini Untuk Setiap Loop
Application.ScreenUpdating = True
_3Deklarasikan Variabel / Gunakan Opsi Eksplisit
VBA tidak mengharuskan Anda mendeklarasikan variabel Anda, kecuali jika Anda menambahkan Option Explicit ke bagian atas modul Anda
Application.ScreenUpdating = True
_4Menambahkan Opsi Eksplisit adalah praktik terbaik pengkodean karena mengurangi kemungkinan kesalahan. Ini juga memaksa Anda untuk mendeklarasikan variabel Anda, yang sedikit meningkatkan kecepatan kode Anda [manfaat lebih terlihat semakin banyak variabel digunakan]
Bagaimana Option Explicit mencegah kesalahan?
Manfaat terbesar dari Option Explicit adalah ini akan membantu Anda menemukan kesalahan pengejaan nama variabel. Misalnya, dalam contoh berikut kami menetapkan variabel bernama 'var1', tetapi nanti kami mereferensikan variabel bernama 'varl'. Variabel 'varl' belum ditentukan sehingga kosong, menyebabkan hasil yang tidak terduga
Application.ScreenUpdating = True
_5Gunakan Dengan – Akhiri Dengan Pernyataan
Jika Anda mereferensikan objek yang sama beberapa kali [mis. Ranges, Worksheets, Workbooks], pertimbangkan untuk menggunakan With Statement. Lebih cepat diproses, dapat membuat kode Anda lebih mudah dibaca, dan menyederhanakan kode Anda
Dengan contoh pernyataan
Application.ScreenUpdating = True
_6Lebih cepat dari
Application.ScreenUpdating = True
_7Kiat Praktik Terbaik Tingkat Lanjut
Lindungi Antarmuka Pengguna Saja
Merupakan praktik yang baik untuk melindungi lembar kerja Anda dari pengeditan sel yang tidak terlindungi untuk mencegah pengguna akhir [atau Anda. ] dari merusak buku kerja secara tidak sengaja. Namun, ini juga akan melindungi lembar kerja agar tidak mengizinkan VBA melakukan perubahan. Jadi, Anda harus membuka proteksi dan melindungi kembali lembar kerja, yang sangat memakan waktu jika dilakukan pada banyak lembar
Application.ScreenUpdating = True
_8Sebagai gantinya, Anda dapat memproteksi sheet dengan mengatur UserInterfaceOnly. = Benar. Ini memungkinkan VBA untuk membuat perubahan pada sheet, sambil tetap melindunginya dari pengguna
Application.ScreenUpdating = True
_9Penting. UserInterFaceOnly diatur ulang ke False setiap kali buku kerja dibuka. Jadi untuk menggunakan fitur luar biasa ini, Anda perlu menggunakan acara Workbook_Open atau Auto_Open untuk mengatur pengaturan setiap kali buku kerja dibuka
Tempatkan kode ini di modul Thisworkbook
Application.Calculation = xlManual
_0atau kode ini dalam modul reguler apa pun
Application.Calculation = xlManual
_1Gunakan Array untuk Mengedit Rentang Besar
Ini bisa sangat memakan waktu untuk memanipulasi rentang sel yang besar [Kel. 100.000+]. Alih-alih mengulang rentang sel, memanipulasi setiap sel, Anda dapat memuat sel ke dalam larik, memproses setiap item dalam larik, lalu mengeluarkan larik kembali ke sel aslinya. Memuat sel ke dalam array untuk manipulasi bisa lebih cepat
Application.Calculation = xlManual
_2Pengkodean VBA Menjadi Mudah
Berhenti mencari kode VBA online. Pelajari lebih lanjut tentang AutoMacro – Pembuat Kode VBA yang memungkinkan pemula untuk membuat kode prosedur dari awal dengan pengetahuan pengkodean minimal dan dengan banyak fitur hemat waktu untuk semua pengguna