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.


Kurs Makra i VBA w Excelu dla początkujących

 

Błąd wykonania 1004

Błąd wykonania 1004

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.


Kurs Makra i VBA w Excelu dla początkujących

 

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.


Kurs Excel dla pracowników biurowych

 

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()


Kurs Makra i VBA w Excelu dla początkujących

 

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().


Kurs Excel 2019 od podstaw

 

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.


Kurs Excel - makra i VBA dla początkujących

 

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 0zgodne 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()


Kurs Makra i VBA w Excelu dla początkujących

 

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:

Kurs Efektywna praca w Excelu - porady i triki

%d bloggers like this: