Funkcje arkuszowe w pracy z tablicami Array (2/2)

Kontynuując zagadnienie dot. funkcji arkuszowych w pracy z tablicami chciałbym wspomnieć jeszcze o funkcji Match (PODAJ.POZYCJĘ). Funkcja ta zwraca informację, na której pozycji znajduje się szukany element w jednowymiarowej tablicy danych. Ogólna konstrukcja funkcji jest następująca


Wykorzystując tablicę danych z kursami NBP z poprzedniego postu oraz przyjmując identyczny mechanizm działania procedury poniższy kod zwróci numer wiersza, w którym znajduje się szukany symbol waluty.


Okno wynikowe dla waluty EUR.

I na koniec ważna wskazówka. Kombinacja funkcji Index oraz Match w praktyce pozwalają na stworzenie innej wersji funkcji VLookup (WYSZUKAJ.PIONOWO).  W naszym przykładzie, chcąc uzyskać kurs waluty dla wskazanego symbolu z wykorzystaniem takiej kombinacji należałoby wewnątrz procedury zastosować poniższą instrukcję. A wynik jej działania zaprezentowano w kolejnym oknie MsgBox.

Funkcje arkuszowe w pracy z tablicami Array (1/2)

Wśród dotychczasowych wpisów poświęconych tablicom Array znaleźć można kilka uwzględniających kluczowe techniki pracy z tablicami, w tym m.in:

tworzenie tablicy z wybranego wiersza innej tablicy z pomocą funkcji Index,  szereg przykładów na wykorzystanie funkcji transpozycji (Transpose), wyszukiwanie unikatowych wartości z wykorzystaniem słownika (Dictionary) i kolekcji (Collection).

Są jeszcze dwie funkcje arkuszowe, które mają swoje szczególne miejsce w pracy z tablicami Array- funkcja VLookup (WYSZUKAJ.PIONOWO) oraz Match (PODAJ.POZYCJĘ), które zaprezentuję w tym i kolejnym poście.

Krótko przypomnę zasadę działania pierwszej z funkcji- VLookup (WYSZUKAJ.PIONOWO)- zwraca wartość pochodzącą z n-tej kolumny tablicy gdy szukana wartość zostanie odnaleziona w określonym wierszu pierwszej kolumny tej tablicy. Schematyczny zapis funkcji to:


Wywołując powyższą funkcję w arkuszu Excel w miejsce tablicy wstawiamy odwołanie do zakresu komórek. W języku VBA w miejscu Tablicy pojawi się zmienna reprezentująca tablicę.

Spójrzmy na konkretny przykład z danymi reprezentującymi kursy walut NBP.

Dysponując powyższymi danymi spróbujmy wywołać poniższą procedurę.


W momencie wywołania musimy wskazać kolejno indeks waluty (zgodny z kolumną A) oraz w kolejnym kroku zaznaczyć obszar danych zaczynający się w A1. Wpisując w pierwszym kroku EUR w wyniku otrzymamy poniższe okno z szukaną wartością.

Funkcja zwracająca symbol kolumny

Mam czasem wrażenie, że to pytanie jest dość powszechne w pracy z Excelem- jak zwrócić symbol określonej kolumny, a konkretnie jej literowy indeks, czyli A, B, Z, AA, AAA, itp? No cóż, w zasobie standardowych funkcji arkuszowych nie dysponujemy odpowiednią formułą, która może wykonać dla nas to zadanie. Dlatego też stworzymy prostą funkcję, która uzupełni ten brak. A właściwie stworzymy dwie proste funkcje, które wykorzystają dwa różne podejścia do zagadnienia.

Wariant 1. Funkcja  zwraca literowy symbol kolumny dla komórki, w której została umieszczona
W rozwiązaniu tym kluczowe będzie wykorzystanie właściwości Application.ThisCell, która to właściwość dostępna jest tylko z poziomu funkcji własnych użytkownika. Jej rolą jest zwrócenie obiektu Range odwołującego się do komórki, do której została wstawiona sama funkcja.
W obu rozwiązaniach sięgniemy natomiast po funkcję Split, która tworzy tablicę z ciągu tekstowego.
Oto pełny kod naszej pierwszej funkcji:


Powyższa funkcja wstawiona do komórki C20 zwróci w wyniku C, w AA10 - AA, w XYZ14 zwróci XYZ.

Wariant 2. Funkcja zwraca literowy symbol kolumny, której numer został wskazany w formie parametru funkcji
W tym przykładzie kluczem będzie stworzenie wirtualnego odwołania do kolumny, której numer jest parametrem funkcji. Następnie dla takiej kolumny odczytamy adres, a dalsze działania są praktycznie zbieżne z wariantem 1. Tak wyglądać więc będzie kolejna z naszych funkcji:


Funkcję tą możemy wywołać w Excelu na kilka sposobów:

Możemy także wykorzystać w formie parametru inną funkcję arkuszową: NR.KOLUMNY:

Powyższe wywołanie będzie tożsame z wywołaniem funkcji z wariantu 1- w komórce, w której wstawimy połączone funkcje zwrócony zostanie symbol kolumny dla tej właśnie komórki, a więc dla komórki C20 otrzymamy C, dla AA10 - AA, a dla XYZ14- XYZ.

Wskazówka! Oba rozwiązania można połączyć w jedną uniwersalną funkcję z opcjonalnym parametrem. Techniki tej uczymy w czasie prowadzonych szkoleń z zakresu VBA dla Excela, szczególnie prowadząc kurs VBA dla analityków i finansistów.

