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

view sourceprint?

1	=Match(SzukanaWartość, Tablica, ParametrKontrolny)

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.

view sourceprint?

01	Sub Rozwiązanie_MA()

02

03	Dim Szukana As Variant

04	Szukana = InputBox("wartość szukana")

05

06	'tablica Array pochodząca z zaznaczenia obszaru komórek

07	Dim TBL As Range

08	Set TBL = Application.InputBox("zaznacz obszar", Type:=8)

09

10	'wariant A- tylko Match

11	MsgBox WorksheetFunction.Match(Szukana, TBL.Columns(1), 0)

12

13

End Sub

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.

view sourceprint?

1	'wariant B- Match + Index = VLookUp

2	MsgBox WorksheetFunction.Index(TBL.Columns(4), _

3	WorksheetFunction.Match(Szukana, _

4	TBL.Columns(1), 0))

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:

view sourceprint?

1	=VLookup(SzukanaWartość, Tablica, n-taKolumna, Fałsz)

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

view sourceprint?

01	Sub Rozwiązanie_VL()

02

03	Dim Szukana As Variant

04	Szukana = InputBox("wartość szukana")

05

06	'tablica Array pochodząca z zaznaczenia obszaru komórek

07	Dim TBL As Range

08	Set TBL = Application.InputBox("zaznacz obszar", Type:=8)

09

10	'funkcja VLOOKUP

11	MsgBox WorksheetFunction.VLookup(Szukana, TBL, 4, 0)

12

13

End Sub

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:

view sourceprint?

1	Function ColumnNameSimple() As String

2

3	Dim TMP As Variant

4	TMP = Split(Application.ThisCell.Address, "$")

5	ColumnNameSimple = TMP(1)

6

7	End Function

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:

view sourceprint?

1	Function ColumnNameEasy(ColNumber) As String

2

3	If ColNumber > 0 Then

4	Dim TMP As Variant

5	TMP = Split(Replace(Columns(ColNumber).Address, ":", ""), "$")

6	ColumnNameEasy = TMP(1)

7

End If

8

9	End Function

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

view sourceprint?

1	=ColumnaNameEasy(26)     zwróci Z

2	=ColumnaNameEasy(27)     zwróci AA

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

view sourceprint?

1	=ColumnaNameEasy(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.

view sourceprint?

01	Sub Wariant_1()

02	'etykieta- to dowolny tekst z dwukropkiem na końcu

03

Powrót:

04	Imię = InputBox("Wprowadź swoje imię")

05

06	'jeżeli nie wprowadzono tekstu to przeskocz do lini _

07	ze wskazaną etykietą

08	If Len(Imię) = 0 Then GoTo Powrót

09

10	'gdy imię zostanie podane wyświetl komunikat

11	MsgBox "Witaj " & Imię

12

End Sub

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.

view sourceprint?

01	Sub Wariant_2()

02	Dim Imię As String

03	'numerowanie wybranych wierszy

04	10  Imię = InputBox("Wprowadź swoje imię")

05

06	'jeżeli nie wprowadzono tekstu to przeskocz do wiersza _

07	o wskazanym numerze

08	If Len(Imię) = 0 Then GoTo 10

09

10	'gdy imię zostanie podane wyświetl komunikat

11	50    MsgBox "Witaj " & Imię

12

End Sub

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.

view sourceprint?

01	Sub Wariant_3()

02	Dim Imię As String

03

04	'pętla- nawigacja pomiędzy początkiem i końcem

05

Do

06	Imię = InputBox("Wprowadź swoje imię")

07	Loop While Len(Imię) = 0

08

09	'gdy imię zostanie podane wyświetl komunikat

10	MsgBox "Witaj " & Imię

11

End Sub

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:

view sourceprint?

1	Environ(ZmiennaSystemowa)

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

view sourceprint?

1	'pobranie systemowej nazwy użytkownika

2	Debug.Print Environ("USERNAME")

3	'pobranie ścieżki do systemowego _

4	folderu plików tymczasowych

5	Debug.Print Environ("TEMP")

6	'pobranie nazwy komputera

7	Debug.Print Environ("COMPUTERNAME")

Kilka innych przydatnych parametrów:

view sourceprint?

01	'symbol dysku głównego

02	Debug.Print Environ("HOMEDRIVE")

03	'ścieżka do folderu użytkownika

04	Debug.Print Environ("HOMEPATH")

05	'ścieżka do folderu Program Files

06	Debug.Print Environ("PROGRAMFILES")

07	'symbol dysku systemowego

08	Debug.Print Environ("SYSTEMDRIVE")

09	'ścieżka do folderu systemu Windows _

10	dwie opcje

11	Debug.Print Environ("SYSTEMROOT")

12	Debug.Print Environ("WINDIR")

13	'ścieżka do profilu użytkownika

14	Debug.Print Environ("USERPROFILE")

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.

view sourceprint?

01	Private Sub Worksheet_Calculate()

02	Debug.Print Now

03

04	'zdarzenie Calculate wywoływane jest _

05	także wtedy, gdy aktywny jest inny arkusz

06	'tu upewniamy się, że aktywny arkusz to _

07	arkusz z naszymi danymi

08

09	If ActiveSheet.Name = Me.Name Then

10

11	'przyjmując założenie, że poniżej tabeli _

12	danych nie ma innych wartości po prostu _

13	sprawdzamy, czy nr wiersza w wyniku _

14	działania właściwości End (Ctrl + Strzałka)

15

16	If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then

17	MsgBox "Brak danych", , "Wynik filtrowania"

18

Else

19	MsgBox "Dane dostępne", , "Wynik filtrowania"

20

End If

21

22

End If

23

End Sub

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.