VBA i wydajność: kiedy używać tablic, a kiedy pracy na komórkach

Kontekst wydajności w VBA: kiedy w ogóle myśleć o optymalizacji

Makro „na chwilę” a stały proces w firmie

Cel osoby sięgającej po hasło „VBA i wydajność: kiedy używać tablic, a kiedy pracy na komórkach” zwykle jest bardzo praktyczny: skrócić czas działania istniejącego makra albo od razu zbudować rozwiązanie, które nie „zabije” Excela przy większych plikach. Kluczowe jest odróżnienie jednorazowego skryptu od procesu, który stanie się elementem codziennej pracy zespołu.

Jeśli makro ma pomóc jednorazowo w uporządkowaniu kilkudziesięciu wierszy, a ty uruchomisz je kilka razy i plik pójdzie do archiwum, rozbudowana optymalizacja może nie mieć sensu. Kod działający 2–3 sekundy zamiast 0,2 sekundy nikomu nie przeszkodzi. Gdy jednak makro przetwarza tysiące wierszy, uruchamia się dziesiątki razy dziennie (również u innych użytkowników) i dotyczy wspólnych plików, każda sekunda różnicy zaczyna się kumulować w godziny.

W praktyce projektowej dobrze jest już na starcie zadać sobie pytanie: czy to narzędzie będzie używane regularnie przez innych, czy jest to jednorazowy „wytrych”? Im bardziej proces przypomina stały element systemu raportowego, tym bardziej opłaca się inwestować w tablice i ogólną optymalizację kodu.

Mikroopóźnienia vs sekundy i minuty w dużych skoroszytach

Operacje wykonywane bezpośrednio na komórkach wydają się szybkie, dopóki skala danych jest niewielka. Pętla po 100 komórkach zakończy się tak szybko, że użytkownik nie zauważy różnicy między kodem zoptymalizowanym a „naiwnym”. Różnica zaczyna być widoczna, gdy liczby rosną do dziesiątek tysięcy wierszy, wielu kolumn i złożonych formuł.

Excel do każdej operacji na komórce musi:

• obsłużyć odwołanie do obiektu (Range, Cells),
• ewentualnie przeliczyć formuły zależne,
• zaktualizować wewnętrzne struktury (formatowanie warunkowe, tabele, filtry).

Te pojedyncze koszty są minimalne, ale przy setkach tysięcy wywołań rosną do poziomu sekund, a nawet minut. Z kolei operacja na tablicy w pamięci omija mechanizmy Excela – dzięki temu iteracje po danych są rzędy wielkości szybsze, jeśli liczba odniesień do arkusza jest ograniczona do minimum.

Skalowanie się operacji: setki vs setki tysięcy komórek

Skalowanie w VBA jest z grubsza liniowe w liczbie iteracji, ale czas pojedynczej iteracji różni się dla pracy na pamięci i dla pracy na arkuszu. Pętla po tablicy, w której nie ma referencji do Range, jest zwykle kilkadziesiąt–kilkaset razy szybsza niż pętla, gdzie w każdym przebiegu czytana lub zapisywana jest komórka.

Dla orientacji można przyjąć bardzo uproszczoną praktyczną granicę:

• do ok. 500–1000 komórek – prosta praca na komórkach zwykle wystarcza,
• powyżej kilku tysięcy komórek – warto zacząć myśleć o tablicach, zwłaszcza przy złożonej logice,
• powyżej kilkudziesięciu tysięcy komórek – tablice i zbiorcze operacje stają się praktycznie koniecznością.

Te liczby nie są sztywną granicą, ale dobrym sygnałem kontrolnym. Im większa ilość danych i częstsze odwołania do komórek wewnątrz pętli, tym większy sens ma przeniesienie przetwarzania do pamięci.

Sygnały, że kod wymaga zmiany podejścia

Optymalizacja nie zawsze jest potrzebna od pierwszego wiersza kodu. Warto jednak rozpoznawać symptomy, które sugerują konieczność przejścia z pracy na komórkach na tablice i przemyślaną architekturę:

• Excel przechodzi w stan „Brak odpowiedzi”, pasek postępu „zamyśla się” na długie chwile,
• użytkownicy skarżą się, że makro „mieli” arkusz i blokuje im pracę na kilka minut,
• każde uruchomienie na wspólnym pliku (np. na dysku sieciowym) kończy się ryzykiem konfliktów i zablokowania skoroszytu,
• do prostych operacji na danych dokładanych jest coraz więcej warunków i filtrów, a każda kolejna zmiana jeszcze bardziej spowalnia działanie.

Jeżeli pojawiają się takie sygnały, pierwsze miejsce, w które warto spojrzeć, to sposób pracy kodu z danymi: czy większość czasu spędza na „chodzeniu” po arkuszu, czy na obróbce w pamięci.

Praca w pamięci kontra operacje na arkuszu

Tablice przenoszą ciężar logiki do pamięci (VBA), natomiast klasyczna praca na komórkach bazuje na mechanizmach Excela. Można to zestawić w prostym ujęciu:

Wydajność VBA mocno rośnie, gdy liczba wejść i wyjść do arkusza jest minimalizowana, a właściwe przetwarzanie odbywa się na danych trzymanych w tablicach, ewentualnie w słownikach czy kolekcjach.

Podstawy: co spowalnia makra w Excelu

Koszt przełączania się między VBA a silnikiem Excela

Każde odniesienie w kodzie do obiektów Excela – Range, Cells, Worksheet, Workbook – to przejście z interpretowanego środowiska VBA do natywnego silnika Excela. Samo to przejście ma swój koszt. Pojedynczo jest niezauważalny, ale tysiące takich odwołań szybko sumują się w odczuwalne opóźnienia.

Największym problemem są pętle, w których każda iteracja:

• odczytuje wartość komórki (np. Cells(i, 1).Value),
• na tej podstawie wykonuje logikę,
• następnie zapisuje coś z powrotem (np. Cells(i, 2).Value = wynik).

Przy 100 wierszach nie ma to znaczenia. Przy 100 000 wierszy jest to 200 000–300 000 przełączeń pomiędzy VBA i Excelem, plus wszystkie działania arkusza (przeliczanie formuł, odświeżanie widoku). Właśnie tutaj tablice pokazują swoją przewagę – zamiast tysięcy drobnych wywołań wykonuje się jeden odczyt zakresu do tablicy, tysiące operacji w pamięci oraz jeden zapis zmodyfikowanej tablicy z powrotem do arkusza.

Odświeżanie ekranu, przeliczanie formuł i zdarzenia

Excel przy każdej zmianie w arkuszu może:

• odmalować ekran (widoczny arkusz, paski przewijania),
• przeliczyć formuły w zależnych komórkach,
• wywołać zdarzenia, np. Worksheet_Change,
• uruchomić reguły formatowania warunkowego.

To wszystko jest potrzebne użytkownikowi przy ręcznej pracy, ale przy wykonywaniu makra zwykle tylko spowalnia. Dlatego podstawowy pakiet optymalizacyjny obejmuje najczęściej:

• Application.ScreenUpdating = False – wyłączenie odświeżania ekranu,
• Application.Calculation = xlCalculationManual – ręczne przeliczanie formuł po zakończeniu operacji,
• Application.EnableEvents = False – zablokowanie zdarzeń, które mogłyby odpalać inne makra.

Te trzy linijki potrafią przyspieszyć makro nawet bez zmiany logiki na tablice, bo ograniczają koszt pobocznych działań Excela. Optymalizacja przez tablice wchodzi później, kiedy tych działań i tak jest zbyt dużo.

Pułapka .Select, .Activate i „chodzenia” po arkuszu

Kod nagrywany rejestratorem makr przepełniony jest sekwencjami typu:

• Range("A1").Select,
• Selection.Offset(1, 0).Select,
• ActiveCell.FormulaR1C1 = "...".

Takie podejście:

• spowalnia, bo każda zmiana zaznaczenia to dodatkowa akcja interfejsu,
• jest podatne na błędy (użytkownik kliknie gdzie indziej, makro się gubi),
• utrudnia przejście do pracy w pamięci i wydajnych wzorców.

Optymalna praca na komórkach polega na używaniu bezpośrednich odwołań do Range i Cells bez zaznaczania, np. Range("A1").Value = 10 zamiast wybierania komórki. Nawet jeżeli nie używasz jeszcze tablic, samo wyrzucenie .Select i .Activate skraca czas wykonania makra.

Setki wywołań pojedynczych komórek vs jeden duży odczyt

Najprostszy przykład różnicy w podejściu wygląda tak:

• Wariant 1: pętla For i = 1 To 10000, a w każdym przebiegu odczyt Cells(i, 1).Value oraz zapis Cells(i, 2).Value.
• Wariant 2: jednorazowy odczyt zakresu Range("A1:A10000").Value do tablicy, przetworzenie tablicy w pamięci, jednorazowy zapis wyników do Range("B1:B10000").

W pierwszym wariancie liczba odwołań do Excela idzie w dziesiątki tysięcy. W drugim – w kilkanaście (kilka większych bloków). W praktyce różnica czasowa jest często drastyczna: od kilku–kilkunastu sekund do ułamków sekundy. Właśnie na tym polega główna korzyść z odczytu zakresu do tablicy i operowania na danych w pamięci.

Tablice w VBA – czym są i dlaczego przyspieszają kod

Tablica jako blok pamięci trzymany w VBA

Tablica w VBA to uporządkowany zbiór elementów tego samego typu (lub typu Variant) przechowywany w pamięci, a nie w arkuszu. Można ją traktować jak „wewnętrzną mini-tabelę” dostęp­ną wyłącznie dla kodu VBA. Dzięki temu:

• odczyt danych z arkusza jest jednorazowy (lub kilka razy, ale na dużych blokach),
• większość operacji odbywa się w pamięci, bez udziału silnika obliczeniowego Excela,
• zapis wyników z powrotem do arkusza także jest wykonywany rzadko, ale w większych porcjach.

Takie podejście minimalizuje ilość „drogich” interakcji z obiektowym modelem Excela. Dlatego, gdy mowa o „VBA i wydajności: kiedy używać tablic, a kiedy pracy na komórkach”, w praktyce chodzi o decyzję: czy wyciągnąć dane do tablicy i przetworzyć wszystko na raz, czy wykonywać logikę bezpośrednio na Range.

Typy tablic: statyczne, dynamiczne, tablice z zakresu

W kontekście Excela pojawiają się przede wszystkim trzy rodzaje tablic:

• Tablice statyczne – rozmiar znany z góry, np. Dim arr(1 To 100) As Long.
• Tablice dynamiczne – rozmiar ustalany w trakcie działania, np. Dim arr() As String + ReDim arr(1 To n).
• Tablice wariantowe wczytywane z zakresu – np. Dim arr As Variant: arr = Range("A1:D1000").Value.

W scenariuszach typowo „excelowych” najczęściej używa się trzeciego wariantu, bo:

• wczytuje się jedną instrukcją cały prostokątny zakres do dwuwymiarowej tablicy,
• nie trzeba ręcznie obliczać rozmiarów – robi to Excel,
• taka tablica jest naturalnie powiązana z układem danych w arkuszu (wiersze/kolumny).

Tablice statyczne i dynamiczne mają sens szczególnie wtedy, gdy przygotowujesz bufor na wyniki, liczysz agregaty, budujesz strukturę wyjściową o znanym kształcie lub działasz bezpośrednio na kolekcji wartości, niezależnie od arkusza.

Variant zawierający tablicę a tablica typowana

Variant jako nośnik danych z arkusza

Kiedy odczytujesz zakres do tablicy przez arr = Range("A1:D1000").Value, zmienna arr jest typu Variant, który zawiera tablicę. To wygodny mechanizm, bo:

• nie musisz deklarować wymiarów – Excel sam tworzy odpowiednią strukturę,
• łatwo przepisać dane z powrotem: Range("A1:D1000").Value = arr,
• taka tablica jest zawsze dwuwymiarowa (nawet przy jednej kolumnie lub jednym wierszu).

Różnica w stosunku do tablicy typowanej (np. Dim arr(1 To 100, 1 To 4) As Double) polega na tym, że:

• tablica w Variant może mieć mieszane typy w komórkach (tekst, liczba, data, pustka),
• dostęp do elementów jest minimalnie wolniejszy niż przy jednolitym typie,
• nie masz bezpośredniej kontroli nad typowaniem – Empty, Null, puste komórki wymagają obsługi.

W typowych scenariuszach pracy z arkuszem wygoda przeważa nad minimalnym narzutem wydajnościowym, dlatego tablice „z zakresu” oparte na Variant są standardem przy optymalizacji makr.

Wymiarowanie i indeksy: skąd się biorą dolne i górne granice

Tablice wczytane z zakresu mają konkretne granice indeksów. Ustala je Excel. Najczęściej jest to:

• LBound(arr, 1) = 1 i UBound(arr, 1) = liczba wierszy zakresu,
• LBound(arr, 2) = 1 i UBound(arr, 2) = liczba kolumn zakresu.

Lepszą praktyką niż „zakładanie” tych wartości jest używanie funkcji LBound i UBound:

Dim r As Long, c As Long
For r = LBound(arr, 1) To UBound(arr, 1)
    For c = LBound(arr, 2) To UBound(arr, 2)
        ' logika przetwarzania
    Next c
Next r

Dzięki temu kod nie zależy sztywno od rozmiaru zakresu ani założeń o tym, gdzie zaczyna się indeksowanie. Przy pracy z różnymi arkuszami czy zmieniającymi się tabelami unika się subtelnych błędów (np. zgubiony pierwszy wiersz).

Tablice jednowymiarowe vs dwuwymiarowe

W VBA istnieje pokusa, żeby wszystko robić na tablicach dwuwymiarowych, bo tak je zwraca Excel. Czasami jednak wygodniej jest sprowadzić dane do jednego wymiaru, szczególnie przy prostych listach, np.:

• lista ID do przeszukania,
• kolejka zadań do wykonania,
• prosty wektor wyników (np. suma po wierszu).

Można wczytać zakres jako dwuwymiarową tablicę, a następnie przepisać go do jednowymiarowej struktury:

Dim src As Variant, arr() As Long
Dim i As Long

src = Range("A1:A1000").Value
ReDim arr(1 To UBound(src, 1))

For i = 1 To UBound(src, 1)
    arr(i) = CLng(src(i, 1))
Next i

Zysk to prostsze iteracje i bardziej przejrzysty kod w dalszej części procedury. Z drugiej strony, przy pracy z wieloma kolumnami i operacjami „wierszowymi” (np. filtr, walidacja, transformacja) naturalniej jest zostać przy układzie arr(wiersz, kolumna).

Praca na komórkach: kiedy jest wystarczająco szybka

Małe zakresy i prosta logika

Nie każdą operację trzeba przenosić do tablic. Jeśli:

• liczba komórek, których dotyka makro, jest mała (kilkadziesiąt / kilkaset),
• kod wykonuje się sporadycznie, a nie dziesiątki razy dziennie,
• logika jest bardzo prosta (kilka podstawień, ustawienie formatów),

to praca bezpośrednio na Range jest absolutnie wystarczająca. Narzut przełączania się między VBA a Excelem jest w takim przypadku pomijalny, a wysiłek przeprojektowania kodu na tablice może być większy niż zysk czasowy.

Zastosowania „jednostrzałowe” i makra pomocnicze

Wiele makr to drobne narzędzia uruchamiane ręcznie: przygotowanie raportu do wysyłki, jednorazowe przerzucenie danych między arkuszami, ustawienie filtrów. W takich miejscach liczy się:

• krótka ścieżka implementacji,
• łatwa modyfikacja przez kogoś innego,
• niska złożoność logiczna.

Jeśli obróbka trwa sekundy, a nie minuty, nie trzeba od razu przechodzić na masowe wczytywanie danych do tablic. Zazwyczaj wystarcza:

• usunięcie .Select/.Activate,
• kilka zbiorczych operacji na zakresach (np. .Copy Destination:=... zamiast pętli),
• ewentualne wyłączenie odświeżania ekranu.

Korzystanie z natywnych funkcji Excela

Są sytuacje, w których kod VBA stara się „ręcznie” robić to, co Excel robi natywnie bardzo szybko: sortowanie, filtrowanie, kopiowanie dużych bloków, wyszukiwanie z użyciem funkcji arkuszowych. Jeżeli logika daje się wyrazić:

• przez formuły arkuszowe,
• przez wbudowany sort / filtr,
• przez proste Range.Copy lub .AutoFill,

to często lepiej jest sterować tymi mechanizmami z poziomu VBA niż przenosić wszystko do tablic i ręcznie odwzorowywać zachowanie Excela. Natywne operacje na całych blokach są w wielu przypadkach bardzo dobrze zoptymalizowane.

Makra „interaktywne” i praca krok po kroku

Czasami użytkownik oczekuje, że makro będzie działać w sposób interaktywny: coś wybiera, makro uzupełnia kilka komórek; zmienia parametr, makro reaguje na bieżąco. W takim scenariuszu:

• przetwarzane są małe fragmenty arkusza,
• użytkownik śledzi efekty krok po kroku,
• łatwość modyfikacji kodu jest istotniejsza niż maksymalna szybkość.

Tutaj podejście „komórkowe” jest zupełnie w porządku. Tablice zyskują sens dopiero wtedy, gdy te interaktywne operacje zaczynają obejmować tysiące komórek lub wywołania makra są częste i masowe.

Kiedy przenosić dane do tablic: kryteria decyzji

Próg ilości danych i liczba przejść po zakresie

O tym, czy opłaca się użyć tablic, decyduje zwykle kombinacja dwóch czynników:

• liczba komórek, które trzeba odczytać/zapisać,
• liczba „przejść” po tych danych.

Jeśli jedno makro:

• dotyka dziesiątek tysięcy komórek,
• musi przejść po danych kilka razy (np. filtr, agregacja, wyszukiwanie powiązań),

to niemal zawsze opłaca się wczytać dane do tablicy na początku, wykonać całą logikę w pamięci i na końcu wypluć wynik jednym strzałem do arkusza. Nawet jeśli kod na tablicach wydaje się początkowo bardziej skomplikowany, zysk czasowy bywa wielokrotny.

Częstotliwość uruchamiania makra

Innym kryterium jest częstotliwość wywołań. Nawet jeśli pojedyncze uruchomienie trwa tylko kilka sekund, ale:

• makro jest uruchamiane wiele razy dziennie,
• jest elementem większego procesu (np. końcowe przygotowanie raportu),
• uruchamia się automatycznie w zdarzeniach (np. przy zmianach danych),

to opłaca się zainwestować w migrację logiki do tablic. Skrócenie pojedynczego przebiegu o kilkadziesiąt procent może oznaczać realne oszczędności czasu zespołu.

Złożoność warunków i transformacji

Im bardziej skomplikowane warunki filtrujące i przekształcenia danych, tym wyraźniej widać przewagę pracy w pamięci. Przykładowo:

• wielopoziomowe If...ElseIf... zależne od kilku kolumn,
• budowanie list wynikowych na podstawie wielu arkuszy,
• agregacja danych z grupowaniem (symulacja prostego „GROUP BY”).

Od strony arkusza takie operacje często kończą się gęstą siecią formuł pomocniczych, kopiowaniem, filtrowaniem i sortowaniem. Przeniesienie całości do tablic:

• redukuje ilość „szumu” w skoroszycie (mniej formuł pomocniczych),
• upraszcza ścieżkę obliczeń (kod zamiast ręcznych kroków),
• umożliwia dalsze skalowanie bez lawinowego spadku wydajności.

Wielokrotne odwołania do tych samych danych

Jeśli logika wymaga ciągłego „sprawdzania” tej samej tabeli (np. słownika stawek, listy dopuszczalnych wartości, tabeli kursów walut), to przeniesienie tych danych do tablicy lub struktury typu Scripting.Dictionary ma podwójną korzyść:

• ogranicza powtarzane odwołania do arkusza,
• skraca czas dostępu (wyszukiwanie po kluczu zamiast liniowego przechodzenia zakresu).

Częsty scenariusz: makro przegląda setki dokumentów (wierszy) i dla każdego musi sprawdzić kilka parametrów w tabeli referencyjnej. Odczytanie tej tabeli raz do tablicy lub słownika, a następnie pracy wyłącznie w pamięci potrafi zmienić minutową operację w ułamki sekundy.

Stabilność rozmiarów danych

Tablice najlepiej sprawdzają się wtedy, gdy dane mają w miarę stabilny kształt (prostokątny zakres, pełne wiersze). Dzięki temu można:

• wczytać je jednym odwołaniem Range(...).Value,
• bezpiecznie odwoływać się do kolumn/słupków po indeksach,
• utrzymać przejrzystą mapę: kolumna → indeks tablicy.

Jeśli dane są silnie „poszatkowane” (pojedyncze komórki rozrzucone po wielu arkuszach), trzeba najpierw je zorganizować: np. zbudować z nich prosty bufor w arkuszu lub od razu w pamięci (poprzez kolekcje, słowniki), a potem dopiero korzystać z typowej pracy na tablicach.

Wzorce pracy z tablicami – odczyt, przetwarzanie i zapis danych

Standardowy wzorzec: jeden odczyt, pętla w pamięci, jeden zapis

Najprostszy i jednocześnie najbardziej uniwersalny wzorzec wygląda tak:

1. Ustal zakres danych wejściowych (np. używając .CurrentRegion albo ostatniego użytego wiersza).
2. Wczytaj zakres do tablicy typu Variant.
3. Przeiteruj tablicę w pamięci, modyfikując wartości lub tworząc osobną tablicę wynikową.
4. Zapisz całą tablicę wynikową z powrotem do arkusza jednym poleceniem.

Przykładowo, transformacja danych w kolumnach A i B z wynikiem w kolumnie C:

Dim src As Variant, res As Variant
Dim r As Long, lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:B" & lastRow).Value

ReDim res(1 To UBound(src, 1), 1 To 1)

For r = 1 To UBound(src, 1)
    res(r, 1) = src(r, 1) & " - " & src(r, 2) ' przykładowa logika
Next r

Range("C1").Resize(UBound(res, 1), 1).Value = res

Cała istota polega na tym, że niezależnie od liczby wierszy, Excel widzi tylko:

• jeden odczyt Range(...).Value,
• jeden zapis Range(...).Value = ....

Przetwarzanie w miejscu vs budowanie osobnej tablicy wynikowej

Przy pracy z tablicą można modyfikować:

• bezpośrednio tablicę wejściową,
• albo budować osobną tablicę wynikową.

Modyfikacja „w miejscu” skraca kod, ale bywa mniej przejrzysta, gdy:

• logika jest wieloetapowa,
• potrzebujesz obu wersji danych (oryginał + wynik),
• wychodzisz poza zakres wejściowy (np. tworzysz dodatkowe kolumny).

Przykład modyfikacji w miejscu (nadpisanie jednej z kolumn wynikiem):

Modyfikacja danych bezpośrednio w tablicy

Jeżeli zakres wejściowy i wyjściowy mają ten sam kształt, często wystarczy nadpisywać dane bezpośrednio w tablicy src. Po zakończeniu pętli tę samą tablicę zapisujesz do arkusza:

Dim src As Variant
Dim r As Long, lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:C" & lastRow).Value

For r = 2 To UBound(src, 1) ' pomijamy nagłówek
    If src(r, 3) < Date Then
        src(r, 3) = Date ' podbijamy datę np. do dzisiaj
    End If
Next r

Range("A1").Resize(UBound(src, 1), UBound(src, 2)).Value = src

Takie podejście jest proste i szybkie, o ile:

• nie zmienia się liczba wierszy lub kolumn,
• nie potrzebujesz „czystego” oryginału po przetworzeniu,
• wiadomo z góry, że wszystkie modyfikacje mieszczą się w zakresie wejściowym.

Redukcja lub rozszerzanie liczby wierszy – tablica wynikowa o zmiennym rozmiarze

Często logika wymaga odfiltrowania części wierszy lub, przeciwnie, wygenerowania ich więcej. Przy pracy na arkuszu robi się z tego wiele operacji wstawiania/usuwania wierszy, co jest kosztowne. W tablicy można działać inaczej:

1. Przechodzisz po danych wejściowych.
2. Decydujesz, czy dany wiersz trafia do wyniku.
3. Zapisujesz tylko „zaakceptowane” rekordy do drugiej tablicy.

Przykład prostego filtrowania: zostaw tylko wiersze, gdzie w kolumnie B jest liczba > 0:

Dim src As Variant, res As Variant
Dim r As Long, outRow As Long
Dim lastRow As Long, colCount As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:D" & lastRow).Value

colCount = UBound(src, 2)
ReDim res(1 To UBound(src, 1), 1 To colCount) ' maksymalny możliwy rozmiar
outRow = 1

' kopiujemy nagłówek bez warunków
Dim c As Long
For c = 1 To colCount
    res(outRow, c) = src(1, c)
Next c
outRow = outRow + 1

For r = 2 To UBound(src, 1)
    If src(r, 2) > 0 Then
        For c = 1 To colCount
            res(outRow, c) = src(r, c)
        Next c
        outRow = outRow + 1
    End If
Next r

' ucinamy niewykorzystaną część
ReDim Preserve res(1 To outRow - 1, 1 To colCount)

Range("F1").Resize(UBound(res, 1), colCount).Value = res

Z punktu widzenia Excela wciąż są tylko dwie operacje I/O, mimo że liczba wierszy wyniku nie jest znana z góry.

Mapowanie kolumn na indeksy tablic – minimalizacja „magicznych numerów”

Przy większych tabelach pojawia się problem „który numer kolumny co oznacza?”. Używanie w kodzie gołych liczb typu src(r, 7) szybko prowadzi do błędów. Rozsądniej jest:

• zdefiniować stałe z indeksami kolumn,
• albo zbudować słownik nazwa kolumny → indeks.

Przykład ze stałymi:

Const COL_ID      As Long = 1
Const COL_NAME    As Long = 2
Const COL_AMOUNT  As Long = 3
Const COL_STATUS  As Long = 4

Dim src As Variant
Dim r As Long, lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:D" & lastRow).Value

For r = 2 To UBound(src, 1)
    If src(r, COL_AMOUNT) < 0 Then
        src(r, COL_STATUS) = "BŁĄD"
    End If
