Isi kandungan:
- Perhitungan helai halting
- Melumpuhkan pengemaskinian skrin kunci
- Mematikan kemas kini bar status
- Memberitahu Excel untuk mengabaikan peristiwa
- Menyembunyikan pemotongan halaman
- Menghidupkan jadual pivot
- Menjahit salinan dan tampalan
- Menggunakan kenyataan Dengan
- Mengelakkan kaedah Pilih
- Mengehadkan perjalanan ke lembaran kerja
Video: Tips Membuat Fungsi Terbilang Koma 2024
Kerana makro Excel anda menjadi semakin kuat dan kompleks, anda mungkin mendapati bahawa mereka kehilangan prestasi. Apabila membincangkan makro, perkataan performance biasanya sama dengan kelajuan . Kelajuan adalah berapa cepat prosedur VBA anda melaksanakan tugas yang dimaksudkan. Berikut adalah sepuluh cara untuk membantu memastikan macro Excel anda berjalan pada tahap prestasi optimum mereka.
Perhitungan helai halting
Adakah anda tahu bahawa setiap sel yang memberi kesan kepada formula apa saja dalam spreadsheet anda ditukar atau dimanipulasi, Excel mengira semula keseluruhan lembaran kerja? Dalam lembaran kerja yang mempunyai banyak formula, perilaku ini secara drastik boleh melambatkan makro anda.
Anda boleh menggunakan Aplikasi ini. Pengiraan harta untuk memberitahu Excel untuk beralih ke mod pengiraan manual. Apabila buku kerja berada dalam mod pengiraan manual, buku kerja tidak akan dikira semula sehingga anda secara eksplisit mencetuskan pengiraan dengan menekan kekunci F9.
Letakkan Excel dalam mod pengiraan manual, jalankan kod anda, dan kemudian beralih ke mod pengiraan automatik.
Sub Macro1 () Permohonan. Pengiraan = xlCalculationManual 'Letakkan kod makro anda di sini Permohonan. Perhitungan = xlKalkulasian Sub Akhir Endapan
Menetapkan mod pengiraan semula ke xlKalkulasiAutomatik secara automatik akan mencetuskan pengiraan semula lembaran kerja, jadi tidak perlu menekan kekunci F9 selepas berjalan makro anda.
Melumpuhkan pengemaskinian skrin kunci
Anda mungkin dapati bahawa apabila makro anda berjalan, skrin anda akan menghasilkan sedikit kelipan. Kelipan ini adalah Excel yang cuba menggambar semula skrin untuk menunjukkan keadaan semasa lembaran kerja. Malangnya, setiap kali Excel mengubah skrin, ia mengambil sumber memori.
Anda boleh menggunakan Aplikasi ini. Property ScreenUpdating untuk melumpuhkan kemas kini skrin sehingga makro anda selesai. Melumpuhkan pengemaskinian skrin menjimatkan masa dan sumber, membolehkan makro anda berjalan sedikit lebih cepat. Setelah kod makro anda selesai berjalan, anda boleh menghidupkan skrin mengemas kini semula.
Sub Macro1 () Permohonan. Pengiraan = xlCalculationManual Application. ScreenUpdating = False 'Letakkan kod makro anda di sini Permohonan. Pengiraan = xlCalculationAutomatic Application. ScreenUpdating = Sub End True
Setelah anda menetapkan property ScreenUpdating kembali ke Benar, Excel secara otomatis akan mencetuskan redraw layar.
Mematikan kemas kini bar status
Bar status Excel, yang muncul di bahagian bawah tetingkap Excel, biasanya memaparkan kemajuan tindakan tertentu dalam Excel.Jika makro anda berfungsi dengan banyak data, bar status akan mengambil beberapa sumber.
Penting untuk diperhatikan bahawa mematikan pengemaskinian skrin berasingan daripada mematikan paparan status bar. Bar status akan terus dikemas kini walaupun anda melumpuhkan pengemaskinian skrin. Anda boleh menggunakan Aplikasi ini. Hartanah DisplayStatusBar untuk melumpuhkan sementara sebarang kemas kini status status, terus meningkatkan prestasi makro anda: Aplikasi
Sub Macro1 (). Pengiraan = xlCalculationManual Application. ScreenUpdating = Permohonan Palsu. DisplayStatusBar = Palsu 'Letakkan kod makro anda di sini Permohonan. Pengiraan = xlCalculationAutomatic Application. ScreenUpdating = Permohonan Benar. DisplayStatusBar = Sub Akhir Sub
Memberitahu Excel untuk mengabaikan peristiwa
Anda boleh melaksanakan makro sebagai prosedur acara, memberitahu Excel untuk menjalankan kod tertentu apabila lembaran kerja atau buku kerja berubah.
Kadangkala, makro standard membuat perubahan yang akan mencetuskan prosedur peristiwa. Contohnya, jika anda mempunyai makro standard yang memanipulasi beberapa sel pada Sheet1, setiap kali sel pada helaian itu ditukar, makro anda perlu berhenti sementara acara Worksheet_Change dijalankan.
Anda boleh menambah tahap meningkatkan prestasi dengan menggunakan ciri EnableEvents untuk memberitahu Excel untuk mengabaikan peristiwa semasa makro anda berjalan.
Tetapkan harta EnableEvents ke False sebelum menjalankan makro anda. Setelah kod makro anda selesai berjalan, anda boleh menetapkan hak milik EnableEvents kembali ke Benar.
Sub Macro1 () Permohonan. Pengiraan = xlCalculationManual Application. ScreenUpdating = Permohonan Palsu. DisplayStatusBar = Permohonan Palsu. EnableEvents = False 'Letakkan kod makro anda di sini Permohonan. Pengiraan = xlCalculationAutomatic Application. ScreenUpdating = Permohonan Benar. DisplayStatusBar = Permohonan Benar. EnableEvents = Sub End True
Menyembunyikan pemotongan halaman
Setiap kali makro anda mengubah bilangan baris, mengubah bilangan lajur, atau mengubah persediaan halaman lembaran kerja, Excel terpaksa mengambil masa mengira semula rehat halaman yang ditunjukkan pada helaian.
Anda boleh mengelakkan kelakuan ini dengan hanya menyembunyikan rehat halaman sebelum memulakan makro anda.
Tetapkan sifat helaian DisplayPageBreaks ke Palsu untuk menyembunyikan pecahan halaman. Jika anda mahu terus memaparkan rehat halaman selepas berjalan makro anda, tetapkan kembali butang helaian DisplayPageBreaks ke Benar.
Sub Macro1 () Permohonan. Pengiraan = xlCalculationManual Application. ScreenUpdating = Permohonan Palsu. DisplayStatusBar = Permohonan Palsu. EnableEvents = Activesheet Palsu. DisplayPageBreaks = False 'Letakkan kod makro anda di sini Permohonan. Pengiraan = xlCalculationAutomatic Application. ScreenUpdating = Permohonan Benar. DisplayStatusBar = Permohonan Benar. EnableEvents = Activesheet Benar. DisplayPageBreaks = Sub Pembaharuan Sub
Menghidupkan jadual pivot
Jika makro anda memanipulasi jadual pivot yang mengandungi sumber data yang besar, anda mungkin mengalami prestasi yang kurang baik apabila melakukan perkara seperti menambahkan medan pivot secara dinamik atau bergerak.
Anda boleh meningkatkan prestasi makro anda dengan menggantung pengiraan semula jadual pivot sehingga semua perubahan medan pivot telah dibuat. Cukup tetapkan PivotTable. ManualUpdate property to True untuk menangguhkan pengiraan semula, jalankan kod makro anda, dan kemudian tetapkan PivotTable. ManualUpdate property kembali ke Palsu untuk mencetuskan pengiraan semula.
Sub Macro1 () ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = Benar 'Letakkan kod makro anda di sini ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = Sub Ujian Salah
Menjahit salinan dan tampalan
Penting untuk diingat bahawa walaupun Perakam Makro menjimatkan masa dengan menulis kod VBA untuk anda, ia tidak selalu menulis kod yang paling berkesan. Contoh utama adalah bagaimana Perekam Makro menangkap sebarang tindakan salin dan tampal yang anda lakukan semasa merakam.
Anda boleh memberikan makro anda sedikit dorongan dengan memotong perantara dan melakukan salinan langsung dari satu sel ke sel destinasi. Kod alternatif ini menggunakan Argumen destinasi untuk memintas papan klip dan menyalin kandungan sel A1 terus ke sel B1.
Julat ("A1"). Salin Destinasi: = Julat ("B1")
Jika anda perlu menyalin nilai-nilai sahaja (tidak memformat atau formula), anda boleh meningkatkan prestasi dengan lebih banyak lagi dengan mengelakkan kaedah Copy semua bersama-sama. Cukup tetapkan nilai sel destinasi ke nilai yang sama yang terdapat dalam sel sumber. Kaedah ini kira-kira 25 kali lebih cepat daripada menggunakan kaedah Salin:
Julat ("B1"). Nilai = Julat ("A1"). Nilai
Jika anda perlu menyalin formula sahaja dari satu sel ke yang lain (bukan nilai atau pemformatan), anda boleh menetapkan formula sel destinasi kepada formula yang sama yang terdapat dalam sel sumber:
Range ("B1"). Formula = Range ("A1"). Formula
Menggunakan kenyataan Dengan
Apabila merakam makro, anda akan sering memanipulasi objek yang sama lebih daripada satu kali. Anda boleh menjimatkan masa dan meningkatkan prestasi dengan menggunakan pernyataan Bersama untuk melakukan beberapa tindakan pada objek yang diberikan dalam satu pukulan.
Kenyataan Dalam yang digunakan dalam contoh berikut memberitahu Excel untuk memohon semua perubahan format pada satu masa:
Dengan Julat ("A1"). Fon. Bold = Benar. Italic = Benar. Underline = xlUnderlineStyleSingle End With
Masuk ke kebiasaan tindakan chunking ke Dengan pernyataan tidak hanya akan membuat makro anda berjalan dengan lebih cepat tetapi juga membuat lebih mudah untuk membaca kod makro anda.
Mengelakkan kaedah Pilih
Perekam Makro suka menggunakan kaedah Pilih untuk memilih objek secara jelas sebelum mengambil tindakan ke atasnya. Pada umumnya tidak perlu memilih objek sebelum bekerja dengan mereka. Sebenarnya, anda boleh meningkatkan prestasi makro secara dramatik dengan tidak menggunakan kaedah Pilih.
Selepas merakam makro anda, menjadikannya tabiat untuk mengubah kod yang dijana untuk menghapuskan kaedah Pilih. Dalam kes ini, kod yang dioptimumkan akan kelihatan seperti berikut:
Helaian ("Sheet1"). Julat ("A1"). FormulaR1C1 = "1000" Helaian ("Sheet2"). Julat ("A1"). FormulaR1C1 = "1000" Helaian ("Sheet3"). Julat ("A1"). FormulaR1C1 = "1000"
Perhatikan bahawa tiada apa yang sedang dipilih.Kod ini hanya menggunakan hierarki objek untuk memohon tindakan yang diperlukan.
Mengehadkan perjalanan ke lembaran kerja
Cara lain untuk mempercepat makro anda adalah untuk mengehadkan jumlah kali anda mencantumkan data lembaran kerja dalam kod anda. Ia sentiasa kurang cekap untuk mengambil data dari lembaran kerja daripada dari ingatan. Maksudnya, makro anda akan berjalan lebih cepat jika mereka tidak perlu berulang kali berinteraksi dengan lembaran kerja.
Sebagai contoh, kod mudah berikut memaksa VBA untuk terus kembali ke Helaian ("Sheet1"). Julat ("A1") untuk mendapatkan nombor yang diperlukan untuk perbandingan yang dilakukan dalam pernyataan Jika:
Untuk ReportMonth = 1 hingga 12 Jika Julat ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth
Cara yang jauh lebih efisien adalah untuk menyimpan nilai dalam Sheet ("Sheet1"). Julat ("A1") kepada pembolehubah yang dipanggil MyMonth. Dengan cara ini, kod tersebut merujuk pembolehubah MyMonth dan bukannya lembaran kerja:
Dim MyMonth sebagai Integer MyMonth = Range ("A1"). Nilai Untuk LaporanMonth = 1 Ke 12 Jika MyMonth = ReportMonth Kemudian MsgBox 1000000 / ReportMonth Akhir Jika Laporan SeterusnyaMemberi
Pertimbangkan untuk menggunakan pembolehubah untuk bekerja dengan data dalam ingatan berbanding dengan lembaran kerja rujukan secara langsung.
