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?

Nowości w 2016- Wpis reklamowy

Niniejszy wpis poświęcony będzie wyłącznie zmianom jakie nastąpiły w ofercie szkoleniowej począwszy od roku 2016.

Pierwsza zmiana dotyczy autorskich szkoleń z podstaw Excel VBA. Szkolenia:  
Excel VBA- Praktyczne podstawy dla analityków
Excel VBA- Kompletne wprowadzenie
oraz
Excel VBA śednio-zaawansowany- praca z danymi

zyskały nowe zagadnienie, dot. integracji środowiska Excel (VBA) z innymi aplikacjami w pakiecie Office. W odpowiedzi na często pojawiające się zapytania dot. np. wysyłania e-maila z Outlook'a lub wstawiania jakiegoś elementu w obszar prezentacji PowerPoint postanowiłem dodać tego typu temat do programu w/w szkoleń.W zależności od ilości dostępnego czasu prezentowane są więc przykłady dot. współpracy z Outlookiem, PowerPointem i MS Wordem. Wybór aplikacji i przykładów integracji zależy, jak zawsze, od uczestników szkoleń.

Druga zmiana dotyczy wprowadzenia do oferty krótkiego, dwudniowego szkolenia z Excel VBA opartego o program szkolenia Microsoft MS 50476 B. Program naszego szkolenia nadal pozostaje autorski, nie mniej zawarliśmy w nim wszystkie tematy wymienione w w/w bazowym szkoleniu firmy Microsoft. Dodatkowo poszerzyliśmy niektóre tematy tak, aby uczestnik po ukończeniu kursu poczuł pełną swobodę w wykorzystywaniu języka VBA w codziennej pracy w procesach prostych automatyzacji zadań. Dodatkowo niska cena- 690 pln netto za dwa dni kursu- mamy nadzieję, że zachęci do uczestnictwa w tym szkoleniu.

To nie koniec zmian w roku 2016. W drugiej połowie roku planujemy uruchomienie autorskich szkoleń z Microsoft Access, Microsoft Access SQL i Microsoft Access VBA.

Serdecznie zapraszamy do uczestnictwa w naszych szkoleniach oraz do śledzenia naszej oferty.

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...

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.

Dlaczego błędna metoda nie zwraca błędu kompilacji?

Dzisiejszy wpis będzie wyjątkowy gdyż kierować będzie wprost na stronę Stackoverflow.com gdzie ostatnio pojawiło się bardzo interesujące pytanie i o wiele ciekawsza odpowiedź. Autor pytania zaprezentował prosty kod: zadając pytanie- dlaczego dwie pierwsze pseudo-metody XXX dla obiektów Application i Workbook nie zwracają błędów prekompilacji podczas gdy błąd jest zwracany przez trzecią z metod. Tym razem pozwolę sobie na bezpośrednie przekierowanie do odpowiedzi na stronie StackOverflow.com. Tutaj znaleźć można bardzo interesujące wyjaśnienie: http://stackoverflow.com/a/28237063/2143262

Łączenie tekstów- ampersand (&) vs. plus (+)

Łączenie tekstów to powszechne działanie w VBA. Teoretycznie do dyspozycji mamy dwa znaki złączające: ampersand: & oraz plus:+. W praktyce jednak nie każdy zdaje sobie sprawę z faktu, że w pewnych okolicznościach zamienne zastosowanie łączników da odmienne wyniki. I choć zagadnienie to dotyczy szczególnie operacji w środowisku Access VBA myślę, że warto pamiętać o różnicach szczególnie gdy pracować będziemy ze zmiennymi.

Ampersand łączyć będzie tekst w każdych okolicznościach i niezależnie od bieżącej wartości przechowywanej w zmiennej.

Plus łączyć będzie teksty tak długo jak zmienne będą przechowywać jakąkolwiek wartość, jeżeli jednak jedna ze zmiennych przechowywać będzie wartość Null to wynikiem złączenia z wykorzystaniem operatora plus również będzie Null.

Różnicę w działaniu operatorów najprościej będzie zaprezentować na poniższym przykładzie gdzie wykorzystane zostały znaki łączenia tekstu w pracy ze zmiennymi, w tym ze zmienną o wartości Null.

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.

Wpis reklamowy- nowe szkolenia w ofercie 2015

Pierwszy wpis w nowym roku poświęcę wyjątkowo nie zagadnieniom związanym z programowaniem w VBA, a ofercie naszej firmy na rok 2015.

Na wstępie chciałbym zwrócić uwagę na zupełną nowość jaką są szkolenia z zakresu programowania baz danych w środowisku SQL Server i TSQL. Program szkolenia został przygotowany w oparciu o praktyczne podejście do zagadnienia, a więc w sposób, który cechuje wszystkie nasze kursy. Wszystkie materiały szkoleniowe zostały opracowane przez trenera z wieloletnim doświadczeniem trenerskim i jednocześnie osobę, która posiada głęboką wiedzę merytoryczną oraz praktyczne doświadczenie w implementacji zaawansowanych rozwiązań w codziennej pracy podczas realizacji projektów dla poważnych klientów branży IT i finansów.

Program szkoleń z obszaru TSQL i SQL Server opracowany został w 4 wariantach tak, aby każdy z uczestników potrafił znaleźć dla siebie odpowiedni kurs. W ofercie znaleźć można kurs dla osób, które nie posiadają żadnej wiedzy w prezentowanym obszarze, dla osób, które posiadają podstawową wiedzę dot. relacyjnych baz danych, a także wariant kompletny- pięciodniowy, który prezentuje komplet zagadnień włącznie z technikami typu 'tips & tricks' w programowaniu w Transact SQL (T-SQL). Dla osób zainteresowanych tylko tym zagadnieniem polecamy jednodniowy kurs o nazwie 'Tips & Tricks języka TSQL'. Informacje na temat opisanych szkoleń znaleźć można pod tym linkiem.

W zakresie kursów Excel VBA na rok 2015 przygotowaliśmy małe zmiany polegające na uporządkowaniu oferty kursów i stworzeniu zalecanej ścieżki szkoleniowej.Wystarczy zaledwie 5 dni, aby poznać najważniejsze jak i zaawansowane techniki programowania w VBA. Pozostawiamy Państwu swobodę decyzji w zakresie długości kursu, w którym chcecie Państwo uczestniczyć. Umożliwiamy też dalszy rozwój umiejętności programowania przez uczestnictwo w kolejnych kursach w wybranych terminach. Elastyczna organizacja szkoleń umożliwia dołączanie do grup na wyższych poziomach zaawansowania. Informacje na temat szkoleń Excel VBA znaleźć można pod tym linkiem.

Sumowanie liczb (rzeczywistych) w dokumencie Word (2 z 2)

O ile sumowanie liczb całkowitych było proste (co zaprezentowałem w poprzednim poście), o tyle suma liczb rzeczywistych wymaga dodatkowych zabiegów.

Osoby znające zagadnienie związane z wykorzystaniem znaków specjalnych w wyszukiwaniu (wildcards) lub techniki Regular Expresion z pewnością spodziewały się, że wystarczy minimalnie przerobić wzorzec wyszukiwania aby uzyskać oczekiwany efekt. Otóż nic bardziej mylnego, następujący zapis:


niestety nie sprawdza się w aplikacji Word. Po prostu nie jest dopuszczalne aby ilość znaków określić na 'zero lub więcej' co kryje się w zapisie {0;}.

Owszem, jesteśmy blisko rozwiązania tworząc następujący wzorzec:


tyle tylko, że powyższy schemat wyszuka również wszystkie przecinki w dokumencie i spróbuje je zsumować co w efekcie wygeneruje błąd. Pozostaniemy jednak przy tym rozwiązaniu i aby było skuteczne dodamy odpowiedni mechanizm obsługi błędów. Całość rozwiązania prezentuje poniższy kod.

Sumowanie liczb (całkowitych) w dokumencie Word (1 z 2)

Tym razem temat wywołany na jednym z forów poświęconych programowaniu w VBA- w jaki sposób zsumować wszystkie wartości liczbowe w dokumencie Word, w określonym zaznaczeniu.
W sytuacji gdyby rzecz dotyczyła sumy w tabeli w dokumencie moglibyśmy sięgnąć po odpowiednie pola sumujące. Tym razem chodzi jednak o dowolny zaznaczony obszar.

Rozwiązanie podzielę na dwie części- sumowanie liczb całkowitych- i ten wariant zostanie zaprezentowany w niniejszym poście- oraz sumowanie liczb rzeczywistych, co jak się okaże jest relatywnie prostym zadaniem.

Aby zsumować liczby rzeczywiste najlepszym rozwiązaniem jest zastosować obiekt Find dla odpowiednio skonstruowanego klucza wyszukiwania z wykorzystaniem znaków specjalnych (wildcards). Poniższy kod realizuje to zadanie wyświetlając na końcu komunikat z podsumowaniem.

Powyższe rozwiązanie w wariancie sumującym liczby rzeczywiste opublikowany zostanie już jutro.

Konwersja tekstu na mowę (2 z 2)

Kontynuując poprzedni wpis dot. konwersji tekstu na mowę zaprezentuję sposób wykonania tej operacji z innej niż Excel aplikacji z pakietu Office. Istnieją dwa sposoby rozwiązania tego problemu.

Wariant A. Odczyt paragrafu z aplikacji MS Word z wykorzystaniem Excela.
W tym wariancie wystarczy z poziomu aplikacji MS Word utworzyć referencję do aplikacji Excel (dla prostoty działania z wykorzystaniem wiązania późnego) a następnie wykorzystać obiekt Speech dostępny w Excelu. Poniższy kod prezentuje takie właśnie działanie. Co ważne, nie musimy wyświetlać aplikacji Excel przez co osoba postronna nie zorientuje się, że odczyt następuje z poziomu innej niż Word aplikacji.


Wariant B. Wykorzystanie zewnętrznej biblioteki do odczytu tekstu
W tym wariancie wykorzystamy bibliotekę o nazwie Microsoft Speech Object Library, do której należy ustawić odpowiednią referencję w Menu >> Tools >>References... Biblioteka ta daje nam dostęp do rozszerzonej wersji generatora mowy. Dzięki szeregu właściwości i metod możemy wpłynąć na jakość dźwięku czy też wybrać odpowiedni język i głos (o ile biblioteka posiada odpowiednie dodatkowe zestawy głosów i języków).
Prosty przykład wykorzystania w/w biblioteki w celu odczytania pierwszego paragrafu z dokumentu MS Word przedstawia poniższa procedura:


Po dodatkowe informacje odsyłam do strony MSDN poświęconej tej bibliotece.

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.

Komórka w tabeli dokumentu Word- wstawianie tekstów i obiektów

Myślę, że warto dodać jeszcze jeden wpis dot. Word VBA i pracy z komórkami tabeli w dokumencie Word. Przeglądając ostatnio kilka forów dostrzegłem, że niekiedy problemy sprawia dodawanie zawartości do komórki tabeli.

Przyjmijmy na początek następującą referencję do komórki w tabeli w aktywnym dokumencie:
Komórka.Range.Text = "Hello World"
Próba dodania prostego tekstu do komórki nie stanowi problemu:


Problemem jednak okazuje się dodanie do komórki np. pola z datą. Poniższy kod wygeneruje błąd (Run-time error '4605'):


Powyższy problem wynika z faktu, ze wewnątrz komórki występuje znak końca komórki, który widać po włączeniu wyświetlania znaków specjalnych:

W tego typu sytuacjach rozwiązaniem problemu jest wstawienie obiektu z pominięciem znaku końca komórki. Wykonać to można najprościej przed określenie początku i końca  obszaru wstawiania:


Warto zwrócić na wartość -1 w powyższym kodzie- dzięki tej korekcie pomijamy problematyczny symbol końca komórki.

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

Kontynuując poprzedni wpis dot. stron dokumentu warto dodać, że istnieje jeszcze jeden ze sposobów odnajdywania strony dokumentu- przez wykorzystanie zakładki "\page". Poniższe odwołanie spowoduje, że zaznaczony zostanie obszar strony na której znajduje się bieżące zaznaczenie:


a stąd już blisko do usunięcia całej strony przez minimalna modyfikację powyższego kodu:


Jak widać wykorzystanie obiektu Bookmarks("\page") domyślnie weryfikuje układ dokumentu przez pryzmat widoku o czym pisałem w poprzednim wpisie.

Przy okazji warto zwrócić uwagę na zakładki specjalne występujące w dokumencie gdyż często okazują się być niezmiernie przydatne. Określane są one mianem zakładek predefiniowanych i pozwalają m.in. na:

• odnalezienie początku dokumentu \StartOfDoc
• odnalezienie końca dokumentu \EndOfDoc
• odnalezienie początku lub końca bieżącego zaznaczenia : \StartOfSel, \EndOfSel

itp. Kompletną listę znaleźć można pod tym linkiem.

Przy okazji serdecznie zapraszam na jesienne szkolenia z VBA dla aplikacji Excel. Nasze kursy organizowane są w Warszawie, Krakowie i Wrocławiu na różnych poziomach zaawansowania.  Szczegółowe informacje na naszej stronie www.SzkoleniaVBA.pl.

Podświetlanie wierszy parzystych i nieparzystych w Excelu z wykorzystaniem formatowania warunkowego

Relatywnie często wykorzystywanym rozwiązaniem jest naprzemienne podświetlenie  wierszy parzystych i nieparzystych. Podstawowej sposoby uzyskania tego efektu to:

1. wypełnienie komórek i skopiowanie formatowania do wskazanego obszaru, co wymaga pracy i jednocześnie staje się problematyczne w momencie gdy dodamy kolejny wiersz   
2. zastosowanie obiektu Tabela (ListObject po stronie obiektów VBA), co wiąże się z umiejętnością wykorzystania obiektu Tabela

Do tej listy chciałbym dodać jeszcze relatywnie prostą choć niezbyt popularną technikę formatowania warunkowego. W tym celu:

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 =MOD(WIERSZ(A1);2)=0 >> określamy formatowanie po wciśnięciu przycisku >>Formatuj… >> Akceptujemy wszystkie ustawienia.

Warto wspomnieć o dwóch podstawowych korzyściach z zastosowania tej techniki: po pierwsze- nie likwiduje ona formatowania wynikającego z wypełnienia komórek innym kolorem. Należy jednak pamiętać, że formatowanie warunkowe ma przewagę nad zwykłym wypełnieniem obszaru. Po drugie- dodanie nowych wierszy w obszarze formatowania spowoduje zastosowanie tej samej reguły, a przez to automatycznie nowe wiersze będą uwzględniały efekt formatowania.

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.

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.