10 Cara untuk Mempercepat Macro Anda - dummies

Karena makro Excel Anda menjadi semakin kuat dan kompleks, Anda mungkin mendapati bahwa mereka kehilangan performa. Saat membahas macro, kata performance biasanya sama dengan speed . Kecepatan adalah seberapa cepat prosedur VBA Anda melakukan tugas yang mereka inginkan. Berikut adalah sepuluh cara untuk membantu agar makro Excel Anda tetap berjalan pada tingkat kinerja optimalnya.

Menghentikan perhitungan lembar

Tahukah Anda bahwa setiap kali sel yang mempengaruhi formula di spreadsheet Anda diubah atau dimanipulasi, Excel menghitung ulang seluruh lembar kerja? Dalam lembar kerja yang memiliki sejumlah besar formula, perilaku ini dapat secara drastis memperlambat makro Anda.

Anda bisa menggunakan Aplikasi. Perhitungan properti untuk memberitahu Excel untuk beralih ke mode perhitungan manual. Saat buku kerja dalam mode perhitungan manual, buku kerja tidak akan menghitung ulang sampai Anda secara eksplisit memicu perhitungan dengan menekan tombol F9.

Tempatkan Excel ke dalam mode perhitungan manual, jalankan kode Anda, dan kemudian beralih kembali ke mode perhitungan otomatis.

Aplikasi Sub Macro1 (). Perhitungan = xlCalculationManual 'Tempatkan kode makro Anda di sini Aplikasi. Perhitungan = xlCalculationAutomatic End Sub

Mengatur mode perhitungan kembali ke xlCalculationAutomatic secara otomatis akan memicu perhitungan ulang lembar kerja, jadi tidak perlu menekan tombol F9 setelah makro Anda berjalan.

Menonaktifkan pemutakhiran layar layar

Anda mungkin memperhatikan bahwa saat makro Anda berjalan, layar Anda cukup berkedip. Berkedip-kedip ini adalah Excel mencoba untuk menggambar ulang layar untuk menunjukkan kondisi lembar kerja saat ini. Sayangnya, setiap kali Excel menggandakan layar, dibutuhkan sumber daya memori.

Anda bisa menggunakan Aplikasi. ScreenUpdating properti untuk menonaktifkan pembaruan layar sampai makro Anda selesai. Menonaktifkan pembaruan layar menghemat waktu dan sumber daya, membiarkan makro Anda berjalan sedikit lebih cepat. Setelah kode makro Anda selesai berjalan, Anda dapat menghidupkan kembali pemutakhiran layar.

Aplikasi Sub Macro1 (). Perhitungan = xlCalculationManual Application. ScreenUpdating = Salah 'Tempatkan kode makro anda disini Aplikasi. Perhitungan = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Setelah Anda menyetel properti ScreenUpdating kembali ke True, Excel secara otomatis akan memicu redraw layar.

Mematikan pembaruan status bar

Bilah status Excel, yang muncul di bagian bawah jendela Excel, biasanya menampilkan kemajuan tindakan tertentu di Excel.Jika makro Anda bekerja dengan banyak data, bilah status akan mengambil beberapa sumber.

Penting untuk dicatat bahwa mematikan pemutaran layar terpisah dari mematikan tampilan status bar. Status bar akan terus diperbarui meskipun Anda menonaktifkan pemutakhiran layar. Anda bisa menggunakan Aplikasi. Properti DisplayStatusBar untuk sementara menonaktifkan pembaruan status bar, semakin meningkatkan kinerja makro Anda:

Aplikasi Sub Macro1 (). Perhitungan = xlCalculationManual Application. ScreenUpdating = Aplikasi Salah. DisplayStatusBar = Salah 'Tempatkan kode makro Anda di sini Aplikasi. Perhitungan = xlCalculationAutomatic Application. ScreenUpdating = Aplikasi Benar DisplayStatusBar = True End Sub

Mengatakan Excel untuk mengabaikan kejadian

Anda dapat menerapkan makro sebagai prosedur acara, memberi tahu Excel untuk menjalankan kode tertentu saat lembar kerja atau buku kerja berubah.

Terkadang, macro standar membuat perubahan yang akan memicu prosedur acara. Misalnya, jika Anda memiliki makro standar yang memanipulasi beberapa sel di Sheet1, setiap kali sel pada lembar itu diubah, makro Anda harus berhenti sementara saat acara Worksheet_Change berjalan.

Anda dapat menambahkan tingkat peningkatan kinerja lainnya dengan menggunakan properti EnableEvents untuk memberi tahu Excel untuk mengabaikan kejadian saat makro Anda berjalan.

Tetapkan properti EnableEvents ke False sebelum menjalankan makro Anda. Setelah kode makro selesai dijalankan, Anda dapat mengatur properti EnableEvents kembali ke True.

Aplikasi Sub Macro1 (). Perhitungan = xlCalculationManual Application. ScreenUpdating = Aplikasi Salah. DisplayStatusBar = Aplikasi Salah. EnableEvents = False 'Tempatkan kode makro Anda di sini Aplikasi. Perhitungan = xlCalculationAutomatic Application. ScreenUpdating = Aplikasi Benar DisplayStatusBar = Aplikasi yang Benar EnableEvents = True End Sub

Menyembunyikan jeda halaman

Setiap kali makro Anda memodifikasi jumlah baris, mengubah jumlah kolom, atau mengubah penyiapan halaman dari lembar kerja, Excel dipaksa untuk mengambil waktu untuk menghitung ulang jeda halaman yang ditunjukkan. di atas lembaran

Anda dapat menghindari perilaku ini hanya dengan menyembunyikan jeda halaman sebelum memulai makro Anda.

Setel properti lembar DisplayPageBreaks ke False untuk menyembunyikan jeda halaman. Jika Anda ingin terus menampilkan jeda halaman setelah makro berjalan, atur properti lembar DisplayPageBreaks kembali ke True.

Aplikasi Sub Macro1 (). Perhitungan = xlCalculationManual Application. ScreenUpdating = Aplikasi Salah. DisplayStatusBar = Aplikasi Salah. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Tempatkan kode makro Anda di sini Aplikasi. Perhitungan = xlCalculationAutomatic Application. ScreenUpdating = Aplikasi Benar DisplayStatusBar = Aplikasi yang Benar EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Menangguhkan update tabel pivot

Jika makro Anda memanipulasi tabel pivot yang berisi sumber data yang besar, Anda mungkin mengalami kinerja buruk saat melakukan hal-hal seperti menambahkan atau memindahkan bidang pivot secara dinamis.

Anda dapat memperbaiki kinerja makro Anda dengan menunda penghitungan ulang tabel pivot sampai semua perubahan bidang pivot telah dilakukan. Cukup atur PivotTable. ManualUpdate properti ke True untuk menunda penghitungan ulang, jalankan kode makro Anda, dan kemudian atur PivotTable. Properti ManualUpdate kembali ke False untuk memicu penghitungan ulang.

Sub Macro1 () ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = True 'Tempatkan kode makro Anda di sini ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = Sub Akhir yang Salah

Steering clear of copy and paste

Penting untuk diingat bahwa meskipun Macro Recorder menghemat waktu dengan menulis kode VBA untuk Anda, kode tersebut tidak selalu menulis kode yang paling efisien. Contoh utama adalah bagaimana Macro Recorder menangkap tindakan copy-dan-paste yang Anda lakukan saat merekam.

Anda dapat memberi dorongan pada makro Anda dengan memotong perantara dan melakukan salinan langsung dari satu sel ke sel tujuan. Kode alternatif ini menggunakan argumen Tujuan untuk memotong clipboard dan menyalin isi sel A1 langsung ke sel B1. Rentang

("A1"). Copy Destination: = Range ("B1")

Jika Anda hanya perlu menyalin nilai (bukan format atau formula), Anda dapat meningkatkan kinerja lebih banyak lagi dengan menghindari metode Copy bersama-sama. Cukup atur nilai sel tujuan ke nilai yang sama yang ditemukan di sel sumber. Metode ini kira-kira 25 kali lebih cepat daripada menggunakan metode Copy:

Range ("B1"). Nilai = Rentang ("A1"). Nilai

Jika Anda hanya perlu menyalin formula dari satu sel ke sel lainnya (bukan nilai atau format), Anda dapat mengatur rumus sel tujuan ke formula yang sama yang terdapat dalam sel sumber: Range 99 (991)). Formula = Rentang ("A1"). Formula

Dengan menggunakan pernyataan Dengan

Saat merekam makro, Anda akan sering memanipulasi objek yang sama lebih dari satu kali. Anda dapat menghemat waktu dan meningkatkan kinerja dengan menggunakan pernyataan With untuk melakukan beberapa tindakan pada objek tertentu dalam satu tembakan.

Pernyataan dengan yang digunakan dalam contoh berikut memberitahu Excel untuk menerapkan semua perubahan format sekaligus:

With Range ("A1"). Font. Bold = Benar Italic = Benar Underline = xlUnderlineStyleSingle End With

Masuk ke kebiasaan tindakan chunking ke dalam Dengan pernyataan tidak hanya akan membuat makro Anda berjalan lebih cepat namun juga memudahkan untuk membaca kode makro Anda.

Menghindari metode Select

Macro Recorder menyukai penggunaan metode Select untuk memilih objek secara eksplisit sebelum melakukan tindakan terhadapnya. Umumnya tidak perlu memilih objek sebelum bekerja dengan mereka. Sebenarnya, Anda dapat secara dramatis memperbaiki kinerja makro dengan tidak menggunakan metode Pilih.

Setelah merekam makro Anda, biasakan mengubah kode yang dihasilkan untuk menghapus metode Pilih. Dalam kasus ini, kode yang dioptimalkan akan terlihat seperti berikut:

Sheets ("Sheet1"). Rentang ("A1"). FormulaR1C1 = "1000" Lembar ("Lembar2"). Rentang ("A1"). FormulaR1C1 = "1000" Lembar ("Lembar3"). Rentang ("A1"). FormulaR1C1 = "1000"

Perhatikan bahwa tidak ada yang dipilih.Kode hanya menggunakan hirarki objek untuk menerapkan tindakan yang dibutuhkan.

Membatasi perjalanan ke lembar kerja

Cara lain untuk mempercepat makro Anda adalah dengan membatasi jumlah data lembar kerja referensi Anda di kode Anda. Selalu kurang efisien untuk mengambil data dari lembar kerja daripada dari memori. Artinya, makro Anda akan berjalan lebih cepat jika tidak harus berulang kali berinteraksi dengan lembar kerja.

Misalnya, kode sederhana berikut memaksa VBA untuk terus kembali ke Lembar ("Sheet1"). Range ("A1") untuk mendapatkan jumlah yang dibutuhkan untuk perbandingan yang dilakukan dalam pernyataan If:

Untuk ReportMonth = 1 To 12 If Range ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End Jika Next ReportMonth

Metode yang jauh lebih efisien adalah dengan menyimpan nilai pada Sheets ("Sheet1"). Range ("A1") ke variabel yang disebut MyMonth. Dengan cara ini, kode tersebut mereferensi variabel MyMonth dan bukan lembar kerja:

Dim MyMonth sebagai Integer MyMonth = Range ("A1"). Nilai Untuk ReportMonth = 1 To 12 Jika MyMonth = ReportMonth Kemudian MsgBox 1000000 / ReportMonth End If Next ReportMonth

Pertimbangkan untuk memanfaatkan variabel untuk bekerja dengan data di memori dibandingkan dengan lembar kerja referensi langsung.