VBA Functions

Publication Date :

Blog Author :

Download FREE VBA Functions Excel Template and Follow Along!
VBA Functions Excel Template.xlsm

Table Of Contents

arrow

Excel VBA Functions

We have seen that we can use the worksheet functions in VBA, i.e., the Excel worksheet functions in VBA coding using the 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, we can use it in an Excel worksheet.

Although we have many functions in Excel to manipulate the data, sometimes we need to have some customization in the tools to save time as we do some tasks repeatedly. For example, we have predefined functions in excel like SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH in excel, etc., but we do some tasks daily for which a single command or function is not available in Excel. Using VBA, we can create the User Defined Functions (UDF) custom function.

VBA Functions
You are free to use this image on your website, templates, etc.. Please provide us with an attribution link.

What do VBA Functions do?

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

In VBA, while defining the 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 (data type or class object).

Marks as Integer

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

How to Create Custom Functions using VBA?

Example

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

VBA Function Example 1

To sum up, the marks scored by an individual student in all subjects, we have an inbuilt function, SUM. But, determining the grade and result based on the criteria set out by the school is not available in Excel by default.

It is the reason why we need to create user-defined functions.

Step 1: Find Total Marks

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

VBA Function Example 1-1

Then, press the "Enter" key to get the result.

VBA Function Example 1-2

Drag the formula to the rest of the cells.

VBA Function Example 1-3

Now, to find out the result (passed, failed, or essential repeat), the criteria set by the school is that.

  • Suppose a student has scored more than or equal to 200 as total marks out of 500. In addition, suppose they have also not failed in any subject (scored more than 32 in each subject), a student is passed.
  • If the student scored more than or equal to 200 but failed in 1 or 2 subjects, then a student will get ā€œEssential Repeatā€ in those subjects.
  • If the student has scored either less than 200 or fails in 3 or more subjects, then the student failed.
Step 2: Create ResultOfStudent Function

To create a function named ā€˜ResultOfStudent,ā€™ we need to open ā€œVisual Basic Editorā€ by using any of the methods below:

VBA Function Example 1-4

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

VBA Function Example 1-5

When we choose this command, the ā€œExcel Optionsā€ dialog box opens.

  • We need to check the box for ā€œDeveloperā€ to get the tab.
VBA Function Example 1-6
  • Using the shortcut key, Alt+F11.
VBA Function Shortcut Key
  • When we open the VBA editor, we need to insert the module by going to the Insert menu and choosing a module.
VBA Function Example 1-7
  • 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
VBA Function Example 1-8

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 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ā€™ is an Integer (to store the number of subjects 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. It adds the value of every cell in the ā€˜Totalā€™ variable. If the cell's value is less than 33, add 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 passes the ā€˜Essential Report,ā€™ ā€˜Passed,ā€™ or ā€˜Failedā€™ to the ā€˜ResultOfStudents.ā€™

Step 3: Apply ResultOfStudents Function to Get Result

ResultOfStudents function takes marks, i.e., selecting 5 marks scored by the student.

VBA Function Example 1-10

Now, select the range of cells, B2: F2.

Example 1-11

Drag the formula to the rest of the cells.

Example 1-13
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 <= 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.ā€™

VBA Function Example 1-9

We need to write the formula and open the brackets in cell H2. Press Ctrl+Shift+A to find out the arguments.

VBA Function Shortcut arguments

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

Example 1-14

Now, select the individual cells, G2 and H2.

Example 1-15

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

Example 1-16

We can highlight the values of less than 33 with the red background color to find out the subjects in which the student failed.

Example 1-17