VBA-funktioner - Guide för att skapa anpassad funktion med VBA

Innehållsförteckning

Excel VBA-funktioner

Excel VBA-funktioner

Vi har sett att vi kan använda kalkylfunktionerna i VBA, det vill säga funktionerna i Excel-kalkylbladet i VBA-kodning med hjälp av application.worksheet-metoden, men hur använder vi en funktion av VBA i Excel, ja sådana funktioner kallas användardefinierade funktioner, när en användare skapar en funktion i VBA kan den också användas i Excel-kalkylblad.

Även om vi har många funktioner i excel för att manipulera data, måste vi ibland ha lite anpassning i verktygen så att vi kan spara vår tid när vi gör vissa uppgifter upprepade gånger. Vi har fördefinierade funktioner i excel som SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH i excel, etc. men vi gör vissa uppgifter dagligen för vilka ett enda kommando eller funktion inte är tillgänglig i Excel, sedan med VBA, vi kan skapa den anpassade funktionen som kallas användardefinierade funktioner (UDF).

Vad gör VBA-funktioner?

De utför vissa beräkningar; och
Returnera ett värde

När vi definierar funktionen i VBA använder vi följande syntax för att specificera parametrarna och deras datatyp.

Datatyp här är den typ av data variabeln kommer att innehålla. Det kan innehålla vilket värde som helst (vilken datatyp eller vilket objekt som helst i vilken klass som helst).

Vi kan ansluta objektet till dess egenskap eller metod genom att använda punkt- eller punkt (.) -Symbolen.

Hur skapar jag anpassade funktioner med VBA?

Exempel

Anta att vi har följande data från en skola där vi behöver hitta de totala poängen som eleven fått, resultat och betyg.

För att sammanfatta poängen för en enskild elev i alla ämnen har vi en inbyggd funktion, dvs. SUM, men att ta reda på betyget och resultatet baserat på de kriterier som fastställts av skolan finns inte som standard i Excel .

Detta är anledningen till att vi behöver skapa användardefinierade funktioner.

Steg 1: Hitta totala poäng

Först hittar vi de totala poängen med SUM-funktionen i excel.

Tryck på Enter för att få resultatet.

Dra formeln till resten av cellerna.

För att ta reda på resultatet (godkänd, misslyckad eller väsentlig upprepning) är kriterierna som skolan ställer.

Om studenten har fått mer än eller lika med 200 som totalt betyg av 500 och studenten inte heller har misslyckats i något ämne (har fått fler än 32 i varje ämne), godkänns en student,
Om eleven har fått mer än eller lika med 200, men eleven har misslyckats i 1 eller 2 ämnen, har en student fått "Essential Repeat" i dessa ämnen,
Om eleven har fått antingen mindre än 200 eller misslyckats i tre eller fler ämnen, misslyckas studenten.

Steg 2: Skapa ResultatOfStudent-funktion

För att skapa en funktion med namnet 'ResultOfStudent' måste vi öppna "Visual Basic Editor" med någon av metoderna nedan:

Genom att använda fliken Utvecklare excel.

Om fliken Utvecklare inte är tillgänglig i MS Excel kan vi få det genom att använda följande steg:

Right-click anywhere on the ribbon then, Choose the Customize the Ribbon in excel‘.

When we choose this command, the “Excel Options” dialog box opens.

We need to check the box for “Developer” to get the tab.

By using the shortcut key, i.e., Alt+F11.

When we open the VBA editor, we need to insert the module by going to the Insert menu and choosing a module.

We need to paste the following code into the module.

Function 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" ElseIf Total>= 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If End Function

The above function returns the result for a student.

We need to understand how this code is working.

The first statement, ‘Function ResultOfStudents(Marks As Range) As String,’ declares a function named ‘ResultOfStudents’ that will accept a range as input for marks and will return the result as a string.

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