Szybka kowersja daty w zapisie String do typu Date

W czasie jednego ze szkoleń z obszarów Access VBA pojawiło się pytanie dot. sposobu konwersji daty przekazywanej w postaci ciągu tekstowego w układzie YYYYMMDD na układ daty YYYY-MM-DD.

Pierwsze podejście, bardzo klasyczne i naturalne, to rozebranie otrzymanego tekstu na czynniki z wykorzystaniem instrukcji Left(), Right() i Mid(), a następnie wykorzystanie funkcji DateSerial() i stworzenie z tego daty. rozwiązanie to mogło by wyglądać następująco:

Tymczasem okazuje się, że z pomocą funkcji Format() możemy bardzo skutecznie skrócić powyższy zapis zyskując dodatkową elastyczność. Oto przykłady:

Oczywiście zachęcam do samodzielnych dalszych eksperymentów z wykorzystaniem funkcji Format.

Konwersja typu Integer na Long

Ze względu na ograniczoną ilość czasu jaką obecnie dysponuję i jaką chciałbym poświęcać na publikację opisów zagadnień i problemów z zakresu VBA od czasu do czasu będę dodawał linki do ciekawych zagadnień z poświęconych programowaniu w VBA. Jako aktywny programista i trener bardzo często spotykam się z problemami, ciekawymi pomysłami oraz informacjami, które nie zawsze są powszechnie znane i wykorzystywane.

Zaczynam od informacji związanej z automatyczną konwersją zmiennych typu Integer (uważanych za przestarzały typ zmiennych) na zmienną typu Long. Co ważne, automatyczna konwersja działa tylko w systemach 32bit'owych. Dodatkowe informację znaleźć można pod tymi linkami:

MSDN: The Integer Data Types
StackOverflow: Why Use Integere Instead Of Long?

Właściwości .Value, .Value2 i .Text a szybkość działania

W niniejszym poście nie będę tłumaczył różnic pomiędzy wskazanymi właściwościami obiektu Range, a więc .Value, .Value2 czy .Text. Na dobrą sprawę nie będę nic tłumaczył tylko odeślę czytelników do bardzo ciekawego wpisu, w którym autor pokusił się o wykonanie testów dot. szybkości działania wskazanych właściwości. I co ciekawe, różnice w działaniu są bardzo znaczące więc tym bardziej każdy z programistów VBA, któremu zależny na efektywnym programowaniu powinien sięgać po właściwą technikę.
Co też ważne, artykuł przypomina o istotnych różnicach w działaniu wspomnianych właściwości.

Link do artykułu:
TEXT vs. VALUE vs. VALUE2 – Slow TEXT and how to avoid it...

Wykorzystanie referencji R1C1 w praktyce (2/2)

Kontynuując tematykę praktycznego wykorzystania referencji R1C1 w pracy z formułami chciałbym wyjaśnić kiedy jej wykorzystanie ma największy sens. Z własnego doświadczenia wskażę na jedną ogólną sytuację- wprowadzanie do uporządkowanego zakresu komórek formuł, które ręcznie jesteśmy wstanie wprowadzić przez przeciągnięcie lub prostą operację Copy-Paste.

Przykład 1. 100 wierszy w kolumnach A:L zawiera przychody ze sprzedaży dla 12 miesięcy roku. Wprowadzenie sumy rocznej w kolumnie M można wykonać pętlą, ale można też wykonać prostą operacją z wykorzystaniem referencji względnej:

Wyjaśnienie: każda ze 100 kolejnych komórek w kolumnie M zawiera w praktyce tego samego typu referencję względną- sumuje obszar od 12 komórki w lewo do 1 komórki po lewej stronie względem kolumny wynikowej (M)

Przykład 2. Arkusz1 zawiera tabelę z listą produktów (kolumna A) i ich cenami (kolumna B)- łącznie 10 tyś wierszy. W Arkusz2 chcemy z pomocą funkcji WYSZUKAJ.PIONOWO odnaleźć ceny dla wybranych produktów, których nazwy znajdują się w kolumnie A (w 100 wierszach). W tym wariancie operacja ręczna również będzie polegała na przygotowaniu formuły w komórce B1 i przekopiowaniu jej w dół. Tak więc operację tą z poziomu kodu VBA możemy zapisać właśnie z wykorzystaniem referencji R1C1:

Wyjaśnienie: powyższa referencja zawiera zarówno adresowanie względne jak i bezwzględne. Pierwszy argument funkcji to adres względny- jedna komórka w lewo względem komórki wynikowej (w kolumnie B). Drugi argument to bezwzględna referencja do Arkusza2 i tabeli w obszarze A1:B10000. Tabela ta jest stała dla każdej komórki, do które wrzucamy formułę WYSZUKAJ.PIONOWO.

Na koniec chciałbym jeszcze zaznaczyć, że referencja R1C1 pojawia się nie tylko w pracy z właściwością obiektu Range.FormulaR1C1. W praktyce referencję tą można spotkać bardzo często w różnych (czasem zaskakujących) okolicznościach. W wątpliwych sytuacjach należy sięgnąć do pomocy lub na stronę Microsoft MSDN. Warto zwrócić też uwagę na fakt, że np. rejestrator makr preferuję taką właśnie formę zapisu dla wszelkiego rodzaju zawartości komórek. Niekiedy jednak referencja R1C1 może być stosowana zamiennie z referencją A1B1 lecz warto zweryfikować szczegóły i różnice w wykorzystaniu danego typu adresowania.

Wykorzystanie referencji R1C1 w praktyce (1/2)

Jednym z ważnych tematów omawianych w czasie prowadzonych kursów jest zagadnienie związane z wykorzystaniem referencji do zakresów określanych jako R1C1. Doświadczenie trenerskie pokazuje również, że zagadnienie to niekoniecznie wydaje się być proste do przyswojenia choć jednocześnie, po chwili zastanowienia i wykonaniu kilku ćwiczeń adresowanie komórek w stylu R1C1 staje się łatwiejsze. Spróbuję w tym wpisie krótko podsumować zasady dot. wykorzystania tej formy referencji.

Na początku chciałbym jednak przypomnieć- referencja R1C1 nie oznacza wyłącznie względnego stylu referencji. Z wykorzystaniem tego typu adresowania możemy także utworzyć referencję bezwzględną. I tak:

1. Jeżeli nasz adres R1C1 zawiera nawiasy kwadratowe to z pewnością będzie to adresowane względne. W tym wariancie dopuszczalne jest także wykorzystanie wartości ujemnych oraz pominięcie wartości liczbowych po literach R lub C.
2. Jeżeli w adresie R1C1 nie ma nawiasów kwadratowych oraz po literach R i C występują liczby to z pewnością mówimy o adresowaniu bezwzględnym.
3. I najważniejsze, warianty względnego i bezwzględnego adresowania mogą być ze sobą łączone w ramach jednego adresu.

Poniższe prosty przykłady pozwolą zobrazować szereg dostępnych wariantów adresowania R1C1. Załóżmy, że komórka Excela o adresie E5 zawierać będzie prostą formułę w stylu =A1, sprawdzimy następnie jaką postać ma adres R1C1 dla danej formuły:

Przykład 1: adres względny Wyjaśnienie: komórka A1 jest o 4 kolumny i wiersze odpowiednio w lewo i do góry względem komórki E5

Przykład 2: adres bezwzględny Wyjaśnienie: komórka B10 traktowana bezwzględnie, R10 to 10 wiersz (R = Row), a C2 to druga kolumna arkusza (C = Column)

Przykład 3: adres względny dla komórki będącej w tej samej kolumnie Wyjaśnienie: komórka E1 jest w tej samej kolumnie co E5, zwracany adres R1C1 wskazuje ten fakt przez brak wartości po C

Przykład 4: adres częściowo względny i bezwzględny Wyjaśnienie: kwadratowe nawiasy sugerują względność, kolumna jest więc o 3 w prawo od kolumny E i będzie to kolumna H. Brak nawiasów po R sugeruje adres bezwzględny dla wierszy i wskazuje na wiersz 10

W kolejnym wpisie przedstawię kilka praktycznych przykładów zastosowania formuły R1C1.

Pętla w oknie Immediate

Myślę, że każdy programista zna i używa okna Immediate w środowisku IDE (Integrated Development Editor) w swojej codziennej pracy. Myślę też jednak, że nie każdy wie, iż w oknie immediate można wykonywać nie tylko pojedyncze instrukcje, ale także zestaw instrukcji złożonych, do których zaliczyć można pętle czy też instrukcje warunkowe.

Wyobraźmy sobie, że naszym celem jest wykonanie następujących operacji:

• wstawienie formuły zaokrąglającej do szeregu komórek zawierającej wartości
• odkrycie wszystkich (ukrytych) arkuszy
• usunięcie wartości mniejszych od zera
• itp.

Każdą z tych operacji możemy wykonać tworząc odpowiednie procedury, tyle tylko, że w tym celu musimy:

• utworzyć moduł
• rozpocząć procedurę Sub
• zadeklarować zmienne
• zamknąć procedurę
• uruchomić ją.

Przyznam, że to dość dużo operacji jak na jednorazową akcję wykonaną dla kolekcji obiektów.

Tymczasem okazuje się, że wystarczy nam okno Immediate, w którym wpisujemy wszystkie niezbędne instrukcje rozdzielając je dwukropkiem. Dwukropek, zresztą nie tylko w oknie Immediate, jest symbolem zakończenia linii instrukcji. Przyjrzyjmy się przykładom dla w/w wybranych przypadków. Na co warto zwrócić uwagę korzystając z tej techniki:

• deklaracja zmiennych nie jest wymagana, wręcz nie jest możliwa w oknie Immediate
• z powodu braku deklaracji zmiennych niezbędna może być pełna deklaracja właściwości i kolekcji, np. Selection.Cells zamiast samego Selection, cell.value zamiast samego cell
• zapis instrukcji z małych/wielkich liter nie ma znaczenia, IntelliSense w tym aspekcie nie działa w oknie Immediate
• teoretycznie dopuszczalny jest zapis wieloliniowy jednak odbywa się to z wykorzystaniem znaku przeniesienia linii (symbolu underscore):

Uzupełnianie pustych komórek w arkuszu MS Excel

Muszę na wstępnie przyznać, że od czasu do czasu zdarza mi się przeglądać ofertę konkurencji głównie w poszukiwaniu inspiracji do tworzenia jeszcze lepszych kursów i z coraz ciekawszej oferty szkoleniowej.

Ostatnio trafiłem na stronie konkurencyjnej firmy szkoleniowej na wpis poświęcony zagadnieniu uzupełniania pustych komórek w arkuszu MS Excel. Autor w długim wywodzie prezentuje rozwiązanie oparte o układ dwóch zagnieżdżonych pętli i instrukcji warunkowych. Z pewnym niedowierzaniem przyjąłem to rozwiązanie- wyznaję bowiem zasadę, że jeżeli prezentować jakiś przykład to raczej w jego najlepszym wydaniu. Oto i rozwiązanie ciekawsze i bez wątpienia szybsze...

Zacznę może od podstawowej zasady- A w języku VBA- w pierwszej kolejności wykorzystujemy aplikację Excel i dostępne tam rozwiązania, dopiero potem sięgamy po kod VBA. Nasz problem rozwiążemy więc następująco:

• zaznaczamy obszar
• Menu >> Narzędzia główne >> Znajdź i zaznacz >> Przejdź do- specjalnie 
• w oknie, które się wyświetli wybieramy Puste i zatwierdzamy przyciskiem OK
• w pasku formuły wstawiamy formułę '=[komórka powyżej]' i wciskamy Ctrl+Enter

i to wszystko....

A jeżeli już musimy sięgnąć po  wariant z VBA to rozwiązanie problemu można osiągnąć w następujący prosty sposób:

• zaznaczamy obszar pamiętając, aby nie zaznaczyć pierwszego wiersza (bo w przypadku pustej komórki w pierwszym rzędzie i tak nie mamy skąd pobrać wartości skoro nic nad nią nie ma)
• jedyny kod VBA jaki musimy wykonać to:

Jak widać nie potrzebujemy pętli, instrukcji warunkowych, wystarczy prosta i skuteczna metoda.
Bardzo wiele z tego typu rozwiązań prezentowanych jest na naszych szkoleniach z programowania VBA dla Excela. W czasie prowadzonych kursów jak zawsze stawiamy na efektywne i praktyczne podejście do tworzenia programów.

Podświetlanie aktywnego wiersza w arkuszu Excel

Po dłuższej wakacyjnej przerwie czas na kolejny wpis tym razem wpis z pogranicza VBA i zaawansowanej techniki pracy z Excelem- jak uzyskać efekt podświetlenia dla aktywnego wiersza?

Wariant 1- tylko VBA

Wariant ten ma podstawową wadę polegającą na tym, że dokonujemy ‘podmiany’ wypełnienia w bieżącym wierszu, a przez to nie jesteśmy w stanie efektywnie przywrócić poprzedniego układu kolorów, o ile takie istniały (pomijam możliwość tworzenia zaawansowanych konstrukcji programistycznych). Zaletą jest to, że możemy podświetlić szerszy obszar niż tylko ten odpowiadający pojedynczemu wierszowi.

Niezbędny kod VBA wymaga oprogramowania zdarzenia Selection_Change. W tym celu proszę w wybranym module arkuszowym wkleić poniższy kod. Dodatkowe informacje znajdują się wewnątrz kodu w postaci komentarzy.

Wariant 2- VBA + formatowanie warunkowe

Wariant ten jest o wiele bardziej efektywny i wydajny. Co najważniejsze- nie zastępuje innych kolorów i formatowania występującego w arkuszu.

Krok 1- zaznaczamy obszar, w którym ma funkcjonować podświetlanie wiersza- w naszym przykładzie A1:K20 (ważne, obszar zaznaczamy od komórki A1 w kierunku K20, nie odwrotnie)

Krok 2- ustawiamy formatowanie warunkowe: Menu >> Narzędzia Główne >>Formatowanie Warunkowe >> Nowa Reguła… >> Użyj formuły do określenia komórek… >> w miejsce formuły wstawiamy =KOMÓRKA("wiersz")=WIERSZ(A1) >> określamy formatowanie po wciśnięciu przycisku >>Formatuj… >> Akceptujemy wszystkie ustawienia. 

Ważne! Komórkę A1 w podaje formule należy odpowiednio zmienić w przypadku zaznaczenia innego obszaru niż przykładowe A1:K20

Krok 3- ostatni krok to wymuszenie przeliczenia komórek po każdej zmianie zaznaczenia. W tym celu w module arkuszowym po stronie edytora VBA dodajemy prostą obsługę zdarzenia Selection_Change:

Przy okazji serdecznie zapraszam na jesienne kursy z programowania VBA. Tym razem zaplanowaliśmy kilka terminów w Warszawie, Krakowie i Wrocławiu. Nasze szkolenia jak zawsze przygotowane są na najwyższym poziomie, w oparciu o wieloletnią praktyczną widzę i ciągle zdobywane i poszerzane doświadczenie.

Wydajność i efektywność 2/2- Mikrooptymalizacja

Wracam do tematów związanych z optymalizacją i tym razem skupię się na drobiazgach, a więc mikroelementach, które potrafią mieć znaczący wpływ na szybkość wykonywanego kodu.

1. Instrukcje warunkowe:

• należy stosować wielostopniową instrukcję If...ElseIf...Else...End If w miejsce instrukcji Select Case
• nie należy stosować instrukcji IIf() gdyż każdorazowo sprawdza warunki dla prawy i fałszu
• używać krótką formę porównawczą If CzyPrawda Then... w miejsce If CzyPrawda=True Then...
• w pracy z tekstem nie używać modułowej deklaracji Option Compare Text. Gdy porównujemy teksty używać instrukcji UCase(), LCase() lub parametrów vbCompareText gdzie dostępne
• sprawdzając, czy zmienna przechowuje tekst używać konstrukcji If Len(tekst) =0 Then... zamiast If tekst ="" Then...
• przed ustawieniem właściwości, gdy nie mamy pewności jej bieżącego stanu, warto sprawdzić jej bieżącą wartość. Odczyt wartości właściwości wykonywany jest szybciej niż zmiana właściwości.

2. Obiekty i zewnętrzne biblioteki:

• nie używać ogólnej deklaracji As Object
• używać techniki wczesnego wiązania
• używać dłuższej techniki wczesnego wiązania, zamiast:

     Dim myObject As New ObjectType
        stosować:
  Dim myObject As ObjectType
  Set myObject = New ObjectType

• wykorzystywać instrukcję With...End With

3. Inne zagadnienia:

• co oczywiste lecz warte przypomnienia- zawsze stosujemy właściwy typ zmiennych, typ Variant nie powinien być wykorzystywany
• używać funkcji konwersji typów (CInt, CDbl, CSng, itp) przy przekazywaniu wartości różnych typów pomiędzy zmiennymi
• operacje dzielenia dla licz całkowitych wykonywać z odwrotnym operatorem dzielenia A\B zamiast A/B
• w pracy ze zmiennymi tekstowymi używać funkcji tekstowych: Mid$, Left$, Right$
• wykonując szereg operacji na zakresie warto przenieść zakres do tablicy i dalsze analizy wykonywać na tablicy
• w procesie pobierania i zwracania wartości pomiędzy komórkami arkusza i zmiennymi stosować zmienne typu Double dzięki czemu unikniemy procesu konwersji, formatowania, zaokrąglania, itp.

