Sprawdzanie właściwości przed jej ustawieniem

Niniejszy temat został wywołany na forum StackOverflow.Com gdzie padło pytanie o sens niniejszego kodu (który tutaj został lekko zmodyfikowany dla celów prezentacyjnych):


Na pierwszy rzut oka w istocie- jaki jest sens sprawdzać czy dany wiersz jest ukryty skoro chcemy i tak ostatecznie odkryć wszystkie dziesięć tysięcy wierszy. Wystarczy przecież wykonać poniższą pętlę:


Otóż pierwszy zapis jest bardzo uzasadniony i ma swoją wyraźną przewagę nad pętlą drugą (choć efekt ostatecznie będzie na 100% identyczny). Porównawczo wygląda to następująco (dla identycznych parametrów środowiska, w którym wykonany został test dla każdego wariantu):

1. pętla pierwsza, wszystkie wiersze były uprzednio odkryte- czas wykonania- 0.2 sek
2. pętla druga, wszystkie wiersze były uprzednio odkryte- czas wykonania- 13.1 sek
3. pętla pierwsza, wszystkie wiersze były uprzednio ukryte- czas wykonania- 15.6 sek


Z czego wynikają różnice. Zasadniczo z prostego założenia, zgodnie z którym odczyt właściwości odbywa się szybciej niż jej ustawienie. Jeżeli istnieje uzasadnienie, że większość elementów (tu: wierszy) może nie wymagać ustawiania właściwości (tu: odkrywania) to warto uprzednio sprawdzić bieżący stan danej właściwości (tu: czy wiersz jest ukryty czy odkryty). Zasadę tę warto stosować do wszystkich właściwości o ile pracujemy na relatywnie dużej kolekcji.

Kolejność ma znaczenie czyli o porządkach w kolekcji

Chyba nie każdy (szczególnie początkujący) programista zdaje sobie sprawę z faktu, że elementy każdej z kolekcji ułożone są w określonym porządku. Często porządek ten nie ma znaczenia dla zadania jakie realizujemy, w innych sytuacjach wiedza ta może wpłynąć na prawidłowość wykonania operacji lub szybkość jej wykonania. Poniżej kilka przykładów, informacji i ciekawostek dot. tego zagadnienia w odniesieniu do aplikacji Excel i Word, gdzie każdy z przykładów zostanie przedstawiony w zapisie pętli For Each.

1. Skoroszyty:


pętla wykonywać się będzie w kolejności w jakiej skoroszyty były otwarte lub utworzone.

2. Arkusze


pętla wykona się w kolejności od lewego do prawego arkusza. Nie ma znaczenia czy arkusz jest ukryty czy nie.

3. Komórki Cells

pętla zostanie wykonana w porządku w jakim piszemy i czytamy, od lewej do prawej, od górnego wiersza w dół zaznaczenia.

4. Komentarze w arkuszu Excel

o kolejności decyduje adres komórki, w której znajduje się komentarz, a następnie kolejność tej komórki w obszarze arkusza zgodnie z zasadami opisanymi dla komórek Cells w punkcie 3 powyżej.

5. Komentarze w dokumencie Word

pętla zostanie wykonana w kolejności występowania komentarzy w dokumencie, od początku dokumentu (od pierwszego komentarza) do końca (do ostatniego komentarza).

6. Zakładki w dokumencie Word. W tym przypadku dostępne są dwa warianty:

zakładki zostaną ułożone w kolejności ... alfabetycznej, wg nazw zakładek.


w tym przypadku zakładki zostaną ułożone w kolejności występowania w dokumencie.

 7. Kształty Shape w Excelu


o kolejności decyduje parametr ZOrderPosition, a więc ułożenie względem osi Z arkusza. Pierwszym kształtem będzie ten `najbliżej obszaru komórek`, ostatni to ten `najbliżej nas.`

Oczywiście każda z kolekcji posiada swoją własną kolejność. Powyższe wybrane przykłady mają na celu zwrócenie uwagi na ten aspekt. Dla innych obiektów warto przeprowadzić swoje własne próby.

Obszar Range dwuwymiarowy do tablicy Array jednowymiarowej

Jedną z typowych operacji wykonywanych w VBA jest pobieranie danych z kolejnych komórek arkusza i wykonywanie określonych działań na pobranych wartościach. Proste działanie w którym najczęściej wykorzystujemy prostą pętlę For...Next.

Co jednak ważne, w sytuacji gdy operacja dotyczy wielu komórek o wiele bardziej efektywnym pozostaje przeniesienie wartości komórek do tablicy Array i przeprowadzenie dalszych działań na elementach tablicy. W sytuacji tej należy jednak pamiętać, że utworzona tablica pozostaje tablicą dwu-wymiarową.

Sytuacja ta ma miejsce także wtedy gdy pobieramy dane z pojedynczej kolumny lub pojedynczego wiersza arkusza. Spójrzmy na poniższy przykład.

 utworzenie poniższych tablic:

sprawi, że obie tablice będą dwuwymiarowe. Pobranie z nich wartości C będzie wymagało utworzenia następujących zapytań:


Istnieje jednak możliwość utworzenia z w/w tablic tablic jednowymiarowych. Aby to uczynić należy transponować tablice. Prześledźmy to w kolejnych krokach dla obu tablic jednocześnie:


W wyniku powyższego działania tablica rowArr pozostanie dwuwymiarowa podczas gdy colArr stanie się tablicą jednowymiarową. Aby więc pobrać wartość C z tablic wywołamy następujące instrukcje:


Jeżeli jednak tablicę rowArr transponujemy ponownie także i ona stanie się jednowymiarową:

Jak ukryć makro po stronie aplikacji Excel i zachować jego publiczny charakter

Wyobraźmy sobie sytuację, w której szereg współpracujących procedur Sub znajduje się w naszym projekcie VBA. Makra rozdzielone zostały na szereg modułów. Zależy nam jednak na tym, aby makra były dostępne z poziomu każdej inne procedury, a jednocześnie zależy nam na tym, aby szereg z tych makr nie był dostępny i widoczny z poziomu Excela...

Powyższe tytułem wstępu, a teraz uporządkujmy możliwe rozwiązania:

Wariant A- raczej oczywisty. Nadanie procedurze parametru Private:

    Private Sub MojaProcedura()

sprawi, że procedura dostępna będzie z poziomu tylko modułu w którym jest makro oraz okna Immediate z przy zachowaniu pełnej referencji: Module1.MojaProcedura. Makro nie będzie dostępne z poziomu aplikacji Excel.

Wariant B oczywisty. Dodanie u góry modułu wpisu:
  
    Option Private Module

co sprawi, że wszystkie makra danego modułu nie są widoczne po stronie Excela i jednocześnie zachowują swój publiczny charakter w zakresie pracy z nimi z poziomu innych modułów i okna Immediate.

Wariant C nie zawsze oczywisty. Konstrukcja publicznej procedury z parametrem:

    Public MojaProceduraParametr(boParametr as Boolean)

czyni z tej procedury procedurę o podobnej charakterystyce jak w wariancie B- nie jest widoczna po stronie aplikacji Excel, ale jest w pełni dostępna z poziomu okna Immediate i innych modułów. Oczywiście wywołując ją należy podać parametr nie mniej nie mamy żadnego obowiązku, aby ten parametr wykorzystać w dalszej części procedury.

Szersze wykorzystanie wariantu C dla aplikacji MS Word, gdzie ze względu na specyficzną sytuację związaną z makrami typu AutoExec rozwiązanie C jest szczególnie przydatne, opisane zostało pod poniższym tematem w serwisie StackOverflow:

Hide AutoExec() and AutoNew() macros from the macro list yet still have them run?

Wyrażenia regularne RegExp raz jeszcze

Postanowiłem raz jeszcze wrócić do zagadnienia związanego z wyrażeniami regularnymi. Tym razem poszerzę temat o dwa obszary- pobieranie określonego fragmentu n-tego elementu spełniającego kryterium wyszukiwania oraz włączenie RegExp do własnej funkcji użytkownika (UDF).

Spójrzmy na początek na poniższy przykładowy tekst:

    Questionnaire results from company web.
    Name: John Smith
    Phone: 1234567
    Name: Jan Kowalski
    Phone: 9876545321
    Name: Johan Schmitt
    Phone: 00112233

Z pomocą RegExp i VBA spróbujemy przygotować rozwiązanie, które umożliwi pobranie np. drugiego imienia  i nazwiska (Jan Kowalski) czy też trzeciego numeru telefonu (00112233) z naszego przykładowego tekstu.

Trudność pierwsza- musimy odnaleźć fragment tekstu, który zaczyna się od Name. W tym celu nasz wzorzec będzie miał postać:

W wyniku czego jesteśmy w stanie otrzymać kolekcję składającą się z elementów:

    Name: John Smith
    Name: Jan Kowalski 
    Name: Johan Schmitt 

Trudność druga- jak pobrać samo imię i nazwisko i pominąć początkowy fragment z wyników wyszukiwania? W tym celu będziemy musieli sięgnąć głębiej w metodę .Execute. Samo wywołanie tej metody tworzy kolekcję zawierającą wszystkie wystąpienia spełniające kryterium .Pattern. Istnieje jednak możliwość pobrania określonego fragmentu n-tego elementu sięgając do kolekcji .SubMatches. Elementami należącymi do tej kolekcji będą wszystkie fragmenty, które zostały ujęte w nawiasach w naszym wzorcu .Pattern.
Ogólna składnia metody .Execute wyglądałaby następująco:


Proponuję zebrać w całość nasz kod. Na początek testowa procedura wywołująca z dodatkowymi komentarzami wewnątrz kodu:


A teraz funkcja właściwa uwzględniająca przedstawione powyżej istotne elementy metody .Execute z dodatkowym komentarzem:

Po wywołaniu procedury Pobieranie_nTego_elementu() uzyskamy w oknie immediate dokładnie to czego szukaliśmy, a więc odpowiednio:

    Jan Kowalski
    00112233