Excel i VBA

Funkcja VBA Vlookup

Funkcja Vlookup w VBA to jedna z funkcji arkuszowych VBA. Wyszukuje określoną wartość w pierwszej kolumnie tabeli/zakresu komórek i zwraca wartość z określonej kolumny tego samego wiersza.

Argumenty funkcji to:

- Arg1 – wartość do wyszukania w pierwszej kolumnie tabeli/zakresu. Argument obowiązkowy;
- Arg2 – tabela/zakres komórek. Argument obowiązkowy
- Arg3 – numer kolumny, z której ma być zwrócony wynik. Argument obowiązkowy
- Arg4 – wartość logiczna True lub False. Dla wartości True wyszukiwana będzie wartość dopasowana, dla wartości False – dokładna. Argument opcjonalny, wartość domyślne to True.

W stosowaniu funkcji ważne są 2 sprawy:

- szukana wartość musi być zawsze w pierwszej kolumnie tabeli/zakresu
- jeżeli szukana wartość występuje kilka razy – funkcja zawsze zwróci tylko odwołanie do pierwszego jej wystąpienia
- jeżeli wyszukiwana wartość nie występuje w zakresie – funkcja zwróci błąd wykonania 1004 – konieczne jest jego obsłużenie w kodzie

Odpowiednikiem funkcji w Excelu jest funkcja Wyszukaj.Pionowo.

Błąd ten pojawia się w sytuacji, gdy wprawdzie kod jest prawidłowy, ale dane będące argumentami np.funkcji arkuszowych odwołują się do błędnych danych w arkuszu.
Dla przykładowego kodu może to wyglądać tak:

Public Sub MojaFunkcja()
Wynik = WorksheetFunction.VLookup(“lipiece”, Worksheets(“Arkusz4”).Range(“A1:B13”), 2, False)
End Sub

Podczas wykonania tego fragmentu kodu pojawił się błąd jw., gdyż w tabeli nie ma komórki z wartością “lipiece”. Tu w celi eliminacji błędu można zmienić ostatni argument funkcji VLookup na True (choć tu trzeba uważać). Gorzej jeżeli np.w funkcji pojawi się np.za duża liczba kolumn (większa od ilości kolumn tabeli) – tu zdecydowanie trzeba skorygować kod.
Generalnie każdy przypadek wystąpienia takiego błędu trzeba dokładnie przeanalizować. No i włączyć obsługę błędów, która zabezpieczy kod przed różnymi niespodziankami.

Funkcje arkuszowe VBA

Funkcje arkuszowe VBA to funkcje Excela, dostępne w ramach obiektu VBA Worksheet.Function. Działają tak samo, jak bezpośrednio w arkuszu.

Właściwie WorksheetFunction jest częścią obiektu Application, ale jak wiele innych może być stosowany samodzielnie.
Czyli można stosować w kodzie zarówno:
Application.WorksheetFunction….
jak i
WorksheetFunction…

Nazwy funkcji w ramach obiektu są w języku angielskim.

Funkcja Wyszukaj.Pionowo

Funkcja Wyszukaj.Pionowo to jedna z funkcji wyszukiwania i odwołań Excela. Wyszukuje wartość w pierwszej kolumnie zadeklarowanego zakresu tabeli i zwraca wartość z określonej kolumny tego samego wiersza .

Argumenty funkcji to:

- Szukana_wartość – wartość do odszukania w pierwszej kolumnie tabeli
- Tabela_tablica– zakres danych do odszukania i pobierania danych
- Nr_indeksu_kolumny – kolumna, z której zostanie pobrana wartość wynikowa funkcji
- Przeszukiwany_zakres – wartość typu logicznego. Prawda (wartość domyślna) – dla wyszukiwania najlepszego dopasowania. Dla wyszukania dokładnej wartości – argument ten musi mieć wartość Fałsz.

Jeżeli szukana wartość nie zostanie znaleziona – w wyniku działania funkcji otrzymamy błąd #N/D!.

W praktycznym zastosowaniu najważniejsze są zasady:

