• DİKKAT !

    Forum içeriğine ve tüm hizmetlerimize erişim sağlamak için foruma kayıt olmalı ya da giriş yapmalısınız. Foruma üye olmak Dosya Yükleme tamamen ücretsizdir.

    Giriş yap veya Şimdi kayıt ol

Çözüldü 50 Mb Üzeri Excel Dosyalarında VBA Makrolarının Bellek Tüketimi (Out of Memory) ve Şişme Sorunu

Bu konu çözüldü olarak işaretlenmiştir. Çözülmediğini düşünüyorsanız konuyu rapor edebilirsiniz.
Durum
Konu Çözümlendiği İçin Kapatılmıştır.
bark 3

bark

Yeni Üye
Katılım
2 Kas 2022
Mesajlar
4
Aldığı beğeni
1
Katkı
%0.00
Excel V
Office 2021 TR
Konu Sahibi
Merhaba hocalarım, şirket içinde kullandığımız ve boyutu 60 MB'ı bulan çok yoğun formüllü ve veri yüklü bir ana dosyamız var. Bu dosya üzerinde çalışan bazı büyük VBA döngülerimiz var (yaklaşık 800 bin satırı tarayıp rapor üretiyor). Makro çalışmaya başladıktan 3-4 dakika sonra Excel doğrudan 'Out of Memory' (Yetersiz Bellek) hatası veriyor ya da donup kapanıyor.Kodun içinde Application.ScreenUpdating = False ve CalculationAutomatic ayarlarını kapatıyorum ama yine de RAM tüketimi tavan yapıyor. Nesneleri Set ... = Nothing yapsam da Excel arka planda hafızayı boşaltmıyor gibi. Bu tarz devasa boyutlu kurumsal dosyalarda VBA'in bellek yönetimini optimize edecek, makronun Excel'i patlatmasını önleyecek ileri düzey teknik bir çözüm veya kod mimarisi var mıdır? Örnek veri yapısını ve kullandığım makro bloğunu ekteki dosyaya koydum. Desteğiniz çok makbule geçer.
 

Ekli dosyalar

Bu konu çözüme ulaşmıştır yüklü dosyaları indirmek için Bronz üye olunuz.
Bu dosyayı indirmek için yetkiniz bulunmamaktadır.
Tarihe göre sırala Oylara göre sırala
bark' Alıntı:
Merhaba hocalarım, şirket içinde kullandığımız ve boyutu 60 MB'ı bulan çok yoğun formüllü ve veri yüklü bir ana dosyamız var. Bu dosya üzerinde çalışan bazı büyük VBA döngülerimiz var (yaklaşık 800 bin satırı tarayıp rapor üretiyor). Makro çalışmaya başladıktan 3-4 dakika sonra Excel doğrudan 'Out of Memory' (Yetersiz Bellek) hatası veriyor ya da donup kapanıyor.Kodun içinde Application.ScreenUpdating = False ve CalculationAutomatic ayarlarını kapatıyorum ama yine de RAM tüketimi tavan yapıyor. Nesneleri Set ... = Nothing yapsam da Excel arka planda hafızayı boşaltmıyor gibi. Bu tarz devasa boyutlu kurumsal dosyalarda VBA'in bellek yönetimini optimize edecek, makronun Excel'i patlatmasını önleyecek ileri düzey teknik bir çözüm veya kod mimarisi var mıdır? Örnek veri yapısını ve kullandığım makro bloğunu ekteki dosyaya koydum. Desteğiniz çok makbule geçer.
Genişletmek için tıkla...
dosyayı farklı kaydet deyip xlsm formatında değil de ikili dosya biçimi xlsb formatında kaydedip dener misiniz.
 
Olumlu oy 0 Negatif oyla
Konu Sahibi
volki_112 hocam selamlar, destek için teşekkür ederim. Dediğiniz gibi ikili dosya biçimi .xlsb olarak kaydettim, dosya boyutunda ciddi bir küçülme oldu valla elinize sağlık. Ama makro döngüsünü başlattığımda maalesef yine 4. dakikada donup 'Out of Memory' vererek patladı. Dosya boyutu küçülse de makro çalışırken RAM tüketimi tavan yapmaya devam ediyor. VBA tarafında bu bellek yükünü hafifletmenin başka bir yolu var mıdır?
 
Olumlu oy 0 Negatif oyla
Dosyada kodları göremedim ama
büyük veri setlerinde değerleri diziye alarak çalışmak
çok daha hızlı sonuçlar verir.
 
