VBA funkcijas - Ceļvedis, kā izveidot pielāgotu funkciju, izmantojot VBA

Satura rādītājs

Excel VBA funkcijas

Excel VBA funkcijas

Mēs esam redzējuši, ka mēs varam izmantot darblapas funkcijas VBA, ti, Excel darblapas funkcijas VBA kodēšanā, izmantojot application.worksheet metodi, bet kā mēs izmantojam VBA funkciju Excel, arī šādas funkcijas sauc par lietotāja definētām funkcijām, kad lietotājs izveido funkciju VBA, to var izmantot arī Excel darblapā.

Lai gan mums programmā Excel ir daudz funkciju, lai manipulētu ar datiem, dažreiz rīkos ir nepieciešama pielāgošana, lai mēs varētu ietaupīt laiku, veicot dažus uzdevumus atkārtoti. Mums programmā Excel ir iepriekš noteiktas funkcijas, piemēram, SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH in excel utt., Bet mēs ikdienā veicam dažus uzdevumus, kuriem programmā Excel nav pieejama viena komanda vai funkcija, pēc tam izmantojot VBA, mēs varam izveidot pielāgotu funkciju, ko sauc par lietotāja definētām funkcijām (UDF).

Ko dara VBA funkcijas?

Viņi veic noteiktus aprēķinus; un
Atgriež vērtību

VBA, definējot funkciju, mēs izmantojam šādu sintaksi, lai norādītu parametrus un to datu tipu.

Datu tips šeit ir datu tips, kuru mainīgais saglabās. Tajā var būt jebkura vērtība (jebkurš datu tips vai jebkuras klases objekts).

Mēs varam savienot objektu ar tā īpašību vai metodi, izmantojot perioda vai punkta (.) Simbolu.

Kā izveidot pielāgotas funkcijas, izmantojot VBA?

Piemērs

Pieņemsim, ka mums ir šādi dati no skolas, kur mums jāatrod skolēnu kopējās atzīmes, rezultāts un atzīme.

Apkopojot katra mācību priekšmeta individuālā skolēna iegūtās atzīmes, mums ir iebūvēta funkcija, ti, SUM, taču, lai uzzinātu vērtējumu un rezultātu, pamatojoties uz skolas noteiktajiem kritērijiem, pēc noklusējuma nav pieejama programmā Excel .

Tas ir iemesls, kāpēc mums ir jāizveido lietotāja definētas funkcijas.

1. darbība: atrodiet kopējās atzīmes

Pirmkārt, mēs atradīsim kopējās atzīmes, izmantojot Excel funkciju SUM.

Nospiediet Enter, lai iegūtu rezultātu.

Velciet formulu uz pārējām šūnām.

Tagad, lai uzzinātu rezultātu (izturēts, neizdevies vai būtisks atkārtojums), skolas noteiktie kritēriji ir šādi.

Ja students ir ieguvis vairāk vai vienāds ar 200 kā kopvērtējums no 500 un arī students nav izgāzies nevienā mācību priekšmetā (katrā priekšmetā ir ieguvis vairāk nekā 32), tad students tiek nokārtots,
Ja students ir ieguvis vairāk vai vienādu vērtējumu ar 200, bet students ir izgāzies 1 vai 2 priekšmetos, tad studentam šajos priekšmetos ir piešķirta “Būtiska atkārtošana”,
Ja students ir ieguvis vai nu mazāk par 200, vai arī neveicas 3 vai vairāk priekšmetos, tad students neiztur.

2. darbība: izveidojiet funkciju ResultOfStudent

Lai izveidotu funkciju ar nosaukumu “ResultOfStudent”, mums jāatver “Visual Basic Editor”, izmantojot kādu no šīm metodēm:

Izmantojot cilni Izstrādātājs, Excel.

Ja cilne Izstrādātājs nav pieejama programmā MS Excel, to varam iegūt, veicot šādas darbības:

Ar peles labo pogu noklikšķiniet uz jebkuras lentes vietas, pēc tam atlasiet Pielāgot lenti excel .

Kad mēs izvēlamies šo komandu, tiek atvērts dialoglodziņš “Excel opcijas” .

Lai iegūtu cilni, mums jāatzīmē izvēles rūtiņa “Izstrādātājs” .

Izmantojot īsinājumtaustiņu, ti, Alt + F11.

Atverot VBA redaktoru, mums jāievieto modulis, dodoties uz izvēlni Ievietot un izvēloties moduli.

Mums modulī ir jāielīmē šāds kods.

Funkcija ResultOfStudents (Atzīmes kā diapazons) Kā virkne Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject kā Integer katram mycell In Marks Kopā = Total + mycell.Value Ja mycell.Value = 200 And CountOfFailedSubject 0 Tad ResultOfStudents = "Essential Repeat" ElseIf Kopā> = 200 Un CountOfFailedSubject = 0, tad ResultOfStudents = "Nokārtots" Cits ResultOfStudents = "Neizdevās" Beigas, ja beigu funkcija

Iepriekš minētā funkcija atgriež rezultātu studentam.

Mums jāsaprot, kā darbojas šis kods.

Pirmais paziņojums “Funkcija ResultOfStudents (Marks As Range) kā virkne” deklarē funkciju ar nosaukumu “ResultOfStudents” , kas pieņem diapazonu kā zīmju ievadi un atgriezīs rezultātu kā virkni.

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

Šie trīs apgalvojumi deklarē mainīgos, ti,

“myCell” kā diapazons,
“Kopā” kā veselais skaitlis (lai saglabātu studentu kopējās atzīmes),
'CountOfFailedSubject' kā vesels skaitlis (lai saglabātu to priekšmetu skaitu, kuros students ir izgāzies).

Katram mycell atzīmēs kopā = Total + mycell.Value Ja mycell.Value <33 Tad CountOfFailedSubject = CountOfFailedSubject + 1 Beigt Ja Nākamais mycell

Šis kods pārbauda visas šūnas diapazonā “ Marks” un pievieno katras šūnas vērtību mainīgajā “ Kopā”. Ja šūnas vērtība ir mazāka par 33, tad mainīgajam “CountOfFailedSubject” pievieno 1 .

Ja Kopā> = 200 Un CountOfFailedSubject 0, tad ResultOfStudents = "Būtisks atkārtojums" ElseIf Kopā> = 200 Un CountOfFailedSubject = 0, tad ResultOfStudents = "Ietīts" Vēl ResultOfStudents = "Neizdevās" Beigu Ja

Šis kods pārbauda vērtību “Total” un “CountOfFailedSubject” vērtību un atbilstoši “ResultOfStudents” nosūta “ Essential Report”, “Passed” vai “Failed”.

3. solis: Lai iegūtu rezultātu, izmantojiet funkciju ResultOfStudents

ResultOfStudents funkcijai tiek piešķirtas atzīmes, ti, studenta iegūto 5 atzīmju atlase.

Tagad atlasiet šūnu diapazonu, ti, B2: F2.

Velciet formulu uz pārējām šūnām.

4. solis: izveidojiet funkciju “GradeForStudent”, lai iegūtu atzīmes

Tagad, lai uzzinātu studenta atzīmi, mēs izveidosim vēl vienu funkciju ar nosaukumu '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.’