- szukana wartość musi być w pierwszej kolumnie, a zwracana wartość w jednej z kolumn na prawo;
- funkcja zawsze zwraca odwołanie do pierwszej znalezionej wartości. Jeżeli argument Szukana_wartość występuje również w kolejnych wierszach, nie są one brane pod uwagę;
- jeżeli ostatni argument ma wartość PRAWDA (to wartość domyślna), pierwsza kolumna (czyli ta, w której szukamy dopasowania) musi być posortowana rosnąco, gdyż w innym przypadku możemy otrzymać bzdurne wyniki.

Odpowiednikiem funkcji w VBA jest funkcja arkuszowa Vlookup.

Bezpieczeństwo makr

Pliki Excela zawierające kod VBA (czyli makra i/lub procedury) mogą niestety zawierać również szkodliwe oprogramowanie i mogą być niebezpieczne dla naszego komputera. Ma to znaczenie szczególnie przy otwieraniu plików nieznanego pochodzenia, pochodzących gdzieś z internetu i warto się zabezpieczyć.
Ustawienia bezpieczeństwa makr dostępne są na karcie Developer w grupie opcji Kod:

Można je też ustawić na karcie Plik –> Opcje –> Centrum Zaufania –> Ustawienia Centrum Zaufania.

Dostępne są tu następujące opcje:

Ja preferuję tu drugą opcję czyli wyłączenie makr i włączenie powiadomień. Dotyczy to ustawień ogólnych Excela i dotyczy wszystkich plików. Po jej wyborze, przy otwarciu pliku z makrami zobaczymy taki komunikat:

Wystarczy, jeżeli naciśniemy przycisk Włącz zawartość i wszystkie makra będą dostępne.

Jest to z pewnością bardzo pożyteczne działanie, ale w sytuacji aplikacji znanych i sprawdzonych, z pewnego źródła – często uciążliwe. Jak się pozbyć tych komunikatów? Przynajmniej w stosunku do tych treści, których jesteśmy pewni?
Służy do tego inna z opcji Centrum Zaufania – Zaufane lokalizacje

Po naciśnięciu przycisku Dodaj nową lokalizację… , otwiera się nowy formularz, w którym definiujemy wybrany folder:

Z reguły warto od razu zaznaczyć także checkbox Podfoldery tej lokalizacji są także zaufane.

W ten sposób może wybrać zaufane lokalizacje na naszym komputerze. Jeżeli chcemy zdefiniować jakiś dysk sieciowy, konieczne jest zaznaczenie wcześniejsze checkboxa Zezwalaj na zaufane lokalizacje w mojej sieci.

Funkcja VBA And

Funkcja And w VBA to jedna z funkcji logicznych VBA. Właściwie to spełnia rolę operatora w iloczynie logicznym.
Wszystkie warunki będące czynnikami takiego iloczynu muszą być typu Boolean (czyli True dla prawdy i False dla fałszu). W wyniku działania otrzymujemy również wartość typu Boolean.

Public Sub Przyklad()
Dim CzyPrawda As Boolean
Dim Warunek1 As Boolean
Dim Warunek1 As Boolean
Dim Warunek1 As Boolean

CzyPrawda = Warunek1 And Warunek2 And Warunek3
End Sub

W wyniku działania otrzymujemy wartość True tylko wtedy, gdy każdy z warunków jest spełniony czyli ma wartość True. W każdym innym przypadku otrzymamy False.

Odpowiednikiem funkcji w Excelu jest funkcja Oraz()

Funkcja ORAZ

Funkcja ORAZ to jedna z funkcji logicznych Excela. Zwraca wartość typu logicznego (Prawda lub Fałsz).
Jest odpowiednikiem iloczynu logicznego czyli koniunkcji.

Argumentami funkcji są warunki, które albo są spełnione albo nie – czyli każdy z nich zwraca wartość Prawda lub Fałsz.

Jeżeli każdy z pojedynczych warunków jest spełniony – funkcja ORAZ zwraca wartość Prawda. Jeżeli choć jeden z warunków nie jest spełniony – w wyniku działania funkcji otrzymujemy Fałsz.

Odpowiednikiem funkcji w VBA jest funkcja And().

Przenoszenie pliku

