Membuat excel vba berjalan lebih cepat

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

Hindari 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

Gunakan 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

Lebih lambat dari ini Untuk Setiap Loop

Application.ScreenUpdating = True

Deklarasikan 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

Menambahkan 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

Gunakan 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

Lebih cepat dari

Application.ScreenUpdating = True

Kiat 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

Sebagai 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

Penting. 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

atau kode ini dalam modul reguler apa pun

Application.Calculation = xlManual

Gunakan 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

Pengkodean 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

Mengapa makro VBA saya sangat lambat?

Bagaimana cara mengoptimalkan Excel VBA?

Bagaimana cara mempercepat loop di VBA?

Apakah VBA lebih cepat dari formula?