Excel VBA Functions

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, COUNTIFCOUNTIFThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more, SUMIFSUMIFThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more, COUNTIFS,”COUNTIFS,””COUNTIFSread more VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more, INDEX, MATCH in excelMATCH In ExcelThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more, 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 (UDFUDFUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.read more) custom function.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Functions (wallstreetmojo.com)

What do VBA Functions do?

  • They carry out certain calculations; andReturn 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 dataData Type Here Is The Type Of DataData type is the core character of any variable, it represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.read more the variable will hold. It can hold any value (data type or class object).

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.

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.

First, we will find the total marks using the SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more.

Then, press the “Enter” key to get the result.

Drag the formula to the rest of the cells.

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.

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

  • Using the Developer tab excel.Developer Tab Excel.Enabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more

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

  • Right-click anywhere on the ribbon. Then, choose to Customize the Ribbon in excelRibbon In ExcelRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.read more‘.

When we choose this command, the “Excel Options” dialog box opens.

  • We need to check the box for “Developer” to get the tab.

  • Using the shortcut key, Alt+F11.

  • When we open the VBA editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more, 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 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.’

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

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

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

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

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

Now, select the individual cells, G2 and H2.

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

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

This article is a guide to VBA Functions. Here, we discuss creating custom functions using VBA code, practical examples, and a downloadable Excel template. You may learn more about VBA from the following articles:-

  • VBA IIFVBA IIFThe “VBA IIF” condition evaluates the supplied expression or logical test and returns TRUE or FALSE as a result.read moreSUMIF With VLOOKUPSUMIF With VLOOKUPSUMIF is used to sum cells based on some condition, which takes arguments of range, criteria, or condition, and cells to sum. When there is a large amount of data available in multiple columns, we can use VLOOKUP as the criteria.read moreExcel SUMIF Between Two DatesExcel SUMIF Between Two DatesWhen we wish to work with data that has serial numbers with different dates and the condition to sum the values is based between two dates, we use Sumif between two dates. read moreExcel VBA Delete RowExcel VBA Delete RowIn VBA, to delete the command and to delete any rows together, the trick is that we give a single row reference if we need to delete a single row. However, for the multiple columns, we can provide numerous row references.read more