ActiveCell.Value, a Cells(“”).Value i co z wydajnością makra?

Swego czasu rejestrując swoje operacje w celu ich późniejszej automatyzacji zawsze do kodu makra miałem wpisywany kod “ActiveCell” ….

Do pewnego momentu mi starczało, ale odwoływanie się przez ActiveCell jest chyba najwolniejszym rozwiązaniem wstawiania danych do komórki z poziomu makra.

Najczęściej ActiveCell wykorzystuję w zestawieniu z:

  1. ActiveCell.Value – wstawiam wartość,
  2. ActiveCell.FormulaR1C – wstawiam formułę,
  3. ActiveCell.FormulaArray – wstawiam formułę tablicową.

W pierwszym przypadku aby wstawić wartość do aktywnej komórki wystarczy np:
ActiveCell.Value=”10″

Jeśli chcemy wstawić jakąś prostą formułę, to używamy:
ActiveCell.FormulaR1C=”=R[-2]C+R[-1]C” – dodajemy wartości z dwóch wierszy powyżej zaznaczonego.

W trzecim przypadku wstawiamy formułę tablicową np:
ActiveCell.FormulaArray = “=SUM(R[1]C5:R[1]C10-RC5:RC10)”

W przypadku wstawia z poziomu makra formuł tablicowych są ograniczenia (ja doświadczyłem już dwóch, może jest więcej).

  • Nie można wstawić formuły tablicowej do scalonej komórki.
  • Długość formuły tablicowej jest ograniczona do 255 znaków (ograniczenie dotyczy wstawiania formuły z poziomu kodu VBa i raczej dotyczy to postaci wynikowej a nie kodu formuły).

Makra nagrywane przez Excel mają istotną wadę, jeśli mają zmieć zawartość dużej ilości komórek – są wolne!, ponieważ przed wstawieniem wartości do określonej komórki najpierw trzeba ją zaznaczyć, a do bardzo dużo zbędnych i czasochłonnych operacji.

Makro można trochę przyspieszyć dłubiąc w nim mało, albo niestety mocno je zmodyfikować, ale mieć konkretny efekt.

Proste rozwiązanie to dodanie kodu wyłączającego odświeżanie ekranu aplikacji na czas wykonania makra, które wykonywane każdorazowo przy zaznaczeniu kolejnej komórki.

Wystarczy w tym celu wstawić na początku makra kod:

Application.ScreenUpdating=False

a na końcu:

Application.ScreenUpdating=True

Efektem powyższego kodu jest wyłączenie odświeżania ekranu podczas pracy makra – czyli nie widzimy, czy coś się dzieje, ale jeśli działa to działa szybciej.

Mimo to nadal mamy mnóstwo zbędnego kodu w postaci wpisów:

Cells(“A5”).Select, który ma za zadanie zaznaczenie określonej komórki, do której następnie wstawiania jest wartość/formuła.

Tego kodu możemy się pozbyć, ale musimy już nad makrem trochę popracować i czasem wręcz sporo.

Kod wygenerowany automatycznie:
Cells(“A5”).Select
ActiveCell.Value
zmieniamy na kod:
Cells(“A5″).Value=”10”,
lub: Cells(1,5).Value=”10″.

Różnica między kodem wygenerowany a napisanym polega na tym, że nowy kod nie wymaga zaznaczenia komórki, aby wpisać do niej wartość/formułę.

W efekcie mamy znaczne zwiększenie wydajność całego makra i szybkość jego działania.

Aby mieć pewność, że makro się skończyło możemy na koniec wpisać kod komunikatu:

MsgBox “Koniec”,vbOKOnly+vbInformation,”Makro”,
lub tylko: MsgBox “Koniec”

Ten wpis został opublikowany w kategorii Excel. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *