VBA-funktioner - Vejledning til oprettelse af brugerdefineret funktion ved hjælp af VBA

Indholdsfortegnelse

Excel VBA-funktioner

Excel VBA-funktioner

Vi har set, at vi kan bruge regnearkfunktionerne i VBA, dvs. funktionerne i Excel-regneark i VBA-kodning ved hjælp af application.worksheet-metoden, men hvordan bruger vi en funktion af VBA i excel, ja sådanne funktioner kaldes brugerdefinerede funktioner, når en bruger opretter en funktion i VBA, kan den også bruges i Excel-regneark.

Selvom vi har mange funktioner i excel til at manipulere dataene, er vi undertiden nødt til at have nogle tilpasninger i værktøjerne, så vi kan spare tid, når vi udfører nogle opgaver gentagne gange. Vi har foruddefinerede funktioner i excel som SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH i excel osv., Men vi udfører daglige opgaver, for hvilke en enkelt kommando eller funktion ikke er tilgængelig i Excel, derefter ved hjælp af VBA, vi kan oprette den brugerdefinerede funktion, der kaldes brugerdefinerede funktioner (UDF).

Hvad gør VBA-funktioner?

De udfører bestemte beregninger; og
Returner en værdi

I VBA bruger vi følgende syntaks til at specificere parametrene og deres datatype, mens vi definerer funktionen.

Datatype her er typen af data, variablen vil indeholde. Det kan indeholde en hvilken som helst værdi (enhver datatype eller objekt i enhver klasse).

Vi kan forbinde objektet med dets egenskab eller metode ved hjælp af punkt eller punkt (.) Symbolet.

Hvordan oprettes brugerdefinerede funktioner ved hjælp af VBA?

Eksempel

Antag, at vi har følgende data fra en skole, hvor vi skal finde de samlede karakterer, der er scoret af eleven, resultat og karakter.

For at opsummere karakterer scoret af en individuel elev i alle fag, har vi en indbygget funktion, dvs. SUM, men at finde ud af karakteren og resultatet baseret på de kriterier, der er fastsat af skolen, er ikke tilgængelig i Excel som standard .

Dette er grunden til, at vi har brug for at oprette brugerdefinerede funktioner.

Trin 1: Find samlede antal markeringer

Først finder vi de samlede karakterer ved hjælp af SUM-funktionen i excel.

Tryk på Enter for at få resultatet.

Træk formlen til resten af cellerne.

Nu for at finde ud af resultatet (bestået, mislykkedes eller væsentlig gentagelse) er de kriterier, som skolen har sat.

Hvis den studerende har scoret mere end eller lig med 200 som samlede karakterer ud af 500, og den studerende heller ikke har bestået noget fag (har scoret mere end 32 i hvert fag), bestås en studerende,
Hvis den studerende har scoret mere end eller lig med 200, men den studerende er svigtet i 1 eller 2 fag, har en studerende "Essential Repeat" i disse fag,
Hvis den studerende enten har scoret mindre end 200 eller ikke består i 3 eller flere emner, er den studerende mislykket.

Trin 2: Opret ResultatOfStudent-funktion

For at oprette en funktion ved navn 'ResultOfStudent' skal vi åbne "Visual Basic Editor" ved hjælp af en af nedenstående metoder:

Ved at bruge fanen Udvikler excel.

Hvis fanen Udvikler ikke er tilgængelig i MS Excel, kan vi få det ved at bruge følgende trin:

Højreklik et vilkårligt sted på båndet, vælg derefter Tilpas båndet i excel ' .

Når vi vælger denne kommando, åbnes dialogboksen "Excel-indstillinger" .

Vi skal markere afkrydsningsfeltet for "Udvikler" for at hente fanen.

Ved hjælp af genvejstasten, dvs. Alt + F11.

Når vi åbner VBA-editoren, skal vi indsætte modulet ved at gå til menuen Indsæt og vælge et modul.

Vi skal indsætte følgende kode i modulet.

Funktion ResultatOfStudents (markerer som interval) Som streng Dim mycell Som Range Dim Total som heltal Dim CountOfFailedSubject Som Integer For Hver mycell I Marks Total = Total + mycell.Value Hvis mycell.Value = 200 And CountOfFailedSubject 0 Så ResultOfStudents = "Essential Repeat" ElseIf I alt> = 200 Og CountOfFailedSubject = 0 Derefter ResultOfStudents = "Bestået" Ellers ResultOfStudents = "mislykkedes" Afslut, hvis slutfunktion

Ovenstående funktion returnerer resultatet for en studerende.

Vi er nødt til at forstå, hvordan denne kode fungerer.

Den første sætning, 'Funktion ResultatOfStudents (markerer som rækkevidde) som streng', erklærer en funktion ved navn 'ResultOfStudents' , der accepterer et interval som input for mærker og returnerer resultatet som en streng.

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

Disse tre udsagn erklærer variabler, dvs.

'myCell' som en rækkevidde,
'Total' som heltal (for at gemme de samlede karakterer scoret af en studerende),
'CountOfFailedSubject' som heltal (for at gemme antallet af emner, hvor en studerende har fejlet).

For hver mycell I Marks I alt = Total + mycell.Value Hvis mycell.Value <33 Så CountOfFailedSubject = CountOfFailedSubject + 1 Afslut Hvis næste mycell

Denne kode kontrollerer for hver celle i ' Marks' -området og tilføjer værdien af hver celle i variablen ' Total' , og hvis værdien af cellen er mindre end 33, tilføjes derefter 1 til variablen 'CountOfFailedSubject' .

Hvis Total> = 200 Og CountOfFailedSubject 0 Så ResultOfStudents = "Essential Gentag" ElseIf Total> = 200 Og CountOfFailedSubject = 0 Så ResultOfStudents = "Bestået" Else ResultOfStudents = "Mislykket" Afslut Hvis

Denne kode kontrollerer værdien af 'Total' og 'CountOfFailedSubject' og videresender ' Essential Report', 'Passed' eller 'Failed' i overensstemmelse hermed til 'ResultOfStudents.'

Trin 3: Anvend ResultatOfStudents-funktionen for at få resultat

ResultOfStudents-funktionen tager karakterer, dvs. valg af 5 karakterer scoret af den studerende.

Vælg nu celleområdet, dvs. B2: F2.

Træk formlen til resten af cellerne.

Trin 4: Opret 'GradeForStudent' funktion for at få karakterer

Nu for at finde ud af karakteren for den studerende opretter vi endnu en funktion ved navn '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.’

We just need to write the formula and open the brackets in Cell H2 and pressing Ctrl+Shift+A to find out about the arguments.

Funktionen GradeForStudent tager de samlede karakterer (summen af karakterer) og resultatet af den studerende som et argument for at beregne karakteren.