Oczywiście, że podstawowym środowiskiem pracy programisty VBA pozostaje Integrated Developer Editor (IDE) dostarczany w pakiecie Office. Nie mniej istnieje kilka narzędzi pomocniczych, które można zintegrować ze środowiskiem lub jako osobne aplikacje mogą stać się niezastąpioną pomocą w codziennej pracy z VBA. Oto kilka przykładowych programów wartych polecenia i uwagi.
1. EZ-Tool
Link do strony dostawcy: www
Narzędzie instalowane jako dodatek środowiska IDE. Wspiera programistę w szeregu czynności: tworzenie procedur, zarządzanie komentarzami, obsługą błędów. Dostarcza schematy dla ADO. W moim odczuciu narzędzie w stylu 'must have'.
2. RibbonX Visual Designer 2010
Link do strony dostawcy: www
Narzędzie wspomagające tworzenie elementów wstążki- zakładek, przycisków i innych kontrolek.
3. Excel VBA Code Cleaner
Link do strony dostawcy: www
Jak sama nazwa wskazuje narzędzie oczyszczające kod ze zbędnych elementów. Narzędzie polecane w przypadku dużych projektów, projektów tworzonych w dłuższych okresach czasu, itp.
środa, 18 grudnia 2013
piątek, 13 grudnia 2013
Unikalne wartości tablicy Array (2/2)
Kontynuując poprzedni wpis o tym samym tytule tym razem trzecia z technik odnajdywania unikalnych elementów kolekcji. Technika ta opiera się o wykorzystanie słownika Dictionary. Wszystkich czytelników, których rozwiązanie to w jakiś sposób zainteresuje odsyłam do tej strony, na której znaleźć można dodatkowe informacje.
Poniższy kod, korzystając z obiektu słownika, zwróci do arkusza unikalne wartości z tablicy. Wewnątrz kodu zawarte zostały dodatkowe informacje i komentarze wyjaśniające cały mechanizm.
Na koniec kluczowe pytanie, którą technikę użyć- kolekcję czy słownik. Osobiście nie udzielę jednoznacznej odpowiedzi, ale wskażę na kilka różnic i podobieństw boldem oznaczając lepsze rozwiązanie.
a) Kolekcja jest obiektem wbudowanym języka VBA, Słownik wymaga tworzenia referencji do osobnej biblioteki.
b) Kolekcja wymaga stosowania obsługi błędów czego nie ma w przypadku Słownika i co jest rozwiązaniem (pozornie?) bardziej profesjonalnym.
c) Dodawanie elementu do kolekcji ignoruje wielkość liter, słownik uwzględnia wielkość liter przez co wymaga stosowanie dodatkowych technik VBA.
d) Kolekcja posiada ograniczony zasób metod i właściwości podczas gdy słownik pozostaje bardziej elastyczny pod względem szerszych zastosowań.
Poniższy kod, korzystając z obiektu słownika, zwróci do arkusza unikalne wartości z tablicy. Wewnątrz kodu zawarte zostały dodatkowe informacje i komentarze wyjaśniające cały mechanizm.
01 | Sub Unikaty_słownik() |
02 | |
03 | 'tworzymy obiekt zewnętrznej biblioteki _ |
04 | wykorzystując późne wiązanie |
05 | Dim Słownik As Object |
06 | Set Słownik = CreateObject("Scripting.Dictionary") |
07 | |
08 | Dim Element As Variant |
09 | Dim arrTablica As Variant |
10 | Dim i As Long |
11 |
12 | 'nasza przykładowa tablica |
13 | arrTablica = Array("Do", "Re", "Mi", "Do", "Fa", "Sol", _ |
14 | "Re", "Mi", "Fa", "La", "Do", "Si", _ |
15 | "Do", "Re", "Mi", _ |
16 | "fa", "sol", "LA", "sI") |
17 |
18 | 'tworzymy słownik |
19 | For Each Element In arrTablica |
20 | 'wymaga stosowania funkcji konwersji tekstu |
21 | Słownik(StrConv(Element, vbProperCase)) = 1 |
22 | Next |
23 |
24 | 'zwracamy elementy do arkusza |
25 | For Each Element In Słownik.keys |
26 | i = i + 1 |
27 | Cells(i, 1) = Element |
28 | Next |
29 |
30 | 'Wynik: Do,Re,Mi,Fa,Sol,La,Si |
31 |
32 | End Sub |
Na koniec kluczowe pytanie, którą technikę użyć- kolekcję czy słownik. Osobiście nie udzielę jednoznacznej odpowiedzi, ale wskażę na kilka różnic i podobieństw boldem oznaczając lepsze rozwiązanie.
a) Kolekcja jest obiektem wbudowanym języka VBA, Słownik wymaga tworzenia referencji do osobnej biblioteki.
b) Kolekcja wymaga stosowania obsługi błędów czego nie ma w przypadku Słownika i co jest rozwiązaniem (pozornie?) bardziej profesjonalnym.
c) Dodawanie elementu do kolekcji ignoruje wielkość liter, słownik uwzględnia wielkość liter przez co wymaga stosowanie dodatkowych technik VBA.
d) Kolekcja posiada ograniczony zasób metod i właściwości podczas gdy słownik pozostaje bardziej elastyczny pod względem szerszych zastosowań.
poniedziałek, 9 grudnia 2013
Unikalne wartości tablicy Array (1/2)
Jak skutecznie odnaleźć unikalne wartości z tablicy Array?
powyższe pytanie usłyszałem podczas kursu VBA prowadzonego ostatnio w Warszawie.
Osobiście stosuję dwie (lub trzy) techniki odszukiwania unikatów:
Technika 1. wartości tablicy wrzucamy w pionowy, czysty obszar arkusza, następnie stosując polecenie usuwania duplikatów (RemoveDuplicates) pozbywamy się zbędnych elementów, a następnie wynik pobieramy do naszej tablicy w kodzie VBA. Przyznam jednak, że rozwiązanie to nie należy do najbardziej efektywnych.
Technika 2. o wiele łatwiej i skuteczniej będzie sięgnąć po obiekt kolekcji (Collection) w VBA. W tym celu wykorzystamy fakt, że dodanie elementu do kolekcji, nie jest możliwe jeżeli element taki już istnieje. Jeżeli podejmujemy taką próbę to kompilator zwraca błąd wykonania. W naszej procedurze zignorujemy taki błąd w wyniku czego zignorujemy jednocześnie dany element.
Więcej informacji na temat kolekcji znaleźć można pod tym linkiem.
Kolekcje nie są jedynym czysto programistycznym sposobem na uzyskanie unikatów z tablicy. O alternatywnej technice napiszę w osobnym poście. Zapraszam do lektury już wkrótce.
powyższe pytanie usłyszałem podczas kursu VBA prowadzonego ostatnio w Warszawie.
Osobiście stosuję dwie (lub trzy) techniki odszukiwania unikatów:
Technika 1. wartości tablicy wrzucamy w pionowy, czysty obszar arkusza, następnie stosując polecenie usuwania duplikatów (RemoveDuplicates) pozbywamy się zbędnych elementów, a następnie wynik pobieramy do naszej tablicy w kodzie VBA. Przyznam jednak, że rozwiązanie to nie należy do najbardziej efektywnych.
Technika 2. o wiele łatwiej i skuteczniej będzie sięgnąć po obiekt kolekcji (Collection) w VBA. W tym celu wykorzystamy fakt, że dodanie elementu do kolekcji, nie jest możliwe jeżeli element taki już istnieje. Jeżeli podejmujemy taką próbę to kompilator zwraca błąd wykonania. W naszej procedurze zignorujemy taki błąd w wyniku czego zignorujemy jednocześnie dany element.
01 | Sub Unikaty_kolekcja() |
02 | |
03 | Dim arrKolekcja As New Collection |
04 | Dim Element As Variant |
05 | Dim arrTablica As Variant |
06 | Dim i As Long |
07 |
08 | 'nasza przykładowa tablica |
09 | arrTablica = Array("Do", "Re", "Mi", "Do", "Fa", "Sol", _ |
10 | "Re", "Mi", "Fa", "La", "Do", "Si", _ |
11 | "Do", "Re", "Mi", _ |
12 | "fa", "sol", "LA", "sI") |
13 |
14 | 'ignorujemy błąd powtarzajacych się elementów |
15 | On Error Resume Next |
16 | 'tworzymy kolekcję |
17 | For Each Element In arrTablica |
18 | arrKolekcja.Add Element, Element |
19 | Next |
20 |
21 | 'przywracamy standardową obsługę błędów |
22 | On Error GoTo 0 |
23 |
24 | |
25 | 'zwracamy elementy kolekcji do arkusza |
26 | For i = 1 To arrKolekcja.Count |
27 | Cells(i, 1) = arrKolekcja(i) |
28 | Next |
29 |
30 | 'Wynik: Do,Re,Mi,Fa,Sol,La,Si |
31 |
32 | End Sub |
Więcej informacji na temat kolekcji znaleźć można pod tym linkiem.
Kolekcje nie są jedynym czysto programistycznym sposobem na uzyskanie unikatów z tablicy. O alternatywnej technice napiszę w osobnym poście. Zapraszam do lektury już wkrótce.
poniedziałek, 2 grudnia 2013
Co w rzeczywistości zawiera komórka w Excelu?
Z pytaniem umieszczonym w tytule niniejszego posta spotkałem się kilkukrotnie, zarówno przeglądając różne wpisy i pytania na forach internetowych, jak również prowadząc szkolenie z zakresu VBA dla Excela. Chodzi bowiem o sytuację, w której chcemy określić rodzaj informacji zawartej w komórce z uwzględnieniem rodzaju formatowania jaki został zastosowany w danym zakresie arkusza.
Gdzie jednak znajduje się problem? Z punktu widzenia VBA liczba, data, czas i procent- wszystkie te elementy są liczbami. Także wartość Prawda/Fałsz w praktyce jest liczbą odpowiadającą 1 lub 0. W naszej sytuacji określić rzeczywisty typ danych znajdujących się w komórce.
W celu rozwiązania tego problemu wystarczy skonstruować prostą funkcję, której pełną postać znajdziecie Państwo poniżej. Kluczowa w tej funkcji pozostaje kolejność sprawdzania poszczególnych typów. Prześledźmy to na przykładzie typu liczbowego, który sprawdzany jest jako ostatni. Musimy się najpierw upewnić, że podane wartości nie są żadnym z typów: datą, wartością czasu, procentem lub wartością Prawda/Fałsz. Każdy z tych typów będąc domyślnie numerycznym zostałby więc rozpoznany jako liczba. Tymczasem nasza funkcja wydaje się działać prawidłowo co prezentuje poniższy zrzut ekranu.
Gdzie jednak znajduje się problem? Z punktu widzenia VBA liczba, data, czas i procent- wszystkie te elementy są liczbami. Także wartość Prawda/Fałsz w praktyce jest liczbą odpowiadającą 1 lub 0. W naszej sytuacji określić rzeczywisty typ danych znajdujących się w komórce.
W celu rozwiązania tego problemu wystarczy skonstruować prostą funkcję, której pełną postać znajdziecie Państwo poniżej. Kluczowa w tej funkcji pozostaje kolejność sprawdzania poszczególnych typów. Prześledźmy to na przykładzie typu liczbowego, który sprawdzany jest jako ostatni. Musimy się najpierw upewnić, że podane wartości nie są żadnym z typów: datą, wartością czasu, procentem lub wartością Prawda/Fałsz. Każdy z tych typów będąc domyślnie numerycznym zostałby więc rozpoznany jako liczba. Tymczasem nasza funkcja wydaje się działać prawidłowo co prezentuje poniższy zrzut ekranu.
01 | Public Function TypWartości(KOMÓRKA As Range) |
02 | |
03 | If IsEmpty(KOMÓRKA) Then |
04 | TypWartości = "PUSTA" |
05 | ElseIf Application.IsText(KOMÓRKA) Then |
06 | TypWartości = "TEKSTOWA" |
07 | ElseIf Application.IsLogical(KOMÓRKA) Then |
08 | TypWartości = "LOGICZNA" |
09 | ElseIf Application.IsErr(KOMÓRKA) Or Application.IsError(KOMÓRKA) Then |
10 | TypWartości = "BŁĄD" |
11 | ElseIf IsDate(KOMÓRKA) Then |
12 | TypWartości = "DATA" |
13 | ElseIf InStr(1, KOMÓRKA.Text, ":") <> 0 Then |
14 | TypWartości = "CZAS" |
15 | ElseIf Right(KOMÓRKA.Text, 1) = "%" Then |
16 | TypWartości = "PROCENT" |
17 | ElseIf IsNumeric(KOMÓRKA) Then |
18 | TypWartości = "LICZBA" |
19 | End If |
20 | End Function |
środa, 27 listopada 2013
Dynamiczne wymiary tablicy wielowymiarowej
Zgodnie z definicją dynamiczna tablica wielowymiarowa może zmienić swój rozmiar (ilość elementów) tylko w zakresie ostatniego jej wymiaru. Jeżeli więc zdefiniujemy naszą tablicę następująco:
to redefinicja wymiaru dotyczyć może tylko drugiego wymiaru, pierwszy wymiar zawsze pozostanie o stałej wielkości równej 1. Możliwe będą więc następujące dalsze procesy:
Możemy jednak stworzyć tablicę wielowymiarową jako złożenie dwóch tablic. W sytuacji tej każdy element tablicy głównej zawierał będzie tablicę podrzędną. Tak mógłby wyglądać zapis takiego rozwiązania:
Jak widać w powyższym przypadku wymiar tablicy głównej można swobodnie zmieniać jak również elementy tablicowe w jej wnętrzu mogą również posiadać różne wymiary. Rozwiązanie to posiada jednak swoje wady, z których części można zaradzić, o czym w dalszej części wpisu.
Wadą pierwszą jest to, że o ile w przypadku pierwszej tablicy dany element pobieramy korzystając z zapisu:
o tyle w drugim przypadku zapis ten jest następujący:
O ile z powyższym w większości przypadków radzimy sobie bez problemu, o tyle problem zasadniczy pojawia się w sytuacji, gdy próbujemy przenieść tablicę do komórek Excela. Dla powyższych przypadków (indeks wymiaru tablic to 2) zapisy te będą następujące:
Chcąc rozwiązać problem dot. tablicy myArrayB i skutecznie przenieść jej wartości do zakresu komórek arkusza wystarczy tablicę tą dwukrotnie transponować. Poniższa instrukcja umożliwia prawidłowe wykonanie tej operacji:
1 | Dim myArrayA() As Variant |
2 | ReDim myArrayA(1,1) |
to redefinicja wymiaru dotyczyć może tylko drugiego wymiaru, pierwszy wymiar zawsze pozostanie o stałej wielkości równej 1. Możliwe będą więc następujące dalsze procesy:
1 | ReDim myArrayA(1,2) |
2 | ReDim myArrayA(1,5) |
3 | 'itd. |
Możemy jednak stworzyć tablicę wielowymiarową jako złożenie dwóch tablic. W sytuacji tej każdy element tablicy głównej zawierał będzie tablicę podrzędną. Tak mógłby wyglądać zapis takiego rozwiązania:
1 | Dim myArrayB() As Variant |
2 | ReDim myArrayB(1) |
3 | |
4 | myArrayB(0) = Array("0x0", "0x1", "0x2") |
5 | myArrayB(1) = Array("1x0", "1x1") |
6 | myArrayB(1) = Array("1x0", "1x1", "1x2") |
7 |
8 | ReDim Preserve myArrayB(2) |
9 | myArrayB(2) = Array("2x0", "2x1", "2x2") |
Jak widać w powyższym przypadku wymiar tablicy głównej można swobodnie zmieniać jak również elementy tablicowe w jej wnętrzu mogą również posiadać różne wymiary. Rozwiązanie to posiada jednak swoje wady, z których części można zaradzić, o czym w dalszej części wpisu.
Wadą pierwszą jest to, że o ile w przypadku pierwszej tablicy dany element pobieramy korzystając z zapisu:
1 | myArrayA(0,0) |
2 | myArrayA(0,1 |
o tyle w drugim przypadku zapis ten jest następujący:
1 | myArrayB(0)(0) |
2 | myArrayB(0)(1) |
O ile z powyższym w większości przypadków radzimy sobie bez problemu, o tyle problem zasadniczy pojawia się w sytuacji, gdy próbujemy przenieść tablicę do komórek Excela. Dla powyższych przypadków (indeks wymiaru tablic to 2) zapisy te będą następujące:
1 | ActiveCell.Resize(3,3) = myArrayA >> zapis poprawny i skuteczny |
2 | ActiveCell.Resize(3,3) = myArrayB >> zapis poprawny ale nieskuteczny |
Chcąc rozwiązać problem dot. tablicy myArrayB i skutecznie przenieść jej wartości do zakresu komórek arkusza wystarczy tablicę tą dwukrotnie transponować. Poniższa instrukcja umożliwia prawidłowe wykonanie tej operacji:
1 | ActiveCell.Resize(3, 3) = Application.Transpose( _ |
2 | Application.Transpose(myArrayB)) |
czwartek, 21 listopada 2013
Funkcja MID w roli operatora
Znana i powszechnie stosowana funkcja MID języka VBA jest nie tylko poleceniem zwracającym fragment tekstu, ale także operatorem, którego zadaniem jest podmiana fragmentu tekstu dla określonej zmiennej.
Wyobraźmy sobie taki tekst:"Uzupełnienie k..... przez f...... MID", w którym w miejsce kropek chcemy wstawić odpowiednie rozwinięcie wyrazów aby uzyskać tekst: "Uzupełnienie kropek przez funkcję MID".
Poniżej przedstawiam dwie proste procedury realizujące to zadanie, pierwsza z nich wykorzystuje funkcję MID w nowej roli. Druga z procedur da dokładnie ten sam efekt. Co jednak ciekawe- pierwsza procedura wykona się około 2-3 razy krócej niż druga.
Źródło powyższego działania funkcji MID zaczerpnąłem z tego pytania na StackOverflow.Com. Zainteresowane osoby zachęcam do zapoznania się z innym ciekawostkami zawartymi w odpowiedziach. O niektórych z tych ciekawostek wspominałem już w swoich wpisach. O innych z pewnością kiedyś wspomnę w strefie wiedzy. Wiele z tych tematów poruszane podczas kursów VBA, które prowadzimy w Warszawie, Krakowie i Wrocławiu.
Wyobraźmy sobie taki tekst:"Uzupełnienie k..... przez f...... MID", w którym w miejsce kropek chcemy wstawić odpowiednie rozwinięcie wyrazów aby uzyskać tekst: "Uzupełnienie kropek przez funkcję MID".
Poniżej przedstawiam dwie proste procedury realizujące to zadanie, pierwsza z nich wykorzystuje funkcję MID w nowej roli. Druga z procedur da dokładnie ten sam efekt. Co jednak ciekawe- pierwsza procedura wykona się około 2-3 razy krócej niż druga.
01 | Sub Przykład1() |
02 | |
03 | Dim strTekst As String |
04 | strTekst = "Uzupełnienie k..... przez f...... MID" |
05 | |
06 | Mid(strTekst, 15, 5) = "ROPEK" |
07 | Mid(strTekst, 28, 6) = "UNKCJĘ" |
08 | MsgBox strTekst |
09 | |
10 | End Sub |
11 |
12 | Sub Przykład2() |
13 | |
14 | Dim strTekst As String |
15 | strTekst = "Uzupełnienie k..... przez f...... MID" |
16 | strTekst = Left(strTekst, 14) & "ROPEK" & Mid(strTekst, 20, 8) _ |
17 | & "UNKCJĘ" & Right(strTekst, 4) |
18 | MsgBox strTekst |
19 | End Sub |
Źródło powyższego działania funkcji MID zaczerpnąłem z tego pytania na StackOverflow.Com. Zainteresowane osoby zachęcam do zapoznania się z innym ciekawostkami zawartymi w odpowiedziach. O niektórych z tych ciekawostek wspominałem już w swoich wpisach. O innych z pewnością kiedyś wspomnę w strefie wiedzy. Wiele z tych tematów poruszane podczas kursów VBA, które prowadzimy w Warszawie, Krakowie i Wrocławiu.
wtorek, 12 listopada 2013
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
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
1 | Debug.Print TypeOf Selection Is Range |
2 | Debug.Print TypeName(Selection) = "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:
1 | Dim A As Variant |
2 | Set A = Selection |
3 | Debug.Print TypeOf A Is Range |
4 | Debug.Print TypeName(A) = "Range" |
Nie mniej tylko instrukcja TypeName pozwala nam określić jakiego typu jest dana zmienna:
1 | Dim B As Variant |
2 | B = 1 |
3 | Debug.Print TypeName(A) >> Range |
4 | Debug.Print TypeName(B) >> Integer |
2. Jeżeli badana zmienna przyjmuje wartość Nothing operator TypeOf zwróci błąd a funkcja TypeName tekst Nothing:
1 | Dim C As Variant |
2 | Set C = Nothing |
3 | Debug.Print TypeOf C Is Range >> błąd wykonania |
4 | Debug.Print TypeName(C) >> Nothing |
3. Operator TypeOf zwraca wartości Prawda/Fałsz, funkcja TypeName zwraca ciąg tekstowy z nazwą typu zmiennej:
1 | Debug.Print TypeOf A Is Range >> True |
2 | Debug.Print TypeName(A) >> Range |
4. Operator TypeOf działa szybciej niż funkcja TypeName.
Dodatkowe informacje dot. w/w instrukcji można znaleźć tutaj:
TypeName
TypeOf
wtorek, 5 listopada 2013
Ograniczona ilość znaków w linii (MS Word)
Wyobraźmy sobie sytuację, że określonym dokumencie MS Word chcemy dokonać takiego podziału tekstu, aby w żadnej linii nie było więcej niż 30 znaków. Przykładowy paragraf, który pierwotnie wygląda następująco:
Lorem
ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas porttitor congue
massa. Fusce posuere, magna sed pulvinar ultricies, purus lectus malesuada
libero, sit amet commodo magna eros quis urna. Nunc viverra imperdiet enim.
Fusce est.
chcemy automatycznie podzielić na następujące linie, z których żadna nie ma więcej niż 30 znaków:
consectetuer adipiscing elit.
Maecenas porttitor congue
massa. Fusce posuere, magna
sed pulvinar ultricies, purus
lectus malesuada libero, sit
amet commodo magna eros quis
urna. Nunc viverra imperdiet
enim. Fusce est.
Poniższe makro wykona za nas tą operację dla wszystkich paragrafów aktywnego dokumentu. Tym razem wszystkie uwagi, ciekawostki i założenia zostały omówione w postaci komentarzy wewnątrz kodu.
01 | Sub Rozwiązanie() |
02 | Dim PAR As Paragraph |
03 | |
04 | 'zmienne pomocnicze odpowiedzialne za |
05 | 'kontrolę długości poszczególnych fragmentów |
06 | 'tekstu dokumentu |
07 | Dim znakPAR&, startPAR&, i&, znakCEL& |
08 | |
09 | 'dla każdego paragrafu dokumentu |
10 | For Each PAR In ActiveDocument.Paragraphs |
11 | |
12 | 'określamy długość paragrafu, jego punkt początkowy, |
13 | 'ilość znaków w linii (tu: 30) |
14 | znakPAR = PAR.Range.Characters.Count |
15 | startPAR = PAR.Range.Start |
16 | znakCEL = 30 |
17 | |
18 | 'dalsza procedura ma sens o ile długość paragrafu |
19 | 'jest większa od limitu długości linii |
20 | If znakPAR > znakCEL Then |
21 | |
22 | For i = (startPAR + znakCEL) To (startPAR + znakPAR) Step znakCEL |
23 | 'sprawdzamy czy znak podziału nie wypada w środku wyrazu |
24 | If Len(Trim(ActiveDocument.Range(i - 1, i + 1).Text)) < 2 Then |
25 | |
26 | 'jeżeli nie to wstawiamy miękki znak podziału |
27 | 'sprawdziwszy gdzie jest pusta spacja |
28 | If ActiveDocument.Range(i - 1, i) = " " Then |
29 | ActiveDocument.Range(i, i).InsertBefore Chr(11) |
30 | Else |
31 | ActiveDocument.Range(i + 1, i + 1).InsertAfter Chr(11) |
32 | End If |
33 | |
34 | Else |
35 | 'jeżeli podział miałby nastąpić w środku wyrazu to |
36 | 'przesuwamy się na początek tegoż i wstawiamy przejście |
37 | 'do nowej linii |
38 | ActiveDocument.Range(i, i).Select |
39 | Selection.MoveLeft wdWord |
40 | Selection.InsertBefore Chr(11) |
41 | i = Selection.Start |
42 | End If |
43 | |
44 | Next |
45 | End If |
46 | Next |
47 | End Sub |
czwartek, 31 października 2013
Niemodalne okienko MsgBox
Standardowe okienko MsgBox występujące w języku VBA ma tą (niekiedy uciążliwą) cechę, że jest okienkiem modalnym. Modalnym czyli takim, które należy zamknąć aby móc powrócić do pracy z aplikacją znajdującą się w tle. Jak więc wywołać proste okienko z przyciskami Tak/Nie/Anuluj w trybie niemodalnym? Jest kilka możliwych wariantów spośród których skupię się szczegółowo tylko na jednym.
Wariant A. Należy stworzyć okno UserForm, a następnie wyświetlić go w trybie niemodalnym:
Wariant B. Wykorzystać okienko znajdujące się w zewnętrznej bibliotece Windows Script Hosting. Poniżej prezentuję kompletny kod, który wyświetla okienko z trzema przyciskami oraz wykonuje akcję zależnie od wybranego przycisku.
Rozwiązanie to posiada dodatkową opcję, która pozwala określić czas, przez jaki okienko będzie widoczne. Niestety, parametr ten nie działa stabilnie. W praktyce tylko ustawienie czasu oczekiwania na 1 sekundę gwarantuje ukrycie okna. W przypadku dłuższego czasu oczekiwania okienko i tak oczekuje na akcję za strony użytkownika. Być może błąd ten zależny jest od wersji oprogramowania nie mniej nie udało mi się odnaleźć informacji na temat rozwiązania tego problemu.
Dodatkowe informacje na temat okna Popup z biblioteki Windows Scripting znajdziecie Państwo pod tym linkiem.
Wariant A. Należy stworzyć okno UserForm, a następnie wyświetlić go w trybie niemodalnym:
1 | UserForm1.Show vbModeLess |
Wariant B. Wykorzystać okienko znajdujące się w zewnętrznej bibliotece Windows Script Hosting. Poniżej prezentuję kompletny kod, który wyświetla okienko z trzema przyciskami oraz wykonuje akcję zależnie od wybranego przycisku.
01 | Sub Okno_Popup() |
02 |
03 | Dim wsMsgBox As Object |
04 | Set wsMsgBox = CreateObject("WScript.Shell") |
05 |
06 | Dim mbResult As Integer |
07 | |
08 | 'czas oczekiwania, opcjonalny, _ |
09 | niestety nie zawsze działa! |
10 | Dim iTime As Integer |
11 | iTime = 2 |
12 | |
13 | 'wyświetlamy okienko określając jego parametry |
14 | mbResult = wsMsgBox.Popup("Okienko by Windows Scripting.", _ |
15 | iTime, "Okienko niemodalne MsgBox!", 3) |
16 | |
17 | Select Case mbResult |
18 | Case 6 |
19 | Debug.Print "klawisz yes/tak" |
20 | Case 7 |
21 | Debug.Print "klawisz no/nie" |
22 | Case Else |
23 | Debug.Print "pozostałe" |
24 | End Select |
25 | |
26 | End Sub |
Rozwiązanie to posiada dodatkową opcję, która pozwala określić czas, przez jaki okienko będzie widoczne. Niestety, parametr ten nie działa stabilnie. W praktyce tylko ustawienie czasu oczekiwania na 1 sekundę gwarantuje ukrycie okna. W przypadku dłuższego czasu oczekiwania okienko i tak oczekuje na akcję za strony użytkownika. Być może błąd ten zależny jest od wersji oprogramowania nie mniej nie udało mi się odnaleźć informacji na temat rozwiązania tego problemu.
Dodatkowe informacje na temat okna Popup z biblioteki Windows Scripting znajdziecie Państwo pod tym linkiem.
wtorek, 22 października 2013
Losowy dokument w Wordzie
Tworząc szereg rozwiązań w VBA dla aplikacji MS Word często testuję określone techniki korzystając z przykładowego 'losowego' dokumentu. Dokument taki można stworzyć w bardzo prosty sposób wykorzystując ukryte polecenia aplikacji MS Word. W tym celu proszę utworzyć nowy pusty dokument w Word'zie i wykorzystać jedną z poniższych technik.
Wariant 1. Aby stworzyć losowy tekst 'Lorem ipsum' sięgamy po polecenie:
=Lorem()
które należy wpisać w pierwszym linii dokumentu i wcisnąć enter. W wyniku otrzymamy trzy paragrafy losowego tekstu.
Rozwiązanie to posiada możliwość parametryzowania:
=Lorem(10)
utworzy 10 paragrafów z losową ilością zdań
=Lorem(10,3)
utworzy 10 paragrafów i po trzy zdania w każdym paragrafie.
Wariant 2. Jeżeli tekst, z których chcemy pracować ma być w języku polskim sięgamy wtedy po polecenie:
=Rand()
które działa podobnie jak prezentowane powyżej polecenie Lorem. Dostępne więc będą wiarianty:
=Rand(10)
=Rand(10,3)
które odpowiednio definiują ilość paragrafów i zdań w każdym paragrafie.
Technika ta przyda się w jednym z kolejnych wpisów, który wkrótce pojawi się w sekcji Strefa Wiedzy. Strefa Wiedzy prowadzona jest jako rozszerzenie dla kursów VBA organizowanych przez naszą firmę w Krakowie, Warszawie i Wrocławiu.
Wariant 1. Aby stworzyć losowy tekst 'Lorem ipsum' sięgamy po polecenie:
=Lorem()
które należy wpisać w pierwszym linii dokumentu i wcisnąć enter. W wyniku otrzymamy trzy paragrafy losowego tekstu.
Rozwiązanie to posiada możliwość parametryzowania:
=Lorem(10)
utworzy 10 paragrafów z losową ilością zdań
=Lorem(10,3)
utworzy 10 paragrafów i po trzy zdania w każdym paragrafie.
Wariant 2. Jeżeli tekst, z których chcemy pracować ma być w języku polskim sięgamy wtedy po polecenie:
=Rand()
które działa podobnie jak prezentowane powyżej polecenie Lorem. Dostępne więc będą wiarianty:
=Rand(10)
=Rand(10,3)
które odpowiednio definiują ilość paragrafów i zdań w każdym paragrafie.
Technika ta przyda się w jednym z kolejnych wpisów, który wkrótce pojawi się w sekcji Strefa Wiedzy. Strefa Wiedzy prowadzona jest jako rozszerzenie dla kursów VBA organizowanych przez naszą firmę w Krakowie, Warszawie i Wrocławiu.
środa, 16 października 2013
Metoda Find w aplikacji MS Word- Przykład 1
Metoda .Find to jedno z bardziej przydatnych narzędzi nie tylko w Wordzie, ale także w Excelu (o czym wkrótce napiszę kilka postów). Ogólna zasada działania metody jest prosta- Znajdź >> Zaznacz. O wiele częściej sięgamy po to rozwiązanie w konfiguracji Znajdź >> Zamień. Tak czy inaczej w pracy z metodą .Find warto zawsze:
a) skorzystać z rejestratora makr
b) wykorzystać umiejętnie znaki specjalne (Wildcards) dostępne w pracy z tą metodą.
Od czasu do czasu przedstawię proste przykłady pracy z techniką Znajdź >> Zamień. Na początek następujące zadanie:
Dokument składa się z szeregu 12-to cyfrowych liczb umieszczonych w kolejnych paragrafach. Zadanie polega na wstawieniu pojedynczych cudzysłowów na początku i końcu każdego ciągu oraz na umieszczeniu wszystkich wpisów w jednej linii. Poniższy zrzut ekran pokazuje sytuację przed i po. Nieprzypadkowo na zrzucie ekranu dostrzec można również znaki podziału paragrafu, które będą kluczowe w rozwiązaniu problemu.
Podejście A. Teoretycznie możemy wykorzystać pętle i przeszukać cały dokument na okoliczność ciągów znaku uprzednio częściowo dokonawszy zamiany znaku paragrafu na przecinek. Rozwiązanie choć skuteczne to nieefektywne i nie będzie tu prezentowane.
Podejście B. Wykorzystując dwukrotnie metodę .Find >> .Replace osiągniemy nasz cel. Proszę zwrócić uwagę na parametr .Wildcards ustawiony odmiennie w pierwszej i drugiej części kodu.
Podejście C. Kluczowe w tym wariancie jest zastąpienie symbolu znaku paragrafu. W miejsce '^p' wstawiamy inny odpowiednik nowego paragrafu wyrażony jako '^13', który nie powoduje kolizji z ustawionym naprzemiennie parametrem .Wildcards w powyższym przykładzie. Dzięki temu nasze ostateczne rozwiązanie staje się jeszcze bardziej kompaktowe.
Prowadząc szkolenie z VBA zawsze staram się prezentować rozwiązania optymalne, choć często wychodzę od rozwiązań naturalnych-intuicyjnych by następnie móc zaprezentować ewolucję rozwiązania podobnie jak ma to miejsce powyżej.
a) skorzystać z rejestratora makr
b) wykorzystać umiejętnie znaki specjalne (Wildcards) dostępne w pracy z tą metodą.
Od czasu do czasu przedstawię proste przykłady pracy z techniką Znajdź >> Zamień. Na początek następujące zadanie:
Dokument składa się z szeregu 12-to cyfrowych liczb umieszczonych w kolejnych paragrafach. Zadanie polega na wstawieniu pojedynczych cudzysłowów na początku i końcu każdego ciągu oraz na umieszczeniu wszystkich wpisów w jednej linii. Poniższy zrzut ekran pokazuje sytuację przed i po. Nieprzypadkowo na zrzucie ekranu dostrzec można również znaki podziału paragrafu, które będą kluczowe w rozwiązaniu problemu.
Podejście A. Teoretycznie możemy wykorzystać pętle i przeszukać cały dokument na okoliczność ciągów znaku uprzednio częściowo dokonawszy zamiany znaku paragrafu na przecinek. Rozwiązanie choć skuteczne to nieefektywne i nie będzie tu prezentowane.
Podejście B. Wykorzystując dwukrotnie metodę .Find >> .Replace osiągniemy nasz cel. Proszę zwrócić uwagę na parametr .Wildcards ustawiony odmiennie w pierwszej i drugiej części kodu.
01 | Sub Podejście_B() |
02 |
03 | With Selection.Find |
04 | .Text = "^p" |
05 | .Replacement.Text = "," |
06 | .MatchWildcards = False |
07 | .Execute Replace:=wdReplaceAll |
08 | |
09 | .Text = "([0-9]{12})" |
10 | .MatchWildcards = True |
11 | .Replacement.Text = "'\1'" |
12 | .Execute Replace:=wdReplaceAll |
13 |
14 | End With |
15 |
16 | End Sub |
Podejście C. Kluczowe w tym wariancie jest zastąpienie symbolu znaku paragrafu. W miejsce '^p' wstawiamy inny odpowiednik nowego paragrafu wyrażony jako '^13', który nie powoduje kolizji z ustawionym naprzemiennie parametrem .Wildcards w powyższym przykładzie. Dzięki temu nasze ostateczne rozwiązanie staje się jeszcze bardziej kompaktowe.
01 | Sub Podejście_C() |
02 |
03 | With Selection.Find |
04 | .Text = "([0-9]{10})(^13)" |
05 | .Replacement.Text = "'\1'," |
06 | .MatchWildcards = True |
07 | .Execute Replace:=wdReplaceAll |
08 | End With |
09 |
10 | End Sub |
Prowadząc szkolenie z VBA zawsze staram się prezentować rozwiązania optymalne, choć często wychodzę od rozwiązań naturalnych-intuicyjnych by następnie móc zaprezentować ewolucję rozwiązania podobnie jak ma to miejsce powyżej.
czwartek, 10 października 2013
Podręczna paleta kolorów wypełnienia
Wyobraźmy sobie sytuację, w której chcemy ułatwić użytkownikowi nanoszenie kolorów w określonym obszarze arkusza. Odpowiednie kolory mogą oznaczać status danego produktu, zamówienia, tworzyć kolorystyczną informacje o postępie zdarzenia, itp. Ot np. spójrzmy na poniższy układ prostej tabeli wraz z wyświetlonym szybkim menu podręcznym przyspieszającym kolorowanie.
Poniżej w kilku krokach prezentuję technikę tworzenia niniejszego rozwiązania.
Krok 1. Procedura tworząca i wyświetlająca menu podręczne z paletą kolorystyczną. Procedura ta wykorzystuje tradycyjne techniki tworzenia menu oparte o obiekty/kolekcje CommandBar/CommandBars.
Krok 2. Jak łatwo zauważyć w powyższej procedurze tworzone menu jest tymczasowe. Niezbędna jest więc procedura usuwania paska zaraz po tym gdy zostanie wykorzystany.
Krok 3. Wykorzystamy obsługę zdarzenia BeforeRightClick w celu wyświetlenia menu tylko w kolumnie C odpowiedniego arkusza. Co ważne, poniższe rozwiązanie wyświetli nasze nowe menu po kliknięciu prawym klawiszem myszy. Nadal pozostawimy użytkownikowi dostęp do standardowego menu podręcznego o ile wybierze na klawiaturze konfigurację Shift + F10.
I na koniec mała wskazówka- wykorzystana w kroku 1 metoda .ShowPopup posiada dodatkowe, opcjonalne parametry, które umożliwiają wskazanie miejsca, w którym ma zostać wyświetlone nasze menu podręczne.
Tworzenie interakcji i rozwiązań ułatwiających pracę z Excelem (i innymi aplikacjami Office) jest ważną umiejętnością każdego programisty co staram się często podkreślać prowadząc kurs z programowania w VBA.
Poniżej w kilku krokach prezentuję technikę tworzenia niniejszego rozwiązania.
Krok 1. Procedura tworząca i wyświetlająca menu podręczne z paletą kolorystyczną. Procedura ta wykorzystuje tradycyjne techniki tworzenia menu oparte o obiekty/kolekcje CommandBar/CommandBars.
01 | Sub RightClickMenu_Utwórz() |
02 | |
03 | Dim tmpCB As CommandBar |
04 | Dim tmpCBpop As CommandBarPopup |
05 | |
06 | 'na wszelki wypadek |
07 | RightClickMenu_Usuń |
08 | |
09 | Set tmpCB = Application.CommandBars.Add("KolorMenu", msoBarPopup, , True) |
10 | Set tmpCBpop = tmpCB.Controls.Add(ID:=1691) |
11 | tmpCB.ShowPopup |
12 |
13 | 'usuwamy po wykonaniu operacji |
14 | RightClickMenu_Usuń |
15 | End Sub |
Krok 2. Jak łatwo zauważyć w powyższej procedurze tworzone menu jest tymczasowe. Niezbędna jest więc procedura usuwania paska zaraz po tym gdy zostanie wykorzystany.
1 | Sub RightClickMenu_Usuń() |
2 | On Error Resume Next |
3 | Application.CommandBars("KolorMenu").Delete |
4 | End Sub |
Krok 3. Wykorzystamy obsługę zdarzenia BeforeRightClick w celu wyświetlenia menu tylko w kolumnie C odpowiedniego arkusza. Co ważne, poniższe rozwiązanie wyświetli nasze nowe menu po kliknięciu prawym klawiszem myszy. Nadal pozostawimy użytkownikowi dostęp do standardowego menu podręcznego o ile wybierze na klawiaturze konfigurację Shift + F10.
1 | Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) |
2 | If Target.Column = 3 Then |
3 | Cancel = True |
4 | RightClickMenu_Utwórz |
5 | End If |
6 | End Sub |
I na koniec mała wskazówka- wykorzystana w kroku 1 metoda .ShowPopup posiada dodatkowe, opcjonalne parametry, które umożliwiają wskazanie miejsca, w którym ma zostać wyświetlone nasze menu podręczne.
Tworzenie interakcji i rozwiązań ułatwiających pracę z Excelem (i innymi aplikacjami Office) jest ważną umiejętnością każdego programisty co staram się często podkreślać prowadząc kurs z programowania w VBA.
piątek, 4 października 2013
Przechowywanie wartości zmiennych po zamknięciu pliku
Szukając odpowiedni na pytanie jak przechować zmienne po zamknięciu pliku przychodzi mi do głowy szereg pomysłów. Na początek wymienię kilka z nich- wykorzystując komórkę w arkuszu (tylko Excel), korzystając z właściwości dokumentu, korzystając z kolekcji Variables (tylko Word), korzystając z rejestru komputera (tylko lokalnie, nie przenoszone z plikiem).
Relatywnie ciekawą techniką jest jednak wykorzystanie instrukcji Put (dla zapisu) i Get (dla odczytu) z zapisem do pliku tekstowego. Rozwiązanie to mam kilka dodatkowych zalet:
1. można szybko i skutecznie przechować wartości tablic, co nie jest możliwe w rozwiązaniach alternatywnych,
2. utworzony plik tekstowy sprawia wrażenie zaszyfrowanego, nie każdy będzie w stanie odczytać jego zawartość.
Skoro są zalety to i zazwyczaj istnieją wady rozwiązania. Otóż i owszem- ograniczona wielkość zmiennej- dla tablicy będzie to niewiele ponad 3000 rekordów.
Jak działa taki mechanizm? Jak wygląda kod zapisu i odczytu?
Wyobraźmy sobie taką oto sytuację, że obszar tabeli Excela A1:E7 chcemy zapisać w postaci zmiennej tablicowej i przesłać w postaci pliku tekstowego do innej osoby.
W celu zapisu posłużymy się takim poniższym przykładowym kodem. Dodatkowe parametry zostały opisane w postaci komentarzy wewnątrz kodu.
Jeżeli operacja powiodła się w domyślnej lokalizacji znajdziemy plik o nazwie "Array.To.File.VBA.bin". Próba otwarcia tego pliku w prostych edytorach tekstu da nam następujący lub podobny efekt:
Plik jest niewielki i jednocześnie sprawia wrażenie jakoby zawartość była zaszyfrowana. Teraz możemy przesłać plik do odbiorcy.
W jaki sposób możemy odczytać nasz plik. Wystarczy uruchomić poniższą procedurę aby wartość naszej tabeli została wprowadzona do arkusza Excel począwszy od aktywnie zaznaczonej komórki. (Dodatkowe uwagi dot. kodu w postaci komentarzy poniżej)
Przydatne linki referencyjne do strony Microsoft MSDN:
Put Statement
Get Statement
Relatywnie ciekawą techniką jest jednak wykorzystanie instrukcji Put (dla zapisu) i Get (dla odczytu) z zapisem do pliku tekstowego. Rozwiązanie to mam kilka dodatkowych zalet:
1. można szybko i skutecznie przechować wartości tablic, co nie jest możliwe w rozwiązaniach alternatywnych,
2. utworzony plik tekstowy sprawia wrażenie zaszyfrowanego, nie każdy będzie w stanie odczytać jego zawartość.
Skoro są zalety to i zazwyczaj istnieją wady rozwiązania. Otóż i owszem- ograniczona wielkość zmiennej- dla tablicy będzie to niewiele ponad 3000 rekordów.
Jak działa taki mechanizm? Jak wygląda kod zapisu i odczytu?
Wyobraźmy sobie taką oto sytuację, że obszar tabeli Excela A1:E7 chcemy zapisać w postaci zmiennej tablicowej i przesłać w postaci pliku tekstowego do innej osoby.
W celu zapisu posłużymy się takim poniższym przykładowym kodem. Dodatkowe parametry zostały opisane w postaci komentarzy wewnątrz kodu.
01 | Sub Write_Array_To_File() |
02 |
03 | Dim Plik As String |
04 | |
05 | Dim fileNum As Integer |
06 | |
07 | Dim tblRange() As Variant |
08 | tblRange = Range("A1:E7").Value |
09 | |
10 | 'nasz plik wynikowy |
11 | Plik = "Array.To.File.VBA.bin" |
12 | 'gdy plik istnieje to uprzednio go skasujemy _ |
13 | wymagana obsługa błędów na okoliczność gdy plik nie istnieje |
14 | On Error Resume Next |
15 | Kill Plik |
16 | On Error GoTo 0 |
17 | |
18 | 'procedura tworzenia pliku |
19 | fileNum = FreeFile |
20 | |
21 | Dim arrLen As Long |
22 | 'parametr długości wg pomocy z MSDN: _ |
23 | =2 + ilość wymiarów * 8 + _ |
24 | wielkość wymiaru 1 * wielkość wymiaru 2 * ilość wymiarów |
25 | 'w praktyce wartość ta jest za mała i zwraca błąd, dlatego _ |
26 | eksperymentalnie została dodana dodatkowa wartość |
27 | 'opcjonalnie można ustawić wartość na poziom max = 32767 |
28 | arrLen = (2 + 2 * 8) + _ |
29 | (UBound(tblRange, 1) * UBound(tblRange, 2) * 2) + 255 |
30 | |
31 | 'otwieramy plik do zapisu i wywołujemy instrukcję Put |
32 | Open Plik For Random As #fileNum Len = arrLen |
33 | Put #fileNum, 1, tblRange |
34 | Close #fileNum |
35 |
36 | End Sub |
Jeżeli operacja powiodła się w domyślnej lokalizacji znajdziemy plik o nazwie "Array.To.File.VBA.bin". Próba otwarcia tego pliku w prostych edytorach tekstu da nam następujący lub podobny efekt:
Plik jest niewielki i jednocześnie sprawia wrażenie jakoby zawartość była zaszyfrowana. Teraz możemy przesłać plik do odbiorcy.
W jaki sposób możemy odczytać nasz plik. Wystarczy uruchomić poniższą procedurę aby wartość naszej tabeli została wprowadzona do arkusza Excel począwszy od aktywnie zaznaczonej komórki. (Dodatkowe uwagi dot. kodu w postaci komentarzy poniżej)
01 | Sub Read_Array_From_File() |
02 | |
03 | Dim Plik As String |
04 | Dim fileNum As Integer |
05 | Dim tblRange() As Variant |
06 |
07 | 'plik musi znajdować się w domyślnej lokalizacji |
08 | Plik = "Array.To.File.VBA.bin" |
09 | fileNum = FreeFile |
10 |
11 | Dim lenAAA As Integer |
12 | 'nie znamy wielkości tabeli- ustawiamy max wartość parametru |
13 | lenAAA = 32767 |
14 | |
15 | 'otwarcie pliku i odczyt zwaratości do zmiennej |
16 | Open Plik For Random As #fileNum Len = lenAAA |
17 | Get #fileNum, 1, tblRange |
18 | Close #fileNum |
19 | |
20 | 'przeniesienie tabeli do arkusza względem aktywnej komórki |
21 | ActiveCell.Resize(UBound(tblRange, 1), UBound(tblRange, 2)) = tblRange |
22 | End Sub |
Przydatne linki referencyjne do strony Microsoft MSDN:
Put Statement
Get Statement
piątek, 27 września 2013
Sprawdzanie właściwości przed jej ustawieniem
Niniejszy temat został wywołany na forum StackOverflow.Com gdzie padło pytanie o sens niniejszego kodu (który tutaj został lekko zmodyfikowany dla celów prezentacyjnych):
Na pierwszy rzut oka w istocie- jaki jest sens sprawdzać czy dany wiersz jest ukryty skoro chcemy i tak ostatecznie odkryć wszystkie dziesięć tysięcy wierszy. Wystarczy przecież wykonać poniższą pętlę:
Otóż pierwszy zapis jest bardzo uzasadniony i ma swoją wyraźną przewagę nad pętlą drugą (choć efekt ostatecznie będzie na 100% identyczny). Porównawczo wygląda to następująco (dla identycznych parametrów środowiska, w którym wykonany został test dla każdego wariantu):
1. pętla pierwsza, wszystkie wiersze były uprzednio odkryte- czas wykonania- 0.2 sek
2. pętla druga, wszystkie wiersze były uprzednio odkryte- czas wykonania- 13.1 sek
3. pętla pierwsza, wszystkie wiersze były uprzednio ukryte- czas wykonania- 15.6 sek
Z czego wynikają różnice. Zasadniczo z prostego założenia, zgodnie z którym odczyt właściwości odbywa się szybciej niż jej ustawienie. Jeżeli istnieje uzasadnienie, że większość elementów (tu: wierszy) może nie wymagać ustawiania właściwości (tu: odkrywania) to warto uprzednio sprawdzić bieżący stan danej właściwości (tu: czy wiersz jest ukryty czy odkryty). Zasadę tę warto stosować do wszystkich właściwości o ile pracujemy na relatywnie dużej kolekcji.
1 | For i =1 to 10000 |
2 | If Me.Rows(i).Hidden = True Then |
3 | Me.Rows(i).Hidden = False |
4 | End If |
5 | Next |
Na pierwszy rzut oka w istocie- jaki jest sens sprawdzać czy dany wiersz jest ukryty skoro chcemy i tak ostatecznie odkryć wszystkie dziesięć tysięcy wierszy. Wystarczy przecież wykonać poniższą pętlę:
1 | For i =1 to 10000 |
2 | Me.Rows(i).Hidden = False |
3 | Next |
Otóż pierwszy zapis jest bardzo uzasadniony i ma swoją wyraźną przewagę nad pętlą drugą (choć efekt ostatecznie będzie na 100% identyczny). Porównawczo wygląda to następująco (dla identycznych parametrów środowiska, w którym wykonany został test dla każdego wariantu):
1. pętla pierwsza, wszystkie wiersze były uprzednio odkryte- czas wykonania- 0.2 sek
2. pętla druga, wszystkie wiersze były uprzednio odkryte- czas wykonania- 13.1 sek
3. pętla pierwsza, wszystkie wiersze były uprzednio ukryte- czas wykonania- 15.6 sek
Z czego wynikają różnice. Zasadniczo z prostego założenia, zgodnie z którym odczyt właściwości odbywa się szybciej niż jej ustawienie. Jeżeli istnieje uzasadnienie, że większość elementów (tu: wierszy) może nie wymagać ustawiania właściwości (tu: odkrywania) to warto uprzednio sprawdzić bieżący stan danej właściwości (tu: czy wiersz jest ukryty czy odkryty). Zasadę tę warto stosować do wszystkich właściwości o ile pracujemy na relatywnie dużej kolekcji.
piątek, 20 września 2013
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.
1. Skoroszyty:
1 | Dim WB As Workbook |
2 | For Each WB In Application.Workbooks |
pętla wykonywać się będzie w kolejności w jakiej skoroszyty były otwarte lub utworzone.
2. Arkusze
1 | Dim SH As WorkSheet |
2 | For Each SH In ActiveWorkbook.WorkSheets |
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
1 | Dim Cell as Range |
2 | For Each Cell In Selection.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
1 | Dim KOM as Comment |
2 | For Each KOM In ActiveSheet.Comments |
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
1 | Dim KOM as Comment |
2 | For Each KOM In ActiveDocument |
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:
1 | Dim BM as Bookmark |
2 | For Each BM In ActiveDocument.Bookmarks |
zakładki zostaną ułożone w kolejności ... alfabetycznej, wg nazw zakładek.
1 | Dim BM as Bookmark |
2 | For Each BM In ActiveDocument.Content.Bookmarks |
w tym przypadku zakładki zostaną ułożone w kolejności występowania w dokumencie.
7. Kształty Shape w Excelu
1 | Dim SH As Shape |
2 | For Each SH In ActiveSheet.Shapes |
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.
poniedziałek, 16 września 2013
Obszar Range dwuwymiarowy do tablicy Array jednowymiarowej
Jedną z typowych operacji wykonywanych w VBA jest pobieranie danych z kolejnych komórek arkusza i wykonywanie określonych działań na pobranych wartościach. Proste działanie w którym najczęściej wykorzystujemy prostą pętlę For...Next.
Co jednak ważne, w sytuacji gdy operacja dotyczy wielu komórek o wiele bardziej efektywnym pozostaje przeniesienie wartości komórek do tablicy Array i przeprowadzenie dalszych działań na elementach tablicy. W sytuacji tej należy jednak pamiętać, że utworzona tablica pozostaje tablicą dwu-wymiarową.
Sytuacja ta ma miejsce także wtedy gdy pobieramy dane z pojedynczej kolumny lub pojedynczego wiersza arkusza. Spójrzmy na poniższy przykład.
utworzenie poniższych tablic:
1 | rowArr = Range("A1:C1") |
2 | colArr = Range("A1:A3" |
sprawi, że obie tablice będą dwuwymiarowe. Pobranie z nich wartości C będzie wymagało utworzenia następujących zapytań:
1 | Debug.Print rowArr(1, 3) |
2 | Debug.Print colArr(3, 1) |
Istnieje jednak możliwość utworzenia z w/w tablic tablic jednowymiarowych. Aby to uczynić należy transponować tablice. Prześledźmy to w kolejnych krokach dla obu tablic jednocześnie:
1 | rowArr = Application.Transpose(rowArr) |
2 | colArr = Application.Transpose(colArr) |
W wyniku powyższego działania tablica rowArr pozostanie dwuwymiarowa podczas gdy colArr stanie się tablicą jednowymiarową. Aby więc pobrać wartość C z tablic wywołamy następujące instrukcje:
1 | Debug.Print rowArr(3, 1) |
2 | Debug.Print colArr(3) |
Jeżeli jednak tablicę rowArr transponujemy ponownie także i ona stanie się jednowymiarową:
1 | rowArr = Application.Transpose(rowArr) |
2 | Debug.Print rowArr(3) |
wtorek, 10 września 2013
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?
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?
czwartek, 5 września 2013
Wyrażenia regularne RegExp raz jeszcze
Postanowiłem raz jeszcze wrócić do zagadnienia związanego z wyrażeniami regularnymi. Tym razem poszerzę temat o dwa obszary- pobieranie określonego fragmentu n-tego elementu spełniającego kryterium wyszukiwania oraz włączenie RegExp do własnej funkcji użytkownika (UDF).
Spójrzmy na początek na poniższy przykładowy tekst:
Questionnaire results from company web.
Name: John Smith
Phone: 1234567
Name: Jan Kowalski
Phone: 9876545321
Name: Johan Schmitt
Phone: 00112233
Z pomocą RegExp i VBA spróbujemy przygotować rozwiązanie, które umożliwi pobranie np. drugiego imienia i nazwiska (Jan Kowalski) czy też trzeciego numeru telefonu (00112233) z naszego przykładowego tekstu.
Trudność pierwsza- musimy odnaleźć fragment tekstu, który zaczyna się od Name. W tym celu nasz wzorzec będzie miał postać:
W wyniku czego jesteśmy w stanie otrzymać kolekcję składającą się z elementów:
Name: John Smith
Name: Jan Kowalski
Name: Johan Schmitt
Trudność druga- jak pobrać samo imię i nazwisko i pominąć początkowy fragment z wyników wyszukiwania? W tym celu będziemy musieli sięgnąć głębiej w metodę .Execute. Samo wywołanie tej metody tworzy kolekcję zawierającą wszystkie wystąpienia spełniające kryterium .Pattern. Istnieje jednak możliwość pobrania określonego fragmentu n-tego elementu sięgając do kolekcji .SubMatches. Elementami należącymi do tej kolekcji będą wszystkie fragmenty, które zostały ujęte w nawiasach w naszym wzorcu .Pattern.
Ogólna składnia metody .Execute wyglądałaby następująco:
Proponuję zebrać w całość nasz kod. Na początek testowa procedura wywołująca z dodatkowymi komentarzami wewnątrz kodu:
A teraz funkcja właściwa uwzględniająca przedstawione powyżej istotne elementy metody .Execute z dodatkowym komentarzem:
Po wywołaniu procedury Pobieranie_nTego_elementu() uzyskamy w oknie immediate dokładnie to czego szukaliśmy, a więc odpowiednio:
Jan Kowalski
00112233
Spójrzmy na początek na poniższy przykładowy tekst:
Questionnaire results from company web.
Name: John Smith
Phone: 1234567
Name: Jan Kowalski
Phone: 9876545321
Name: Johan Schmitt
Phone: 00112233
Z pomocą RegExp i VBA spróbujemy przygotować rozwiązanie, które umożliwi pobranie np. drugiego imienia i nazwiska (Jan Kowalski) czy też trzeciego numeru telefonu (00112233) z naszego przykładowego tekstu.
Trudność pierwsza- musimy odnaleźć fragment tekstu, który zaczyna się od Name. W tym celu nasz wzorzec będzie miał postać:
1 | .Pattern = "Name:\s*(.*)\s* |
W wyniku czego jesteśmy w stanie otrzymać kolekcję składającą się z elementów:
Name: John Smith
Name: Jan Kowalski
Name: Johan Schmitt
Trudność druga- jak pobrać samo imię i nazwisko i pominąć początkowy fragment z wyników wyszukiwania? W tym celu będziemy musieli sięgnąć głębiej w metodę .Execute. Samo wywołanie tej metody tworzy kolekcję zawierającą wszystkie wystąpienia spełniające kryterium .Pattern. Istnieje jednak możliwość pobrania określonego fragmentu n-tego elementu sięgając do kolekcji .SubMatches. Elementami należącymi do tej kolekcji będą wszystkie fragmenty, które zostały ujęte w nawiasach w naszym wzorcu .Pattern.
Ogólna składnia metody .Execute wyglądałaby następująco:
1 | .Execute(tekst)(nteWystąpienie).SubMatches(ntyFragment) |
Proponuję zebrać w całość nasz kod. Na początek testowa procedura wywołująca z dodatkowymi komentarzami wewnątrz kodu:
01 | Sub Pobieranie_nTego_Elementu() |
02 | Dim myText As String |
03 | myText = "Questionnaire results from company web." & Chr(13) & _ |
04 | "Name: John Smith" & Chr(10) & _ |
05 | "Phone: 1234567" & Chr(10) & _ |
06 | "Name: Jan Kowalski" & Chr(10) & _ |
07 | "Phone: 9876545321" & Chr(10) & _ |
08 | "Name: Johan Schmitt" & Chr(10) & _ |
09 | "Phone: 00112233" |
10 | |
11 | 'wzorzec .Pattern ze względu na Name |
12 | Debug.Print GetItem(myText, "Name:\s+(.*)\s*", 1) |
13 | 'wzorzec .Pattern ze względu na Phone |
14 | Debug.Print GetItem(myText, "Phone:\s+(.*)\s*", 2) |
15 | End Sub |
A teraz funkcja właściwa uwzględniająca przedstawione powyżej istotne elementy metody .Execute z dodatkowym komentarzem:
01 | Function GetItem(ByVal STR As String, Wzorzec As String, iNum As Long) |
02 |
03 | Dim objRegExp As Object |
04 | Set objRegExp = CreateObject("vbscript.regexp") |
05 | |
06 | With objRegExp |
07 | .Global = True 'niezbędne ustawienie |
08 | .Pattern = Wzorzec |
09 | |
10 | 'kluczowa konstrukcja metody i kolekcji SubMatches |
11 | GetItem = .Execute(STR)(iNum).SubMatches(0) |
12 | |
13 | End With |
14 | End Function |
Po wywołaniu procedury Pobieranie_nTego_elementu() uzyskamy w oknie immediate dokładnie to czego szukaliśmy, a więc odpowiednio:
Jan Kowalski
00112233
piątek, 30 sierpnia 2013
Przeglądarka zdjęć w Excelu
W czasie dwóch prowadzonych szkoleń z VBA dla Excel w Krakowie i Wrocławiu padło pytanie o możliwość stworzenia czegoś na wzór przeglądarki zdjęć w Excelu w oparciu o nazwy plików graficznych umieszczonych w kolumnie arkusza.
Oczywiste pytanie, które również zadałem brzmiało- po co i dlaczego mielibyśmy tworzyć takie rozwiązanie? Jak się okazało, i tu będę bazował na podobnych elementach uzyskanych odpowiedzi, chodziło o szybką i skuteczną weryfikację jak wygląda dany produkt w bazie produktów danej firmy.
Poniżej przedstawiam wariant uproszczony przygotowany w trzech krokach. Wszelkie modyfikacje i rozszerzenia są jak najbardziej możliwe i zalecane.
Krok 1. Tworzymy listę plików graficznych.
W tym celu w komórce A1 dowolnego arkusza proszę wprowadzić ścieżkę do katalogu, w którym znajdują się nasze pliki graficzne, np: c:\Users\Imię Naziwsko\Documents\Moje obrazy.
Następnie proszę wywołać poniższe makro, którego celem będzie utworzenie listy wszystkich plików graficznych JPG począwszy od rzędu 11 w kolumnie A.
Krok 2. Wstawianie i usuwanie grafiki.
Poniższe dwa makra będą odpowiedzialne za wyświetlenie oraz usunięcie pliku graficznego z naszego arkusza. Dodatkowe informacje na temat ich działania zawarte są w komentarzach wewnątrz kodu.
Krok 3. Automatyczne działanie z pomocą obsługi zdarzenia.
Na koniec połączymy nasze makra ze zdarzeniem. Chcemy bowiem aby wyświetlił się nam obraz tylko wtedy, gdy zaznaczymy pojedynczą komórkę w kolumnie zawierającej nazwę pliku, a w każdym z pozostałych przypadków chcemy usunąć zbędną grafikę. W tym celu w module naszego arkusza dodamy następujący kod obsługi zdarzenia Worksheet_SelectionChange:
Oczywiste pytanie, które również zadałem brzmiało- po co i dlaczego mielibyśmy tworzyć takie rozwiązanie? Jak się okazało, i tu będę bazował na podobnych elementach uzyskanych odpowiedzi, chodziło o szybką i skuteczną weryfikację jak wygląda dany produkt w bazie produktów danej firmy.
Poniżej przedstawiam wariant uproszczony przygotowany w trzech krokach. Wszelkie modyfikacje i rozszerzenia są jak najbardziej możliwe i zalecane.
Krok 1. Tworzymy listę plików graficznych.
W tym celu w komórce A1 dowolnego arkusza proszę wprowadzić ścieżkę do katalogu, w którym znajdują się nasze pliki graficzne, np: c:\Users\Imię Naziwsko\Documents\Moje obrazy.
Następnie proszę wywołać poniższe makro, którego celem będzie utworzenie listy wszystkich plików graficznych JPG począwszy od rzędu 11 w kolumnie A.
01 | Sub ListaPlikowKatalogu_All() |
02 |
03 | 'instrukcja działa dla katalogu określonego jako bieżący |
04 | ChDir ActiveSheet.Cells(1, 1) |
05 | |
06 | Dim TMP As String |
07 | Dim Wiersz As Integer |
08 | Wiersz = 11 |
09 | 'inicjacja kryterium nazwy- pliki JPG |
10 | Cells(Wiersz, 1) = Dir("*.jpg") |
11 | |
12 | Do |
13 | Wiersz = Wiersz + 1 |
14 | TMP = Dir() |
15 | Cells(Wiersz, 1) = TMP |
16 |
17 | Loop While TMP <> "" |
18 | |
19 | Cells(10, 1).Select |
20 | End Sub |
Krok 2. Wstawianie i usuwanie grafiki.
Poniższe dwa makra będą odpowiedzialne za wyświetlenie oraz usunięcie pliku graficznego z naszego arkusza. Dodatkowe informacje na temat ich działania zawarte są w komentarzach wewnątrz kodu.
01 | Sub Pokaz_Fote(Kolumna) |
02 | Dim Nazwa$, Sciezka$ |
03 | Sciezka = ActiveSheet.Cells(1, Kolumna) |
04 | Nazwa = Sciezka & "\" & ActiveCell.Value |
05 |
06 | 'kasujemy wszystkie kształty- |
07 | 'tu wywołujemy osobne makro |
08 | Kasuje_Wszystkie_Kształty |
09 | |
10 | 'wstawianie pliku graficznego i wyświetlenie go w określonej pozycji |
11 | 'uwaga! nie znamy proporcji grafiki a musimy podać parametry wymiarów |
12 | ActiveSheet.Shapes.AddPicture Nazwa, True, True, _ |
13 | ActiveCell.Offset(0, 1).Left, _ |
14 | ActiveCell.Offset(0, 1).Top, 1600, 1600 |
15 | |
16 | 'dlatego tu następuje dostosowanie wymiaru i proporcji |
17 | With ActiveSheet.Shapes(1) |
18 | .LockAspectRatio = msoTrue |
19 | .ScaleHeight 1, msoTrue, msoScaleFromTopLeft |
20 | .Width = 200 |
21 | End With |
22 |
23 | End Sub |
1 | Sub Kasuje_Wszystkie_Kształty() |
2 | Dim SHP As Shape |
3 | For Each SHP In ActiveSheet.Shapes |
4 | SHP.Delete |
5 | Next 'SHP |
6 | End Sub |
Krok 3. Automatyczne działanie z pomocą obsługi zdarzenia.
Na koniec połączymy nasze makra ze zdarzeniem. Chcemy bowiem aby wyświetlił się nam obraz tylko wtedy, gdy zaznaczymy pojedynczą komórkę w kolumnie zawierającej nazwę pliku, a w każdym z pozostałych przypadków chcemy usunąć zbędną grafikę. W tym celu w module naszego arkusza dodamy następujący kod obsługi zdarzenia Worksheet_SelectionChange:
01 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) |
02 | On Error GoTo ErrorHandler |
03 | |
04 | 'gdy wybrano potencjalną pozycję z listy plików graficznych |
05 | If Target.Row > 10 And Target.Cells.Count = 1 And _ |
06 | UCase(Right(Target.Value, 3)) = "JPG" Then |
07 | 'wywoływne jest makro wyświetlające kolejne wybierane pliki |
08 | Call Wstawianie_Zdjec.Pokaz_Fote(Target.Column) |
09 | |
10 | Else |
11 | Kasuje_Wszystkie_Kształty |
12 | End If |
13 | Exit Sub |
14 | ErrorHandler: |
15 | Debug.Print "Błąd obsługi zdarzenia o godzinie: " & Time |
16 | End Sub |
poniedziałek, 26 sierpnia 2013
Nawiasy mają znaczenie!- procedura parametryzowana
Myślę, że wielu programistów nie zdaje sobie sprawy z faktu, że wywołując zewnętrzną procedurę parametryzowaną Sub ma znaczenie to, czy parametry zostaną podane w nawiasie czy też nie. Chodzi o różnicę, którą obrazują następujące składnie wywołania:
Zanim wyjaśnię różnicę i przyczynę różnicy najlepiej będzie jeżeli spojrzymy na przykład:
Wywołanie procedury Foo sprawi, że najpierw otrzymamy w wyniku 'Hello', a następnie uzyskamy wartość zmiennej 'Hello World". Skąd ta różnica? Otóż ujęcie argumentów procedury w nawias (wywołanie 1 powyżej) zmienia typ parametru z ByRef na ByVal, to zaś sprawia, że zmienna przekazana do podprocedury `Boo` nie zmienia swojej wartości. Przekazanie argumentu bez nawiasu sprawia, że parametr jest typu ByRef a więc wszystkie operacje na nim wykonane w podprocedurze zostaną również przekazane do procedury nadrzędnej.
Podobną różnicę w zachowaniu i wyniku wywołania podprocedury znajdziemy korzystając z instrukcji Call. W tym przypadku dodatkowy nawias będzie zmieniał tryb przekazania parametru. Obrazują to następujące przykłady:
1 | ProceduraParametryzowana Argument |
2 | ProceduraParametryzowana (Argument) |
Zanim wyjaśnię różnicę i przyczynę różnicy najlepiej będzie jeżeli spojrzymy na przykład:
01 | Sub Foo() |
02 |
03 | Dim STR As String |
04 |
05 | STR = "Hello" |
06 |
07 | Boo (STR) 'wywołanie 1 |
08 | Debug.Print STR '>> Hello |
09 |
10 | Boo STR 'wywołanie 2 |
11 | Debug.Print STR '>> Hello World |
12 |
13 | End Sub |
1 | Sub Boo(ByRef PARAM As String) |
2 |
3 | PARAM = PARAM + " World" |
4 |
5 | End Sub |
Wywołanie procedury Foo sprawi, że najpierw otrzymamy w wyniku 'Hello', a następnie uzyskamy wartość zmiennej 'Hello World". Skąd ta różnica? Otóż ujęcie argumentów procedury w nawias (wywołanie 1 powyżej) zmienia typ parametru z ByRef na ByVal, to zaś sprawia, że zmienna przekazana do podprocedury `Boo` nie zmienia swojej wartości. Przekazanie argumentu bez nawiasu sprawia, że parametr jest typu ByRef a więc wszystkie operacje na nim wykonane w podprocedurze zostaną również przekazane do procedury nadrzędnej.
Podobną różnicę w zachowaniu i wyniku wywołania podprocedury znajdziemy korzystając z instrukcji Call. W tym przypadku dodatkowy nawias będzie zmieniał tryb przekazania parametru. Obrazują to następujące przykłady:
1 | Call Boo(STR) >> przekazanie ByRef >>Wynik Hello World |
2 | Call Boo((STR)) >> przekazanie ByVal >>Wynik Hello |
poniedziałek, 19 sierpnia 2013
MS Word- podmiana akcji przycisku na wstążce
Aplikacja MS Word udostępnia ciekawą i praktyczną opcję, która umożliwia zamianę standardowej akcji wywołanej dowolnym przyciskiem znajdującym się na wstążce na akcję własną użytkownika. Proces zamiany jest relatywnie łatwy do wykonania i zaprogramowania choć od razu muszę zaznaczyć rzecz ważną- podmiana ta dotyczyć będzie wskazanego szablonu lub dokumentu. Nie zmienia ona akcji danego przycisku dla wszystkich dokumentów. Tak czy inaczej rozwiązanie to znajduje szereg praktycznych i przydatnych zastosowań. Żałuję też jednocześnie, że podobnych rozwiązań nie da się zrealizować we wszystkich aplikacjach MS Office.
Aby osiągnąć cel opisany w powyższym wprowadzeniu musimy wykonać kilka kroków i operacji. Całość zaprezentuję na bazie aktualizacji działania przycisku Wersja Robocza w zakładce Widok aplikacji MS Word 2010 (przycisk zaznaczony na poniższym zrzucie ekranu).
Krok 1. Otwieramy dokument, w którym chcemy zapisać zindywidualizowane zachowanie przycisku.
Krok 2. Przechodzimy do zakładki i wywołujemy polecenie: Widok >> Makra >> Wyświetl makra.
Krok 3. W wyświetlonym oknie Makra, w jego środkowej części z listy rozwijalnej Makra w: wybieramy opcję: Polecenia programu Word.
Krok 4. Chyba najtrudniejszy z etapów- na otrzymanej liście makr musimy odnaleźć i zaznaczyć to makro, które (wydaje się nam) powiązane jest z danym przyciskiem. Czym się kierować w poszukiwaniach- intuicją i logiką opartą o nazwy zakładek, nazwę grupy poleceń czy wreszcie nazwę naszego przycisku, który modyfikujemy.
W naszym przykładzie od razu trafimy na pewną trudność- otóż przycisk Wersja robocza nie będzie reprezentowany przez makro ViewDraft lecz makro ViewNormal. Aby się przekonać czy dokonaliśmy właściwego wyboru możliwe, że będziemy musieli wykonać i powtórzyć kilka kolejnych kroków.
Proszę pamiętać, aby zaznaczyć wybrane makro i nie zmieniać tego zaznaczenia!
Krok 5. W naszym oknie Makra na liście Makra w: dokonujemy ponownej zmiany- tym razem wskazujemy tam plik Worda, z którym chcemy powiązać indywidualne ustawienia przycisku.
Wskazówka! jeżeli w tym kroku zamiast pliku wskażemy odpowiedni szablon, np. Normal.Dotm, to nasza zmiana obsługi przycisku powiązana zostanie z tym szablonem a w konsekwencji ze wszystkimi dokumentami, które na bazie szablonu powstaną.
Krok 6. Klikamy w przycisk Utwórz w wyniku czego zostaniemy przeniesieni do edytora VBA w obszar utworzonego makra (proszę zwrócić uwagę na lokalizację kodu- znajduje się on w module w naszym dokumencie). W tym konkretnym przypadku makro powinno mieć następującą postać:
Krok 7. W tym momencie każda modyfikacja, która zostanie dokonana w otrzymanym makrze zostanie jednocześnie powiązana z przyciskiem 'Wersja robocza'. Przekonajmy się o tym dodając do naszego kodu proste polecenie:
A następnie proszę przejść do aplikacji MS Word i wcisnąć przycisk, którego kod poddaliśmy modyfikacji.
Krok 8. Proszę pamiętać o zapisaniu naszego pliku w wariancie z obsługą makr, a więc rozszerzeniem DOCM.
Aby osiągnąć cel opisany w powyższym wprowadzeniu musimy wykonać kilka kroków i operacji. Całość zaprezentuję na bazie aktualizacji działania przycisku Wersja Robocza w zakładce Widok aplikacji MS Word 2010 (przycisk zaznaczony na poniższym zrzucie ekranu).
Krok 1. Otwieramy dokument, w którym chcemy zapisać zindywidualizowane zachowanie przycisku.
Krok 2. Przechodzimy do zakładki i wywołujemy polecenie: Widok >> Makra >> Wyświetl makra.
Krok 3. W wyświetlonym oknie Makra, w jego środkowej części z listy rozwijalnej Makra w: wybieramy opcję: Polecenia programu Word.
Krok 4. Chyba najtrudniejszy z etapów- na otrzymanej liście makr musimy odnaleźć i zaznaczyć to makro, które (wydaje się nam) powiązane jest z danym przyciskiem. Czym się kierować w poszukiwaniach- intuicją i logiką opartą o nazwy zakładek, nazwę grupy poleceń czy wreszcie nazwę naszego przycisku, który modyfikujemy.
W naszym przykładzie od razu trafimy na pewną trudność- otóż przycisk Wersja robocza nie będzie reprezentowany przez makro ViewDraft lecz makro ViewNormal. Aby się przekonać czy dokonaliśmy właściwego wyboru możliwe, że będziemy musieli wykonać i powtórzyć kilka kolejnych kroków.
Proszę pamiętać, aby zaznaczyć wybrane makro i nie zmieniać tego zaznaczenia!
Krok 5. W naszym oknie Makra na liście Makra w: dokonujemy ponownej zmiany- tym razem wskazujemy tam plik Worda, z którym chcemy powiązać indywidualne ustawienia przycisku.
Wskazówka! jeżeli w tym kroku zamiast pliku wskażemy odpowiedni szablon, np. Normal.Dotm, to nasza zmiana obsługi przycisku powiązana zostanie z tym szablonem a w konsekwencji ze wszystkimi dokumentami, które na bazie szablonu powstaną.
Krok 6. Klikamy w przycisk Utwórz w wyniku czego zostaniemy przeniesieni do edytora VBA w obszar utworzonego makra (proszę zwrócić uwagę na lokalizację kodu- znajduje się on w module w naszym dokumencie). W tym konkretnym przypadku makro powinno mieć następującą postać:
01 | Sub ViewNormal() |
02 | ' |
03 | ' ViewNormal Makro |
04 | ' Zmienia widok edycji na normalny |
05 | ' |
06 | If ActiveWindow.View.SplitSpecial = wdPaneNone Then |
07 | ActiveWindow.ActivePane.View.Type = wdNormalView |
08 | Else |
09 | ActiveWindow.View.Type = wdNormalView |
10 |
11 | End If |
12 |
13 | End Sub |
Krok 7. W tym momencie każda modyfikacja, która zostanie dokonana w otrzymanym makrze zostanie jednocześnie powiązana z przyciskiem 'Wersja robocza'. Przekonajmy się o tym dodając do naszego kodu proste polecenie:
1 | MsgBox "Dokonano zmiany działania przycisku" |
A następnie proszę przejść do aplikacji MS Word i wcisnąć przycisk, którego kod poddaliśmy modyfikacji.
Krok 8. Proszę pamiętać o zapisaniu naszego pliku w wariancie z obsługą makr, a więc rozszerzeniem DOCM.
poniedziałek, 12 sierpnia 2013
Zapisanie zakresu komórek arkusza w postaci pliku JPG
Niniejsze rozwiązanie znane jest wielu praktykom VBA, szczególnie tym osobom, które wysyłają fragmenty arkusza w postaci grafiki wstawionej w wiadomości e-mail.
Jak wyglądać będzie makro, którego zadaniem będzie zapisanie utworzenie pliku graficznego JPG prezentującego fragment obszaru arkusza? Pełne rozwiązanie poniżej. W tym wypadku wszystkie dodatkowe komentarze zostały umieszczone poniżej.
I jeszcze dwa obrazy graficzne na koniec- zrzut ekranu z naszą tabelą, która została poddana procesowi exportu oraz plik, który powstał w wyniku działania powyższego makra.
Jak wyglądać będzie makro, którego zadaniem będzie zapisanie utworzenie pliku graficznego JPG prezentującego fragment obszaru arkusza? Pełne rozwiązanie poniżej. W tym wypadku wszystkie dodatkowe komentarze zostały umieszczone poniżej.
01 | Sub SaveRangeAsJPG() |
02 |
03 | Dim SHT As Worksheet |
04 | Dim RNG As Range |
05 |
06 | Application.ScreenUpdating = False |
07 | |
08 | 'określamy arkusz z naszym obszare |
09 | Set SHT = Sheets("Arkusz1") |
10 |
11 | 'określamy zakres, który będziemy eksportować... |
12 | Set RNG = SHT.Range("A1:D5") |
13 | '... kopiujemy ten obszar |
14 | RNG.CopyPicture Appearance:=xlScreen, Format:=xlPicture |
15 |
16 | 'cały trick kryje się w następujących krokach: |
17 | '1. tworzymy wykres- proszę zwrócić uwagę na _ |
18 | szerokość i wysokość wykresu- taki zapis pozawala _ |
19 | zachować proporcje naszego obszaru |
20 | With SHT.ChartObjects.Add(Left:=100, Top:=100, _ |
21 | Width:=RNG.Width, Height:=RNG.Height) |
22 | With .Chart |
23 | 'wklejamy w wykres nasze komórki |
24 | .Paste |
25 | |
26 | 'wykorzystujemy metodę export wykresu |
27 | .Export ThisWorkbook.Path & "\TabelaExport.jpg" |
28 | End With |
29 | |
30 | 'dla porządku usuwamy wykres- nie jest nam już potrzebny |
31 | .Delete |
32 |
33 | End With |
34 |
35 | End Sub |
I jeszcze dwa obrazy graficzne na koniec- zrzut ekranu z naszą tabelą, która została poddana procesowi exportu oraz plik, który powstał w wyniku działania powyższego makra.
poniedziałek, 5 sierpnia 2013
Testowanie zgodności ciągów tekstowych- wyrażenia regularne RegExp- 4/4
W ostatnim wpisie dot. wyrażeń regularnych RegExp chciałbym zwrócić uwagę metodę .Execute, którą wykorzystałem poprzednio w celu odnalezienia i pobrania adresu e-mail z podanego ciągu tekstowego.
Metoda .Execute zwraca kolekcję Matches Collection, która zawiera wszystkie wystąpienia ciągów tekstowych spełniających określone kryteria. W tej sytuacji w relatywnie łatwy sposób możemy pobrać elementy odpowiadające naszemu wzorcowi.
Prześledźmy to na jednym tylko przykładzie, którego zadaniem będzie pobranie wszystkich adresów e-mail z podanego ciągu tekstowego. W zakresie dalszego zrozumienia zastosowania kolekcji Matches odsyłam do komentarzy w poniższym kodzie.
Metoda .Execute zwraca kolekcję Matches Collection, która zawiera wszystkie wystąpienia ciągów tekstowych spełniających określone kryteria. W tej sytuacji w relatywnie łatwy sposób możemy pobrać elementy odpowiadające naszemu wzorcowi.
Prześledźmy to na jednym tylko przykładzie, którego zadaniem będzie pobranie wszystkich adresów e-mail z podanego ciągu tekstowego. W zakresie dalszego zrozumienia zastosowania kolekcji Matches odsyłam do komentarzy w poniższym kodzie.
01 | Sub RegExp_pobranie_wybranych() |
02 |
03 | 'proszę pamiętać o referencji do biblioteki RegExp |
04 | Dim objRE As New RegExp |
05 | Dim Tekst As String |
06 | Dim Wynik As Variant |
07 | |
08 | 'nasz przeszukiwany tekst- zawiera 3 e-maile w różnych zapisach |
09 | Tekst = "Tekst zawiera e-maile adres.email@domena.com.pl w treści. " & _ |
10 | "E-maile jan.kowalski@domena.pl są na różnych pozycjach. " & _ |
11 | "regexp_cool@domena.com oraz posiadają różną konstrukcję." |
12 | |
13 | With objRE |
14 | .Global = True |
15 | .Pattern = _ |
16 | "([A-Za-z0-9_\.-]+)@([A-Za-z0-9_\.-]+[A-Za-z0-9_][A-Za-z0-9_])" |
17 | If .test(Tekst) Then |
18 | 'Ważne! nasz wynik jest obiektem typu Match, |
19 | 'wymaga użycia instrukcji Set! |
20 | Set Wynik = .Execute(Tekst) |
21 | End If |
22 | End With |
23 | |
24 | 'zwracamy wynik- wszystkie pobrane e-maile |
25 | Dim EMail As Match |
26 | For Each EMail In Wynik |
27 | Debug.Print EMail.Value |
28 | Next |
29 | 'inny sposób na zwrócenie odnalezionych e-maili |
30 | Debug.Print Wynik(0).Value |
31 | Debug.Print Wynik(1).Value |
32 | Debug.Print Wynik(2).Value |
33 | |
34 | 'dodatkowo możemy pobrać także inne parametry |
35 | 'odnalezionych fragmentów tekstu |
36 | |
37 | For Each EMail In Wynik |
38 | 'adres e-mail, pozycja w tekście, długość tekstu e-maila |
39 | Debug.Print EMail.Value, EMail.FirstIndex, EMail.Length |
40 | Next |
41 | End Sub |
środa, 31 lipca 2013
Testowanie zgodności ciągów tekstowych- wyrażenia regularne RegExp- 3/4
Zanim uruchomimy pierwszą pełną procedurę z wykorzystaniem techniki wyrażeń regularnych (RegExp) przyjrzyjmy się dodatkowo właściwościom obiektu RegExp
.Pattern pozwoli nam zdefiniować wzorzec porównawczy,
.Global określa, czy wzorzec dotyczy pierwszego (= False),
czy wszystkich wystąpień w tekście (= True),
.IgnoreCase określa sposób testowania wyrażenia ze względu na wielkość liter (domyślnie =False,
wyszukiwanie binarne uwzględniające wielkość liter),
oraz metodom obiektu Regexp:
.Test zwraca informację czy można dopasować wyrażenie do ciągu tekstowego,
.Replace dokonuje zamiany fragmentu wzorcowego na nowy wskazany ,
.Execute wykonuje operację zwracając obiekt Match z kolekcji MatchCollection.
W dużym skrócie podsumowując powyższe elementy obiektu RegExp określę to w sposób następujący- pozwalają one na wskazanie wzorca a następnie dokonanie zamiany fragmentu tekstu na inny lub pobranie poszczególnych elementów w całości lub dla pierwszego wystąpienia.
Czas na zebranie całości zagadnienia w konkretne rozwiązania i prezentację przykładowych pełnych konstrukcji kodu VBA. Wykonując poniższe operacje w środowisku VBA proszę nie zapomnieć o Kroku 1, a więc ustanowieniu referencji do biblioteki RegExp.
Przykład 1. zamiana wyrazów w ciągu tekstowy, wyraz 'jest' na 'był'
Przykład 2. z podanego ciągu tekstowego pozostawić tylko cyfry
Przykład 3. z podanego ciągu tekstowego usunąć wszystkie liczby
Przykład 4. W przykładzie nr 2 dodatkowo pozostały podwójne spacje oraz zbędna spacja przed kropką i wykrzyknikiem. To wszystko także chcemy usunąć i wyprostować. W tej sytuacji utworzymy kaskadowy mechanizm RegExp:
Przykład 5. W powyższym przykładzie zastosowana została technika zamiany kolejności wyrazów. Jest to jedna z wielu dodatkowych korzyści pracy z RegExp. Poniżej prosty przykład, który zamienia kolejność 3 pierwszych wyrazów podanego tekstu:
Przykład 6. Z podanego tekstu chcemy pobrać tylko adres e-mai. W tym celu wykorzystamy jeden z wielu możliwych wzorców odpowiadających adresowi e-mail i stworzymy następujący kod:
Powyższy przykład zamiast metody .Replace wykorzystuje metodę .Execute. Ale o tym napiszę już w kolejnym wpisie za kilka dni...
Osobom, które są zainteresowane wyrażeniami regularnymi polecam poniższe linki, które pozwolą na poszerzenie i ugruntowanie informacji przedstawionych w ostatnich postach. Linki te poświęcone są ogólni RegExp i niekoniecznie wskazują na bezpośrednie wykorzystanie tej techniki w VBA.
Korzystanie z wyrażeń regularnych w programie Microsoft Visual Basic 6.0Regular-Expressions.Info
.NET Framework Regular Expressions
RegExp Help
Regular-Expressions.info
Introduction to Regular Expressions
.Pattern pozwoli nam zdefiniować wzorzec porównawczy,
.Global określa, czy wzorzec dotyczy pierwszego (= False),
czy wszystkich wystąpień w tekście (= True),
.IgnoreCase określa sposób testowania wyrażenia ze względu na wielkość liter (domyślnie =False,
wyszukiwanie binarne uwzględniające wielkość liter),
oraz metodom obiektu Regexp:
.Test zwraca informację czy można dopasować wyrażenie do ciągu tekstowego,
.Replace dokonuje zamiany fragmentu wzorcowego na nowy wskazany ,
.Execute wykonuje operację zwracając obiekt Match z kolekcji MatchCollection.
W dużym skrócie podsumowując powyższe elementy obiektu RegExp określę to w sposób następujący- pozwalają one na wskazanie wzorca a następnie dokonanie zamiany fragmentu tekstu na inny lub pobranie poszczególnych elementów w całości lub dla pierwszego wystąpienia.
Czas na zebranie całości zagadnienia w konkretne rozwiązania i prezentację przykładowych pełnych konstrukcji kodu VBA. Wykonując poniższe operacje w środowisku VBA proszę nie zapomnieć o Kroku 1, a więc ustanowieniu referencji do biblioteki RegExp.
Przykład 1. zamiana wyrazów w ciągu tekstowy, wyraz 'jest' na 'był'
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "To jest 123 tekst 45! 6 Zawiera też 789 liczby 0." |
06 | With objRE |
07 | .IgnoreCase = True |
08 | .Global = True |
09 | .Pattern = "jest" |
10 | Wynik = .Replace(Tekst, "był") |
11 | End With |
12 | Debug.Print Wynik '>> To był 123 tekst 45! 6 Zawiera też 789 liczby 0. |
Przykład 2. z podanego ciągu tekstowego pozostawić tylko cyfry
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "To jest 123 tekst 45! 6 Zawiera też 789 liczby 0." |
06 | With objRE |
07 | .Global = True |
08 | .Pattern = "[a-żA-Ż!?.]*\s*" |
09 | Wynik = .Replace(Tekst, vbNullString) |
10 | End With |
11 | Debug.Print Wynik '>> 1234567890 |
Przykład 3. z podanego ciągu tekstowego usunąć wszystkie liczby
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "To jest 123 tekst 45! 6 Zawiera też 789 liczby 0." |
06 | With objRE |
07 | .Global = True |
08 | .Pattern = "\d" |
09 | Wynik = .Replace(Tekst, vbNullString) |
10 | End With |
11 | Debug.Print Wynik '>> To jest tekst ! Zawiera też liczby |
Przykład 4. W przykładzie nr 2 dodatkowo pozostały podwójne spacje oraz zbędna spacja przed kropką i wykrzyknikiem. To wszystko także chcemy usunąć i wyprostować. W tej sytuacji utworzymy kaskadowy mechanizm RegExp:
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "To jest 123 tekst 45! 6 Zawiera też 789 liczby 0." |
06 | With objRE |
07 | .Global = True |
08 | 'usuwamy liczby |
09 | .Pattern = "\d" |
10 | Wynik = .Replace(Tekst, vbNullString) |
11 | 'usuwamy spacje podwójne |
12 | .Pattern = "\s{2,}" |
13 | Wynik = .Replace(Wynik, " ") |
14 | 'usuwamy spację przed znakami specjalnymi... |
15 | .Pattern = "(\s)([.!?])" |
16 | '...stosując technikę zamiany |
17 | Wynik = .Replace(Wynik, "$2") |
18 | End With |
19 | Debug.Print Wynik '>>To jest tekst! Zawiera też liczby. |
Przykład 5. W powyższym przykładzie zastosowana została technika zamiany kolejności wyrazów. Jest to jedna z wielu dodatkowych korzyści pracy z RegExp. Poniżej prosty przykład, który zamienia kolejność 3 pierwszych wyrazów podanego tekstu:
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "To jest 123 tekst 45! 6 Zawiera też 789 liczby 0." |
06 | With objRE |
07 | 'ważne ustawienie dot. właściwości Global |
08 | .Global = False |
09 | .IgnoreCase = True |
10 | .Pattern = "(\S+)(\s+)(\S+)(\s+)(\S+)*" |
11 | Wynik = .Replace(Tekst, "$5$4$3$2$1") |
12 | End With |
13 | Debug.Print Wynik '>>123 jest To tekst 45! 6 Zawiera też 789 liczby 0. |
Przykład 6. Z podanego tekstu chcemy pobrać tylko adres e-mai. W tym celu wykorzystamy jeden z wielu możliwych wzorców odpowiadających adresowi e-mail i stworzymy następujący kod:
01 | Dim objRE As New RegExp |
02 | Dim Tekst As String |
03 | Dim Wynik As Variant |
04 |
05 | Tekst = "Ten tekst zawiera email gdzieś adres.email@domena.com.pl w swojej treści." |
06 | With objRE |
07 | .Global = True |
08 | .Pattern = _ |
09 | "([A-Za-z0-9_\.-]+)@([A-Za-z0-9_\.-]+[A-Za-z0-9_][A-Za-z0-9_])" |
10 | If .test(Tekst) Then |
11 | Wynik = .Execute(Tekst)(0).Value |
12 | End If |
13 | End With |
14 | Debug.Print Wynik |
Powyższy przykład zamiast metody .Replace wykorzystuje metodę .Execute. Ale o tym napiszę już w kolejnym wpisie za kilka dni...
Osobom, które są zainteresowane wyrażeniami regularnymi polecam poniższe linki, które pozwolą na poszerzenie i ugruntowanie informacji przedstawionych w ostatnich postach. Linki te poświęcone są ogólni RegExp i niekoniecznie wskazują na bezpośrednie wykorzystanie tej techniki w VBA.
Korzystanie z wyrażeń regularnych w programie Microsoft Visual Basic 6.0Regular-Expressions.Info
.NET Framework Regular Expressions
RegExp Help
Regular-Expressions.info
Introduction to Regular Expressions
poniedziałek, 22 lipca 2013
Testowanie zgodności ciągów tekstowych- wyrażenia regularne RegExp- 2/4
Kontynuując temat tworzenia wyrażeń regularnych RegExp omówię najważniejszą umiejętność związaną z tą techniką, a więc budowanie wzorców porównawczych.
Krok 2.
To najtrudniejsza część pracy z RegExp- budowa składni tworzącej kryteria. Spojrzymy na to zadanie najpierw przez pryzmat wybranych znaków (kodów) specjalnych, następnie przedstawię proste przykłady, a na koniec zaprezentuję kilka złożonych wzorców.
A) Znaki i symbole specjalne
\d cyfra
\D nie cyfra
\s spacja
\S nie spacja
\w znak alfanumeryczny, litera
\W nie znak alfanumeryczny
\b granica słowa, pozycja między słowem a spacją
\n znak nowej linii (vbCrLf, Chr(10), Chr(13), vbNewLine)
\r znak nowej linii (Chr(13))
\t znak tabulacji (vbTab)
. (kropka) dowolny znak z wyjątkiem znaku nowej linii
* powtórzenie symbolu dowolną ilość lub zero razy
? powtórzenie symbolu zero lub jeden raz
+ powtórzenie poprzedniego wyrażenia co najmniej raz
\ używany jako znak wyjścia dla znaków specjalnych
^ na początku tekstu
także symbol zaprzeczenia(patrz poniżej)
$ na końcu tekstu
[] przedział znaków
[A-Z] dowolna wielka litera
[a-z] dowolna mała litera
[0-9] dowolna cyfra
[^0-9] nie zawiera cyfry
{} wskazuje ilość dopuszczalnych powtórzeń:
a{1} jednokrotne powtórzenie litery a
a{0,2} zero do dwóch powtórzeń litery a
() grupowanie
| alternatywa dla grupowania
Proszę jednocześnie pamiętać, że dla prostych wyrażeń bez udziału powtórzeń i znaków specjalnych zastosujemy technikę podobną do tej opisanej przy pracy z operatorem Like w jednym z wcześniejszych postów. RegExp jest także, co do zasady, wrażliwy na wielkość liter
Spójrzmy na przykłady:
RegExp Pattern Dopasowanie Wyjaśnienie (opcjonalne)
(czerwonym-dopasowanie)
abc abcabcabc
234 1234567
Jan Jan
Janek
janek wielkość liter ma znaczenie
ab*c acde
abcde
abbbbcde
ab?c acde
abcde
abbbcde brak dopasowania
ab+c acde brak dopasowania
abcde
abbbcde
ab.d abcd
ab1d
abc1d brak dopasowania
a.*d ad
abcd
asfas13wbd
\d\d 10
99
100
9999 dwa podwójne układy cyfr
\w+ alfa dowolny, każdy wyraz
Witaj Jan!
\w+@\w+ a@a uproszczony schemat dla e-maila
email@domena.pl
\w+@\w+.\w+ email@domena.pl
jan.kowalski@domena.pl
^ab.*c abc
ab123fdcdef
babc tekst nie zaczyna się od a
^ab.*c$ abc
ab123c
ab123cd nie kończy się na c
ab123cdc powtórzenie c nie ma znaczenia
bacdc nie zaczyna się od a
^\d 1abc zaczyna się od cyfry
\d$ abc1 kończy się cyfrą
[abc] abc znaki wskazane w nawiasie
abcdef
abcdefabc
[^abc] abcdefabc nie a, nie b, nie c
[A-Z] abcDEF wielkie litery
ABCDEF
abcdef
^[A-Z].+ Abc123d zaczyna się od wielkiej litery
abc123d
[+*?.] ..??**++ nawiasy likwidują znaczenie znaków specjalnych
^[1-9][0-9]*$ 1 liczba całkowita większa od zera
1000
-1000
123.45 tylko liczba całkowita
(ab)+ ab
ababab
bababa
(ab|ba)+ ab
ba
ababab rozpozna jako jeden ciąg
bacbacab rozpozna trzy ciągi
abcdef
(abc)? abc ale także dla pustego ciągu
a(\d+)c a123c zawiera cyfry pomiędzy a i c
a1c
ac
abc
a{2} alfaabeta
a{1,2} alfaabeta
Inne gotowe przykłady z tego źródła:
A) weryfikacja, czy podano prawidłowy e-mail:
^[A-Za-z0-9_\.-]+@[A-Za-z0-9_\.-]+[A-Za-z0-9_][A-Za-z0-9_]$
Wskazówka! w sieci znaleźć można wiele przykładów weryfikacji adresów e-mail, kolejny z nich podam w trzeciej lekcji poświęconej RegExp. Zainteresowanych tym zagadnieniem odsyłam do tej dyskusji.
B) weryfikacja zapisu daty w układzie: yyyy-MMM -dd, gdzie MMM to trzyliterowy skrót zapisany z wielkiej litery:
^\d\d\d\d-[A-Z][a-z][a-z]-\d\d$
C) weryfikacja daty jak powyżej rozszerzona, sprawdzamy, czy miesiąc podany w języku polskim lub angielskim:
^20\d\d-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-(0[1-9]|[1-2][0-9]|3[01])$
^20\d\d-(Sty|Lut|Mar|Kwi|Maj|Cze|Lip|Sie|Wrz|Paź|Lis|Gru)-(0[1-9]|[1-2][0-9]|3[01])$
Krok 2.
To najtrudniejsza część pracy z RegExp- budowa składni tworzącej kryteria. Spojrzymy na to zadanie najpierw przez pryzmat wybranych znaków (kodów) specjalnych, następnie przedstawię proste przykłady, a na koniec zaprezentuję kilka złożonych wzorców.
A) Znaki i symbole specjalne
\d cyfra
\D nie cyfra
\s spacja
\S nie spacja
\w znak alfanumeryczny, litera
\W nie znak alfanumeryczny
\b granica słowa, pozycja między słowem a spacją
\n znak nowej linii (vbCrLf, Chr(10), Chr(13), vbNewLine)
\r znak nowej linii (Chr(13))
\t znak tabulacji (vbTab)
. (kropka) dowolny znak z wyjątkiem znaku nowej linii
* powtórzenie symbolu dowolną ilość lub zero razy
? powtórzenie symbolu zero lub jeden raz
+ powtórzenie poprzedniego wyrażenia co najmniej raz
\ używany jako znak wyjścia dla znaków specjalnych
^ na początku tekstu
także symbol zaprzeczenia(patrz poniżej)
$ na końcu tekstu
[] przedział znaków
[A-Z] dowolna wielka litera
[a-z] dowolna mała litera
[0-9] dowolna cyfra
[^0-9] nie zawiera cyfry
{} wskazuje ilość dopuszczalnych powtórzeń:
a{1} jednokrotne powtórzenie litery a
a{0,2} zero do dwóch powtórzeń litery a
() grupowanie
| alternatywa dla grupowania
Proszę jednocześnie pamiętać, że dla prostych wyrażeń bez udziału powtórzeń i znaków specjalnych zastosujemy technikę podobną do tej opisanej przy pracy z operatorem Like w jednym z wcześniejszych postów. RegExp jest także, co do zasady, wrażliwy na wielkość liter
Spójrzmy na przykłady:
RegExp Pattern Dopasowanie Wyjaśnienie (opcjonalne)
(czerwonym-dopasowanie)
abc abcabcabc
234 1234567
Jan Jan
Janek
janek wielkość liter ma znaczenie
ab*c acde
abcde
abbbbcde
ab?c acde
abcde
abbbcde brak dopasowania
ab+c acde brak dopasowania
abcde
abbbcde
ab.d abcd
ab1d
abc1d brak dopasowania
a.*d ad
abcd
asfas13wbd
\d\d 10
99
100
9999 dwa podwójne układy cyfr
\w+ alfa dowolny, każdy wyraz
Witaj Jan!
\w+@\w+ a@a uproszczony schemat dla e-maila
email@domena.pl
\w+@\w+.\w+ email@domena.pl
jan.kowalski@domena.pl
^ab.*c abc
ab123fdcdef
babc tekst nie zaczyna się od a
^ab.*c$ abc
ab123c
ab123cd nie kończy się na c
ab123cdc powtórzenie c nie ma znaczenia
bacdc nie zaczyna się od a
^\d 1abc zaczyna się od cyfry
\d$ abc1 kończy się cyfrą
[abc] abc znaki wskazane w nawiasie
abcdef
abcdefabc
[^abc] abcdefabc nie a, nie b, nie c
[A-Z] abcDEF wielkie litery
ABCDEF
abcdef
^[A-Z].+ Abc123d zaczyna się od wielkiej litery
abc123d
[+*?.] ..??**++ nawiasy likwidują znaczenie znaków specjalnych
^[1-9][0-9]*$ 1 liczba całkowita większa od zera
1000
-1000
123.45 tylko liczba całkowita
(ab)+ ab
ababab
bababa
(ab|ba)+ ab
ba
ababab rozpozna jako jeden ciąg
bacbacab rozpozna trzy ciągi
abcdef
(abc)? abc ale także dla pustego ciągu
a(\d+)c a123c zawiera cyfry pomiędzy a i c
a1c
ac
abc
a{2} alfaabeta
a{1,2} alfaabeta
Inne gotowe przykłady z tego źródła:
A) weryfikacja, czy podano prawidłowy e-mail:
^[A-Za-z0-9_\.-]+@[A-Za-z0-9_\.-]+[A-Za-z0-9_][A-Za-z0-9_]$
Wskazówka! w sieci znaleźć można wiele przykładów weryfikacji adresów e-mail, kolejny z nich podam w trzeciej lekcji poświęconej RegExp. Zainteresowanych tym zagadnieniem odsyłam do tej dyskusji.
B) weryfikacja zapisu daty w układzie: yyyy-MMM -dd, gdzie MMM to trzyliterowy skrót zapisany z wielkiej litery:
^\d\d\d\d-[A-Z][a-z][a-z]-\d\d$
C) weryfikacja daty jak powyżej rozszerzona, sprawdzamy, czy miesiąc podany w języku polskim lub angielskim:
^20\d\d-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-(0[1-9]|[1-2][0-9]|3[01])$
^20\d\d-(Sty|Lut|Mar|Kwi|Maj|Cze|Lip|Sie|Wrz|Paź|Lis|Gru)-(0[1-9]|[1-2][0-9]|3[01])$
Subskrybuj:
Posty (Atom)