What are Excel VBA Functions?
A VBA function is used to perform a task and return the result. We can create a user-defined function using the VBA function, which will be used in Excel like any other Excel function. It allows the users to write code in a compact block, name it, and return a value.
Let us see a simple example of a user-defined function. Here, we gave the function a name and used it to multiply two numbers passed as arguments. Open VBA, click the Insert tab to open a new module, and write the code below. Go to the active Excel worksheet and use the = operator to call the function “Multiplication” to multiply the integers and return the result.
Code:
Function Multiplication(X, y)
Multiplication = X * y
End Function
Table of contents
Key Takeaways
- VBA functions are in-built in Excel, or we can create a function using VBA, which will return the value as a result.
- The function is typically declared as follows:
Function Function_name(parameter-list)
statement 1
statement 2
……
statement n
End Function
- Once the function is created, we can apply the function in our active worksheet.
- Besides user-defined functions, VBA has multiple categories of in-built functions, which include Math/Trig, String/Text, and Conversions.
What do VBA Functions do?
- VBA functions take a series of arguments, perform a task, and return a value after the function is executed.
- VBA functions allow the user to customize and place a function on the right side of an expression, like intrinsic functions such as SQR.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Create Custom Functions using VBA?
Let us understand this by taking an example to find the total marks, results, and Grade scored by some students. The total marks can be obtained in Excel using the in-built SUM function. First, however, the Result and Grade functions need to be written. Look for the below steps to see how it’s done:
Step 1: Open an Excel worksheet and write the data as shown below:
Step 2: Use the Excel inbuilt Function “SUM” to calculate the total marks. Use =SUM(B2:F2) in cell G2 and press Enter for this. Once you get the total marks, drag the formula down to the remaining cells up to G11.
Step 3: Now, let us assume the following criteria given by the school for the result:
- For Pass: If a student scores 200 or more total marks out of 500.
- For Fail: If the total score is less than 200.
- For Exam repeat: Suppose a student scores total marks >200 but is failed in two or more subjects.
Thus, to create a Function named “ResultOfStudent” in VBA, follow the further steps.
Step 4: Click the Developer option and select Visual Basic Application (VBA). Here, click Insert and open a new Module.
If the Developer option is missing in your Excel, then follow the below steps to activate the Developer tool in your Excel.
Open the Excel sheet and click on the Excel Ribbon anywhere. Next, select the “Customize the Ribbon” option.
In the “Customize the Ribbon” option dialog box, check the “Developer” option.
To open the VBA options, go to the “Developer” tab and click the “Visual Basic” option.
After clicking the “Visual Basic” option, a “Microsoft Visual Basic for Applications” window will open. Next, click “Insert” and select “Module.” Finally, you are ready to type your VBA code into the VBA editor.
Now let’s get back to our steps for creating a function.
Step 5: Write the VBA code with a sub-procedure in the VBA editor. Assign the name “ResultOfStudents” to the function and define it as a String datatype, as it should return a result as a string.
Further, define variables mycell as Range, Total as Integer, and CountOfFailedSubjects as Integer.
Step 6: Write the code below to check the marks range for every cell. Further, the code adds the value of each cell in the ‘Total’ variable. Write the following condition:
If the cell’s value is <35, a count of one is added to the CountOfFailedSubject variable.
Step 7: The below code checks the value of Total, CountOfFailedSubjects, Exam Repeat, Passed or Failed within the function, ResultOfStudents.
Code:
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 < 35 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell If Total <= 200 And CountOfFailedSubject >= 2 Then
ResultOfStudents = “Failed “
ElseIf Total >= 200 And CountOfFailedSubject = 0 Then
ResultOfStudents = “Passed”
Else
ResultOfStudents = “Repeat Exam”
End If
End Function
Step 8: We will apply this function in our active Excel worksheet.
Step 9: Enter the created function and drag the result to the rest of the cells.
As observed, based on our code and conditions provided, the result of all the students are obtained.
To find these students’ grades, we create a Function, “GradeForStudent.”
Step 10: Before writing a code, we must see the condition for the Grades. Please refer Table.1 for the same.
Table.1: Condition to obtain Grades for students.
Total Marks | Result | Grade |
---|---|---|
400-450 | Passed | A |
350-400 | Passed | B |
300-350 | Passed | C |
250-300 | Passed | D |
200-250 | Passed | E |
<200 | Failed | Failed |
Step.11: Write the below code and run it.
Code:
Function GradeForStudent(TotalMarks As Integer, Result As String) As String
If TotalMarks > 450 And TotalMarks <= 500 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “A+” ElseIf
TotalMarks > 400 And TotalMarks <= 450 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “A” ElseIf
TotalMarks > 350 And TotalMarks <= 400 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “B” ElseIf
TotalMarks > 300 And TotalMarks <= 350 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “C” ElseIf TotalMarks > 250 And TotalMarks <= 300 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “D” ElseIf TotalMarks > 200 And TotalMarks <= 250 And (Result = “Passed” Or Result = “Repeat Exam”) Then
GradeForStudent = “E” ElseIf
TotalMarks < 200 Or Result = “Failed” Then
GradeForStudent = “F”
End If
End Function
As observed, the total marks and results are checked, and the grades are printed accordingly.
Step 12: We will apply the generated function “GradeOfStudent” in our worksheet.
Step 13: Press Enter and drag the formula to the rest of the cells.
Examples
Example #1
We will create a VBA Function named “AddThree” in this example.” Here, we can add any three desired numbers from the list of multiple numbers.
Step 1: Open a module in the VBA editor and write the below code:
Step 2: Apply the function “AddThree” in the Excel sheet.
Step 3: Press enter, and we can see the addition of three numbers, as shown in the image below.
Code:
Function AddThree(X, Y, Z As Integer) As Integer
AddThree = X + Y + Z
End Function
Example #2
Create a VolumeOfCuboid function to calculate a cuboid’s length, breadth, and height.
Step 1: Write the below code in a module in the VBA Editor under the Developer tab.
Step 2: Apply the ” VolumeOfCuboid ” function in the Excel sheet below.
Step 3: Enter and see the result as shown below. Drag the function to the next cell as well.
Code:
Function VolumeOfCuboid(X, Y, Z As Integer) As Integer
VolumeOfCuboid = X * Y * Z
End Function
Example #3
To write a GetNumeric function for extracting numbers from a text. Suppose we have a range of cell values and want to extract only a number from it. The manual process will be very tedious and time-consuming. However, we can perform that work in just one click by creating a VBA function, “GetNumeric.”
Step 1: Write the below code in the VBA Editor’s module:
Explanation:
Function GetNumeric(CellRef As String) As Long
This line starts with “Function” followed by the function name “GetNumeric.” Then, the “CellRef” will extract the numeric part from the Long.
Dim StringLength As Integer
Declares the variable as Integer.
StringLength = Len(CellRef)
Assigns the length to the string.
For X = 1 To StringLength
If IsNumeric(Mid(CellRef, X, 1)) Then Result = Result & Mid(CellRef, X, 1)
Next X
GetNumeric = Result
End Function
IF statement checks each character of the string; if it is numeric, it adds that numeric character to the Result variable.
Step 2: Apply the function to the Excel sheet in cell B1.
Step.3: Enter and see the result below.
In the above result, we can see that only the numbers are extracted from the cells.
Note: For all the examples to work without the #NAME Error, an Excel error, we must open an Excel sheet and a VBA module under the Developer tab. Paste the code provided, run the code, and then apply the function in the Excel sheet. The created function will show you the desired result.
Code:
Function GetNumeric(CellRef As String) As Long
Dim StringLength As Integer
Declares the variable as Integer.
StringLength = Len(CellRef)
Assigns the length to the string.
For X = 1 To StringLength
If IsNumeric(Mid(CellRef, X, 1)) Then Result = Result & Mid(CellRef, X, 1)
Next X
GetNumeric = Result
End Function
Important Things to Note
- VBA has multiple in-built functions to perform different tasks. However, useful VBA functions can also be created by the user (user-defined function) in Excel.
- We should define the function by assigning it a name to create basic VBA functions.
- To define a function, we must select a particular data type in VBA which is the data type of the returned result.
- VBA String Functions are very essential as it repeats a character multiple times and returns the exact string.
- A list of VBA functions with Strings includes:
- LEN Function
- LEFT Function
- RIGHT Function in VBA
- MID Function
- TRIM Function
Frequently Asked Questions (FAQs)
String VBA functions are in-built functions that form a new string by data manipulation. The output of this function returns the same string as the result. VBA String functions are either VBA string or Text.
The end function in VBA is used to End the code execution. The execution of the code immediately ends, and the control leaves the program.
No. A function considers the evaluated data, performs a task, and returns a result. In contrast, a macro assumes some unevaluated syntax and works on that.
The Boolean function is an in-built function in VBA. It declares variables as True or False. Therefore, it comes under logical functions.
Download Template
This article must help understand the VBA Functions, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to VBA Functions. Here we explain how to create and access Excel VBA functions, with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply