poniedziałek, 30 listopada 2015
Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 3/3
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
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
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
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ę
poniedziałek, 21 września 2015
Jak wyrównać tekst w kształtach Shape
Oto prosty kod realizujący zadanie wraz z kilkoma komentarzami:
środa, 2 września 2015
Microsoft Word- zamiana tekstu na obiekt ContentControl (lub inny)
poniedziałek, 17 sierpnia 2015
Paste i PasteSpecial... częsty błąd
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
piątek, 3 lipca 2015
Względna pozycja komórki wewnątrz zakresu Range
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)
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)
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
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)
piątek, 17 kwietnia 2015
Jak sprawdzić nazwy arkuszy w zamkniętym pliku Excel?
środa, 4 marca 2015
Dlaczego błędna metoda nie zwraca błędu kompilacji?
piątek, 27 lutego 2015
Debugowanie kodu- prosta sztuczka
piątek, 20 lutego 2015
Jak odnaleźć źródło danych dla wykresu?
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
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 (+)
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
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
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
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
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.