ActiveCell.FormulaR1C1=”=RC[1]+RC[2]”

Witam

Dziś kilka słów o tym jak można poprawić już zarejestrowane formuły w makrach, aby od nowa nie rejestrować całego makra, co jest dość istotne, jeśli makro jest długie.

Formuła dodawania dla pól: B1 i C1 wpisana w Arkuszu np. w komórce A1 wygląda tak: =B1+C1 (oczywiste), ale w kodzie VBa już wygląda to tak: ActiveCell.FormulaR1C1 = “=RC[1]+RC[2]” gdzie “ActiveCell.” oznacza aktualnie zaznaczoną komórkę (w tym wypadku A1), a “FormulaR1C1“, że wpisana zawartość jest formułą matematyczną.

Co oznacza reszta?

Reszta to odwołanie względne do wiersza “R” i kolumny “C”. Może być jeszcze odwołanie  bezwzględne (odwołanie do komórki przez $ postawiony przed literą, cyfrą lub oboma).

kombinacji dla odwołania jest 8:

  1. R[-1]C[-1].
  2. R[-1]C.
  3. R[-1]C[1].
  4. RC[-1]
  5. RC[1]
  6. R[1]C[-1]
  7. R[1]C
  8. R[1]C[1]
  9. RC – jest odwołaniem do pola w którym wpisana jest formuła i Excel taką formułę zgłasza jako odwołanie cykliczne.

Można to też zobrazować poglądowym rysunkiem.

Powyżej zaprezentowany opis dotyczy odwołania względnego tzn. względem pola, w którym jest wpisana formuła. Jeśli formułę przekopiujemy do innego pola, to obliczenia będą dokonywane na podstawie innych pól, ale tak samo ułożonych względem pola, w którym jest wpisana formuła.

A co teraz zrobić aby np. część napisanego równania tak zmodyfikować, żeby pierwsza liczba sumy była zawsze brana z B2? W arkuszu taka modyfikacja jest prosta i polega na zmianie B2 w $B$2. W zarejestrowanym makrze zmiana jest równie “kosmetyczna” i wystarczy odwołać się bezpośrednio do komórki, czyli w tym wypadku zamiast RC[-1] wpisać: R1C2 – co dosłownie oznacza Wiersz pierwszy, kolumna druga. W przykładzie tak został tak zmodyfikowany zapis, aby zawsze się odwoływać do tej komórki, ale można odwoływać się do wybranego wiersza np R10C[2] i zmieniać kolumny, lub do wybranej kolumny R[-2]C5 i zmieniać wiersze.

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”

Dostęp do danych w Arkuszu.

Jak większość wie do danych w arkuszach możemy ograniczyć dostęp zmieniając właściwości komórek i włączając ochronę arkusza.

Każda komórka arkusza ma możliwość ustawienia dwóch istotnych parametrów w zakładce “ochrona”. Jest tam opcja zablokuj i ukryj.

Opcja zablokuj przeznaczone jest do blokowania możliwości edycji danych zawartych w komórce.

Natomiast opcja ukryj sprawia, że zaznaczając taką komórkę w wierszu formuły nie widzimy jej zawartości – trudniej jest określić czy dana tam zawarta, to wartość, czy wynik obliczeń formuły. Dodatkowo opcja ukryj sprawia, że w przypadku niezaznaczonej opcji zablokuj wchodząc w tryb edycji komórki pole staje się puste – znika wcześniej widoczna tam zawartość.

Ustawienie powyższych opcji nic nie wnosi w działanie arkusza aż do momentu, kiedy włączymy jego ochronę. Włączenie ochrony wywołuje następujące skutki:

  • komórki z włączoną opcją zablokuj nie mogą być edytowane,
  • komórki z włączoną opcją ukryj nie wyświetlają w pasku adresu swojej zawartości po ich zaznaczeniu a w trybie edycji “znika” ich zawartość,
  • komórka z włączonymi oboma właściwościami nie wyświetla po zaznaczeniu w wierszu formuły swojej zawartości.

Powiązanie opcji zablokuj i ukryj często jest stosowane (w zależności od konfiguracji) do zabezpieczenia formuł przed niepowołaną edycją a czasem również do ukrycia treści formuły.

Arkusz w trybie chronionym i niechronionym to często stosowane i ogólnie znane tryby jego pracy, ale nie jedyne do wykorzystania! Podczas włączania ochrony arkusza możemy przed dodatkowe opcje zmodyfikować zakres uprawnień użytkownika, ale o tym kiedy indziej….

Jest jeszcze trzeci mniej znany, ale bardzo przydatny tryb pracy arkusza szczególnie tym, którzy wykorzystują makra do obsługi arkuszy, czy automatyzacji powtarzalnych czynności.

Arkusz można ustawić w tryb InterfaceOnly. W tym trybie z poziomy makra możemy zmieniać zawartość komórek, ale użytkownik już nie, chyba, że ma odblokowany dostęp do zakresów, czy wybranych komórek.

Niestety ten tryb pracy przynajmniej ja umiem ustalić tylko z poziomu makra.

Typowy kod generowany przez rejestrator makr przy blokowaniu arkusza wygląda następująco:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions

Natomiast jeśli chcemy arkusz ustawić w tryb InterfaceOnly należy ten kod zmodyfikować do postaci:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterFaceOnly:=InterfaceOnly
ActiveSheet.EnableSelection = xlNoRestrictions

W tak zablokowanym arkuszu możemy z poziomu makra zmieniać jego zawartość, ale niestety są ograniczenia dla części funkcjonalności dostępnych z poziomu makr.

Na forach trafiłem na takie operacje niedostępne w trybie interfaceonly jak:

  • PivotCache.Refresh,
  • AddComment,
  • FormatConditions,
  • Validation.Add

Możliwe, że jest więcej, ale ja na razie nie trafiłem. Sam zmagałem się z 2 i czwartym ograniczeniem. Skończyło zdjęciem ochrony na czas wykonywania kodu tego przez makro i potem ponownego zabezpieczenia.

Niestety o ile tryb chroniony i niechroniony ustalamy na stałe o tyle tryb jest interfaceonly “ulotny” i przy ponownym otwarciu arkusza trzeba go ponownie ustawiać, choć osobiście uważam, że “gra warta zachodu”, bo kodu jest mniej.

Na razie tyle. Może w wolnej chwili uda mi się to uzupełnić o więcej kodu.