Funkce VBA - Průvodce vytvořením vlastní funkce pomocí VBA

Obsah

Funkce Excel VBA

Funkce Excel VBA

Viděli jsme, že můžeme použít funkce listu ve VBA, tj. Funkce listu aplikace Excel v kódování VBA pomocí metody application.worksheet, ale jak použijeme funkci VBA v aplikaci Excel, tyto funkce se nazývají uživatelem definované funkce, když uživatel vytvoří funkci ve VBA, může být také použit v listu aplikace Excel.

I když máme v Excelu mnoho funkcí pro manipulaci s daty, někdy musíme mít v nástrojích nějaké přizpůsobení, abychom mohli ušetřit čas opakovaným prováděním některých úkolů. V aplikaci Excel máme předdefinované funkce jako SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH v aplikaci Excel atd., Ale denně děláme některé úkoly, pro které není v aplikaci Excel k dispozici jediný příkaz nebo funkce, pak pomocí VBA můžeme vytvořit vlastní funkci, která se nazývá User Defined Functions (UDF).

Co dělají funkce VBA?

Provádějí určité výpočty; a
Vrátit hodnotu

Ve VBA při definování funkce používáme následující syntaxi k určení parametrů a jejich datového typu.

Datový typ je typ dat, který bude proměnná obsahovat. Může obsahovat jakoukoli hodnotu (jakýkoli datový typ nebo objekt jakékoli třídy).

Objekt můžeme spojit s jeho vlastností nebo metodou pomocí tečky nebo tečky (.).

Jak vytvořit vlastní funkce pomocí VBA?

Příklad

Předpokládejme, že máme následující data ze školy, kde potřebujeme zjistit celkový počet známek získaných studentem, výsledek a známku.

Abychom shrnuli známky dosažené jednotlivým studentem ve všech předmětech, máme vestavěnou funkci, tj. SUM, ale zjistit známku a výsledek na základě kritérií stanovených školou není ve výchozím nastavení k dispozici v aplikaci Excel .

To je důvod, proč musíme vytvářet uživatelsky definované funkce.

Krok 1: Najděte celkem známek

Nejprve pomocí aplikace SUM v Excelu najdeme celkové známky.

Výsledek získáte stisknutím klávesy Enter.

Přetáhněte vzorec do ostatních buněk.

Nyní, abyste zjistili výsledek (úspěšný, neúspěšný nebo základní opakování), jsou kritéria stanovená školou.

Pokud student zaznamenal více než nebo rovno 200 jako celkový počet známek z 500 a student také neprospěl v žádném předmětu (zaznamenal více než 32 v každém předmětu), je student úspěšně
Pokud student zaznamenal více než nebo rovno 200, ale student selhal v 1 nebo 2 předmětech, pak má student v těchto předmětech „Essential Repeat“,
Pokud student skóroval buď méně než 200, nebo neuspěl ve 3 nebo více předmětech, pak student neuspěl.

Krok 2: Vytvořte funkci ResultOfStudent

Chcete-li vytvořit funkci s názvem „ResultOfStudent“, musíme otevřít „Editor jazyka“ pomocí kterékoli z níže uvedených metod:

Pomocí karty Vývojář excel.

Pokud karta Vývojář není v MS Excel k dispozici, můžeme ji získat pomocí následujících kroků:

Klepněte pravým tlačítkem na libovolné místo na pásu karet a poté zvolte Přizpůsobit pás karet v aplikaci Excel ' .

Když zvolíme tento příkaz, otevře se dialogové okno „Možnosti aplikace Excel“ .

Abychom kartu dostali, musíme zaškrtnout políčko „Vývojář“ .

Pomocí klávesové zkratky, tj. Alt + F11.

Když otevřete editor VBA, musíme vložit modul tak, že přejdeme do nabídky Vložit a vybereme modul.

Do modulu musíme vložit následující kód.

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

Výše uvedená funkce vrací výsledek pro studenta.

Musíme pochopit, jak tento kód funguje.

První příkaz „Function ResultOfStudents (Marks As Range) As String“ deklaruje funkci s názvem „ResultOfStudents“, která přijme rozsah jako vstup pro značky a vrátí výsledek jako řetězec.

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

Tyto tři příkazy deklarují proměnné, tj.

'myCell' jako Range,
'Celkem' jako celé číslo (pro uložení celkového počtu známek získaných studentem),
'CountOfFailedSubject' jako celé číslo (pro uložení počtu předmětů, ve kterých student selhal).

Pro každou mycell ve značkách Total = Total + mycell.Value If mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell

Tento kód kontroluje každou buňku v rozsahu „ Marks“ a přidává hodnotu každé buňky do proměnné „ Total“ , a pokud je hodnota buňky menší než 33, přidá 1 do proměnné „CountOfFailedSubject“ .

If Total> = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If

Tento kód kontroluje hodnotu „Total“ a „CountOfFailedSubject“ a předává „ Essential Report“, „Passed“ nebo „Failed“ odpovídajícím způsobem „ResultOfStudents.“

Krok 3: Použijte funkci ResultOfStudents k získání výsledku

Funkce ResultOfStudents bere známky, tj. Výběr 5 známek, které student získal.

Nyní vyberte rozsah buněk, tj. B2: F2.

Přetáhněte vzorec do ostatních buněk.

Krok 4: Vytvořte funkci 'GradeForStudent', abyste získali známky

Nyní, abychom zjistili známku pro studenta, vytvoříme ještě jednu funkci s názvem '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.’