Next r

Taka konstrukcja znacznie ułatwia późniejszą rozbudowę i modyfikacje. Przy zmianie układu tabeli często wystarczy poprawić wartości stałych.

Jednowymiarowe kontra dwuwymiarowe tablice – kiedy które podejście

Zakres wczytany z arkusza do Variant tworzy tablicę dwuwymiarową: (wiersz, kolumna). W praktyce pojawiają się trzy scenariusze:

• praca na pełnej siatce (typowa tabela) – używasz dwuwymiarowej tablicy tak, jak jest,
• obróbka jednej kolumny – wygodniejsze jest przerzucenie jej do tablicy jednowymiarowej,
• budowanie list (np. unikalne wartości) – naturalna jest lista jednowymiarowa.

Przekonwertowanie kolumny na tablicę jednowymiarową:

Dim src As Variant, arr() As Variant
Dim r As Long, lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:A" & lastRow).Value

ReDim arr(1 To UBound(src, 1) - 1) ' bez nagłówka

For r = 2 To UBound(src, 1)
    arr(r - 1) = src(r, 1)
Next r

Na takiej liście łatwiej wykonać proste operacje, np. sortowanie własną metodą czy wyszukiwanie, szczególnie jeśli wynik nie musi wracać od razu do arkusza.

Tworzenie tablic dynamicznych przy nieznanej z góry liczbie pozycji

Gdy z góry nie wiadomo, ile elementów trafi do tablicy (np. budowa listy błędów, unikalnych kodów), najprostsza droga to:

1. zacząć od niewielkiego rozmiaru,
2. przy zapełnieniu – ReDim Preserve i powiększyć tablicę,
3. po zakończeniu przyciąć ją do faktycznego rozmiaru.

Przykład zbierania identyfikatorów wierszy, które spełniają warunek:

Dim src As Variant, ids() As Long
Dim r As Long, lastRow As Long
Dim cap As Long, cnt As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:C" & lastRow).Value

cap = 100
ReDim ids(1 To cap)
cnt = 0

For r = 2 To UBound(src, 1)
    If src(r, 3) = "BŁĄD" Then
        cnt = cnt + 1
        If cnt > cap Then
            cap = cap * 2
            ReDim Preserve ids(1 To cap)
        End If
        ids(cnt) = src(r, 1) ' np. kolumna ID
    End If
Next r

If cnt > 0 Then
    ReDim Preserve ids(1 To cnt)
End If

Takie powiększanie „skokowe” (np. podwajanie rozmiaru) eliminuje setki małych ReDim Preserve, które byłyby kosztowne czasowo.

Łączenie tablic z obiektami: słowniki i kolekcje

W wielu przypadkach czysta tablica nie wystarczy, bo potrzebujesz szybkiego wyszukiwania po kluczu. Naturalny wzorzec to:

• wczytać dane tabelaryczne do tablicy,
• na ich podstawie zbudować słownik (Scripting.Dictionary) lub kolekcję,
• w docelowej pętli korzystać wyłącznie z tej struktury w pamięci.

Przykład prostego słownika kod → stawka:

Dim src As Variant
Dim r As Long, lastRow As Long
Dim rateDict As Object

Set rateDict = CreateObject("Scripting.Dictionary")

lastRow = Sheets("Stawki").Cells(Rows.Count, "A").End(xlUp).Row
src = Sheets("Stawki").Range("A2:B" & lastRow).Value

For r = 1 To UBound(src, 1)
    If Not rateDict.Exists(src(r, 1)) Then
        rateDict.Add src(r, 1), src(r, 2)
    End If
Next r

Potem w głównym kodzie możesz wielokrotnie sięgać do rateDict(kod) praktycznie bez kosztu przeszukiwania zakresu.

Buforowanie danych między wywołaniami – tablice jako cache

Jeżeli ta sama tabela referencyjna jest używana przez kilka procedur lub wielokrotnie w zdarzeniach (np. Worksheet_Change), częste wczytywanie jej z arkusza jest marnowaniem czasu. Lepiej:

• przechować ją w module w postaci zmiennej modułowej,
• odświeżać tylko wtedy, gdy faktycznie się zmieniła,
• udostępniać ją innym procedurom jako gotową tablicę.

Przykładowy szkic:

Private ratesCache As Variant
Private ratesLoaded As Boolean

Public Sub LoadRates()
    Dim lastRow As Long
    With Sheets("Stawki")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ratesCache = .Range("A2:B" & lastRow).Value
    End With
    ratesLoaded = True
End Sub

Public Function GetRate(ByVal code As String) As Double
    Dim r As Long

    If Not ratesLoaded Then LoadRates

    For r = 1 To UBound(ratesCache, 1)
        If ratesCache(r, 1) = code Then
            GetRate = ratesCache(r, 2)
            Exit Function
        End If
    Next r
End Function

W prostszych zastosowaniach wystarczy taka liniowa pętla. Przy większych tabelach sensownie jest na starcie zbudować słownik z tej tablicy.

Optymalizacja pętli po tablicach – kilka praktycznych drobiazgów

Na dużych zestawach danych sama konstrukcja pętli ma już znaczenie. Kilka zasad:

• obliczaj UBound przed pętlą, nie w każdej iteracji,
• unikaj wywołań funkcji arkuszowych wewnątrz dużych pętli – lepiej przenieść logikę do czystego VBA,
• stosuj Long jako typ licznika zamiast Integer.

Przykład „lepszej” konstrukcji pętli:

Dim src As Variant
Dim r As Long, lastRow As Long
Dim maxRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
src = Range("A1:B" & lastRow).Value

maxRow = UBound(src, 1)

For r = 2 To maxRow
    ' logika...
Next r

Na małych zakresach różnica jest nieodczuwalna, ale przy setkach tysięcy wierszy takie drobnostki sumują się do zauważalnych oszczędności.

Przetwarzanie wieloarkuszowe – jeden bufor zamiast wielu odwołań

Zdarza się, że logika musi zaglądać do kilku arkuszy w celu zbudowania jednego wyniku. Bez tablic powstaje gęsta siatka odwołań Sheets(...).Cells(...). Zamiast tego:

• wczytaj każdy arkusz do oddzielnej tablicy,
• operuj wyłącznie na tych buforach w pamięci,
• na końcu zapisz wynik do wybranego arkusza jednym ruchem.

Przykładowy scenariusz: konsolidacja trzech raportów sprzedaży miesięcznej do jednej tabeli rocznej. Szybciej będzie:

1. dla każdego arkusza „M1”, „M2”, „M3” pobrać CurrentRegion do osobnej tablicy,
2. wyliczyć łączną liczbę wierszy,
3. przydzielić jedną dużą tablicę wynikową i sekwencyjnie przepisać dane.

W ten sposób Excel otrzymuje finalny zestaw sklejonych danych, zamiast być angażowanym w tysiące kopiowań między arkuszami.

Stopniowa migracja istniejących makr na tablice

Przepisanie dużego makra „na raz” na tablice bywa ryzykowne. Bezpieczniejszym podejściem jest podział na etapy:

1. wydzielenie fragmentu kodu, który najdłużej działa lub najwięcej razy odwołuje się do komórek,
2. zastąpienie samego tego fragmentu pracą na tablicy,
3. pomiar czasu przed i po zmianie,
4. powtórzenie procesu dla kolejnych wąskich gardeł.

Przykład z praktyki: procedura raportowa, która pierwotnie przechodziła pętlą po każdym wierszu arkusza i w środku tej pętli odczytywała kilka dodatkowych komórek z innego arkusza. Po pierwszej iteracji optymalizacji:

• główny zakres wczytano raz do tablicy,
• tabelę referencyjną wczytano do drugiej tablicy i zbudowano z niej słownik,
• pętla w pamięci zastąpiła tysiące odwołań arkuszowych.

Czas działania spadł z poziomu kilkudziesięciu sekund do poniżej sekundy, przy minimalnej zmianie interfejsu użytkownika (te same przyciski, ten sam raport końcowy).

Kontrola pamięci i granice podejścia tablicowego

Tablice nie są darmowe – przy ogromnych zakresach rozmiar wczytanych danych może być istotny. Zwykle da się bez problemu obsłużyć:

• setki tysięcy wierszy i kilkanaście kolumn,
• kilka takich tablic jednocześnie, o ile nie uruchamiasz równolegle innych ciężkich procesów.

Problemy mogą wystąpić, gdy:

• wczytujesz bardzo szerokie tabele (dziesiątki–setki kolumn) z formułami i formatami,

Najczęściej zadawane pytania (FAQ)

Kiedy w VBA naprawdę opłaca się przejść z pracy na komórkach na tablice?

Kluczowym kryterium jest skala danych i częstotliwość uruchamiania makra. Jeśli obrabiasz jednorazowo kilkadziesiąt czy kilkaset komórek, proste pętle po Range/Cells zwykle wystarczą i nie ma sensu komplikować kodu tablicami.

Tablice zaczynają mieć duże znaczenie, gdy:

• zasięg rośnie do kilku tysięcy komórek i więcej,
• makro działa cyklicznie (np. codziennie, wielokrotnie dziennie),
• ten sam kod będzie uruchamiać wiele osób na wspólnych plikach.

W takich warunkach sumaryczny zysk z przerzucenia logiki do pamięci liczony jest w godzinach oszczędzonego czasu pracy.

Jak rozpoznać, że moje makro w Excelu jest „za wolne” i wymaga optymalizacji?

Daje się zauważyć kilka typowych objawów. Excel często przechodzi w stan „Brak odpowiedzi”, pasek postępu na dole okna zamiera na długie chwile, a użytkownicy mówią wprost, że klikają uruchomienie makra i „idą po kawę”.

Czerwone flagi to także:

• makro blokuje wspólny plik na kilka minut, powodując konflikty zapisu,
• każde dorzucenie kolejnego warunku czy filtru w kodzie jeszcze mocniej go spowalnia,
• czas wykonania rośnie dramatycznie po zwiększeniu liczby wierszy w źródle.

Jeśli widzisz taki wzorzec, prawdopodobnie pętla zbyt często „chodzi” po arkuszu zamiast przetwarzać dane w pamięci.

Czy tablice w VBA zawsze są szybsze niż praca na Range/Cells?

Przy dużej liczbie iteracji – praktycznie tak, pod warunkiem że liczba odwołań do arkusza jest ograniczona do kilku dużych odczytów i zapisów. Pętla po tablicy w pamięci jest zwykle dziesiątki lub setki razy szybsza niż ta sama logika realizowana jako tysiące odczytów/zapisów Range.

