**Excel VBA Function (Table of Contents)**

We have seen that we can use the worksheet functions in VBA, i.e. the functions of excel worksheet in VBA coding using application. worksheet method, but how do we use a function of VBA in excel, well such functions are called user-defined functions, when a user creates a function in VBA it can be also used in excel worksheet.

## What are VBA Functions in Excel?

Although we have many functions in Excel to manipulate the data, sometimes we need to have some customization in the tools so that we can save our time as we do some task repeatedly. We have predefined function in Excel like SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH etc. but we do some tasks on daily basis for which a single command or function is not available in Excel, then by using VBA, we can create the custom excel VBA function which is called User Defined Functions (UDFs) in Excel.

### What does the VBA Functions do?

- They carry out certain calculations; and
- Return a value

In Excel VBA, while defining VBA function, we use the following syntax to specify the parameters and their data type.

Data type here is the type of data the variable will hold, it can hold any value (any data type or object of any class).

We can connect the object with its property or method by using the period or dot (.) symbol.

**How to Create Custom Excel Functions using VBA?**

Below is the example to create custom excel function using VBA.

### Example #1

Suppose, we have the following data from a school where we need to find the total marks scored by the student, result, and grade.

As to sum up the marks scored by an individual student in all subjects, we have in-built function i.e., SUM, but to find out the grade and result based on the criteria set out by the school is not available in the Excel by default.

This is the reason why we need to create user-defined functions in MS Excel.

First, we will find the total marks using the SUM function.

Drag the Formula to the rest of cells.

Now to find out the Result (Passed, Failed, or Essential Repeat), the criteria set by the school is that

- If the student has scored more than or equal to 200 as total marks out of 500 and the student is also not failed in any subject (has scored more than 32 in each subject) then a student is passed,
- If the student has scored more than or equal to 200 but the student is failed in 1 or 2 subjects then a student has got “Essential Repeat” in those subjects,
- If the student has scored either less than 200 or fails in 3 or more subjects then the student is failed.

To create a VBA function in excel named ‘ResultOfStudent’, we need to open “Visual Basic Editor” by using any of the methods below:

If the Developer tab is not available in the MS Excel, then we can get that by using the following steps:

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

When we choose this command, **“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 VB 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 < 33 Then

CountOfFailedSubject = CountOfFailedSubject + 1

End If

Next mycell

If Total >= 200 And CountOfFailedSubject <= 2 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 VBA 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 string.

‘ Dim mycell As Range

Dim Total As Integer

Dim CountOfFailedSubject As Integer’

These three statements **declares 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 for 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 ‘**Marks’** range and adds the value of every cell in ‘**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 <= 2 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 pass the **‘Essential Report’**, **‘Passed’** or **‘Failed’** accordingly to the **‘ResultOfStudents’**.

### Example #2

Now to find out the grade for the student, we will create one more vba function in excel named **‘GradeForStudent’**. The code would be:

Function GradeForStudent(TotalMarks As Integer, Result As String) As String

If TotalMarks > 440 And TotalMarks <= 500 And ((Result = “Passed” Or Result = “Essential Repeat”) Or Result = “Essential Repeat”) Then

GradeForStudent = “A”

ElseIf TotalMarks > 380 And TotalMarks <= 440 And (Result = “Passed” Or Result = “Essential Repeat”) Then

GradeForStudent = “B”

ElseIf TotalMarks > 320 And TotalMarks <= 380 And (Result = “Passed” Or Result = “Essential Repeat”) Then

GradeForStudent = “C”

ElseIf TotalMarks > 260 And TotalMarks <= 320 And (Result = “Passed” Or Result = “Essential Repeat”) Then

GradeForStudent = “D”

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

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 rest of Cells.

The gradeforstudent function takes Total marks (sum of marks) and result of the student as an argument to calculate the grade.

Now Select the respective cells i.e **G2,H2**

Now we just need to press **Ctrl+D** after selecting the cells to copy down the formulas.

We can highlight the values less than 33 with the red background color so that we find out the subjects in which student is failed.

You can download these VBA Functions Excel Template – VBA Functions Excel Template

### Recommended Articles

This has been a guide to VBA Functions in Excel. Here we discuss how to create custom excel functions using VBA along with practical examples and downloadable excel template. You may learn more about VBA from the following articles –

- VBA IF vs IIF Condition
- Excel VBA ArrayList
- Proper Formula in Excel
- Examples of SUMIF With VLOOKUP
- SUMIF Between Two Dates
- 1004 Error in VBA
- Subroutine in VBA
- For Next Loop in Visual Basic
- Delete Row with VBA

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion