Funkcie VBA Sprievodca vytvorením vlastnej funkcie pomocou VBA

Obsah

Funkcie VBA programu Excel

Funkcie VBA programu Excel

Videli sme, že môžeme použiť funkcie listu vo VBA, tj. Funkcie listu Excel v kódovaní VBA pomocou metódy application.worksheet, ale ako použijeme funkciu VBA v programe Excel, tieto funkcie sa nazývajú funkcie definované používateľom, keď používateľ vytvorí funkciu vo VBA, môže sa použiť aj v pracovnom hárku programu Excel.

Aj keď máme v Exceli veľa funkcií na manipuláciu s údajmi, niekedy musíme mať v nástrojoch nejaké prispôsobenie, aby sme si mohli ušetriť čas opakovanými úlohami. V programe Excel máme preddefinované funkcie ako SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH v programe Excel atď., Ale niektoré úlohy robíme denne, pre ktoré nie je v Exceli k dispozícii jeden príkaz alebo funkcia, potom pomocou VBA môžeme vytvoriť vlastnú funkciu, ktorá sa nazýva User Defined Functions (UDF).

Čo robia funkcie VBA?

Vykonávajú určité výpočty; a
Vrátiť hodnotu

Vo VBA pri definovaní funkcie používame nasledujúcu syntax na špecifikáciu parametrov a ich dátového typu.

Typ údajov je typ údajov, ktoré bude premenná obsahovať. Môže obsahovať ľubovoľnú hodnotu (akýkoľvek údajový typ alebo objekt akejkoľvek triedy).

Objekt môžeme spojiť s jeho vlastnosťou alebo metódou pomocou symbolu bodky alebo bodky (.).

Ako vytvoriť vlastné funkcie pomocou VBA?

Príklad

Predpokladajme, že máme nasledujúce údaje zo školy, kde musíme zistiť celkový počet známok, ktoré dosiahol študent, výsledok a známku.

Ak chceme zhrnúť známky, ktoré jednotlivý študent zaznamenal vo všetkých predmetoch, máme zabudovanú funkciu, tj SUM, ale zistiť známku a výsledok na základe kritérií stanovených školou nie je v programe Excel predvolene k dispozícii .

To je dôvod, prečo potrebujeme vytvoriť funkcie definované používateľom.

Krok 1: Nájdite celkový počet známok

Najskôr pomocou programu SUMA v programe Excel nájdeme celkové známky.

Výsledok získate stlačením klávesu Enter.

Potiahnite vzorec do zvyšku buniek.

Teraz, keď chcete zistiť výsledok (úspešný, neúspešný alebo nevyhnutné opakovanie), sú kritériá stanovené školou.

Ak študent z celkového počtu 500 bodov dosiahol celkovú známku 200 alebo viac, a taktiež neuspel v žiadnom predmete (z každého predmetu dosiahol viac ako 32 bodov), potom študent získa
Ak študent dosiahol skóre 200 alebo viac, ale študent neuspel v 1 alebo 2 predmetoch, potom získal v týchto predmetoch „nevyhnutné opakovanie“,
Ak študent dosiahol skóre menej ako 200 alebo neuspel v 3 alebo viacerých predmetoch, potom študent neuspel.

Krok 2: Vytvorte funkciu ResultOfStudent

Ak chcete vytvoriť funkciu s názvom 'ResultOfStudent', musíme otvoriť program „Visual Basic Editor“ pomocou ktorejkoľvek z nasledujúcich metód:

Pomocou karty Vývojár vynikajte.

Ak karta Vývojár nie je k dispozícii v programe MS Excel, môžeme ju získať pomocou nasledujúcich krokov:

Kliknite pravým tlačidlom myši kdekoľvek na páse s nástrojmi a potom vyberte príkaz Prispôsobiť pásku v programe Excel ' .

Keď zvolíme tento príkaz, otvorí sa dialógové okno „Možnosti programu Excel“ .

Aby sme kartu dostali, musíme začiarknuť políčko „Vývojár“ .

Pomocou klávesovej skratky, tj. Alt + F11.

Keď otvoríme editor VBA, musíme vložiť modul tak, že prejdeme do ponuky Vložiť a vyberieme modul.

Nasledujúci kód musíme vložiť do modulu.

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" Celkom> = 200 a CountOfFailedSubject = 0 Potom ResultOfStudents = "Úspešne" Else ResultOfStudents = "Neúspešne" Koniec, ak je funkcia konca

Vyššie uvedená funkcia vráti výsledok pre študenta.

Musíme pochopiť, ako tento kód funguje.

Prvý príkaz „Function ResultOfStudents (Marks As Range) As String“ deklaruje funkciu s názvom „ResultOfStudents“, ktorá prijme rozsah ako vstup pre značky a vráti výsledok ako reťazec.

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

Tieto tri výroky deklarujú premenné, tj.

'myCell' ako Range,
'Celkom' ako celé číslo (na uloženie celkových známok dosiahnutých študentom),
„CountOfFailedSubject“ ako celé číslo (na uloženie počtu predmetov, v ktorých študent zlyhal).

Pre každú mycell v značkách Total = Total + mycell.Value If mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell

Tento kód skontroluje každú bunku v rozsahu „ Známky“ a pridá hodnotu každej bunky do premennej „ Celkom“. Ak je hodnota bunky menšia ako 33, potom do premennej „CountOfFailedSubject“ pridá 1 .

Ak Celkom> = 200 A CountOfFailedSubject 0, potom ResultOfStudents = "Základné opakovanie" ElseIf Celkom> = 200 A CountOfFailedSubject = 0 Potom ResultOfStudents = "Úspešne" Else ResultOfStudents = "Zlyhané" Koniec Ak

Tento kód kontroluje hodnotu „Total“ a „CountOfFailedSubject“ a odovzdáva „ Essential Report“, „Passed“ alebo „Failed“ zodpovedajúcim spôsobom „ResultOfStudents“.

Krok 3: Použite funkciu ResultOfStudents na získanie výsledku

Funkcia ResultOfStudents vezme známky, tj výber 5 známok, ktoré študent ohodnotil.

Teraz vyberte rozsah buniek, tj. B2: F2.

Pretiahnite vzorec do zvyšku buniek.

Krok 4: Vytvorením funkcie „GradeForStudent“ získate známky

Teraz, aby sme študentovi zistili známku, vytvoríme ešte jednu funkciu s názvom „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.’