Elektroda.pl
Elektroda.pl
X

Wyszukiwarki naszych partnerów

Wyszukaj w ofercie 200 tys. produktów TME
Europejski lider sprzedaży techniki i elektroniki.
Proszę, dodaj wyjątek elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

VBA - Odchudzenie makra - Jak skrócić, by wykonywanie zajmowało mniej czasu?

Draco2020 09 Mar 2014 16:50 2145 12
  • #1 09 Mar 2014 16:50
    Draco2020
    Poziom 7  

    Hej Wszystkim,

    moglibyście mi napisać odchudzoną wersję poniższego makra

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Makro wykonuje się tak długo, że można się zestarzeć. Dlatego będę wdzięczny, za jego skrócenie. Było by też fajnie, jak by ktoś wyjaśnił co i jak :)

    Pozdrawiam

    0 12
  • #2 09 Mar 2014 17:12
    marcinj12
    Poziom 40  

    Pierwsze pytanie - czy naprawdę masz aż 600.000 wierszy w tym pliku?
    Drugi - czy musisz wstawiać formuły, nie możesz od razu przeliczonych wartości?

    0
  • #3 09 Mar 2014 17:35
    Draco2020
    Poziom 7  

    Ad1

    Szczerze mówiąc nie wiem. Ogólnie problem polega na tym, że z pliku tekstowego zawierającego kilkadziesiąt tysięcy stron muszę znaleźć odpowiedni zakres i przekopiować go (to mi działa bez problemu) a następnie obrobić. I tu pojawia się rzeczywisty problem, ponieważ nie wiem, czy ten fragment będzie miał 50 tysięcy wierszy, czy 300 tysięcy wierszy dlatego ustawiłem zakres na 600 tysięcy.

    Ad2.

    Przeliczone wartości to znaczy? Wydaje mi się, że muszą być te formuły, bo jak inaczej wyciągnę dni tygodnia dla poszczególnych dat?

    0
  • #4 09 Mar 2014 18:26
    marcinj12
    Poziom 40  

    To od tego zacznij - główną "bolączką" tego makra jest to, że wstawiasz kilkanaście kolumn formuł na 600.000 wierszy - to się musi długo liczyć...

    Na początku skróć zakres do niezbędnego minimum: poleceniem

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    ustalisz sobie w aktywnym arkuszu ostatni numer wiersza (ważne, żeby przedtem zdjąć autofiltr). Potem operacje możesz wykonywać tylko na tym zakresie, np. zamiast
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    pisać:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Oprócz takich funkcji jak ustawianie formatów kolumny, ZAWSZE staraj się operować na minimalnym zakresie danych, potrzebnych do wykonania obliczeń.

    Jeśli chodzi o dalszą optymalizację, to np. zamiast takiego fragmentu:
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    będzie o wiele szybciej, jeśli wstawisz:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Spróbuj zastąpić tym wzorcem wszystkie pozostałe kolumny. Tam, gdzie to możliwe, zastąp przeliczone formuły wartościami jak wyżej.

    Tam, gdzie wstawiasz stałe wartości:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    rób po prostu:
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Gdzie się da, unikaj wstawiania lub usuwania wierszy czy kolumn.

    Zamiast robić Range(xxxx).Select i potem Selection.xxxx, złącz te dwa wiersze razem i rób np.:
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Jeśli masz gdzieś jakieś formuły, które nie muszą być przeliczanie na czas generowania makra, użyj na początku makra:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    a na końcu:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    (tu niespodzianka - nie wyłączy to przeliczania formuł wstawianych jak w przykładzie z With... na początku ).
    Tylko uważaj, bo to polecenie wyłącza przeliczanie formuł w skoroszycie - jak się makro wywali, to potem ich nie przywróci i trzeba je potem włączyć ręcznie. Sprawdź, czy makro będzie działać szybciej z takim ustawieniem - jeśli nie, to go nie wstawiaj.

    Wprowadź te poprawki, wklej kod, potem zobaczymy, co dalej.

    0
  • #5 09 Mar 2014 19:39
    Draco2020
    Poziom 7  

    Nie wiem, czy dobrze to zrozumiałem.

    Mam takie coś

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Wrzuciłem do excel i puściłem obróbkę listy...póki co mieli

    Dodano po 5 [minuty]:

    marcinj12 napisał:


    Jeśli masz gdzieś jakieś formuły, które nie muszą być przeliczanie na czas generowania makra, użyj na początku makra:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    a na końcu:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    (tu niespodzianka - nie wyłączy to przeliczania formuł wstawianych jak w przykładzie z With... na początku ).
    Tylko uważaj, bo to polecenie wyłącza przeliczanie formuł w skoroszycie - jak się makro wywali, to potem ich nie przywróci i trzeba je potem włączyć ręcznie. Sprawdź, czy makro będzie działać szybciej z takim ustawieniem - jeśli nie, to go nie wstawiaj.


    Czy dobrze rozumiem, że w pewnym momencie w excelu będę musiał sam wstawić mu formuły? Jeżeli tak to odpada - z pliku będą korzystały też inne osoby

    0
  • #6 09 Mar 2014 20:29
    marcinj12
    Poziom 40  

    Częściowo dobrze. Jeszcze:
    - Po kolorowaniu składni na forum widać, że we fragmencie:

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    masz podwójny cudzysłów na końcu.

    - Ciągle masz odwołania "na sztywno" - tak ma być?
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    etc.
    Ostatni wiersz możesz ustalić w każdym arkuszu, pisząc np.:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    albo
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    - Docelowo, w całym kodzie można pozbyć się słówek Select i Selection. Możesz zostawić najwyżej przełączanie się na inne arkusze: Sheets("Pick Area A").Select jak inaczej nie potrafisz, a także zaznaczanie domyślnej komórki żeby pozbyć się zaznaczenia tekstu: Range("A1").Select. Całą resztę z tymi słówkami do przeróbki , np:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    na:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    na:
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    - Podobna sprawa dotyczy ActiveCell i AutoFill - można tak kod przerobić, żeby ich nie było, np.
    zamiast:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    dać przykład z With... jak robiłeś poprzednio. Jeżeli to nie muszą być formuły, dodaj też .Copy i .PasteSpecial , jeżli muszą - to bez tych dwóch linijek.

    Także następnym razem jak wkleisz kod, nie chcę widzieć żadnych .Select, Selection, ActiveCell i AutoFill ;) [za wyjątkiem tych o których pisałem w trzecim podpunkcie]

    Także generalnie jesteś na dobrej ścieżce, tylko wprowadź zmiany wszędzie.

    Cytat:
    Czy dobrze rozumiem, że w pewnym momencie w excelu będę musiał sam wstawić mu formuły? Jeżeli tak to odpada - z pliku będą korzystały też inne osoby
    No to już zależy od Ciebie - jeżeli w danej kolumnie nie musi być formuł, a tylko wartości po przeliczeniu, robisz:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Jeżeli w kolumnie muszą być jakieś formuły, bo dane będą się zmieniać, zostawiasz tylko:
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    ale pamiętaj, że formuły będą się każdorazowo przeliczać, co spowolni plik dla dużej ilości danych...
    A to o czym pisałem a Application.Calculation = xxx to nic innego jak ustawienie przeliczania formuł na Ręczne /na czas działania makra/, tak samo jak możesz zrobić to z poziomu Excela.

    Dobrze by było, jakbyś albo wstawił plik z danymi, albo dał chociaż jakiś feedback co do czasów przeliczeń, żeby można było jakoś postępy prześledzić ;)

    0
  • #7 09 Mar 2014 21:59
    Draco2020
    Poziom 7  

    marcinj12 napisał:

    - Ciągle masz odwołania "na sztywno" - tak ma być?
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    etc.


    Szczerze mówiąc to nie wiem, część makra robiłem przez rejestrowanie, część przez pisanie własnoręczne. Ta część, o którą pytasz była rejestrowana

    marcinj12 napisał:

    Także następnym razem jak wkleisz kod, nie chcę widzieć żadnych .Select, Selection, ActiveCell i AutoFill ;) [za wyjątkiem tych o których pisałem w trzecim podpunkcie]


    Aż się boję wstawiać ten kod :|

    A oto mój nieszczęsny kod po przeróbce:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Co do czasu wykonywania. Ostatnio robił to u mnie prawie 1,5h a komputer się strasznie mulił. Patrząc na wydajność zużycie procka wynosiło 76% a pamięć była zajęta na ponad 550.000K. Dzięki Twoim przeróbkom zużycie procka spadło do 51-55% a zajmowanie pamięci do ok 350.000K i nareszcie mogłem korzystać z innych aplikacji :). Jednak po godzinie mielenia excel'a miałem dosyć i wymusiłem mu zamknięcie, tak więc nie podam całkowitego czasu :(

    0
  • #8 09 Mar 2014 22:30
    marcinj12
    Poziom 40  

    A dział Ci to makro? Bo u mnie się wywala w pewnym momencie... Weź wklej spakowany plik Excela z już wklejonymi przykładowymi, nieobrobionymi danymi przed kliknięciem przycisku. Tak, żebym tylko mógł kliknąć przycisk i makro zaczęło się wykonywać.

    0
  • #9 09 Mar 2014 22:47
    Draco2020
    Poziom 7  

    Teraz mnie zszokowałeś...mi to mieliło ponad godzine wyświetlające, że excel jest niedostępny a Ty masz od razu bład...wow. Zaraz Ci wyślę wszystko na PW

    0
  • #10 09 Mar 2014 22:49
    adamas_nt
    Moderator Programowanie

    Przyglądam się (aż mało dziury nie wydrapałem we włosach) i nie rozumiem...

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Czemu służy ostatnia linijka w tym fragmencie?

    Czy rzeczywiście wszystkie dane z pliku tekstowego są istotne?
    Nie lepiej byłoby pobierać wiersz po wierszu z pliku txt i wstawiać gotowe dane?

    Najważniejsze: jeśli plik tekstowy zawiera 194583 wiersze, z czego "użytecznych" jest 80%, daje nam ~155666. Gdyby makro "zużywało" 1 sek. na wiersz... Chyba, że coś źle liczę.

    0
  • #11 09 Mar 2014 23:06
    Draco2020
    Poziom 7  

    Tak zaawansowany nie jestem :/ Ostatnia linijka

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Ma na celu usunięcie wszystkich niepotrzebnych "pierdół", które zostały wybrane poprzez filtr. Niestety nie wiem ile będzie takich wierszy, tak więc ustawiłem zakres do 500000...chociaż patrząc teraz na to to chyba o wiele za dużo, zwłaszcza, że na początku zakładałem, że wszystkie formuły mają się liczyć do 600000 wiersza

    Dodano po 7 [minuty]:

    Ogólnie z tym plikiem tekstowym jest taki problem, że generuje się raz 50.000 stron raz 60.000, innym razem 42.000 itp. i około 90% z nich jest mi nie potrzebnych. I teraz tak:

    1. Mam makro, które częściowo obrabia ten plik - i chodzi, jak trzeba
    2. Makro do wyszukiwania potrzebnego mi zakresu - działa, jak trzeba
    3. Makro opracowujące wybrany zakres - tu się muli muli i muli
    4. Makro kopiujące wyniki do osobnego pliku - również działa, jak trzeba

    Na samym końcu do makra nr 4 dopisze polecenie, by arkusze, w których znajduje się ta potężna ilość danych, zostały wyczyszczone

    0
  • Pomocny post
    #12 09 Mar 2014 23:45
    marcinj12
    Poziom 40  

    Faktycznie, to usuwanie danych na początku chwilę trwa... U mnie jakieś 30s.
    Ale dalej mi się nie podobają te funkcje na 600.000 wierszy: popraw je na:

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Oczywiście ost_allegat_list możesz użyć tylko jeden raz, przy pierwszym wykorzystaniu.
    Oprócz tego funkcje SUMIF trochę się liczą, niemniej u mnie całość nie zajmuje więcej niż minutę...
    PS. Widzę że nie "załapałeś" mojej idei nazewnictwa zmiennych: ost_area_A to był przykład oznaczający "ostatni wiersz w arkuszu area_A. Dla arkusza o nazwie "Pick Area FGHJ" nazwałbym tą zmienną: ost_area_FGHJ albo coś w ten deseń etc. :)

    A makro wywala mi się na fragmencie:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #13 10 Mar 2014 00:11
    Draco2020
    Poziom 7  

    Poprawiłem :)
    Dla miejsca, przy którym miałeś błąd użyłem teraz

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Dodano po 3 [minuty]:

    Testować będę jutro już nie mam sił

    0
TME logo Szukaj w ofercie
Zamknij 
Wyszukaj w ofercie 200 tys. produktów TME
TME Logo