Olumlu oy 0 Negatif oyla
Konu Sahibi
Yanıtınız için çok teşekkürler Patron hocam. Haklısınız, ben klasik hücre döngüsü kullandığım için sanal bellek (Virtual RAM) logları bir süre sonra kilitleniyor.
Tabloyu bir Variant diziye atayıp döngüyü tamamen RAM üzerinde döndürmek performansı kesinlikle uçuracaktır. Bu dizi mantığını 800 bin satırlık büyük bir veri seti için VBA'de en optimize şekilde nasıl kurgulayabiliriz? Mantığı oturtmam için ufak bir kod yapısı paylaşabilir misiniz hocayı zorlamayacaksa? Çok makbule geçer.
 
Olumlu oy 0 Negatif oyla
Küçük bir örnek.
modül içinde aynı işi yapan klasik ve dizi yöntemi ile iki makro var
 

Ekli dosyalar

Bu konu çözüme ulaşmıştır yüklü dosyaları indirmek için Bronz üye olunuz.
Bu dosyayı indirmek için yetkiniz bulunmamaktadır.
  • Beğen
İfadeler: Admin
Olumlu oy 1 Negatif oyla
Çözüm
bark' Alıntı:
Yanıtınız için çok teşekkürler Patron hocam. Haklısınız, ben klasik hücre döngüsü kullandığım için sanal bellek (Virtual RAM) logları bir süre sonra kilitleniyor.
Tabloyu bir Variant diziye atayıp döngüyü tamamen RAM üzerinde döndürmek performansı kesinlikle uçuracaktır. Bu dizi mantığını 800 bin satırlık büyük bir veri seti için VBA'de en optimize şekilde nasıl kurgulayabiliriz? Mantığı oturtmam için ufak bir kod yapısı paylaşabilir misiniz hocayı zorlamayacaksa? Çok makbule geçer.
Genişletmek için tıkla...
kodlarını yapay zekaya ver. bu kodu en hızlı ve verimli şekilde pc yi yormayacak şekilde nasıl yazabiliriz diye sor
 
Olumlu oy 0 Negatif oyla
Konu Sahibi
  • Beğen
İfadeler: Patron
Olumlu oy 0 Negatif oyla
Selamlar herkese, konu çözülmüş ve kilitlenmiş ama gelecekte aynı bellek hatasıyla bu başlığa gelecek arkadaşlara ışık tutması adına yazmak istedim.
Özellikle volki_112 Bey'in "kodları yapay zekaya ver" önerisi ve konu sahibinin aldığı olumsuz sonuç bence bu konunun en can alıcı noktası. volki_112 Bey kesinlikle kötü niyetle söylememiş, pratik bir yol göstermek istemiş ama büyük veri optimizasyonunda yapay zekaya güvenmek maalesef hüsranla sonuçlanıyor.
Yapay zekanın bu konuda çuvallamasının çok net bir sebebi var: Ham kodu bota verdiğinizde, bot mantığı anlamadan sadece ezbere döngünün başına "ScreenUpdating = False" veya "CalculationManual" ekleyip kodu size geri verir. Oysa 800 bin satırda Excel'i patlatan şey ekran güncellemesi değil; hücre hücre veri okurken arka planda biriken "Undo" (Geri Al) logları ve şişen sanal bellektir (Virtual RAM). Yapay zeka bu mimariyi ve arka plan bellek yönetimini algılayamaz.
Patron Hocamın belirttiği gibi veriyi hücreden kurtarıp Variant Diziye (Array) almak işin tek gerçek ilacıdır. Ancak devasa kurumsal dosyalarda dizi kullansanız bile RAM'i tamamen rahatlatmak için şu 3 altın kuralı mimariye eklemek şarttır:
  1. Scripting.Dictionary Kullanımı: Eğer veri tararken mükerrer kontrolü yapıyorsanız, yapay zekanın yazdığı gibi iç içe For-Next döngüsü kurmak intihardır. Veriyi hafızada Dictionary nesnesine atayıp saniyeler içinde aratmalısınız.
  2. Erase Komutu ile Zorunlu Temizlik: İşlem bitince nesneleri sadece Nothing yapmak yetmez. Dinamik dizilerin kapladığı alanı RAM'den tamamen söküp atmak için Erase DiziAdi komutuyla hafızayı boşaltmak gerekir.
  3. Bloklama (Chunking): Eğer PC hala kasılıyorsa, 800 bin satırı tek seferde diziye yüklemek yerine 200'er bin satırlık bloklar halinde hafızaya alıp işlemek RAM tavanını engeller.
Patron Hocamın dizi mantığına ek olarak, yapay zekanın asla akıl edemeyeceği, Dictionary ve Erase temizliğini içeren en optimize kalıp mimari şudur:

HTML:
ASP.net:
İçeriği görebilmek için Giriş yap ya da Üye ol.

Özetle; volki_112 Bey'in kulaklarını çınlatmış olalım, yapay zeka standart işlerde harika olsa da iş kurumsal boyutlardaki büyük verilere ve bellek optimizasyonuna geldiğinde maalesef sınıfta kalıyor. Patron hocanın ellerine sağlık, gerçek tecrübe her zaman yapay zekayı eliyor. Herkese iyi forumlar.
 
  • Beğen
İfadeler: Patron
Olumlu oy 0 Negatif oyla
ZaLim' Alıntı:
Selamlar herkese, konu çözülmüş ve kilitlenmiş ama gelecekte aynı bellek hatasıyla bu başlığa gelecek arkadaşlara ışık tutması adına yazmak istedim.
Özellikle volki_112 Bey'in "kodları yapay zekaya ver" önerisi ve konu sahibinin aldığı olumsuz sonuç bence bu konunun en can alıcı noktası. volki_112 Bey kesinlikle kötü niyetle söylememiş, pratik bir yol göstermek istemiş ama büyük veri optimizasyonunda yapay zekaya güvenmek maalesef hüsranla sonuçlanıyor.
Yapay zekanın bu konuda çuvallamasının çok net bir sebebi var: Ham kodu bota verdiğinizde, bot mantığı anlamadan sadece ezbere döngünün başına "ScreenUpdating = False" veya "CalculationManual" ekleyip kodu size geri verir. Oysa 800 bin satırda Excel'i patlatan şey ekran güncellemesi değil; hücre hücre veri okurken arka planda biriken "Undo" (Geri Al) logları ve şişen sanal bellektir (Virtual RAM). Yapay zeka bu mimariyi ve arka plan bellek yönetimini algılayamaz.
Patron Hocamın belirttiği gibi veriyi hücreden kurtarıp Variant Diziye (Array) almak işin tek gerçek ilacıdır. Ancak devasa kurumsal dosyalarda dizi kullansanız bile RAM'i tamamen rahatlatmak için şu 3 altın kuralı mimariye eklemek şarttır:
  1. Scripting.Dictionary Kullanımı: Eğer veri tararken mükerrer kontrolü yapıyorsanız, yapay zekanın yazdığı gibi iç içe For-Next döngüsü kurmak intihardır. Veriyi hafızada Dictionary nesnesine atayıp saniyeler içinde aratmalısınız.
  2. Erase Komutu ile Zorunlu Temizlik: İşlem bitince nesneleri sadece Nothing yapmak yetmez. Dinamik dizilerin kapladığı alanı RAM'den tamamen söküp atmak için Erase DiziAdi komutuyla hafızayı boşaltmak gerekir.
  3. Bloklama (Chunking): Eğer PC hala kasılıyorsa, 800 bin satırı tek seferde diziye yüklemek yerine 200'er bin satırlık bloklar halinde hafızaya alıp işlemek RAM tavanını engeller.
Patron Hocamın dizi mantığına ek olarak, yapay zekanın asla akıl edemeyeceği, Dictionary ve Erase temizliğini içeren en optimize kalıp mimari şudur:

HTML:
ASP.net:
İçeriği görebilmek için Giriş yap ya da Üye ol.

Özetle; volki_112 Bey'in kulaklarını çınlatmış olalım, yapay zeka standart işlerde harika olsa da iş kurumsal boyutlardaki büyük verilere ve bellek optimizasyonuna geldiğinde maalesef sınıfta kalıyor. Patron hocanın ellerine sağlık, gerçek tecrübe her zaman yapay zekayı eliyor. Herkese iyi forumlar.
Genişletmek için tıkla...
yapay zekayı kullanırken sıfır kod bilgimiz var ise maalesef dediğiniz gibi işe yaramıyor. Ben sıfırdan kod yazamıyorum ama kodları okuyup anlayacak kadar azcık öğrendim. Yapay zekaya sorarken mutlaka dizi kullan, hafızaya al gibi işi daha betimleyici şeyler kullanmak gerekli. dictionary ve array aynı iii yapıyor gözükse de dictionary mac lerde çalışmıyor mesela. mac te de kullanılabilecek bir dosya var ise mutlaka array kullanmak gerek.

arkadaş kodunu paylaşsa daha verimli sonuçlar gelir aslında
 
Olumlu oy 0 Negatif oyla
bark' Alıntı:
çok yoğun formüllü
Genişletmek için tıkla...

Pratik bir öneri dosya yapınız uygunsa kayıt işi bittiğinde verilerin bulunduğu alanın son formüllü satırın üstündeki verileri kopyalayıp Özel Yapıştır > Değerleri ile formüllerden kurtulun.
 
  • Beğen
İfadeler: Admin
Olumlu oy 0 Negatif oyla
Durum
Konu Çözümlendiği İçin Kapatılmıştır.
Geri
Üst