Jak szybko przenieść czyli usunąć w jednym miejscu i przenieść w inne miejsce na dysku) plik? Jednym ze sposobów jest wykorzystanie obiektu FileSystem.Object.
Służy do tego metoda FileSystem.Object.MoveFile
Ma ona kilka parametrów wejściowych:

- Object – nazwa obiektu zdefiniowanego jako obiekt FSO. Argument obowiązkowy.
- Source – nazwa pliku do skopiowania. Argument obowiązkowy, ale dozwolone są znaki zastępcze w nazwie.
- Destination – nazwa pliku po przeniesieniu. Argument obowiązkowy.

Zarówno stara jak i nowa nazwa pliku musi być pełna czyli z z dokładnym podaniem dysku i katalogu.

Przykładowy kod może wyglądać tak:

Public Sub PrzeniesPlik()
Dim FSO As Object
Dim StaryPlik As String
Dim NowyPlik As String
StaryPlik = “C:\Folder1\plik.png”
NowyPlik = “C:\Folder2\plik.png”
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.MoveFile StaryPlik, NowyPlik
Set FSO = Nothing
End Sub

Warto też wcześniej zwrócić uwagę na sprawdzenie, czy plik do przeniesienia istnieje. Jeżeli nie, to w trakcie wykonywania procedury wystąpi błąd wykonania 53.

Funkcja WeekDay

Funkcja WeekDay to jedna z funkcji Daty i Czasu VBA. Zwraca wartość liczbową (od 1 do 7) odpowiadającą numerowi dnia tygodnia.

Argumenty funkcji to:

- Date– wartość daty (argument obowiązkowy)
- FirstDayofWeek -pierwszy dzień tygodnia:
  - vbUseSystem lub 0 – zgodne z ustawieniami systemu komputera
  - vbSunday lub 1 – dla ustawień niedziela=1 do sobota=7
    wartość domyślna
  - vbMonday lub 2 – dla ustawień poniedziałek=1 do niedziela=7
  - vbTuesday lub 3 – dla ustawień wtorek =1 do poniedziałek=7
  - vbWednesday lub 4 – dla ustawień środa=1 do wtorek=7
  - vbThursday lub 5 – dla ustawień czwartek =1 do środa=7
  - vbFriday lub 6 – dla ustawień piątek =1 do czwartek=7
  - vbSaturday lub 7 – dla ustawień sobota =1 do piątek=7

Wynikiem działania funkcji jest liczba całkowita 1 do 7 – w zależności od przyjętego argumentu wejściowego.

Odpowiednikiem funkcji w Excelu jest funkcja Dzien.Tyg()

Formatowanie warunkowe z użyciem pasków danych

Formatowanie warunkowe w Excelu to bardzo obszerne narzędzie z wieloma możliwościami wizualizacji danych. Jednym z najprostszych jest sposobów jest formatowanie wstawiające do komórki paski danych.

Najprościej można pokazać to na przykładzie.
Załóżmy, że mamy arkusz z średnimi temperaturami powietrza. Zaznaczamy zakres temperatur i na karcie Narzędzia główne wybieramy Formatowanie warunkowe:

Efekt jest widoczny od razu.

Oczywiście takie formatowanie warunkowe można zmienić, dostosowując je do własnych potrzeb. Z menu formatowania warunkowego wybieramy Zarządzaj regułami, odszukujemy wprowadzoną regułą i przechodzimy do jego edycji.

W okienku edycji widać poszczególne opcje do wyboru. Możemy je zmieniać tak, jak nam się podoba.

Po zmianie kolorów, nie zapominajmy o wartościach ujemnych.

U mnie efekt końcowy wyszedł tak:

A tu trochę wsparcia z Excela:

Excel i VBA

Funkcja VBA Vlookup

Like this:

Błąd wykonania 1004

Like this:

Funkcje arkuszowe VBA

Like this:

Funkcja Wyszukaj.Pionowo

Like this:

Bezpieczeństwo makr

Like this:

Funkcja VBA And

Like this:

Funkcja ORAZ

Like this:

Przenoszenie pliku

Like this:

Funkcja WeekDay

Like this:

Formatowanie warunkowe z użyciem pasków danych

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: