poniedziałek, 30 listopada 2015

Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 3/3

W ostatnim wpisie dot. wykorzystania SQL i ADODB chciałbym przedstawić dwa zagadnienia- pracę z nazwanymi zakresami oraz złączenia tabel danych w SQL (prosta technika JOIN).

1. Obszary nazwane (Named Ranges) rozpoznawane są przez ADODB jako samodzielne tabele. To zaś daje nam możliwość bardziej elastycznego podejścia do obszarów, które możemy wykorzystać w zapytaniu SQL. Nasz przykładowy obszar nazwany o nazwie TabelaProdukty mógłby wyglądać następująco:

Aby wykorzystać obszar nazwany w zapytaniu SQL umieszczamy jego nazwę w kwadratowych nawiasach. Poniższe zapytanie pobierze więc tabelę w całości:

2. Ostatnią z niezwykle przydatnych umiejętności jest wykorzystanie złączeń tabel. Wyobraźmy sobie sytuację, w której -dla naszych przykładowych danych- chcemy uzyskać informację o ilości sprzedaży w podziale na kategorie produktów:

Po wywołaniu powyższej kwerendy SQL uzyskamy następującą tabelę wynikową:

piątek, 13 listopada 2015

Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 2/3

Pierwsza rzecz, która wymaga uzupełnienia względem poprzedniego wpisu (o tym samym tytule lecz z numerkiem 1) to technika dodawania nagłówków do tabeli wynikowej. Aby to uzyskać potrzebna będzie pętla oraz odwołanie do kolekcji Fields, a więc kolekcji kolumn tabeli danych. Brakujący kod wraz z komentarzem wygląda następująco:


Po uzupełnieniu poprzedniego kodu nasza tabela wynikowa wyglądać będzie następująco (dla wariantu 2):

Bez wątpienia uzyskane nagłówki w pełni nam nie odpowiadają. Aby je poprawić musimy zmodyfikować nasze zapytanie SQL przez dodanie aliasów:

wtorek, 3 listopada 2015

Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 1/3

Nie każdy programista VBA wie, że w całkiem prosty sposób można wykorzystać język SQL do pobrania danych z określonego arkusza aktywnego skoroszytu. Bardziej zaawansowani programiści znają ryzyka związane z tą techniką- tzw. memory leaks, problem związany brakiem pamięci, ale także z brakiem dostępu do części danych w sytuacji gdy są one aktualizowane równocześnie z procesem pobierania. Pomimo wspomnianych zagrożeń uważam, że wspomniana technika jest na tyle interesująca, iż postanowiłem poświęcić jej co najmniej dwa wpisy. Zaprezentuję więc prostą technikę pobierającą SQL z wykorzystaniem ADODB (Microsoft ActiveX Data Objects).

1. Chyba najprostszy sposób pracy z ADODB oparty jest o technikę wczesnego wiązania dzięki czemu uzyskujemy dostęp do podpowiedzi IntelliSense dla obiektów, właściwości i metod związanych z ADODB. W tym celu w naszym projekcie, w edytorze VBA przechodzimy do Menu >> Tools >> References... i na liście odnajdujemy bibliotekę ADODB 2.8 (idealna dla Office od 2007 do 2013).


2. Bez wątpienia potrzebujemy zestawu danych testowych, które powinny znajdować się w określonym arkuszu. Najczęściej dane te znajdują się począwszy od komórki A1. Co jednak ważne- nie jest to wymagane. Silnik pobierania będzie wyszukiwał pierwszej zajętej komórki i tę właśnie uzna za początek naszej tabeli danych.


3. A teraz sam kod... wszelkie dodatkowe informacje umieszczone zostały poniżej w postaci komentarzy. A co się stanie w wyniku wywołania poniższej przykładowej procedury? Zależnie od wybranego zapytania SQL albo uzyskamy wybrany wycinek danych albo tabelę z podsumowaniem sprzedaży.

piątek, 23 października 2015

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

czwartek, 8 października 2015

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.

poniedziałek, 21 września 2015

Jak wyrównać tekst w kształtach Shape

Pomysł na niniejszy wpis powstał po przeczytaniu pytania i kilku odpowiedzi w serwisie StackOverflow. Postawione pytanie było proste lecz tu przedstawię je w zmodyfikowanej i bardziej rozbudowanej formie- jak uzyskać efekt, w którym poszczególne paragrafy w kształcie są naprzemiennie wyrównane do prawej i lewej strony (zgodnie z tym co prezentuje poniższy przykładowy obrazek)?

Oto prosty kod realizujący zadanie wraz z kilkoma komentarzami:

środa, 2 września 2015

Microsoft Word- zamiana tekstu na obiekt ContentControl (lub inny)

Operacja do wykonania wydaje się być prosta- określone wystąpienie tekstu w dokumencie chcemy zamienić nie na inny tekst lecz na…obiekt. Pierwsza myśl to często- 'jaki jest symbol specjalny dla danego obiektu?'. W istocie, w metodzie .Find możemy zastosować znaki specjalne (Wildcards) w celu wyszukiwania i podmiany tekstu. Tutaj zastosujemy jednak inne i jednocześnie bardzo proste rozwiązanie- w miejscu wyszukanego tekstu po prostu wstawimy (dodamy) interesujący nas obiekt (w przykładzie będzie to ContentControl). Oto przykład wraz z dodatkowymi komentarzami.

poniedziałek, 17 sierpnia 2015

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:

środa, 22 lipca 2015

Przekazywanie tablic pomiędzy procedurami i zmiennymi

Praca z tablicami może bywać problematyczna szczególnie dla początkujących programistów VBA. W niniejszym blogu powstało już wiele wpisów dot. pracy z tablicami. Ostatnio jednak trafiłem na pytanie dot. przekazywania tablicy stałoelementowej pomiędzy różnymi procedurami, innymi zmiennymi, itp. Myślę, że warto krótko podsumować obowiązujące zasady szczególnie, że nie są to zasady skomplikowane. Zacznijmy od prostej definicji bazowej tablicy: Problem 1. Jak przekazać powyższą tablice do innej zmiennej? Istnieją dwa i tylko dwa dostępne scenariusze: oraz wariant drugi: Problem 2. Jak przekazać tablicę do funkcji, procedury parametryzowanej lub właściwości? Tu obowiązują identyczne zasady jak w przekazywaniu tablicy pomiędzy zmiennymi: Problem 3. Jak utworzyć funkcję, która będzie zwracać tablicę o określonej ilości elementów? W praktyce nie ma takiej możliwości. Jeżeli jednak przypiszemy do zwracenj wartości odpowiednią tablicę to funkcja stanie się funkcją tablicową.

piątek, 3 lipca 2015

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:

piątek, 19 czerwca 2015

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.

piątek, 12 czerwca 2015

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.

piątek, 8 maja 2015

Konwersja tablicy Array na Recordset

Tablice Array są istotnym elementem wielu rozwiązań tworzonych w VBA. Co zrobić jednak w sytuacji gdy określoną tablicę (jedno- lub wielowymiarową) chcemy przefiltrować ze względu na określone zmienne kryteria? W Excelu rozwiązanie to wydaje się być proste - przenieść tablicę do komórek Excela i zastosować filtrowanie. Co jednak zrobić w kodzie VBA stworzonym w innych aplikacjach z pakietu Office? Odpowiedź jest relatywnie prosta- należy przekonwertować tablicę Array na obiekt ADODB.Recordset aby następnie zyskać możliwość filtrowania danych zgodnie z zasadami języka SQL. Myślę więc, że każdy kto spotkał się z podobnym problemem może uznać niniejsze zagadnienie za interesujące.

Krok 1- tworzymy referencję do biblioteki ADODB z poziomu edytora VBA (Menu >> Tools >> References... >> zaznaczamy Microsoft ActiveX Data Objects 2.8 Library)

Krok 2- tworzymy tablicę array przenosząc komórki Excela do tablicy lub tworząc ją na poziomie kodu. Krok 3- tworzymy obiek ADODB.Recordset i konfigurujemy pola tego obiektu Krok 4- przenosimy dane z tablicy do obiektu Recordset Krok 5- tak utworzony obiekt możemy swobodnie filtrować korzystając z właściwości .Filter obiektu Recordset. W poniższym kodzie efekt filtrowania testujemy przenosząc wybrane wartości obiektu Recordset do komórek arkusza Excel.

środa, 22 kwietnia 2015

Funkcja sprawdzająca istnienie arkusza w zewnętrznym pliku Excela? (kontynuacja)

Kontynuując zagadnienie prezentowane przed kilkoma dniami chciałbym zaprezentować podobne rozwiązanie do tam opisanego lecz tym razem oparte o funkcję użytkownika UDF. Cel- funkcja ma zwracać wartości True/False w odpowiedzi na pytanie czy we wskazanym skoroszycie (tu podamy pełną ścieżkę do pliku) istnieje określony arkusz (tu podamy jego nazwę). Poniżej kod funkcji wraz z dodatkowymi komentarzami. Funkcja tej postaci działać będzie zarówno w środowisku VBA jak również w dowolnej komórce Excela.

piątek, 17 kwietnia 2015

Jak sprawdzić nazwy arkuszy w zamkniętym pliku Excel?

Szukając odpowiedzi na pytanie z tytułu nasuwa się jedna prosta- otworzyć plik :) ...jednak zdecydowanie nie tego rozwiązania poszukujemy w tym momencie. Chciałbym zaprezentować technikę, która umożliwia odczyt i weryfikację nazw wszystkich arkuszy we wskazanym skoroszycie, ale bez otwierania tego pliku. Aby wykonać powyższe zadanie będziemy wykorzystywać zaawansowaną technikę z obszaru ADODB okreśalną mianem ADOX (Activex Data Objects Extension). Wykorzystanie ADOX umożliwia nam odczyt parametrów bazy danych oraz modyfikację struktury bazy danych przez usuwanie tabel, dodawanie nowych tabel o określonych polach, itp. Szereg informacji na temat ADOX znaleźć można na stronie Microsof MSDN, np. tutaj. W celu stworzenia skutecznego rozwiazania naszego zadania pierwszy krok naszego działania to ustawienie referencji do biblioteki ‘Microsoft ADO Ext. 2.8 for DDL and Security’ (przypomnę, referencję ustawiamy w środowisku VBA >> Menu >> Tools >> References... >> odnajdujemy bibliotekę, zaznaczamy i akceptujemy OK). Potrzebna będzie nam także podstawowa biblioteka ADO w celu ustanowienia połączenia z naszym plikiem Excela, czyli dodatkowo ustawiamy referencję do zasobów ‘Microsoft ActiveX Data Objects 2.8 Library’. Poniżej prezentuję rozwiązanie, które listuje do okna Immediate wszystkie arkusze wskazanego pliku XLSM. Dodatkowe informacje na temat nowych obiektów i ich rola zostały opisane w formie komentarzy w kodzie poniżej.

środa, 4 marca 2015

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

piątek, 27 lutego 2015

Debugowanie kodu- prosta sztuczka

Zdobywając coraz większą wiedzę i doświadczenie w obszarze VBA zaczynamy tworzyć coraz bardziej rozbudwoane linie kodu. Szczególnie referencje do złożonych obiektów przestają być problematyczne dzięki coraz lepszemu rozumieniu hierarchii obiektów i samych obiektów i kolekcji. W pewnych sytuacjach zdarzyć się może, że określona linia kodu będzie mieć następującą schematyczną postać: lub w konkretnym przykładzie: Jeżeli nasza referencja jest błędna zwrócony zostanie błąd 445: `Object doesn’t support this action`. Pytanie jednak- której ‘akcji’? jeżeli wszystko wydaje się być poprawne czasem trudno o szybkie odnalezienie błędu. Najprostszym rozwiązaniem jest więc rozbicie poszczególnych elementów składowych naszego kodu na kolejne zmienne. W tym wariancie błąd zostanie zwrócony w linii ze wskazaniem konkretnej właściwości/obiektu, który jest błędny. Poniżej dwa przykłady takiego ‘sprytnego debugowania’- wariant krótki bez deklaracji konkretnego oczekiwanego typu obiektu oraz wariant rozbudowany definiujący konkretne typy obiektów (zalecany).

piątek, 20 lutego 2015

Jak odnaleźć źródło danych dla wykresu?

Utworzenie wykresu z wykorzystaniem kodu VBA jest relatywnie łatwe, zawsze można wspomóc się rejestratorem makr co szczególnie dotyczy początkujących adeptów programowania. W jaki sposób odnaleźć źródło danych dla wskazanego wykresu, a więc zakres komórek, na bazie których nasz wykres rysuje linie czy kolumny? W tym zadaniu nie pomoże rejestrator makr, całą koncepcję musimy oprzeć na obiektach powiązanych z wykresem wykorzystując precyzyjnie hierarchię tych obiektów i odpowiednie właściwości.

Jako punkt odniesienia przyjmijmy następujący wykres liniowy posiadający jedną serię danych.



W rozwiązaniu, które poniżej musimy wykorzystać następujące obiekty i ich właściwości:

1. Obiekt Chart, czyli wykres, i jego właściwość:
2. Series, czyli serię danych wykresu, oraz jej właściwość
3. Formula, zwracającą formułę tworzącą referencję do zakresu danych.

Właściwy kod niezbędny do odnalezienia poszukiwanego zakresu wraz z dodatkowymi komentarzami znajduje się poniżej.

A co jeżeli chcielibyśmy odnaleźć kolejne elementy, punkty naszego wykresu i referencję do zakresu dla każdego z nich? W tym celu w miejscu oznaczonym gwiazdką w powyższym przykładzie musimy wstawić poniższy kod.

piątek, 6 lutego 2015

Zaokrąglanie liczb- funkcja Round vs. UDF

W dobie operacji finansowych wykonywanych dla jednostek wyrażanych w czterech, pięciu miejscach po przecinku zaokrąglanie wyników może sprawiać pewien problem. Szczególny problem pojawia się w momencie, gdy sięgamy po standardową, wbudowaną funkcję VBA Round. Okazuje się bowiem, że funkcja ta nie zaokrągla w sposób jaki oczekujemy. Zobrazuje to poniższy przykładowy kod: Jak widać zwracane wynik są w niektórych sytuacjach lekko zaskakujące. Teoretycznie bowiem obowiązuje tu zasada, iż w sytuacji, gdy zaokrąglana wartość wynosi 5 (jak w każdym powyższym przypadku) to zaokrąglenie w górę nastąpi o ile poprzednia liczba jest nieparzysta, oraz w dół- gdy poprzednia liczba jest parzysta. Jak widać nie w każdym przypadku jest to prawdziwe.

Jeżeli więc potrzebujemy klasycznej techniki zaokrąglania to niezbędne jest stworzenie własnej funkcji zaokrąglającej. Poniżej przykład takie funkcji:


Wyniki działania naszej funkcji zaokrąglającej prezentuje poniższy przykład:

poniedziałek, 2 lutego 2015

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

piątek, 30 stycznia 2015

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

wtorek, 20 stycznia 2015

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.

czwartek, 15 stycznia 2015

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.