Very slow down VBA code execution.

Question

I have been using the MS Excel application for a long time (MS Office 2013), which contains extensive VBA code.

February Windows 10 updates slowed down Excel's VBA code execution very much. I also installed the last updates from March, but nothing helped.

Before the February updates, one calculation took 60-65 seconds. Currently, after these updates, it lasts over 180 seconds (three times longer). I have to do these calculations many times, so it takes me a lot of time.

Can you do something about it, somehow speed up the VBA code execution?

I am asking for effective advice.

--

Artur Kw

___________________________

Bardzo zwolniło wykonywanie kodu VBA.

Od dawna używam aplikacji MS Excel (MS Office 2013), która zawiera rozbudowany kod VBA.

Lutowe aktualizacje Windows 10 bardzo spowolniły wykonywanie kodu VBA Excela.  Zainstalowałem również ostatnie poprawki z marca, ale nic nie pomogły.

Przed poprawkami z lutego jedno wykonanie obliczeń trwało 60-65 sekund. Obecnie po tych aktualizacjach trwa ponad 180 sekund (trzy razy dłużej). Muszę te obliczenia wykonywać wielokrotnie, więc zajmuje mi to bardzo dużo czasu.

Czy można coś na to poradzić, jakoś przyspieszyć wykonywanie kodu VBA?

Bardzo proszę o skuteczną radę.

--

Artur Kw

Answer 1

Oczywiście poprawię obsługę błędów i zdarzeń, ale jestem przekonany, że to nie jest przyczyna spowolnienia i poprawa kodu w tym zakresie nie usunie mojego problemu.

Trochę mi zajmie poprawa kodu, ale i tak nie spodziewam się osiągnięcia sukcesu.

Co prawda napisałem wcześniej, że obsługa błędów jest uproszczona, ale nie jest aż tak, żeby niektóre fragmenty kodu były opuszczane i żeby wpływało to na błędne działanie aplikacji. Olewajka błędu następuje tylko wtedy, gdy błąd ten jest całkowicie nieistotny lub gdy zaraz i tak uniemożliwi dalsze wykonywanie kodu. Za każdym razem, gdy pojawia się istotny błąd, wykonywanie aplikacji jest definitywnie przerywane uniemożliwiając błędne obliczenia. Zresztą wyniki obliczeń są sprawdzane, więc każdą nieprawidłowość łatwo zidentyfikować.

Przed lutym nie było problemu z przewlekłym wykonywaniem obliczeń. Moim zdaniem wygląda to tak, jakby było spowodowane zmianą sposobu obsługi schowka systemowego albo pamięci RAM, bo ta aplikacja wykonuje miliony operacji kopiowania i sortowania danych.

Ale nie zauważyłem problemu z wydajnością i działaniem innych programów na moim komputerze.

Czy w zakresie obsługi pamięci i/lub schowka systemowego lutowa aktualizacji Windows 10 coś zmieniła?

Answer 2

Ok no to zastosuj zamiast zamrażanie ekranu taką procedurę:

Na wejście call BlockEvScreenCalc(false) i na wyjście call BlockEvScreenCalc

Public Sub BlockEvScreenCalc(Optional ByVal bWlacz As Boolean = True, _

                             Optional Status As String = "")

'VBATools.pl Polskei dodatki do office

    With Application

        If bWlacz Then

            .EnableEvents = True

            .Calculation = xlCalculationAutomatic

            .ScreenUpdating = True

            .Cursor = xlDefault

        Else

            .ScreenUpdating = False

            .Calculation = xlCalculationManual

            .EnableEvents = False

            .Cursor = xlWait

        End If

        .StatusBar = Status

    End With

End Sub

Poza tym on error to olewajka, obsłuż kod jak należy, aby tam gdzie spodziewasz się błędu odwołać się do jego numeru, a tam gdzie nie niech wychodzi z procedury mówiąc ci jakiego rodzaju błąd natrafił. Olewajka może niektóre błędne linie kodu opuścić a więc wpływa to na inne ich poczynanie. Czyli tak to wygląda

sub nazwa_pocedury()

call BlockEvScreenCalc(false)

on error goto blad1

'...jakiś kod

call BlockEvScreenCalc

msgbox "Kod wykonał się prawidłowo"

exit sub

blad1:

dim er$: er = Err.Number  & " " & err.Description

call BlockEvScreenCalc

msgbox "błąd: " & er

end sub

Answer 3

Sorry.

ad 2. Nie ani kod VBA, ani formuły nie uległy zmianie. Oczywiście formuły obecne w arkuszach powielały się do kolejnych wierszy, ale tak było zawsze.

Answer 4

Bardzo dziękuję za zainteresowanie.

Odpowiadam kolejno.

ad 1. Nie, dane są porównywalne. Oczywiście czas trwania jednej tury obliczeń rósł wraz z przyrostem liczy danych. Na początku było to około 20 sekund. Do lutego br. przybyły 1622 pakiety danych, a czas przeliczania wzrósł do 60-65 sekund - w zależności od obciążenia i czasu pracy komputera od uruchomienia systemu, czyli przyrastał mniej niż 0.03 sekundy na pakiet. Po aktualizacji Win10 z lutego br. nastąpiło skokowe (3-krotne) wydłużenie czasu trwania obliczeń do ponad 180 sekund

• bez przyrostu liczby danych.

ad 3. Nie, cały proces przeliczania odbywa się automatycznie bez zatrzymywania zdarzeń, jedynymi zabiegami związanymi z odświeżaniem jest instrukcja Application.ScreenUpdating na początku i na końcu kodu.

Jedynie w przypadku błędów mają się pojawiać komunikaty MsgBox i Application.Speech.Speak, ale one akurat się nie pojawiają. Obsługa błędów jest bardzo uproszczona, zazwyczaj On Error Resume Next. Komunikaty (MsgBox) pojawiają na koniec obliczeń, ale to już po długotrwałym oczekiwaniu na przeliczenie danych.

ad 4. Nie ma żadnych połączeń ze źródłami zewnętrznymi. Kod samoczynnie pobiera dane do przeliczenia z innego prostego lokalnego skoroszytu, ale tu nie ma żadnych zmian ani problemów. Wszystkie dane są kopiowane (bez formatu) pomiędzy trzema skoroszytami .xlsm składającymi się na aplikację - zawsze otwieranymi przy starcie aplikacji. Nie ma tabel połączonych ze źródłami zewnętrznymi. Jedynie kod VBA wykonuje się jednocześnie w tych trzech skoroszytach, ale tak było zawsze. Wszystkie trzy skoroszyty, w których zawiera się aplikacja są od zawsze w tym samym folderze.

ad 5. Najdalszy we wszystkich arkuszach ostatni używany wiersz ma nr 6680, nie ma żadnych problemów w tym zakresie. Największy skoroszyt przechowujący wyniki obliczeń w dniu 10 stycznia 2020 r. ważył 10 301 KB, a obecnie waży 10 605 KB.

ad 6. Nie, nikt nie ma dostępu do mojej aplikacji, ani kod VBA nie był edytowany, ani skoroszyty .xlsm nie były nawet otwierane poza moim Excelem.

Jak już pisałem problem pojawił się nagle po jednoczesnym zainstalowaniu:

• aktualizacji zbiorczej dla systemu Windows 10 ver. 1909 dla systemów opartych na architekturze x64 (KB4532695)
• i aktualizacji zbiorczej dla programów .NET Framework 3,5 i 4,8 dla systemu Windows 10 ver. 1909 dla systemów opartych na architekturze x64 (KB4534132).

Następne poprawki zainstalowałem dopiero 5 marca br., ale nic nie dały.

Przeanalizowałem już wcześniej te kwestie i niestety jestem bezradny, dlatego zwróciłem się z prośbą o pomoc do społeczności Microsoft.

--

Artur

Answer 5

Artek, forum a regionizację i nie musisz pisać po angielsku, ponieważ nigdy te pytanie nie trafi do tych osób.

Co do kodu, to sprawa w 99% odnosi się do ilości danych, nie zaś do funkcjonowania warstwy programistycznej developera (porównaj ten i wszystkie poz punkty z kopią). 

1. czy twoje dane nie zwiększyły znacznie swojej ilości od czasu kiedy kod działał wydajniej,
2. czy nie dodawałeś jakiś formuł lub formatowania warunkowego (opartego an formułach), które muszą się obliczyć,
3. czy stosujesz standardowe zatrzymanie zdarzeń, odświeżania i przeliczania w kodzie (standardowe przyspieszacze)
4. czy nie masz więcej danych w cache pliku (połączenia zewnętrzne, ilość TP czy tabel połączonych ze źródłami zewn)
5. czy nie rozrasta się twój plik nadmiernie (możliwa edycja ostatnich obszarów arkuszy)
6. czy plik jaki używasz nie edytowała osoba na innym niż MS produkcie (co mogło wpłynąć na jego niestandardowy format),

Jak sobie odpowiesz na te pytania porównując z poprzednią wersją to będziesz miał odpowiedź.

Polecam taki narzędzie do tworzenia kopii zapasowych: http://vbatools.pl/autozapis-skoroszytow/