Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Wyciek pamięci?

Quasi · Wysłany: 26-02-2024, 09:59 **Wyciek pamięci?**

Cześć mądre Głowy! :-)

Case study
Mam kod, który generuje w pętli ok. 4000 raportów PDF. Na początku każdej iteracji makro otwiera szablon XLTX i wgrywa, za pomocą filtra zaawansowanego, listę wpłat dla danej osoby… Cały proces trwa ok. 1h, pliki ważą 300-500 KB.

Problem
Przy pierwszych testach na moim komputerze (Windows 64-bit, Excel 32-bit, 16 GB RAM), po wygenerowaniu ok. 60-65% raportów pojawiał się błąd pamięci. Excel jakby się zawieszał na chwilę, a następnie wyświetlał monit.

W rzeczywistości mój komputer przeznacza na Excela ok. 1,1 GB RAM i po przekroczeniu tej liczby, dostawałem zawsze taki właśnie komunikat.. Oczywiście ta zarezerwowana pamięć nieco zwiększała się po każdej iteracji, aż do przekroczenia 1,1 GB.

Literatura
Problem ten jest słabo opisany na polskich forach, ale dużo lepiej wygląda to na grupach anglojęzycznych.
https://stackoverflow.com/questions/14396998/how-to-clear-memory-to-prevent-out-of-memory-error-in-vba
https://www.automateexcel...f-memory-error/
https://www.exceldemy.com...nk-grey-screen/
Ludzie sugerowali zwłaszcza, aby po każdej iteracji zapisywać plik, co miało resetować pamięć RAM. To rozwiązanie nie sprawdzało się jednak w nowszej wersji Excela (u mnie nie działało), nie byłem też fanem takiego podejścia, bo wydłużyłoby to mocno cały proces.

Rozwiązanie…
Problem udało mi się przezwyciężyć w zaskakująco prosty sposób. Instrukcja DoEvents umieszczona na samym końcu każdej iteracji resetuje pamięć RAM. Od teraz wykorzystanie RAM waha się w przedziale 300-600MB, więc z powodzeniem mogę wykonać raporty dla wszystkich osób… na swoim komputerze 😉.

Komputer Klienta
O ile na moim komputerze każdy test wypadał pomyślnie, o tyle na komputerze Klienta... niekoniecznie. Najpierw zrobiliśmy test na 3000 raportach i było OK. Gdy jednak musieliśmy sprawdzić program w warunkach bojowych (4000 raportów), pojawił się niespodziewany zonk...

Tym razem udawało się zrobić 80% raportów i Excel jakby na chwilę się zawieszał, makro przestało się wykonywać, a Excel pozostawiał szary ekran (nie zamknięty plik). Nie było żadnego monitu. Komputer mojego Klienta ma 8 GB RAM.

5 miesięcy później…
Wróciliśmy do tematu tydzień temu. Dziś dostałem informację od Klienta, że wykonał testy. Na tym samym kompie co ostatnio, Excel wciąż przerywa pracę… Na innym komputerze (16 GB RAM), wszystko jednak działa OK. Ja w międzyczasie dokupiłem 16 GB i na moim kompie (32 GB RAM), wszystkie 10 testów zakończyło się sukcesem.

Wnioski i przemyślenia
Zastanawiam się czy problem faktycznie leży w pamięci RAM... No bo skoro Klient ma Excela 365 i 8 GB RAM, to przecież instrukcja DoEvents resetuje RAM po każdej iteracji i cały proces zwykle nie przekracza 600 MB pamięci... Z drugiej strony zastanawiam się, czy to jednak nie jest też kwestia procesora. W trakcie generowania plików PDF, jego zużycie wynosi u mnie ok. 80%.

PS. Kodu udostępnić niestety nie mogę, bo jest już własnością Klienta. Jest to bardziej problem techniczny/sprzętowy niż merytoryczny i zastanawiam się czy na pewno jest to tylko kwestia RAMu.

Dzięki śliczne za wszystkie podpowiedzi! :-)

ple4 · Wysłany: 26-02-2024, 15:38

master_mix · Wysłany: 26-02-2024, 21:11

DoEvents, może pomóc, ale czy na pewno zapewni ci to spokojną pracę Twojego projektu ? .... nie sądzę.
A rozszerzanie pamięci RAM maszyny nie ma sensu - poza tym, co za pomysł żeby klienta zmuszać do rozbudowy hardware,
bo ja sobie tak napisałem kod ;)

Sytuacja wygląda następująco:
Nie ma możliwości zarządzania pamięcią w VBA w trakcie działania procesu.
W VBA kod działa w środowisku uruchomieniowym excela - maszyna wirtualna - i jest interpretowany w trakcie wykonywania procesu (procedury),
to środowisko nie ma "wyrafinowanego" systemu zarządzania pamięcią,
jak Java czy .net w których rolę tą pełni rozbudowany mechanizm GarbageCollector (GC)

Najważniejsze aspekty sprawy:
Sprawa rozbija się o zarządzanie tzw, Stertą w pamięci.
java i .net tworzą w pamięci obszar tzw zarządzanej sterty, którą te środowiska są w stanie "manipulować"
VBA korzysta ze sterty natywnej - obsługiwanej przez środowisko systemowe.
W VBA mamy dostępne: Erase dla tablic i Nothing dla obiektów, to całe zarządzanie pamięcią :)
Ale ... Erase i Nothing nie zwalniają pamięci od razu (fizycznie), usuwają jedynie wskaźnik do obiektów i tablic
W momencie zakończenia działania procedury-procesu (pamiętamy: interpretowany w trakcie wykonywania procesu),
na chwilę obsługę przejmuje sys. operacyjny i zwalnia fizycznie pamięć
(wcześniej zarezerwowane obszary przez naszą maszynę wirtualną) w blokach do których nie ma już referencji, czyli ustawia status bloków = Wolny.
(są 3 stany pamięci w sys. : Wolny, Zarezerwowany, Używany)

To właśnie wymusiłeś przez DoEvents, oddając kontrolę windowsowi, który mógł posprzątać.

Quasi · Wysłany: 26-02-2024, 22:09

ple4 · Wysłany: 27-02-2024, 17:12

1. Trzeba by jeszcze sprawdzić, w którym miejscu kodu 'DoEvents' "działa najlepiej"
2. Spróbuj jeszcze ewentualnie zwiększyć plik wymiany na danym kompie, jeśli jest on ustawiany na sztywno i jeśli administratorzy na to pozwolą (lub w ogóle może ustawić na wielkość zarządzaną przez system)

Rafał B. · Wysłany: 27-02-2024, 20:21

Ciekawe czy podział na paczki uruchamiane przez `Application.OnTime`z zerowym czasem też przekazuje na moment sterowanie do systemu operacyjnego. Chociaż i tak nie byłoby w tym raczej żadnej przewagi nad `VBA.DoEvents`.

Ogólnie to DoEvents jest też trochę demonizowane nad wyraz- a 95% płaczu nad tą metodą z biblioteki standardowej, to używanie implicite ActiveSheet, ActiveWorkbook w kodzie w stylu `Range("abc").Value`, `Sheets("xyz").Range...`, który często widujemy nawet wśród powszechnie szanowanych osób na tym forum, zamiast w pełni kwalifikowanych obiektów Range. W dobrym kodzie DoEvents nie powinien robić szkód poza jakimiś rzadkimi przypadkami. W formularzach jest to bardziej skomplikowane, ale czasem nieodzowne, .Repaint niestety nie zawsze działa prawidłowo. Każdy programista VBA musi czasem tego DoEvents użyć, jak działa, to gitara :-P

Ale jakieś WinAPI, na pewno ktoś byłby w stanie wyczarować. I w oparciu o to napisać sobie własną klasę GarbageCollector :-D

Artik · Wysłany: 03-03-2024, 14:34

master_mix · Wysłany: 03-03-2024, 20:01

ple4 · Wysłany: 04-03-2024, 22:42

Dość zachęcająco wyglądają próby z PdfCreator'em - trzeba tylko odklikać opcje: "Otwórz plik po zapisaniu" i "Pokaż szybkie akcje ..." bo zbędne.
Oczywiście nie próbowane na 4 tys. plików ... więc może to ułuda i mrok ... ale na mniejszych próbkach radzi sobie dość sprawnie - ogólny schemat:

Kod:

Sub słoik_z_powidłami()
Const prn01 As String = "PDFCreator" ' druk. PdfCreator
Const prn02 As String = "Wariatka na Ne01:" ' druk. dom.

Dim opdf As Object, pjob As Object, s As Object, wsh As Object

Set wsh = CreateObject("WScript.Network")
wsh.SetDefaultPrinter prn01

Set opdf = CreateObject("PDFCreator.JobQueue")
opdf.Initialize
'...
s.PrintOut
opdf.WaitForJob (10)
Set pjob = opdf.NextJob
pjob.SetProfileByGuid ("DefaultGuid")
pjob.ConvertTo ("C:\TEMP\" & s.Name & "_prnpdf.pdf")
'Do Until pjob.IsFinished = True: DoEvents: Loop ' ???
Do
Loop Until pjob.IsFinished = True ' ???
'...
opdf.ReleaseCom
Set opdf = Nothing

wsh.SetDefaultPrinter prn02
Set wsh = Nothing

Shell "taskkill /f /im PDFCreator.exe", vbHide ' ???
End Sub

Rafał B. · Wysłany: 29-07-2024, 16:56

Odświeżam, bo ostatnio przypadkowo w Internecie natknąłem się na podobny problem i tam oprócz przedstawionego w wątku rozwiązania VBA.DoEvents przedstawiono inne obejście problemu przez... zwykły zapis pliku :shock:

. Może ktoś się zderzy kiedyś z problemem wątkotwórcy, to będzie mógł przetestować czy zapis/zapis kopii faktycznie zwolni pamięć.