Przy małych zakresach (do ok. 500–1000 komórek) różnica bywa pomijalna, a tablica może wręcz niepotrzebnie komplikować kod. Dlatego tablice mają największy sens tam, gdzie:

• jest dużo iteracji,
• logika jest rozbudowana,
• ta sama operacja ma być powtarzana regularnie.

Od ilu wierszy/komórek powinienem zacząć myśleć o optymalizacji VBA?

Nie ma jednej magicznej liczby, ale kilka orientacyjnych progów dobrze porządkuje temat:

• do ok. 500–1000 komórek – prosta praca na komórkach jest zwykle wystarczająca,
• powyżej kilku tysięcy komórek – opłaca się rozważyć tablice, zwłaszcza dla rozbudowanych warunków,
• powyżej kilkudziesięciu tysięcy komórek – tablice i zbiorcze operacje praktycznie stają się koniecznością.

Jeśli obsługujesz raport z kilkoma arkuszami po kilkanaście tysięcy wierszy każdy, planowanie tablic i ograniczanie odwołań do Range to już nie „fajny dodatek”, tylko warunek sprawnego działania.

Dlaczego pętle po komórkach Range tak bardzo spowalniają makra?

Każde odwołanie do Range, Cells, Worksheet czy Workbook to przełączenie pomiędzy kodem VBA a silnikiem Excela. Pojedyncze wywołanie jest tanie, ale przy setkach tysięcy odczytów i zapisów koszt tych przełączeń staje się głównym źródłem opóźnień.

Typowy „zabójca wydajności” wygląda tak: w każdej iteracji pętli odczytujesz komórkę, przetwarzasz wynik i zapisujesz go w inną komórkę. Przy 100 wierszach nie ma problemu. Przy 100 000 wierszy robi się z tego kilkaset tysięcy przełączeń plus przeliczanie formuł, odświeżanie ekranu i wywoływanie zdarzeń. Zastąpienie tego jednym odczytem całego zakresu do tablicy i jednym zbiorczym zapisem na końcu drastycznie skraca czas działania.

Jakie są „szybkie wygrane” w przyspieszaniu makr bez przebudowy na tablice?

Nawet bez zmiany logiki na tablice można sporo zyskać, ograniczając działania Excela w tle. Typowy pakiet startowy to:

• Application.ScreenUpdating = False – wyłączenie odświeżania ekranu,
• Application.Calculation = xlCalculationManual – tymczasowe wyłączenie automatycznego przeliczania formuł,
• Application.EnableEvents = False – blokada zdarzeń, które mogłyby odpalać inne makra.

Po zakończeniu makra stany trzeba oczywiście przywrócić. W wielu projektach już samo to skraca czas wykonywania z minut do sekund, a dopiero kolejnym krokiem jest przenoszenie przetwarzania do tablic.

Czy zawsze trzeba unikać .Select i .Activate w VBA?

.Select i .Activate nie są „zakazane”, ale w makrach nastawionych na wydajność robią więcej szkody niż pożytku. Każde zaznaczanie komórek czy aktywowanie arkusza to dodatkowa operacja interfejsu, która spowalnia kod i zwiększa ryzyko błędów (np. gdy użytkownik kliknie coś w trakcie działania makra).

Zamiast „chodzenia” po arkuszu poprzez Select/ActiveCell:

• odwołuj się bezpośrednio do Range/Cells (np. Worksheets(„Dane”).Range(„A1”)),
• pracuj na z góry zdefiniowanych zakresach i tablicach,
• manipuluj danymi w pamięci, a do arkusza zapisuj gotowy wynik „za jednym zamachem”.

To podejście jest stabilniejsze, szybsze i ułatwia dalszą rozbudowę oraz profilowanie wydajności.

Co warto zapamiętać

• O poziomie optymalizacji decyduje kontekst: jednorazowe makro do kilkudziesięciu wierszy nie wymaga zaawansowanych zabiegów, ale proces uruchamiany codziennie na wspólnych plikach szybko zwróci inwestycję w szybszy kod.
• Kluczowy problem to skala danych: przy setkach komórek praca bezpośrednio na Range jest akceptowalna, natomiast przy dziesiątkach tysięcy wierszy kumulują się mikroopóźnienia i Excel zaczyna „mielić” skoroszyt.
• Tablice drastycznie redukują liczbę odwołań do Excela – odczytujesz i zapisujesz dane zbiorczo, a całą logikę pętli wykonujesz w pamięci, co bywa dziesiątki lub setki razy szybsze niż iterowanie po komórkach.
• Praktyczna granica: do ok. 500–1000 komórek wystarczy zwykle praca na komórkach, powyżej kilku tysięcy rozsądnie jest przejść na tablice, a przy kilkudziesięciu tysiącach operacje w pamięci stają się de facto koniecznością.
• Sygnały alarmowe to m.in. częsty stan „Brak odpowiedzi”, długie zawieszki paska postępu, blokowanie wspólnych plików oraz każda zmiana logiki, która zauważalnie spowalnia makro – wtedy trzeba przeanalizować, ile czasu kod spędza na „chodzeniu” po arkuszu.
• Praca na komórkach jest prostsza i bardziej intuicyjna, ale wolniejsza; tablice wymagają lepszego zaplanowania struktury danych i pracy z indeksami, jednak w zamian dają dużą poprawę wydajności kosztem większego zużycia pamięci.