VBA-funksjoner - Veiledning for å lage tilpasset funksjon ved hjelp av VBA

Innholdsfortegnelse

Excel VBA-funksjoner

Excel VBA-funksjoner

Vi har sett at vi kan bruke regnearkfunksjonene i VBA, dvs. funksjonene til Excel-regneark i VBA-koding ved hjelp av application.worksheet-metoden, men hvordan bruker vi en funksjon av VBA i Excel, vel slike funksjoner kalles brukerdefinerte funksjoner, når en bruker oppretter en funksjon i VBA, kan den også brukes i Excel-regneark.

Selv om vi har mange funksjoner i overlegenhet for å manipulere dataene, noen ganger må vi ha litt tilpasning i verktøyene slik at vi kan spare tid når vi gjør noen oppgaver gjentatte ganger. Vi har forhåndsdefinerte funksjoner i excel som SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH in excel, etc., men vi gjør noen oppgaver på daglig basis som en enkelt kommando eller funksjon ikke er tilgjengelig i Excel, deretter ved å bruke VBA, vi kan lage den egendefinerte funksjonen som kalles brukerdefinerte funksjoner (UDF).

Hva gjør VBA-funksjoner?

De utfører visse beregninger; og
Returner en verdi

I VBA, mens vi definerer funksjonen, bruker vi følgende syntaks for å spesifisere parametrene og deres datatype.

Datatypen her er typen data variabelen vil ha. Den kan ha hvilken som helst verdi (hvilken som helst datatype eller gjenstand i hvilken som helst klasse).

Vi kan koble objektet med dets egenskap eller metode ved å bruke punkt- eller punkt (.) -Symbolet.

Hvordan lage egendefinerte funksjoner ved hjelp av VBA?

Eksempel

Anta at vi har følgende data fra en skole der vi trenger å finne de totale karakterene som er oppnådd av studenten, resultatet og karakteren.

For å oppsummere karakterene til en individuell student i alle fag, har vi en innebygd funksjon, dvs. SUM, men å finne ut karakteren og resultatet basert på kriteriene som er angitt av skolen, er ikke tilgjengelig i Excel som standard .

Dette er grunnen til at vi trenger å lage brukerdefinerte funksjoner.

Trinn 1: Finn antall merker

Først finner vi de totale merkene ved hjelp av SUM-funksjonen i excel.

Trykk Enter for å få resultatet.

Dra formelen til resten av cellene.

Nå for å finne ut resultatet (Bestått, Mislykket eller Viktig Gjenta), er kriteriene som er satt av skolen.

Hvis studenten har scoret mer enn eller lik 200 som totaltall av 500, og studenten heller ikke har bestått i noe fag (har scoret mer enn 32 i hvert fag), blir studenten bestått,
Hvis studenten har scoret mer enn eller lik 200, men studenten ikke består i 1 eller 2 fag, har en student fått "Essential Repeat" i disse fagene,
Hvis eleven har scoret enten mindre enn 200 eller ikke bestått i 3 eller flere emner, mislykkes studenten.

Trinn 2: Opprett ResultatOfStudent-funksjon

For å opprette en funksjon som heter 'ResultOfStudent', må vi åpne "Visual Basic Editor" ved å bruke en av metodene nedenfor:

Ved å bruke kategorien Developer excel.

Hvis kategorien Utvikler ikke er tilgjengelig i MS Excel, kan vi få det ved å bruke følgende trinn:

Høyreklikk hvor som helst på båndet, og velg deretter Tilpass båndet i excel ' .

Når vi velger denne kommandoen, åpnes dialogboksen “Alternativer for Excel” .

Vi må merke av i boksen for "Utvikler" for å få fanen.

Ved å bruke hurtigtasten, dvs. Alt + F11.

Når vi åpner VBA-redigereren, må vi sette inn modulen ved å gå til Sett inn-menyen og velge en modul.

Vi må lime inn følgende kode i modulen.

Funksjon ResultatOfStudents (merker som rekkevidde) Som streng Dim mycell Som Range Dim Total som Integer Dim CountOfFailedSubject Som Integer For Each mycell In Marks Total = Total + mycell.Value If mycell.Value = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Totalt> = 200 Og CountOfFailedSubject = 0 Så ResultatOfStudents = "Bestått" Ellers ResultatOfStudents = "Mislyktes" Avslutt hvis sluttfunksjon

Funksjonen ovenfor returnerer resultatet for en student.

Vi må forstå hvordan denne koden fungerer.

Den første setningen, 'Funksjon ResultatOfStudents (merker som rekkevidde) som streng,' erklærer en funksjon med navnet 'ResultatOfStudents' som vil akseptere et område som input for merker og vil returnere resultatet som en streng.

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

Disse tre utsagnene erklærer variabler, dvs.

'myCell' som en rekkevidde,
'Total' som heltal (for å lagre totale karakterer scoret av en student),
'CountOfFailedSubject' som heltall (for å lagre antall fag der en student ikke har bestått).

For hver mycell I Marks Total = Total + mycell.Value Hvis mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell

Denne koden sjekker for hver celle i ' Merker' -området og legger til verdien av hver celle i ' Total' -variabelen, og hvis verdien av cellen er mindre enn 33, legger den til 1 til variabelen 'CountOfFailedSubject' .

Hvis totalt> = 200 og CountOfFailedSubject 0 så ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Mislyktes" Avslutt hvis

Denne koden sjekker verdien av 'Total' og 'CountOfFailedSubject' og sender ' Essential Report', 'Passed' eller 'Mislyktes' tilsvarende til 'ResultOfStudents.'

Trinn 3: Bruk ResultatOfStudents-funksjonen for å få resultat

ResultatOfStudents-funksjonen tar karakter, det vil si valg av 5 karakterer gitt av studenten.

Velg nå Celleområdet, dvs. B2: F2.

Dra formelen til resten av cellene.

Trinn 4: Opprett 'GradeForStudent' -funksjon for å få karakterer

Nå for å finne ut karakteren for studenten, vil vi opprette en funksjon til som heter '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.’