Функции на VBA - Ръководство за създаване на персонализирана функция с помощта на VBA

Съдържание

Функции на Excel VBA

Функции на Excel VBA

Видяхме, че можем да използваме функциите на работния лист във VBA, т.е. функциите на работния лист на Excel в кодирането на VBA, използвайки метода application.worksheet, но как да използваме функция на VBA в Excel, а такива функции се наричат дефинирани от потребителя функции, когато потребителят създаде функция във VBA, тя може да се използва и в работен лист на Excel.

Въпреки че имаме много функции в Excel за манипулиране на данните, понякога трябва да имаме някои персонализации в инструментите, за да можем да спестим времето си, докато правим многократно някои задачи. Имаме предварително дефинирани функции в Excel като SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH в Excel и т.н. VBA, можем да създадем персонализирана функция, която се нарича потребителски дефинирани функции (UDF).

Какво правят VBA функциите?

Те извършват определени изчисления; и
Връща стойност

Във VBA, докато дефинираме функцията, използваме следния синтаксис, за да зададем параметрите и техния тип данни.

Типът данни тук е типът данни, които променливата ще съдържа. Той може да съдържа всяка стойност (всеки тип данни или обект от всеки клас).

Можем да свържем обекта с неговото свойство или метод, като използваме символа точка или точка (.).

Как да създам персонализирани функции с помощта на VBA?

Пример

Да предположим, че имаме следните данни от училище, където трябва да намерим общия брой точки, отбелязани от ученика, резултата и оценката.

Що се отнася до обобщаването на оценките, отбелязани от отделен ученик по всички предмети, ние имаме вградена функция, т.е. .

Това е причината, поради която трябва да създадем дефинирани от потребителя функции.

Стъпка 1: Намерете общите марки

Първо, ще намерим общите оценки, използвайки функцията SUM в Excel.

Натиснете Enter, за да получите резултата.

Плъзнете Формулата до останалите клетки.

Сега, за да разберете резултата (преминат, неуспешен или съществен повтор), критериите, зададени от училището, са следните.

Ако студентът е набрал повече от или равен на 200 като общ бал от 500 и студентът също не е провален по нито един предмет (е набрал повече от 32 във всеки предмет), тогава студентът е преминат,
Ако студентът е набрал повече или равно на 200, но студентът не е успял по 1 или 2 предмета, тогава студентът има „Основно повторение“ по тези предмети,
Ако студентът е набрал или по-малко от 200, или се провали по 3 или повече предмета, тогава студентът е неуспешен.

Стъпка 2: Създайте функция ResultOfStudent

За да създадем функция с име „ResultOfStudent“, трябва да отворим „Редактор на Visual Basic“, като използваме някой от методите по-долу:

С помощта на раздела за програмисти Excel.

Ако раздела за програмисти не е наличен в MS Excel, тогава можем да го получим, като използваме следните стъпки:

Щракнете с десния бутон на мишката някъде върху лентата, след това изберете Персонализиране на лентата в Excel ' .

Когато изберем тази команда, се отваря диалоговият прозорец „Опции на Excel“ .

Трябва да поставим отметка в квадратчето за „Разработчик“, за да получим раздела.

Чрез използване на клавиш за бърз достъп, т.е. Alt + F11.

Когато отворим редактора на VBA, трябва да вмъкнем модула, като отидем в менюто Вмъкване и изберем модул.

Трябва да поставим следния код в модула.

Функция ResultOfStudents (Маркира като обхват) Като низ Dim мицела като диапазон Dim Общо като цяло Цяло Dim CountOfFailedSubject Като цяло за всяка мицела в марки Общо = Общо + mycell.Value Ако mycell.Value = 200 И CountOfFailedSubject 0 Тогава ResultOfStucialIf = "Esult" Общо> = 200 и CountOfFailedSubject = 0 Тогава ResultOfStudents = "Преминали" В противен случай ResultOfStudents = "Неуспешно" Край, ако крайната функция

Горната функция връща резултата за ученик.

Трябва да разберем как работи този код.

Първият израз, „Функция ResultOfStudents (Маркира като диапазон) като низ“, декларира функция с име „ResultOfStudents“, която ще приеме диапазон като вход за марки и ще върне резултата като низ.

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