Funkcje takie jak WYSZUKAJ.PIONOWO, LICZ.JEŻELI i SUMA.JEŻELI są nazywane funkcjami arkusza. Ogólnie funkcje, które są wstępnie zdefiniowane w programie Excel i gotowe do użycia w arkuszu, są funkcjami arkusza. Nie możesz zmienić ani zobaczyć kodu tych funkcji w VBA.
Z drugiej strony funkcje zdefiniowane przez użytkownika i funkcje specyficzne dla VBA, takie jak MsgBox lub InputBox, są funkcjami VBA.
Wszyscy wiemy, jak korzystać z funkcji VBA w VBA. Ale co, jeśli chcemy używać VLOOKUP w VBA. Jak to zrobimy? W tym artykule dokładnie to zbadamy.
Korzystanie z funkcji arkusza roboczego w VBA
Aby uzyskać dostęp do funkcji arkusza roboczego, używamy klasy Application. Prawie wszystkie funkcje arkusza są wymienione w klasie Application.WorksheetFunction. Za pomocą operatora kropki możesz uzyskać do nich dostęp.
W dowolnym podrzędnym napisz Application.WorksheetFunction. I zacznij pisać nazwę funkcji. Intellisense VBA pokaże nazwy funkcji dostępnych do użycia. Po wybraniu nazwy funkcji, zapyta ona o zmienne, jak każda funkcja w programie Excel. Ale będziesz musiał przekazać zmienne w zrozumiałym formacie VBA. Na przykład, jeśli chcesz przekazać zakres A1:A10, będziesz musiał przekazać go jako obiekt zakresu, taki jak Range("A1:A10").
Wykorzystajmy więc kilka funkcji arkusza, aby lepiej to zrozumieć.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w VBA
Aby zademonstrować, jak można użyć funkcji WYSZUKAJ.PIONOWO w VBA, tutaj mam przykładowe dane. Muszę pokazać nazwę i miasto podanego identyfikatora logowania w oknie wiadomości za pomocą VBA. Dane są rozłożone w zakresie A1:K26.
Naciśnij klawisze ALT + F11, aby otworzyć VBE i wstawić moduł.
Zobacz poniższy kod.
Sub WsFuncitons() Dim loginID As String Nazwa Dim, miasto As String loginID = "AHKJ_1-3357042451" 'Użycie funkcji WYSZUKAJ.PIONOWO w celu uzyskania nazwy podanego identyfikatora w nazwie tabeli = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26" ), 2, 0) 'Użycie funkcji WYSZUKAJ.PIONOWO w celu uzyskania miasta o podanym identyfikatorze w tabeli city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0) MsgBox ("Name: " & name & vbLf & "Miasto: " & miasto) Koniec Sub
Po uruchomieniu tego kodu otrzymasz ten wynik.
Możesz zobaczyć, jak szybko VBA drukuje wynik w oknie komunikatu. Teraz przyjrzyjmy się kodowi.
Jak to działa?
1.
Dim loginID jako ciąg
Nazwa dim, miasto As String
Najpierw zadeklarowaliśmy dwie zmienne typu string do przechowywania wyniku zwróconego przez funkcję WYSZUKAJ.PIONOWO. Użyłem zmiennych typu ciąg, ponieważ jestem pewien, że wynik zwrócony przez WYSZUKAJ.PIONOWO będzie wartością ciągu. Jeśli funkcja arkusza ma zwracać liczbę, datę, zakres itp. typ wartości, użyj tego rodzaju zmiennej do przechowywania wyniku. Jeśli nie masz pewności, jaki rodzaj wartości zostanie zwrócony przez funkcję arkusza, użyj zmiennych typu wariantu.
2.
loginID = "AHKJ_1-3357042451"
Następnie użyliśmy zmiennej loginID do przechowywania wartości wyszukiwania. Tutaj użyliśmy wartości zakodowanej na sztywno. Możesz także użyć referencji. Na przykład. Możesz użyć Range("A2").Value, aby dynamicznie aktualizować wartość wyszukiwania z zakresu A2.
3.
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
Tutaj używamy funkcji WYSZUKAJ.PIONOWO, aby uzyskać. Teraz, gdy poprawisz funkcję i otworzysz nawias, wyświetli wymagane argumenty, ale nie tak opisowe, jak w programie Excel. Sam zobacz.
Musisz pamiętać, jak i jakiej zmiennej musisz użyć. Zawsze możesz wrócić do arkusza roboczego, aby zobaczyć opisowe szczegóły zmiennej.
Tutaj wartością wyszukiwania jest Arg1. Dla Arg1 używamy loginID. Tablica przeglądowa to Arg2. Dla Arg2 użyliśmy Range("A1:K26"). Zauważ, że nie użyliśmy bezpośrednio A2:K26, jak to robimy w Excelu. Indeks kolumn to Arg3. Dla Arg3 użyliśmy 2, ponieważ nazwa znajduje się w drugiej kolumnie. Typ wyszukiwania to Arg4. Użyliśmy 0 jako Arg4.
miasto = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
Podobnie otrzymujemy nazwę miasta.
4.
MsgBox ("Nazwa: " & nazwa & vbLf & "Miasto: " & miasto)
Na koniec wypisujemy nazwę i miasto za pomocą Messagebox.
Dlaczego warto korzystać z funkcji arkusza roboczego w VBA?
Funkcje arkusza mają moc ogromnych obliczeń i nie będzie mądrym ignorowanie mocy funkcji arkusza. Na przykład, jeśli chcemy uzyskać odchylenie standardowe zbioru danych i chcesz napisać w tym celu cały kod, może to zająć godziny. Ale jeśli wiesz, jak korzystać z funkcji arkusza STDEV.P w VBA, aby uzyskać obliczenia za jednym razem.
Sub GetStdDev() std = Application.WorksheetFunction.StDev_P(Range("A1:K26")) End Sub
Korzystanie z wielu funkcji arkusza roboczego VBA
Powiedzmy, że musimy użyć dopasowania indeksu, aby pobrać niektóre wartości. Jak byś napisał formułę w VBA. Oto co chyba napiszesz:
Sub IndMtch() Val = Application.WorksheetFunction.Index(Result_range, _ Application.WorksheetFunction.Match(search_value, _ lookup_range, match_type)) End Sub
To nie jest złe, ale jest zbyt długie. Właściwym sposobem korzystania z wielu funkcji jest użycie bloku With. Zobacz poniższy przykład:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(zakres_wyniku, .Match(spoglądana_wartość, szukany_zakres, typ_dopasowania)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(liczby) End With End Sub
Jak widać, użyłem bloku With, aby poinformować VBA, że będę używał właściwości i funkcji Application.WorksheetFunction. Nie muszę więc tego wszędzie definiować. Właśnie użyłem operatora kropki, aby uzyskać dostęp do funkcji INDEKS, DOPASUJ, WYSZUKAJ.PIONOWO i STDEV.P. Po użyciu instrukcji End With nie jesteśmy w stanie uzyskać dostępu do funkcji bez użycia w pełni kwalifikowanych nazw funkcji.
Więc jeśli musisz używać wielu funkcji arkusza roboczego w VBA, użyj z blokiem.
Nie wszystkie funkcje arkusza roboczego są dostępne poprzez Application.WorksheetFunction
Niektóre funkcje arkusza roboczego są bezpośrednio dostępne do użycia w VBA. Nie musisz używać obiektu Application.WorksheetFunction.
Na przykład funkcje takie jak Len(), która służy do pobierania liczby znaków w ciągu, lewo, prawo, środek, przycinanie, przesunięcie itp. Te funkcje mogą być bezpośrednio używane w VBA. Oto przykład.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Zobacz, tutaj użyliśmy funkcji LEN bez użycia obiektu Application.WorksheetFunction.
Podobnie możesz użyć innych funkcji, takich jak left, right, mid, char itp.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) Koniec Sub
Kiedy uruchomisz powyższą sub, zwróci ona:
5 On o ll
Tak, tak, w ten sposób można korzystać z funkcji arkusza kalkulacyjnego programu Excel w VBA. Mam nadzieję, że wyjaśniłem wystarczająco, a ten artykuł ci pomógł. Jeśli masz jakiekolwiek pytania dotyczące tego artykułu lub czegokolwiek innego związanego z VBA, zapytaj w sekcji komentarzy poniżej. Do tego czasu możesz przeczytać o innych powiązanych tematach poniżej.
Co to jest funkcja CSng w Excel VBA? | Funkcja SCng to funkcja języka VBA, która konwertuje dowolny typ danych na liczbę zmiennoprzecinkową o pojedynczej precyzji („biorąc pod uwagę, że jest to liczba”). Najczęściej używam funkcji CSng do konwersji liczb sformatowanych tekstowo na liczby rzeczywiste.
Jak uzyskać tekst i numer w odwrotnej kolejności przez VBA w programie Microsoft Excel? | Aby odwrócić liczbę i tekst, używamy pętli i funkcji mid w VBA. 1234 zostanie zamienione na 4321, „ty” zostanie zamienione na „uoy”. Oto fragment.
Formatuj dane za pomocą niestandardowych formatów liczb za pomocą VBA w programie Microsoft Excel | Aby zmienić format liczb określonych kolumn w programie Excel, użyj tego fragmentu kodu VBA. Zapisuje format liczb o określonym formacie za pomocą jednego kliknięcia.
Używanie zdarzenia zmiany arkusza do uruchamiania makra po wprowadzeniu jakiejkolwiek zmiany | Aby uruchomić makro za każdym razem, gdy arkusz się zaktualizuje, używamy Zdarzeń arkusza roboczego VBA.
Uruchom makro, jeśli w arkuszu w określonym zakresie wprowadzono jakiekolwiek zmiany | Aby uruchomić kod makra, gdy zmieni się wartość w określonym zakresie, użyj tego kodu VBA. Wykrywa każdą zmianę dokonaną w określonym zakresie i uruchamia zdarzenie.
Najprostszy kod VBA do podświetlania bieżącego wiersza i kolumny za pomocą | Użyj tego małego fragmentu kodu VBA, aby podświetlić bieżący wiersz i kolumnę arkusza.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
