Funcții VBA - Ghid pentru crearea unei funcții personalizate utilizând VBA

Cuprins

Funcții Excel VBA

Funcții Excel VBA

Am văzut că putem folosi funcțiile foii de lucru în VBA, adică funcțiile foii de lucru Excel în codificarea VBA utilizând metoda application.worksheet, dar cum folosim o funcție a VBA în Excel, ei bine, aceste funcții sunt numite funcții definite de utilizator, atunci când un utilizator creează o funcție în VBA, poate fi folosit și în foaia de lucru Excel.

Deși avem multe funcții în Excel pentru a manipula datele, uneori trebuie să avem o anumită personalizare a instrumentelor, astfel încât să ne putem economisi timpul pe măsură ce facem anumite sarcini în mod repetat. Avem funcții predefinite în Excel, cum ar fi SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH în Excel etc., dar facem zilnic sarcini pentru care o singură comandă sau funcție nu este disponibilă în Excel, apoi prin VBA, putem crea funcția personalizată care se numește Funcții definite de utilizator (UDF).

Ce fac funcțiile VBA?

Ei efectuează anumite calcule; și
Returnează o valoare

În VBA, în timp ce definim funcția, folosim următoarea sintaxă pentru a specifica parametrii și tipul lor de date.

Tipul de date aici este tipul de date pe care variabila le va păstra. Poate deține orice valoare (orice tip de date sau obiect din orice clasă).

Putem conecta obiectul cu proprietatea sau metoda sa folosind simbolul punct sau punct (.).

Cum se creează funcții personalizate utilizând VBA?

Exemplu

Să presupunem că avem următoarele date dintr-o școală în care trebuie să găsim notele totale obținute de elev, rezultat și notă.

În ceea ce privește rezumarea notelor obținute de un elev individual la toate disciplinele, avem o funcție încorporată, adică SUM, dar pentru a afla nota și rezultatul pe baza criteriilor stabilite de școală nu este disponibil în mod implicit în Excel. .

Acesta este motivul pentru care trebuie să creăm funcții definite de utilizator.

Pasul 1: Găsiți marcaje totale

În primul rând, vom găsi notele totale folosind funcția SUM în excel.

Apăsați Enter pentru a obține rezultatul.

Trageți Formula în restul celulelor.

Acum, pentru a afla rezultatul (trecut, eșuat sau repetare esențială), criteriile stabilite de școală sunt acelea.

Dacă studentul a obținut mai mult sau egal cu 200 ca note totale din 500 și studentul nu este, de asemenea, eșuat la nicio materie (a obținut mai mult de 32 la fiecare disciplină), atunci un student este promovat,
Dacă studentul a obținut un scor mai mare sau egal cu 200, dar studentul eșuează la 1 sau 2 materii, atunci un student are „Repetare esențială” la aceste materii,
Dacă studentul a obținut fie mai puțin de 200, fie eșuează la 3 sau mai multe materii, atunci studentul eșuează.

Pasul 2: Creați funcția ResultOfStudent

Pentru a crea o funcție numită „ResultOfStudent”, trebuie să deschidem „Visual Basic Editor” utilizând oricare dintre metodele de mai jos:

Folosind fila Developer excel.

Dacă fila Dezvoltator nu este disponibilă în MS Excel, atunci o putem obține urmând pașii următori:

Faceți clic dreapta oriunde pe panglică, apoi alegeți Personalizați panglica în excel ' .

Când alegem această comandă, se deschide caseta de dialog „Opțiuni Excel” .

Trebuie să bifăm caseta „Dezvoltator” pentru a obține fila.

Utilizând tasta de comandă rapidă, adică Alt + F11.

Când deschidem editorul VBA, trebuie să introducem modulul accesând meniul Insert și alegând un modul.

Trebuie să lipim următorul cod în modul.

Funcția ResultOfStudents (marchează ca interval) Ca șir Dim mycell Ca interval Dim Total Ca întreg Dim CountOfFailedSubject Ca întreg pentru fiecare mycell În puncte Total = Total + mycell.Value If mycell.Value = 200 Și CountOfFailedSubject 0 Atunci ResultOfStudents = "Repetare esențială" ElseIf Total> = 200 Și CountOfFailedSubject = 0 Apoi ResultOfStudents = "Trecut" Altfel ResultOfStudents = "Eșuat" Sfârșit dacă funcția Sfârșit

Funcția de mai sus returnează rezultatul pentru un student.

Trebuie să înțelegem cum funcționează acest cod.

Prima declarație, „Funcția ResultOfStudents (marchează ca interval) ca șir”, declară o funcție numită „ResultOfStudents” care va accepta un interval ca intrare pentru mărci și va returna rezultatul ca șir.

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.’