Funkcja arkusza VBA | Jak korzystać z funkcji arkusza roboczego w VBA?

Funkcje arkusza kalkulacyjnego Excel VBA

Funkcja arkusza roboczego w VBA jest używana, gdy musimy odwołać się do konkretnego arkusza roboczego, normalnie, gdy tworzymy moduł, kod wykonuje się w aktualnie aktywnym arkuszu skoroszytu, ale jeśli chcemy wykonać kod w określonym arkuszu, używamy funkcji arkusza, ta funkcja ma różne zastosowania i aplikacje w VBA.

Najlepszą rzeczą w VBA jest to, że podobnie jak w przypadku używania formuł w arkuszu roboczym, VBA również ma swoje własne funkcje. Jeśli to jest najlepsze, to ma też piękną rzecz, że „możemy używać funkcji arkusza również w VBA”.

Tak!!! Słyszałeś to dobrze, mamy również dostęp do funkcji arkusza roboczego w VBA. Możemy uzyskać dostęp do niektórych funkcji arkusza podczas pisania kodu i uczynić go częścią naszego kodu.

Jak korzystać z funkcji arkusza roboczego w VBA?

Możesz pobrać ten szablon VBA WorksheetFunction Template tutaj - VBA WorksheetFunction Template

W arkuszu wszystkie formuły zaczynają się od znaku równości (=), podobnie w kodowaniu VBA, aby uzyskać dostęp do formuł arkusza, należy użyć słowa „Funkcja arkusza roboczego”.

Zanim wprowadzisz jakąkolwiek formułę arkusza roboczego, musisz podać nazwę obiektu „WorksheetFunction”, a następnie wstawić kropkę (.), A otrzymasz listę wszystkich funkcji dostępnych pod tym obiektem.

W tym artykule skupimy się wyłącznie na tym, jak używać funkcji arkusza roboczego w kodowaniu VBA, co zwiększy wartość Twojej wiedzy o kodowaniu.

# 1 - Proste funkcje arkusza SUMA

Ok, aby zacząć od funkcji arkusza, zastosuj prostą funkcję SUMA w programie Excel, aby dodać liczby z arkusza.

Załóżmy, że w arkuszu znajdują się miesięczne dane dotyczące sprzedaży i kosztów, takie jak poniższy.

W B14 i C14 musimy otrzymać sumę powyższych liczb. Wykonaj poniższe kroki, aby rozpocząć proces stosowania funkcji „SUMA” w programie Excel VBA.

Krok 1: Utwórz prostą nazwę makra programu Excel.

Kod:

 Sub Worksheet_Function_Example1 () End Sub

Krok 2: Ponieważ potrzebujemy wyniku w komórce B14, zaczynamy kod jako Range („B14”). Wartość =

Kod:

 Sub Worksheet_Function_Example1 () Range („B14”). Wartość = End Sub

Krok 3: W B14 potrzebujemy wartości będącej wynikiem sumy liczb. Aby więc uzyskać dostęp do funkcji SUMA z arkusza, należy uruchomić kod jako „ Funkcja arkusza roboczego”.

Kod:

Sub Worksheet_Function_Example1 () Range („B14”). Value = WorksheetFunction. Napis końcowy

Krok 4: W momencie umieszczenia kropki (.) Zacznie się wyświetlać dostępne funkcje. Więc wybierz SUMA z tego.

Kod:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum End Sub

Krok 5: Teraz podaj odniesienie do powyższych liczb, tj. Range („B2: B13”).

Kod:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub

Krok 6: Podobnie dla następnej kolumny zastosuj podobny kod, zmieniając odwołania do komórek.

Kod:

 Sub Worksheet_Function_Example1 () Range („B14”). Value = WorksheetFunction.Sum (Range („B2: B13”)) Range („C14”). Value = WorksheetFunction.Sum (Range („C2: C13”)) End Sub

Krok 7: Teraz uruchom ten kod ręcznie lub za pomocą klawisza F5, aby uzyskać sumę w komórkach B14 i C14.

Wow, mamy nasze wartości. Jedną rzeczą, na którą musisz zwrócić uwagę, jest to, że nie mamy żadnej formuły w arkuszu, ale właśnie otrzymaliśmy wynik funkcji „SUMA” w VBA.

# 2 - Użyj funkcji WYSZUKAJ.PIONOWO jako funkcji arkusza roboczego

Zobaczymy, jak korzystać z funkcji WYSZUKAJ.PIONOWO w VBA. Załóżmy, że poniżej znajdują się dane, które masz w arkuszu Excela.

W komórce E2 utworzyłeś rozwijaną listę wszystkich stref.

W oparciu o wybór dokonany w komórce E2 musimy pobrać kod PIN dla odpowiedniej strefy. Ale tym razem przez VBA WYSZUKAJ.PIONOWO, a nie WYSZUKAJ.PIONOWO w arkuszu. Wykonaj poniższe czynności, aby zastosować WYSZUKAJ.PIONOWO.

Krok 1: Utwórz prostą nazwę makra w procedurze podrzędnej.

Kod:

 Sub Worksheet_Function_Example2 () End Sub

Krok 2: Potrzebujemy wyniku w komórce F2. Więc zacznij kod jako Range („F2”). Wartość =

Kod:

 Sub Worksheet_Function_Example2 () Range ("F2"). Wartość = End Sub

Krok 3: Aby uzyskać dostęp do arkusza, funkcja WYSZUKAJ.PIONOWO uruchamia kod jako „ Funkcja arkusza.VLOOKUP ”.

Kod:

 Sub Worksheet_Function_Example2 () Range („F2”). Value = WorksheetFunction.Vlookup (End Sub

Krok 4: Jednym z problemów jest to, że składnia nie daje żadnych wskazówek dotyczących pracy z WYSZUKAJ.PIONOWO. Musisz mieć absolutną pewność co do składni, nad którą pracujesz.

Pierwsza składnia funkcji WYSZUKAJ.PIONOWO to „Wartość wyszukiwania”. W tym przypadku naszą wartością wyszukiwania jest wartość komórki E2, więc zapisz kod jako Range („E2”). Wartość

Kod:

 Sub Worksheet_Function_Example2 () Range („F2”). Value = WorksheetFunction.Vlookup (Range („E2”). Value, End Sub

Krok 5: Teraz drugim argumentem jest nasza tablica tabel, w tym przypadku zakres naszych tablic wynosi od A2 do B6. Więc kod będzie Range („A2: B6”)

Kod:

 Sub Worksheet_Function_Example2 () Range („F2”). Value = WorksheetFunction.Vlookup (Range („E2”). Value, Range („A2: B6”), End Sub

Krok 6: Trzecim argumentem będzie, z której kolumny potrzebujemy danych z tablicy tabeli. Tutaj potrzebujemy danych z drugiej kolumny, więc argument będzie wynosił 2.

Kod:

 Sub Worksheet_Function_Example2 () Range („F2”). Value = WorksheetFunction.Vlookup (Range („E2”). Value, Range („A2: B6”), 2, End Sub

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
We don’t have access to all the functions only a few.
We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.