Na koniec przypomnę najważniejszą z technik, o której wspomniałem w poprzednim poście poświęconym zagadnieniom optymalizacji- nie zapominajmy o A w VBA...

Przy okazji zapraszam na prowadzone przez nas szkolenia z zakresu programowania w VBA, które organizujemy w Warszawie, Krakowie i Wrocławiu. W czasie naszych kursów VBA prezentujemy szereg technik związanych z efektywnym programowaniem.

Wydajność i efektywność 1/2- Makrooptymalizacja

Zagadnienia dot. optymalizacji i efektywności programów w VBA podzielone zostaną na dwie części zgodnie z przyjętą gdzieniegdzie systematyką:

1. makrooptymalizacja dot. zagadnień szerszych, dużych, właściwego doboru narzędzi, techniki, rozwiązania czy algorytmu,
2. mikrooptymalizacja dot. szeregu drobnych detali w kodzie, które w całościowym ujęciu mają istotny wpływ na szybkość wykonywania procedury.

Muszę jednak od razu poczynić zastrzeżenia zanim przejdę do prezentacji krótkich opisów w/w technik. Optymalizacja jest tematem 'żywym', co jakiś czas zdarza się, że pojawiają się nowe sugestie, tak więc nie należy traktować niniejszych postów jako zamkniętą listę działań optymalizacyjnych. I drugie zastrzeżenie- niektóre z technik można uznać za sporne, a więc takie, które nie w każdym przypadku przekładają się na widoczną poprawę efektywności.

Na początek kilka zagadnień dot. makrooptymalizacji:

1. W aspekcie pracy z pętlami, tablicami i kolekcjami:

• unikać zagnieżdżonych pętli, stosować techniki wyjścia z pętli (Exit For, Exit Do)
• w procesach porównywania posortować porównywane kolekcje/tablice
• w pracy z kolekcjami stosować pętle For Each...Next
• w pracy z tablicami stosować pętle For i...Next
• w pracy z tablicami dynamicznymi stosować płynną redefinicję wymiaru

2. W pracy z obiektami:

• unikać aktywowania (.Activate) i zaznaczanie (.Select)
• wyłączać odświeżanie ekranu (Application.ScreenUpdating = False)
• wyłączać (gdy tylko jest to bezpieczne) standardowe okna potwierdzeń (Application.DisplayAllerts = False)
• wyłączać przeliczanie formuł, szczególnie gdy kod VBA ingeruje w strukturę wartości i formuł w skoroszycie (Application.Calculation = xlManual)

3. Inne aspekty:

• unikać przesadnego stosowania zmiennych publicznych
• gdy możliwe i dopuszczalne zerować wartości zmiennych publicznych instrukcją End
• nie tworzyć własnych funkcji jeżeli podobna funkcja istniej w zbiorze funkcji VBA lub WorksheetFunction

A na koniec chyba najważniejsza z technik optymalizacji- nie zapominać o A w VBA... innymi słowy, jeżeli można coś wykonać z wykorzystaniem dostępnych, gotowych narzędzi w aplikacji to zróbmy to w miejsce tworzenia nowego kodu VBA.

Nawigacja wewnątrz procedury a stary poczciwy BASIC

Powinienem zacząć od tego, że część prezentowanych poniżej technik należy do grupy tych niezalecanych. Łamią bowiem zasady programowania strukturalnego, a więc takiego tworzenia programu, którego przebieg zgodny jest z kolejnością zapisu kodu. Praktyka jest jednak inna- w drobnych, prostych rozwiązaniach sięganie po nawigację wewnątrz procedury bywa najszybszym sposobem na rozwiązanie danego problemu.

Przypomnę najpierw najbardziej znaną i dobrze opisaną technikę nawigacji wewnątrz procedury opartą o instrukcje GoTo. Sposoby nawigacji oprzemy o wspólny przykład- chcemy pobrać od użytkownika jego imię i dopóki nie zostanie wprowadzona jakaś wartość dopóty będziemy wykonywać fragment kodu.

Wariant 1 oparty o etykietę tekstową prezentuje prosty poniższy przykład.


Wariant 2 wykorzystuje stare rozwiązanie z klasycznego języka programowania BASIC. 
Język ten był dość popularny w latach '80-'90 XX wieku, a jego klasyczną zasadą było, że każdy wiersz zaczynał się od numeru, numery były zaś ułożone w kolejności rosnącej, choć nie musiały to być kolejne numery całkowite. Okazuje się, że rozwiązanie to można zastosować także w VBA. Zasadnicza różnica sprowadza się do tego, że nie musimy jednak numerować każdego wiersza, wystarczy, że uczynimy to z wybranymi. Dodatkowo nie jest wymagane, aby numery były w kolejności rosnącej. Poniższy kod wykorzystuje tą technikę w celu rozwiązania naszego problemu.


Wariant 3 opiera się o pętle Do...Loop i prezentuję go jako najwłaściwsze z rozwiązań. 
Podobnego rodzaju przykład jest jednym z wielu prezentowanych w czasie kursu VBA jakie prowadzimy w celu prezentacji działania pętli Do...Loop.

Sortowanie alfabetyczne arkuszy

Kilkukrotnie spotkałem się ze skoroszytami składającymi się z dziesiątek, wręcz setek arkuszy. W kilku przypadkach autorzy tego typu skoroszytów mieli problem z utrzymaniem układu arkuszy w porządku alfabetycznym (na czym bardzo im zależało). Prosty kod VBA potrafi wykonać tą operację w... ułamku sekundy. Prezentując metody sortowania arkuszy chciałbym jednak zwrócić uwagę na umiejętność wykorzystania technik arkuszowych w celu przyspieszenia tego typu rozwiązania.

Podejście 1. 
Problem sortowania czegokolwiek to szerokie zagadnienie. Możemy zastosować kilka różnych metod sortujących zależnie od sytuacji. Wyobraźmy sobie jednak, że nie znamy się na sortowaniu bąbelkowym, zliczającym, itp.,  ale potrafimy stworzyć prosty mechanizm logiczny, który ujmę w następujący algorytm:

a. dla kolejnych arkuszy sprawdź, czy arkusz następny nie powinien być przed arkuszem sprawdzanym
b. jeżeli tak to arkusz następny przenieś przed arkusz sprawdzany
c. rozpocznij weryfikację od początku

Rozwiązanie powyższe przedstawia prosty poniższy kod:


Podejście 2.
Jedną z najbardziej wydajnych technik sortowania jest ta, która znamy z procesu sortowanie komórek. W tym podejściu wykorzystamy tą technikę. Kolejne kroki algorytmu to:

a. utworzymy tymczasowy arkusz i zapiszemy do niego nazwy wszystkich arkuszy naszego skoroszytu
b. posortujemy listę uzyskaną w powyższym kroku
c. kolejno ułożymy arkusze w porządku zgodnym z posortowaną listą z punktu b
d. a na koniec wykasujemy nasz tymczasowy arkusz z punktu a.

Powyższy algorytm prezentuje poniższy kod. Z pewnością na pierwszy rzut oka widać różnicę w długości kodu. Proszę jednak zapoznać się z podsumowaniem na końcu niniejszego postu.


Podsumowanie.
Powyższe dwie procedury są doskonałym sposobem na porównanie wydajności różnych technik. Choć wydaje się, że wykonując znacznie więcej kroków w podejściu 2 kod może wykonywać się dłużej to wcale tak nie jest. Otóż procedura 2 pozwala na wykonanie zadania w czasie około 5-7 razy krótszym niż wariant 1.

Ciekawostka.
Gdybyśmy chcieli zmienić kolejność sortowania na malejące to w obu zaprezentowanych wariantach wystarczy dosłownie wstawić lub zamienić po jednym znaku:

a. w Podejściu 1 o kierunku sortowania decyduje znak >< porównujący nazwy arkuszy
b. w Podejściu 2 o kierunku sortowania decyduje obecność lub brak pojedynczego przecinka co prezentują poniższe linie kodu:

Evaluate czyli... (2/2)

Metoda Evaluate raz jeszcze... Tym razem zaprezentuję, jak myślę, zaskakujące dla wielu czytelników zastosowaniu tej instrukcji, a więc metoda Evaluate jako sposób na skróconą definicję tablic Array.

Skrócona metoda tworzenia tablicy jedno- lub wielowymiarowej opiera się na stworzeniu ciągu tekstu gdzie:
a) symbole {} oznaczają definicję tablicy
b) każdy przecinek rozdziela elementy tablicy należące do tego samego jej wymiaru
d) każdy średnik rozdziela wymiary tablicy.
Skrócona metoda ma swoje źródło w sposobie definiowana tablic Array po stronie komórki Excela. Tam właśnie wykorzystujemy w/w symbole i technikę. Evaluate pozwoli nam więc na przeniesienie rozwiązania znanego z aplikacji Excel do środowiska VBA.

Oto przykład tworzenia tablicy Array:

Stwórzmy teraz tablicę dwuwymiarową w krótszym zapisie:

Stosując skrócony zapis funkcji Evaluate możemy powyższy przykład skrócić do absolutnego minimum:

Wykonanie powyższych przykładów i zwrócenie wartości do arkusza Excel obrazuje poniższy zrzut ekranu.

Metoda Evaluate jest metodą szybką, wydajną, efektywną. Umożliwia stosowanie skróconego zapisu w wielu sytuacjach. Jednym z problemów z jakim się spotkamy w pracy z Evaluate to proces debugowania tej instrukcji. Zagadnienia tego nie będę omawiał przyjmując założenie, że stosowanie Evaluate nie sprawi nikomu problemu. :)

Evaluate czyli... (1/2)

Zgodnie ze słownikową definicją 'evaluate' znaczy 'oceniać, szacować'. Zgodnie z definicją metody Evaluate języka VBA dodać należy do polskich odpowiedników także słowo 'konwertować, zamieniać'. A czym w praktyce jest metoda Evaluate i w jakich przypadkach warto i należy po nią sięgnąć? Spójrzmy na tą metodę przez pryzmat przykładów.

1. Zwracanie wyniku funkcji arkuszowych i obliczeń matematycznych
Z pewnością znany jest czytelnikom dostęp do funkcji arkuszowych z wykorzystaniem obiektu WorksheetFunction. Poniższe zapisy zwracają wartość sumy dla obszaru A1:A2

Alternatywnie podobną operację wykonamy z pomocą metody Evaluate:


Wobec faktu, że nawiasy kwadratowe są odpowiednikiem instrukcji Evaluate także i ten zapis będzie odpowiadał powyższemu przykładowi:


Skoro zaś Evaluate dokonuje obliczeń matematycznych dlatego też możemy rozszerzyć nasz powyższy przykład:

2. Odwołanie do zakresu komórek- konwersja tekstu na obiekt
Metoda Evaluate pozwala na stosowanie krótkiego zapisu adresu komórek. Poniższe trzy odwołania są poprawne:

Co ciekawe, zgodnie z informacjami znajdującymi się w sieci ostatnie z odwołań należy do najbardziej efektywnych (najszybszych). Podobnie zresztą metody Evaluate wykona szybciej obliczenia w porównaniu do zastosowania obiektu WorksheetFunction z przykładu 1.

Evaluate możemy również wykorzystać w pracy z obszarami nazwanymi. Oto przykłady zarówno w odniesieniu do wykonywania obliczeń jak i pracy z zaznaczeniem.


W sieci znaleźć można szereg opinii na temat tego, że instrukcja Evaluate nie jest...doceniana. Chyba się z tym zgodzę i przyznam, że sam niezmiernie rzadko z niej korzystam. Nie sposób jednak nie wiedzieć o jej istnieniu. Z pewnością należy mieć też na uwadze, że wprowadzenie skróconego zapisu opartego o kwadratowe nawiasy może nie być czytelne dla szeregu początkujących programistów. Warto pokusić się o odpowiedni komentarz w naszym kodzie przy pierwszym wystąpieniu skróconego zapisu metody Evaluate.

Metoda Evaluate ma jeszcze jedno ciekawe i zaskakujący wykorzystanie. Ale o tym w osobnym- kolejnym- poście...

Narzędzia programisty VBA

Oczywiście, że podstawowym środowiskiem pracy programisty VBA pozostaje Integrated Developer Editor (IDE) dostarczany w pakiecie Office. Nie mniej istnieje kilka narzędzi pomocniczych, które można zintegrować ze środowiskiem lub jako osobne aplikacje mogą stać się niezastąpioną pomocą w codziennej pracy z VBA. Oto kilka przykładowych programów wartych polecenia i uwagi.

1. EZ-Tool 
 Link do strony dostawcy: www

Narzędzie instalowane jako dodatek środowiska IDE. Wspiera programistę w szeregu czynności: tworzenie procedur, zarządzanie komentarzami, obsługą błędów. Dostarcza schematy dla ADO. W moim odczuciu narzędzie w stylu 'must have'.

2. RibbonX Visual Designer 2010
Link do strony dostawcy: www

Narzędzie wspomagające tworzenie elementów wstążki- zakładek, przycisków i innych kontrolek.

3. Excel VBA Code Cleaner
Link do strony dostawcy: www

Jak sama nazwa wskazuje narzędzie oczyszczające kod ze zbędnych elementów. Narzędzie polecane w przypadku dużych projektów, projektów tworzonych w dłuższych okresach czasu, itp.

Funkcja MID w roli operatora

Znana i powszechnie stosowana funkcja MID języka VBA jest nie tylko poleceniem zwracającym fragment tekstu, ale także operatorem, którego zadaniem jest podmiana fragmentu tekstu dla określonej zmiennej.

Wyobraźmy sobie taki tekst:"Uzupełnienie k..... przez f...... MID", w którym w miejsce kropek chcemy wstawić odpowiednie rozwinięcie wyrazów aby uzyskać tekst: "Uzupełnienie kropek przez funkcję MID".

Poniżej przedstawiam dwie proste procedury realizujące to zadanie, pierwsza z nich wykorzystuje funkcję MID w nowej roli. Druga z procedur da dokładnie ten sam efekt. Co jednak ciekawe- pierwsza procedura wykona się około 2-3 razy krócej niż druga.


Źródło powyższego działania funkcji MID zaczerpnąłem z tego pytania na StackOverflow.Com. Zainteresowane osoby zachęcam do zapoznania się z innym ciekawostkami zawartymi w odpowiedziach.  O niektórych z tych ciekawostek wspominałem już w swoich wpisach. O innych z pewnością kiedyś wspomnę w strefie wiedzy. Wiele z tych tematów poruszane podczas kursów VBA, które prowadzimy w Warszawie, Krakowie i Wrocławiu.

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ą:

Czyszczenie (i nie tylko) okna Immediate

Mam pewne (raczej) dobre przyzwyczajenie, że w większości procedur wstawiam na etapie ich tworzenia szereg instrukcji Debug.Print w celu kontroli wartości lub postępu działania procedury. Uciążliwe jednak staje się w pewnym momencie 'przepełnienie' okna Immediate informacjami znajdującymi się tam. Jeżeli dodatkowo korzystam z Immediate aby wykonać szybkie instrukcje lub sprawdzić/zmienić zmienne to po krótkim czasie panuje tam chaos. Jak sobie radzić z podobnym problemem. Oto i kilka rozwiązań.

1. Moja najstarsza metoda to wywołanie na starcie procedury poniższej instrukcji, która rozdziela dotychczasowe zapisy pozwalając łatwo odnaleźć początek wyników przekazanych w obecny wywołaniu:

2. Metoda druga to wykorzystanie darmowej aplikacji, którą ściągnąłem z sieci, a która nazywa się MZ-Tools. Aplikacja ta dodaje pasek narzędzi w środowisku IDE, na którym z łatwością można odnaleźć symbol gumki służący do czyszczenia okna Immediate.

3. Powyższe działanie jest o ułamek szybsze niż wykonanie 3 kroków: kliknięcie w Immediate >> Ctrl+a >> klawisz Del

4. To pomysł znaleziony ostatnio na forum, aby na początku procedury wywołać następującą instrukcję:

Jedyną wadą tego rozwiązania jest to, że działa tylko w Excelu.

5. Inna zaskakująca idea to wykorzystanie techniki API. Nie zaprezentuję tego kodu tutaj gdyż jest długi i wg mnie nie wart zachodu.

6. Znalazłem też, że tak to określę, 'pokręcony' pomysł, który zamiast kasować wrzuca maksymalną ilość dostępnych w Immediate wierszy poniższą instrukcją:

Podsumowując, każde rozwiązanie ma swoje wady. Ja pozostanę przy pierwszych trzech, które będę stosował adekwatnie do sytuacji i potrzeb. I to też polecam czytelnikom.