Funkcje VBA - Przewodnik po tworzeniu funkcji niestandardowej za pomocą VBA

Spisie treści

Funkcje Excel VBA

Funkcje Excel VBA

Widzieliśmy, że możemy korzystać z funkcji arkusza roboczego w VBA, tj. Funkcji arkusza programu Excel w kodowaniu VBA metodą application.worksheet, ale jak używamy funkcji VBA w programie Excel, cóż, takie funkcje nazywa się funkcjami zdefiniowanymi przez użytkownika, kiedy użytkownik tworzy funkcję w VBA, można jej również użyć w arkuszu programu Excel.

Chociaż w programie Excel mamy wiele funkcji do manipulowania danymi, czasami musimy dostosować narzędzia, abyśmy mogli zaoszczędzić czas, ponieważ wielokrotnie wykonujemy niektóre zadania. Mamy predefiniowane funkcje w programie Excel, takie jak SUMA, LICZ.JEŻELI, SUMA.JEŻELI, WYSZUKAJ.PIONOWO, INDEKS, PODAJ.POZYCJĘ w programie Excel itp., Ale codziennie wykonujemy niektóre zadania, dla których pojedyncze polecenie lub funkcja nie jest dostępne w programie Excel, a następnie za pomocą VBA, możemy stworzyć funkcję niestandardową, która nazywa się Funkcje zdefiniowane przez użytkownika (UDF).

Co robią funkcje VBA?

Wykonują określone obliczenia; i
Zwróć wartość

W VBA podczas definiowania funkcji używamy następującej składni do określenia parametrów i ich typu danych.

Typ danych to typ danych, które będzie przechowywać zmienna. Może zawierać dowolną wartość (dowolny typ danych lub obiekt dowolnej klasy).

Możemy połączyć obiekt z jego właściwością lub metodą za pomocą kropki lub kropki (.).

Jak tworzyć funkcje niestandardowe za pomocą VBA?

Przykład

Załóżmy, że mamy następujące dane ze szkoły, w której musimy znaleźć całkowitą liczbę ocen uzyskanych przez ucznia, wynik i ocenę.

Jeśli chodzi o podsumowanie ocen uzyskanych przez indywidualnego ucznia ze wszystkich przedmiotów, mamy wbudowaną funkcję, czyli SUMA, ale sprawdzenie oceny i wyniku na podstawie kryteriów określonych przez szkołę nie jest domyślnie dostępne w Excelu .

To jest powód, dla którego musimy tworzyć funkcje zdefiniowane przez użytkownika.

Krok 1: Znajdź całkowitą liczbę punktów

Najpierw znajdziemy całkowitą liczbę punktów za pomocą funkcji SUMA w programie Excel.

Naciśnij klawisz Enter, aby uzyskać wynik.

Przeciągnij formułę do pozostałych komórek.

Teraz, aby dowiedzieć się, jaki jest wynik (powodzenie, niepowodzenie lub zasadnicze powtórzenie), kryteria określone przez szkołę są takie.

Jeżeli student uzyskał co najmniej 200 punktów na 500 punktów, a student również nie zaliczył zaliczenia z żadnego przedmiotu (uzyskał więcej niż 32 punkty z każdego przedmiotu), student jest zdany,
Jeśli student uzyskał więcej niż lub równo 200 punktów, ale uczeń nie zaliczył 1 lub 2 przedmiotów, student uzyskał „Essential Repeat” z tych przedmiotów,
Jeśli uczeń uzyskał mniej niż 200 punktów lub nie zaliczył 3 lub więcej przedmiotów, wówczas student nie zdaje egzaminu.

Krok 2: Utwórz funkcję ResultOfStudent

Aby utworzyć funkcję o nazwie „ResultOfStudent”, musimy otworzyć „Visual Basic Editor” za pomocą jednej z poniższych metod:

Korzystając z zakładki Deweloper, Excel.

Jeśli karta Deweloper nie jest dostępna w MS Excel, możemy to uzyskać, wykonując następujące czynności:

Kliknij prawym przyciskiem myszy w dowolnym miejscu na wstążce, a następnie wybierz opcję Dostosuj wstążkę w programie Excel .

Po wybraniu tego polecenia otwiera się okno dialogowe „Opcje programu Excel” .

Musimy zaznaczyć pole „Developer”, aby uzyskać zakładkę.

Używając klawisza skrótu, czyli Alt + F11.

Kiedy otwieramy edytor VBA, musimy wstawić moduł, przechodząc do menu Wstaw i wybierając moduł.

Musimy wkleić następujący kod do modułu.

Funkcja ResultOfStudents (Marks As Range) As String Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer For Each mycell In Marks Total = Total + mycell.Value If mycell.Value = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If End Function

Powyższa funkcja zwraca wynik dla ucznia.

Musimy zrozumieć, jak działa ten kod.

Pierwsza instrukcja, „Function ResultOfStudents (Marks As Range) As String”, deklaruje funkcję o nazwie „ResultOfStudents” , która przyjmie zakres jako dane wejściowe dla znaczników i zwróci wynik jako ciąg.

Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer

These three statements declare variables, i.e.,

‘myCell’ as a Range,
‘Total’ as Integer (to store total marks scored by a student),
‘CountOfFailedSubject’ as integer (to store the number of subjects in which a student has failed).

For Each mycell In Marks Total = Total + mycell.Value If mycell.Value < 33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell

This code checks for every cell in the ‘Marks’ range and adds the value of every cell in the ‘Total’ variable, and if the value of the cell is less than 33, then adds 1 to the ‘CountOfFailedSubject’ variable.

If Total>= 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total>= 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If

This code checks the value of ‘Total’ and ‘CountOfFailedSubject’ and passes the ‘Essential Report,’ ‘Passed,’ or ‘Failed’ accordingly to the ‘ResultOfStudents.’

Step 3: Apply ResultOfStudents Function to Get Result

ResultOfStudents function takes marks, i.e., selection of 5 marks scored by the student.

Now Select the Range of cells, i.e., B2: F2.

Drag the Formula to the rest of the Cells.

Step 4: Create ‘GradeForStudent’ Function to get Grades

Now to find out the grade for the student, we will create one more function named ‘GradeForStudent.’

The code would be:

Function GradeForStudent(TotalMarks As Integer, Result As String) As String If TotalMarks> 440 And TotalMarks 380 And TotalMarks 320 And TotalMarks 260 And TotalMarks = 200 And TotalMarks <= 260 And (Result = "Passed" Or Result = "Essential Repeat") Then GradeForStudent = "E" ElseIf TotalMarks < 200 Or Result = "Failed" Then GradeForStudent = "F" End If End Function

This function assigns a ‘Grade’ to the student based on the ‘Total Marks’ and ‘Result.’