Bu öğretici, VBA Makrolarının ve diğer VBA en iyi uygulamalarının nasıl hızlandırılacağını tartışacaktır.
VBA Kodunu Hızlandırma Ayarları
Aşağıda VBA kodunuzu hızlandırmak için birkaç ipucu bulacaksınız. İpuçları, önem derecesine göre gevşek bir şekilde düzenlenmiştir.
VBA kodunuzun hızını artırmanın en kolay yolu, ScreenUpdating'i devre dışı bırakmak ve Otomatik Hesaplamaları devre dışı bırakmaktır. Bu ayarlar tüm büyük prosedürlerde devre dışı bırakılmalıdır.
Ekran Güncellemeyi Devre Dışı Bırak
Varsayılan olarak Excel, çalışma kitaplarındaki değişiklikleri VBA kodu çalışırken gerçek zamanlı olarak görüntüler. Excel, her kod satırı için değişiklikleri yorumlayıp görüntülediğinden, bu işlem hızında büyük bir yavaşlamaya neden olur.
Ekran Güncellemeyi kapatmak için:
| 1 | Application.ScreenUpdating = Yanlış |
Makronuzun sonunda Ekran Güncellemeyi tekrar açmalısınız:
| 1 | Application.ScreenUpdating = Doğru |
Kodunuz çalışırken ekranı “yenilemeniz” gerekebilir. “Yenile” komutu yoktur. Bunun yerine, Ekran Güncellemeyi tekrar açmanız ve tekrar devre dışı bırakmanız gerekecektir.
Hesaplamaları Manuel Olarak Ayarla
Bir hücre değeri değiştirildiğinde, Excel'in tüm bağımlı hücreleri yeniden hesaplamak için "hesaplama ağacını" izlemesi gerekir. Ek olarak, bir formül değiştirildiğinde, Excel'in tüm bağımlı hücreleri yeniden hesaplamanın yanı sıra "hesaplama ağacını" da güncellemesi gerekecektir. Çalışma kitabınızın boyutuna bağlı olarak, bu yeniden hesaplamalar makrolarınızın makul olmayan şekilde yavaş çalışmasına neden olabilir.
Hesaplamaları Manuel olarak ayarlamak için:
| 1 | Application.Calculation = xlManual |
Tüm çalışma kitabını manuel olarak yeniden hesaplamak için:
| 1 | Hesaplamak |
Ayrıca, daha yüksek hız için gerekirse yalnızca bir sayfa, aralık veya tek hücre hesaplayabileceğinizi unutmayın.
Otomatik Hesaplamaları geri yüklemek için (prosedürünüzün sonunda):
| 1 | Uygulama.Hesaplama = xlOtomatik |
Önemli! Bu bir Excel ayarıdır. Hesaplamaları otomatik olarak yeniden ayarlamazsanız, çalışma kitabınız siz söyleyinceye kadar yeniden hesaplamayacaktır.
Yukarıdaki ayarlardan en büyük iyileştirmeleri göreceksiniz, ancak fark yaratabilecek birkaç ayar daha var:
Etkinlikleri Devre Dışı Bırak
Olaylar, özel durumlara neden olan “tetikleyicilerdir”. olay prosedürleri koşmak. Örnekler şunları içerir: çalışma sayfasındaki herhangi bir hücre değiştiğinde, çalışma sayfası etkinleştirildiğinde, çalışma kitabı açıldığında, çalışma kitabı kaydedilmeden önce vb.
Olayları devre dışı bırakmak, herhangi bir makro çalıştığında küçük hız iyileştirmelerine neden olabilir, ancak çalışma kitabınız olaylar kullanıyorsa hız iyileştirmesi çok daha büyük olabilir. Ve bazı durumlarda, sonsuz döngüler oluşturmaktan kaçınmak için olayları devre dışı bırakmak gerekir.
Olayları devre dışı bırakmak için:
| 1 | Application.EnableEvents = Yanlış |
Etkinlikleri tekrar açmak için:
| 1 | Application.EnableEvents = Doğru |
Sayfa Sonlarını Devre Dışı Bırak
PageBreaks'i devre dışı bırakmak belirli durumlarda yardımcı olabilir:
- İlgili çalışma sayfası için daha önce bir PageSetup özelliği ayarladınız ve VBA prosedürünüz birçok satırın veya sütunun özelliklerini değiştirir
- VEYA VBA prosedürünüz Excel'i sayfa sonlarını hesaplamaya zorlar (Baskı Önizleme'yi görüntüleme veya PageSetup'ın herhangi bir özelliğini değiştirme).
Sayfa Sonlarını devre dışı bırakmak için:
| 1 | ActiveSheet.DisplayPageBreaks = Yanlış |
Sayfa Sonlarını yeniden etkinleştirmek için:
| 1 | ActiveSheet.DisplayPageBreaks = Doğru |
VBA Hızını Artırmak için En İyi Uygulamalar
Etkinleştirmekten ve Seçmekten Kaçının
Bir Makro kaydettiğinizde, birçok Etkinleştir ve Seç yöntemi göreceksiniz:
| 12345678 | Alt Yavaş_Örnek()Sayfalar("Sayfa2").SeçinAralık("D9").SeçActiveCell.FormulaR1C1 = "örnek"Aralık("D12").SeçActiveCell.FormulaR1C1 = "demo"Aralık("D13").SeçinAlt Bitiş |
Nesneleri etkinleştirmek ve seçmek genellikle gereksizdir, kodunuza karışıklık katarlar ve çok zaman alırlar. Mümkün olduğunda bu yöntemlerden kaçınmalısınız.
Geliştirilmiş Örnek:
| 1234 | Alt Hızlı_Örnek()Sayfalar("Sayfa2").Range("D9").FormulaR1C1 = "örnek"Sayfalar("Sayfa2").Range("D12").FormulaR1C1 = "demo"Alt Bitiş |
Kopyalamak ve Yapıştırmaktan Kaçının
Kopyalama önemli miktarda bellek gerektirir. Ne yazık ki, VBA'ya dahili belleği temizlemesini söyleyemezsiniz. Bunun yerine Excel, (görünüşte) belirli aralıklarla dahili belleğini temizleyecektir. Bu nedenle, birçok kopyala ve yapıştır işlemi gerçekleştirirseniz, kodunuzu önemli ölçüde yavaşlatabilecek ve hatta Excel'in çökmesine neden olabilecek çok fazla bellek tüketme riskiyle karşı karşıya kalırsınız.
Kopyalayıp yapıştırmak yerine hücrelerin değer özelliklerini ayarlamayı düşünün.
| 123456789 | Alt KopyalaYapıştır()'YavaşAralık("a1:a1000").Aralığı Kopyala("b1:b1000")'Daha hızlıAralık("b1:b1000").Değer = Aralık("a1:a1000").DeğerAlt Bitiş |
For Loops yerine For Every döngülerini kullanın
Nesneler arasında döngü yapılırken For Her döngüsü, For Döngüsü'nden daha hızlıdır. Örnek:
Bu Döngü İçin:
| 123456 | Alt Döngü1()Menzil olarak dim ii = 1 ila 100 içinHücreler(i, 1).Değer = 1sonraki benAlt Bitiş |
| 123456 | Alt Döngü2()Aralık olarak hücreyi karartAralıktaki Her Hücre İçin("a1:a100")hücre.Değer = 1Sonraki hücreAlt Bitiş |
Değişkenleri Bildir / Seçeneği Açık Kullan
Modülünüzün üstüne Option Explicit eklemediğiniz sürece VBA, değişkenlerinizi bildirmenizi gerektirmez:| 1 | Seçenek Açık |
| 1234 | Alt SeçenekExplicit()var1 = 10MsgBox değişkeniAlt Bitiş |
Birlikte Kullan - İfadelerle Bitir
Aynı nesnelere birden çok kez başvurursanız (ör. Aralıklar, Çalışma Sayfaları, Çalışma Kitapları), With İfadesini kullanmayı düşünün. İşlenmesi daha hızlıdır, kodunuzun okunmasını kolaylaştırabilir ve kodunuzu basitleştirir.Açıklama örneği ile:| 12345678 | Alt Faster_Example()Sayfalarla ("Sayfa2").Range("D9").FormulaR1C1 = "örnek".Range("D12").FormulaR1C1 = "demo".Range("D9").Font.Bold = Doğru.Range("D12").Font.Bold = Doğruİle bitmekAlt Bitiş |
| 123456 | Alt Yavaş_Örnek()Sayfalar("Sayfa2").Range("D9").FormulaR1C1 = "örnek"Sayfalar("Sayfa2").Range("D12").FormulaR1C1 = "demo"Sheets("Sayfa2").Range("D9").Font.Bold = TrueSheets("Sayfa2").Range("D12").Font.Bold = TrueAlt Bitiş |
Gelişmiş En İyi Uygulama İpuçları
Yalnızca Kullanıcı Arayüzünü Koru
Son kullanıcının (veya sizin!) yanlışlıkla çalışma kitabını bozmasını önlemek için çalışma sayfalarınızı korumasız hücreleri düzenlemekten korumak iyi bir uygulamadır. Ancak bu, çalışma sayfalarını VBA'nın değişiklik yapmasına izin vermekten de koruyacaktır. Bu nedenle, birçok sayfada yapıldığında çok zaman alan çalışma sayfalarının korumasını kaldırmanız ve yeniden korumanız gerekir.
| 12345 | Alt UnProtectSheet()Sayfalar(“sayfa1”).Korumayı kaldır ”şifre”'Sayfa1'i DüzenleSayfalar ("sayfa1"). "Parolayı" koruyunAlt Bitiş |
Bunun yerine, UserInterfaceOnly:=True ayarıyla sayfaları koruyabilirsiniz. Bu, VBA'nın sayfaları kullanıcıdan korurken sayfalarda değişiklik yapmasına izin verir.
| 1 | Sheets(“sheet1”).Protect Password:="password", UserInterFaceOnly:=True |
Önemli! UserInterFaceOnly, çalışma kitabı her açıldığında False değerine sıfırlanır. Bu harika özelliği kullanmak için, çalışma kitabı her açıldığında ayarı ayarlamak için Workbook_Open veya Auto_Open olaylarını kullanmanız gerekecek.
Bu kodu Thisworkbook modülüne yerleştirin:
| 123456 | Özel Alt Çalışma Kitabı_Open()Dim ws As Çalışma SayfasıÇalışma Sayfalarında Her ws içinws.Protect Parola:="parola", UserInterFaceOnly:=TrueSonrakiAlt Bitiş |
veya herhangi bir normal modüldeki bu kod:
| 123456 | Özel Alt Otomatik_Aç()Dim ws As Çalışma SayfasıÇalışma Sayfalarında Her ws içinws.Protect Parola:="parola", UserInterFaceOnly:=TrueSonrakiAlt Bitiş |
Büyük Aralıkları Düzenlemek için Dizileri Kullanın
Geniş hücre aralıklarını işlemek çok zaman alabilir (Ör. 100.000+). Hücre aralıkları arasında dolaşmak, her bir hücreyi işlemek yerine, hücreleri bir diziye yükleyebilir, dizideki her bir öğeyi işleyebilir ve ardından diziyi orijinal hücrelerine geri gönderebilirsiniz. Hücreleri manipülasyon için dizilere yüklemek çok daha hızlı olabilir.
| 1234567891011121314151617181920212223242526272829303132 | Alt DöngüRange()Aralık olarak hücreyi karartDim tÇift Olarak BaşlatStart = ZamanlayıcıAralıktaki Her Hücre İçin("A1:A100000")hücre.Değer = hücre.Değer * 100Sonraki hücreDebug.Print (Zamanlayıcı - tStart) & "saniye"Alt BitişAlt LoopArray()Varyant Olarak Dim arrÖğeyi Varyant Olarak KarartDim tÇift Olarak BaşlatStart = Zamanlayıcıarr = Aralık("A1:A100000").Değerarr'daki her öğe içinöğe = öğe * 100Sıradaki eşyaAralık("A1:A100000").Değer = arrDebug.Print (Zamanlayıcı - tStart) & "saniye"Alt Bitiş |