Nawigacja wewnątrz procedury a stary poczciwy BASIC

Powinienem zacząć od tego, że część prezentowanych poniżej technik należy do grupy tych niezalecanych. Łamią bowiem zasady programowania strukturalnego, a więc takiego tworzenia programu, którego przebieg zgodny jest z kolejnością zapisu kodu. Praktyka jest jednak inna- w drobnych, prostych rozwiązaniach sięganie po nawigację wewnątrz procedury bywa najszybszym sposobem na rozwiązanie danego problemu.

Przypomnę najpierw najbardziej znaną i dobrze opisaną technikę nawigacji wewnątrz procedury opartą o instrukcje GoTo. Sposoby nawigacji oprzemy o wspólny przykład- chcemy pobrać od użytkownika jego imię i dopóki nie zostanie wprowadzona jakaś wartość dopóty będziemy wykonywać fragment kodu.

Wariant 1 oparty o etykietę tekstową prezentuje prosty poniższy przykład.


Wariant 2 wykorzystuje stare rozwiązanie z klasycznego języka programowania BASIC. 
Język ten był dość popularny w latach '80-'90 XX wieku, a jego klasyczną zasadą było, że każdy wiersz zaczynał się od numeru, numery były zaś ułożone w kolejności rosnącej, choć nie musiały to być kolejne numery całkowite. Okazuje się, że rozwiązanie to można zastosować także w VBA. Zasadnicza różnica sprowadza się do tego, że nie musimy jednak numerować każdego wiersza, wystarczy, że uczynimy to z wybranymi. Dodatkowo nie jest wymagane, aby numery były w kolejności rosnącej. Poniższy kod wykorzystuje tą technikę w celu rozwiązania naszego problemu.


Wariant 3 opiera się o pętle Do...Loop i prezentuję go jako najwłaściwsze z rozwiązań. 
Podobnego rodzaju przykład jest jednym z wielu prezentowanych w czasie kursu VBA jakie prowadzimy w celu prezentacji działania pętli Do...Loop.

Environ- dane systemowe w kodzie VBA

Całkiem użyteczną funkcją zaimplementowaną w języku VBA jest funkcja Environ, której celem jest pozyskiwanie wybranych informacji o systemie operacyjnym, w którym w danym momencie pracujemy. Warto jednak zwrócić uwagę na fakt, że są to 'wybrane informacje'. Niestety, nie będziemy w stanie pobrać każdego parametru związanego z systemem operacyjnym.

Zanim więc zaprezentuję relatywnie wąską listę dostępnych opcji spójrzmy tylko na samą składnię funkcji:


gdzie:
ZmiennaSystemowa reprezentowana jest przez tekst lub odpowiednią wartość numeryczną. Należy też pamiętać, że wynikiem zwracanym przez funkcję Environ również jest tekst.

Do najbardziej przydatnych przykładów zastosowania funkcji Environ należy zaliczyć:


Kilka innych przydatnych parametrów:



Wszystkich zainteresowanych pełną listą zmiennych systemowych dostępnych z pomocą funkcji Environ odsyłam do tego linku. Znaleźć tam można gotowe do przekopiowania i uruchomienia makro, które w wyniku zwróci listę szukanych wartości w arkuszu Excela.

Wywołanie akcji w następstwie filtrowania

Zacznijmy od pytania: czy można wywołać akcję w wyniku zmiany parametrów filtrowania?
Doświadczeni programiści od razu zastanowią się, czy można zastosować odpowiednie zdarzenie w celu wykonania tej operacji lecz niestety, przynajmniej na moment tworzenia tego wpisu nie istnieje zdarzenie powiązane z działaniem filtra.

W jaki sposób moglibyśmy obejść ten problem? Odpowiedź jest dość prosta- należy wykorzystać inne zdarzenie, a konkretnie zdarzenie Calculate. W praktyce bowiem, przy każdej zmianie filtra następuje przeliczanie formuł komórkowych. Inne zdarzenia typu SelectionChange lub Change nie dadzą oczekiwanych rezultatów choćby dlatego, że filtr można wywołać samą myszą bez zmiany bieżącego zaznaczenia.

Zanim zaprezentuję kolejne etapy tego rozwiązania postawmy sobie cel: chcemy wywołać proste okno MsgBox z informacją, czy wynik filtrowania zwrócił jakieś dane czy też nie.

Krok 1. Nasze dane testowe, przyjmijmy, że mają następującą postać.

Krok 2. W arkuszu z danymi wstawmy przynajmniej jedną funkcję arkuszową, no chyba, że formuły już tam istnieją. Ja wstawię w komórce AA1 prostą funkcję zwracającą bieżącą datę i godzinę: = TERAZ()

Krok 3. Tworzymy zdarzenie Calculate dla arkusza z danymi. Proszę zwrócić uwagę na dodatkowe szczególne kryteria, parametry i założenia, które powinniśmy uwzględnić. Zaprezentowane zostały w postaci komentarzy w poniższym kodzie.


Krok 4. Wynik działania procedur prezentują poniższe zrzuty ekranu.

Uwaga końcowa! Co oczywiste wariant ten nie jest ani idealny, ani jedyny możliwy. W praktyce bowiem każde przeliczenie formuł również będzie skutkować wywołaniem powyższego zdarzenia. W zależności od okoliczności niezbędne będzie więc rozbudowanie mechanizmu o dalsze warunki i parametry.