Nowość! Profesjonalny kurs Excel VBA w wersji online

Udało się! Po wielu miesiącach intensywnej pracy uruchomiłem stronę z profesjonalnym kursem Excel VBA w wersji video. Ponad 16 lat praktycznego doświadczenia w programowaniu w Excel VBA i Office VBA przeniosłem na łącznie ponad 26 godzin filmów podzielonych wg kilku kryteriów:

• część podstawową - około 15 godzin filmów
• część zaawansowaną - około 11 godzin filmów

Całość to:

• 24 zagadnienia
• ponad 100 tematów
• kilkadziesiąt plików z przykładami, zadaniami
• forum wymiany informacji do każdego tematu (o jakość dba administrator strony)

Do Twojej dyspozycji przygotowałem:

• około godzinę bezpłatnych filmów
• tańszy dostęp do kursu podstawowego
• droższy dostęp do całości materiału

Serdecznie zapraszam na strone www.vbaonline.pl

A jeżeli preferujesz szkolenia stacjonarne to dla Ciebie przygotowałem ofertę łączoną - mocne trzydniowe wprowadzenie do Excel VBA w postaci kursu stacjonarnego oraz pełny dostęp do kursu on-line. Opis tej oferty znajdziesz tutaj: www.szkoleniavba.pl

Wstrzymanie wykonania kodu na dokładnie jedną sekundę

Niniejszy post jak wiele innych został zainspirowany zapytaniem na jednym z międzynarodowych forów. W dużym skrócie pytanie brzmiało- 'jak wstrzymać dalsze wykonywanie kodu na dokładnie jedną sekundę?' Pierwsza odpowiedź jaka została podana wyglądała następująco: Wadą powyższego rozwiązania jest wykorzystanie instrukcji TimeValue i Application.Wait. Precyzja obu z nich to ledwie sekunda. Jeżeli więc uruchomimy powyższe rozwiązanie np. o godzinie 12:35:45,65 (65 setnych sekundy) to kod będzie oczekiwał niecałą sekundę i dalsza akcja zostanie uruchomiona o 12:35:46,00, lub kilka setnych sekundy później. (Zarówno w powyższym jak i poniższym przykładzie dodane zostały instrukcje Debug.Print, których celem jest prześledzenie rzeczywistego czasu oczekiwania). Jeżeli więc zależy nam na dokładnie, lub prawie dokładnie jednej sekundzie oczekiwania musimy sięgnąć po rozwiązanie wykorzystujące instrukcję Timer: Chciałbym dodać jednak pewne istotne ostrzeżenie- powyższe rozwiązanie nie zadziała w momencie gdy nastąpi przekroczenie północy. Jeżeli istnieje możliwość należy dodać dodatkowe warunki do proponowanego rozwiązania.

Paste i PasteSpecial... częsty błąd

Niniejszy wpis jest ostrzeżeniem i przypomnieniem dot. operacji wklejania. Zauważyłem ostatnio zarówno w swoich relacjach zawodowych jak i w internecie kilka wpisów sugerujących wątpliwości związane z metodami Paste i PasteSpecial.

Na początek krótki przegląd dostępnych rozwiązań:
1. obiekt Range posiada wyłącznie metodę PasteSpecial
2. obiekt Worksheet posiada metody Paste i PasteSpecial
3. I jako ciekawostkę dodam, że szereg innych obiektów może również posiadać metodę Paste, np. Chart.Paste

A teraz prosty przykład z prawidłowymi zapisami operacji wklejania. Naszym celem jest skopiować zakres komórek A1:A10 z aktywnego arkusza do tego samego arkusza w kolejnych kolumnach B:E. Proszę zwrócić uwagę na dodatkowe komentarze w przykładowym kodzie.

Sytuacja bardzo podobnie będzie wyglądać w przypadku kopiowania kształtu (figury, wykresu i innych obiektów warstwy rysunkowej). Tu schemat postępowania I dostępne metody będą podobne co obrazuje poniższy przykład:

Względna pozycja komórki wewnątrz zakresu Range

W jaki sposób odnaleźć w najprostszy sposób informację o numerze wiersza i kolumny dla określonej komórki lecz nie w relacji do arkusza, a w relacji do innego wskazanego zakresu? Zaprezentuję Państwu dwa przykładowe rozwiązania dla tak postawionego zadania. Zagadnienie to pojawiło się ostatnio na forum programistycznym i myślę, że warto przypomnieć szczególnie drugą technikę pracy z zakresami.

Wspólne założenia do projektu prezentuje poniższy startowy kod wraz z komentarzami:


Wariant 1. Najprostszy, w którym wykorzystamy różnicę pomiędzy numerami wierszy zakresu odniesienia i wskazanej komórki:


Wariant 2. W którym tworzymy tymczasowy zakres od początku zakresu odniesienia do wskazanej komórki i wyliczamy odpowiednio ilość kolumn i wierszy zakresu tymczasowego:

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.

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.

Konwersja tekstu na mowę (1 z 2)

W dwóch najbliższych wpisach chciałbym krótko nawiązać do dwóch technik umożliwiających konwersję tekstu na mowę, a więc możliwości odczytania tekstu przez generatora mowy dostępnego w środowisku Office.

Zacznijmy od wersji prostej- generatora mowy dołączonego do pakietu MS Office, a konkretnie aplikacji Excel. I tu warto od razu podkreślić- poniższy przykład działa wyłącznie w aplikacji Excel i nie jest dostępny dla innych aplikacji pakietu.

Proponowane rozwiązanie opiera się o wykorzystanie obiektu Speech:
oraz jego właściwości Speak:

gdzie wśród parametrów szczególnie zainteresują nas:
Text który wskazuje tekst do odczytania oraz  
ApeakAsync  informujący kompilatora o tym, czy wstrzymać dalsze wykonywanie procedury do zakończenia czytania (domyślne działanie, wartość False parametru) czy też kontynuować procedurę bez oczekiwania na zakończenie odczytu (wartość True parametru). Przeanalizujmy poniższy przykład:


Po wykonaniu powyższej procedury przekonamy się, że tylko jeden z tekstów zostanie odczytany prawidłowo. Zależy to od wersji pakietu Office i częściowo od jego wersji językowej. W aplikacji Excel 2010 PL prawidłowo zostanie odczytany tekst angielski i niepoprawnie tekst polski. W wersji aplikacji Excel 2013 PL efekt będzie odwrotny i uzyskamy prawidłową wymogę dla tekstu w języku polskim.

Usuwanie stron w MS Word z pomocą VBA 1/2

Każdy kto kiedykolwiek miał styczność z VBA dla Worda ma świadomość, że nie istnieje jednoznacznie definiowany obiekt odpowiadający stronie dokumentu. Od razu wyjaśnię dlaczego tak jest dla tych osób, które są zaskoczone tym faktem- dokument traktowany jest bowiem jako ciąg tekstu o zdefiniowanych początku i końcu. Trudno na etapie obiektowej analizy zawartości dokumentu (sprawdzając ilość słów, paragrafów czy zdań)  jednoznacznie określić ile on zajmie stron skoro użytkownik ostatecznie będzie mógł zastosować czcionkę różnej wielkości, zastosuje inny format papieru, itp., a przez to wpłynie na różną ilość stron dokumentu.

Nie jest jednak tak źle- pojecie strony pojawia się wtedy, gdy zaczynamy się odwoływać do dokumentu przez pryzmat bieżącego widoku. I tak na przykład aby sprawdzić ilość stron dokumentu możemy zastosować następujące zapytanie:


W powyższym przykładzie jesteśmy w stanie odnaleźć kolekcję Pages odpowiadającą stronie dokumentu. Ale kolekcja ta pojawia się jako element widoku, który reprezentowany jest przez obiekt okna Window (ActiveWindow).

Powyższy sposób nie jest jedynym na określenie ilości stron dokumentu. O wiele bardziej popularny sposób to odwołanie się do właściwości .Information obiektu Range, która zwraca szereg informacji dot. pliku Word:


W kolejnym wpisie zaprezentuję jeszcze jeden sposób odnajdywania strony, a technikę tą zaprezentuję w kontekście usuwania całej strony pliku.

Pasek postępu w komórce Excela

Całkiem dużym zainteresowaniem cieszy się mój zeszłoroczny wpis dot. paska postępu wyświetlanego w pasku stanu (StatusBar) aplikacji Excel, którego treść można znaleźć pod tym linkiem. Pomyślałem więc, że dodam jeszcze jeden wpis prezentujący inny wariant paska postępu. Nadal będę trzymał się koncepcji aby pasek był rozwiązaniem minimalistycznym. Tym razem więc informacje o postępie umieścimy w...dowolnej komórce Excel. Oczekiwany efekt prezentuje poniższa grafika:

I tu należy uczynić ważne zastrzeżenie- rozwiązanie to nie będzie dostępne dla wszystkich wersji MS Excel gdyż oparte jest o zaawansowane techniki formatowania warunkowego, które rozbudowane zostało począwszy od wersji 2007.

Krok 1 (wariant ręczny).
Tworzymy komórkę z formatowaniem warunkowym, która będzie zawierać nasz pasek postępu. Proponowany wariant to: Menu >> Formatowanie warunkowe >> Pasek danych gdzie następnie wartości ustawiamy jako liczby i określamy ich minimalną i maksymalną wartość jako odpowiednio 0 i 1. Wskazane będzie również dodanie do naszej komórki obramowania.

Krok 1 (kod VBA)
Proponuję wariant dynamicznego tworzenia paska, a więc taki, gdzie nie definiujemy stałego miejsca jego lokalizacji, a zależnie od okoliczności wyświetlić go możemy w dowolnej komórce Excela (cały przykład obrazuje działanie dla aktywnej komórki). W tym celu będziemy potrzebowali kod VBA, który utworzy w komórce formatowanie warunkowe. Poniższy kod dostarcza niezbędny minimalny zestaw instrukcji VBA (komentarze wewnątrz kodu dodatkowo wyjaśniają poszczególne sekcje).


Krok 2
Nie pozostało nam nic innego jak wypróbować nasz kod w oparciu o testową procedurę.


Krok 3. 
Myślę, że po wykonaniu zadania nasz pasek powinien zniknąć. Chyba najproście można to zrobić kopiując pustą komórkę (np. ostatnią komórkę w arkuszu) w miejsce naszego paska postępu. Poniższy kod można dodać po zakończeniu pętli, przed zakończeniem procedury.


Jeżeli ktoś miałby wątpliwość jak cała koncepcja działa proponuję skopiować do nowego modułu obie powyższe procedury. Następnie proszę uruchomić procedurę z kroku 2 i obserwować zachowania aktywnej komórki.

Export wybranych obszarów komórek do pliku PDF

Podstawowy sposób zapisania w formacie PDF obszarów komórek Excel opiera się na wykorzystaniu metody .ExportAsFixedFormat. Warto jednak wiedzieć, że metoda ta dostępna jest dla różnych obiektów na różnych poziomach hierarchii:

• komórek- obiekt Range
• arkusza- obiekt Worksheet
• skoroszytu- obiekt Workbook

1. Obiekt Range udostępnia metodę wprost umożliwiając stworzenie instrukcji:


Warto jednak pamiętać o możliwości wykorzystania innych metod definiowania obiektu Range. Poniższy przykład również jest prawidłowy lecz co ciekawe- każdy z obszarów wewnątrz instrukcji Union zostanie zapisany na osobnej stronie dokumentu PDF:


Powyższa techniki nie zadziała oczywiście dla obszarów pochodzących z różnych arkuszy. W wariancie tym, co ważne, ignorowane są jednak obszary wydruku które są istotne przy eksporcie opisanym w punkcie 2 i 3 poniżej.

2. Obiekt Worksheet umożliwia wydrukowanie całego arkusza lub jego części ustawionej jako obszar wydruku:

3. Obiekt Workbook również posiada metodę .ExportAsFixFormat a jej wykorzystanie spowoduje eksport wszystkich ustawionych obszarów wydruku lub całych arkuszy do pliku PDF:


Co powinniśmy zrobić jeżeli chcemy wyeksportować do PDF różne obszary z różnych arkuszy? W takiej sytuacji musimy w pierwszym kroku ustawić obszary wydruku indywidualnie dla każdego arkusza wykorzystując poniższe dostępne techniki:

a następnie wywołać metodę eksportu dla skoroszytu.

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:

Przycisk w relacji do komórki wynikowej

Wyobraźmy sobie sytuację, w której w arkuszu, w określonej kolumnie umieszczamy szereg przycisków. Ich rola niech będzie banalna- zwiększenie/zmniejszanie wartości określonej komórki. Przyjmijmy jednak założenie, że komórka wynikowa znajduje się w określonej relacji do przycisku- np. jest to komórka bezpośrednio po lewej stronie względem naszego przycisku. Całość, w lekko rozbudowanym wariancie, niech zobrazuje poniższy schemat.

Chcąc zachować względną relację pomiędzy komórką wynikową a komórką nad którą znajdują się przyciski warto sięgnąć po .TopLeftCell. Właściwość ta występuje w przypadku większości obiektów warstwy rysunkowej i wskazuje komórkę (obiekt Range), nad którą znajduje się lewy górny róg naszego obiektu.

Kod dla przycisku czerwonego, który zmniejsza ilość elementów w kolumnie B będzie więc następujący:

Kod dla przycisku zielonego, który zwiększa ilość elementów w kolumnie B będzie więc następujący:

Zaletą stosowania takiego rozwiązanie jest wspomniana wyżej relacja w położeniu pomiędzy przyciskiem a kolumną wynikową. Pozwala nam to na wstawienie kilku kolumn po lewej stronie od kolumny B oraz  wierszy powyżej naszego przykładowego rzędu. W obu przypadkach przyciski nadal będą działać w prawidłowej relacji do komórki wynikowej.

TypeName i TypeOf- przydatne funkcje i operatory

Muszę przyznać, że prowadząc szkolenie z zakresu VBA zazwyczaj brakuje czasu aby zaprezentować i omówić dwie ważne instrukcje języka VBA: funkcję TypeName i operator TypeOf. Cel stosowania obu instrukcji jest podobny- określić rodzaj obiektu lub zmiennej. Sposób stosowania i wynik mogą być zgoła odmienne.

Spójrzmy na różnice i podobieństwa pomiędzy prezentowanymi instrukcjami przez pryzmat przykładów.

1. Sprawdzamy czy bieżące zaznaczenie odpowiada obiektowi określonego typu, tu: obiektowi Range
 

choć w obu wypadkach uzyskamy wyniku True proszę zwrócić uwagę, że wynikiem pracy funkcji TypeName jest ciąg tekstowy. TypeOf można użyć tylko w relacji do obiektu.


Oczywiście możemy dokonać podobnego sprawdzenia w relacji do zmiennej:

Nie mniej tylko instrukcja TypeName pozwala nam określić jakiego typu jest dana zmienna:

2. Jeżeli badana zmienna przyjmuje wartość Nothing operator TypeOf zwróci błąd a funkcja TypeName tekst Nothing:

3. Operator TypeOf zwraca wartości Prawda/Fałsz, funkcja TypeName zwraca ciąg tekstowy z nazwą typu zmiennej:

4. Operator TypeOf działa szybciej niż funkcja TypeName.

Dodatkowe informacje dot. w/w instrukcji można znaleźć tutaj:
TypeName
TypeOf

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.

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?

Testowanie zgodności ciągów tekstowych- podstawy- 2/2

Kontynuując tematykę pracy z tekstem i szukania odpowiedzi na pytanie 'czy tekst B spełnia określone kryteria zgodności z tekstem A?' zaprezentuje technikę opartą o wykorzystanie operatora Like.

Ogólna konstrukcja wykorzystania operatora opiera się o schemat:

tekst_A Like wzorzec_B

gdzie w strukturze wzorca dostępne są następujące znaki i bloki specjalne
*        dowolny ciąg tekstu lub ciąg pusty
?        pojedynczy znak- litera lub cyfra
#        pojedyncza cyfra 0 do 9
[zakres]    wskazany zakres znaków, liter, cyfr
[!zakres]   wskazany zakres znaków, liter, cyfr nie uwzględniany w wyszukiwaniu

Spójrzmy jednak na przykłady w celu zrozumienia zastosowania operatora Like.Tym razem naszym badanym tekstem będzie następujący ciąg:
A = "Lorem ipsum dolor 100 sit amet, consectetuer adipiscing elit."

1. Czy w tekście A znajduje się określony fragment tekstu?

Proszę pamiętać, że domyślne porównanie tekstowe odbywa się w sposób binarny, a więc taki, który rozróżnia wielkość liter. Jednym ze sposobów obejścia tego problemu jest zastosowanie następującej techniki:

2. Czy tekst A zaczyna się od małej litery?

3. A może tekst zaczyna się od dowolnej litery (nie cyfry, nie znaku specjalnego)?

Uwaga, nie możemy zapisać zapytania jako "[A-z]" gdy pomiędzy literami A-Z i a-z znajduje się zestaw znaków specjalnych. Można to sprawdzić w następujący sposób (wraz z listą zwracanych wartości):

Jak więc widać powyżej zakresy znaków ujęte w kwadratowych nawiasach [] odpowiadają znakom w kolejności zgodnej z numeracją ANSI.

4. Czy tekst kończy się na literę lub liczbę?

5. A może tekst kończy się wybranym znakiem specjalnym?

6. Sprawdźmy czy tekst zawiera liczbę trzy- lub cztero-cyfrową?

7. Weryfikacja czy tekst jest zdaniem wymaga sprawdzenia, czy zaczyna się od litery wielkiej i kończy jednym ze znaków specjalnych?

8. A na koniec kilka drobnych przykładów, które zwracają wartość TRUE dla analizowanego ciągu tekstowego:

Wszystkich zainteresowanych szerzej zagadnieniem zastosowania opeartora Like odsyłam bezpośrednio do pomocy VBA na stronach Microsoft MSDN:
Like Operator
Comparing Strings by Using Comparison Operators

Testowanie zgodności ciągów tekstowych- podstawy- 1/2

Niniejsze zagadnienie ma swoje szerokie zastosowanie w całym środowisku VBA i nie dotyczy tylko Excela i Worda lecz wszystkich aplikacji MS Office. Mało tego, spokojnie można stwierdzić, że temat ten jest powszechnym zagadnieniem we wszystkich językach programowania. My spojrzymy jednak na to wyłącznie przez pryzmat VBA.

Ogólnie rzecz ujmując temat na najbliższych kilka postów to poszukiwanie odpowiedzi na pytanie: jak sprawdzić, czy określony ciąg tekstowy spełnia wskazane kryteria? Postaram się przedstawić dostępne techniki lecz nie sposób będzie zaprezentować wszystkich możliwych rozwiązań, schematów czy parametrów. Jest to temat wyjątkowo szeroki i zależny od indywidualnych problemów z jakimi mierzy się każdy programista. Spojrzymy na zagadnienie przez pryzmat rozwiązań prostych (2 posty) oraz możliwość zastosowania `wyrażeń regularnych RegExp` (kilka kolejnych wpisów).

Zaczynamy od rozwiązań najprostszych. Przyjmijmy założenie, że nasz tekst bazowy to:
A = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit."

1. Czy w tekście A znajduje się tekst B? 
W tym wariancie wystarczy wykorzystać funkcję InStr

Należy jednak pamiętać, że porównania tekstowe standardowo dokonywane są binarnie, a więc uwzględniają wielkość liter. Aby pominąć tą niedogodność sięgnijmy po jedno z dwóch poniższych rozwiązań:

2. Czy tekst A zaczyna się od tekstu B?
Tu wystarczy sięgnąć po funkcję Left pamiętając ponownie, że porównanie odbywa się w binarnie.

3. Czy tekst A kończy się tekstem B?
Rozwiązanie będzie podobne jak w punkcie 2 przy czym zastosujemy funkcję Right.

4. Czy określony tekst B znajduje się na określonej pozycji w tekście A?
Poniższy przykład prezentuje rozwiązanie, w którym sprawdzamy czy tekst B znajduje się w tekście A począwszy od 13 litery.

Funkcje informacyjne, część 5/5- funkcje arkuszowe po stronie VBA

To już ostatni post w serii poświęconej funkcjom informacyjnym. Tym razem sięgnę do zasobów funkcji arkuszowych i przedstawię przykładowe z nich, a konkretnie funkcje CZY.LICZBA oraz funkcje błędów: CZY.BRAK, CZY.BŁ i CZY.BŁĄD. Zadanie to będzie relatywnie łatwe gdyż zakładam, iż każdy z programistów VBA jest co najmniej dobry,m jak nie bardzo dobrym specjalistą w pracy z Excelem.

Na początek przypomnę więc technikę wykorzystania funkcji arkuszowych w programach VBA. Po daną funkcję możemy sięgnąć z wykorzystaniem jednej z dwóch technik (tu dla funkcji SUMA):

w obu wypadkach w wyniku otrzymamy  sumę obszaru A1:A10 przypisaną do zmiennej WynikSuma, a różnica pomiędzy powyższymi wywołaniami dotyczy sposobu obsługi możłiwych błędów wynikłych w czasie wywołania instrukcji (ale to temat na osobny post).

Wracając do funkcji informacyjnych:

1. Funkcja CZY.LICZBA dostępna jest z poziomu VBA w postaci instrukcji:

Funkcja zwróci wartość TRUE o ile testowana wartość jest liczbą. Podobną funkcją jest funkcja VBA IsNumeric prezentowana w poprzednich postach. Pomiędzy tymi funkcjami  wystąpią różnice w wyjątkowych sytuacjach, np.:

2. Funkcja CZY.BRAK dostępna jest z poziomu VBA w postaci instrukcji:

Funkcja ta zwróci TRUE tylko wtedy, gdy testowana wartość, najczęściej komórka arkusza, zawiera błąd typu #N/D.

3.  Funkcja CZY.BŁ dostępna jest z poziomu VBA w postaci instrukcji:

Funkcja ta zwróci wartość TRUE w przypadku każdego innego błędu niż ten opisany w punkcie 3 powyżej.

4. Funkcja CZY.BŁĄD dostępna jest z poziomu VBA w postaci instrukcji:

Funkcja ta zwróci TRUE w sytuacji, gdy kontrolowana wartość komórki zawiera błąd dowolnego typu, a więc zarówno błędy obsługiwane przez funkcje z punktu 2 jak i z punktu 3 powyżej.

Funkcje informacyjne, część 4/5- funkcje VBA

Kontynuując omawianie funkcji informacyjnych (z grupy VBA) przedstawię na zakończenie dwie funkcje: IsMissing, IsError.

1. Funkcja IsMissing ma swoje szczególne zastosowanie w tworzeniu własnych funkcji użytkownika. Wykorzystamy ją wtedy gdy dany parametr funkcji jest opcjonalny. Wewnątrz funkcji zapewne będziemy chcieli przetestować czy użytkownik podał ten parametr czy też go pominął.

Wyobraźmy sobie funkcję, która standardowo liczy pole kwadratu, a jeżeli użytkownik poda długość dwóch boków to funkcja obliczy pole prostokąta przyjmując jako wymiary boków figury kolejne podane argumenty.


W powyższym przykładzie dzięki funkcji IsMissing sprawdzamy czy użytkownik podał drugi z argumentów i określamy jaki wzór zostanie zastosowany do obliczeń.

Funkcja IsMissing nie ma szczególnego zastosowania w innych przypadkach. Sprawdzenie pustych komórek, nieprzypisanych zmiennych, pustych ciągów tekstowych każdorazowo zwraca wartość False jak dla poniższych przykładów:

2. Funkcja IsError zwraca wartość True w sytuacji gdy testowana wartość lub zmienna zwracają błąd. Funkcja ta znajdzie swoje główne zastosowanie w kilku przypadkach- w sytuacji gdy testujemy wartość zwracaną przez naszą funkcję, w sytuacji gdy sprawdzamy czy dana komórka zawiera błąd (bład formuły), w sytuacji testowania wartości zwracanych przez funkcje arkuszowe wywołanych jako rozwinięcie obiektu Application. Przyjrzyjmy się przykładom i komentarzom w poniższym kodzie:

Uwaga! Poniższe wywołanie zwróci błąd kompilacji, funkcja IsError nie będzie skuteczna w tym aspekcie. W celu 'przechwycenia' tego błędu niezbędne będzie zastosowanie procedury obsługi błędów.