FREE Online Courses: Your Passport to Excellence - Start Now
In this tutorial, we will learn about VBA Functions like Return, call and Examples.
What is a Function?
The term function refers to a code snippet which performs the specified task provided in the code and it provides or returns a result. Functions help to carry out repetitive tasks such as reading the data from the database, performing required calculations etc.
If the user is developing a program that calculates the average sale of a company. The user can create a function that accepts the monthly sales. The function can then use the sales details to calculate the average and return the value.
Why use functions in Excel VBA?
The advantages of using functions are:
1. Break code into small modules
Source code lines of a program vary as per the requirement and objective of the program. A program can contain multiple thousand source code lines. As the code lines increase, the complexity of the program also increases. In order to reduce the complexity of the program, the user can make use of subroutines and break down the program into smaller modules.
2. Reusability of the code
If the user has a program which needs to access the multiple databases, then the user can create a function that handles the database interaction. Instead of writing the code every time, the user can call it whenever it’s required.
3. Functions are self-documenting
The functions and subroutines are called self documenting because a programmer can say what the program does by just looking at the name of the function or subroutine.
Function Vs Subroutine in VBA Excel
A subroutine allows the user to execute a set of code while a function returns a value.
Rules of naming functions
Before naming the subroutines and functions, follow the rules:
- The first character is a letter or an underscore.
- Do not use special characters or numerics such as 1, !, @, &, ., #
- The name should not be a reserved keyword as the compiler uses it for specific tasks.
- The function name should not contain space.
VBA Syntax for declaring a Function
Function Function_name(list of parameters) Statement1 . . . Statement_n End Function
VBA Syntax for declaring a sample Function
Private Function FunctionName (ByVal argument_1 As Integer, ByVal argument_2 As Integer) total=argument_1+argument_2 End Function
Code Explanation:
-
Private Function functionName() –
Here the sub declares a function named “functionName” and starts the body of the function. To specify the scope of the function, the keyword “private” is used.
-
ByVal argument_1 As Integer, ByVal argument_2 As Integer –
This declares two parameters of integer data type named argument_1 and argument_2
functionName=argument_1+argument_2
This evaluates the two numbers and assigns the value to the functionName.
End Sub –
This denotes the end of the function body.
Function demonstrated with Example:
Functions are also the same like subroutines. The one main difference between the function and subroutine is that the function returns a value whenever it is called whereas the subroutine does not return a value when it is called.
Now let’s see how to create a function which accepts two numbers and returns the product of the numbers.
The below steps are the processes on how to call functions in vba.
1: Design the user interface and set the properties as per the requirement for the user controls.
2: Add the function in the program.
3: Mention the client event code for the button which calls the subroutine.
4: Application testing.
Step by step process
Step – 1: Designing the user interface and setting the properties
Design the user interface by setting the name properties to btnDisplayProduct and the caption to DataFlair_Function.
Your GUI appears as follows:
Step 2: Adding the function.
Open the code window by clicking on the view code option under the developer tab. Add the following function code in the VBE editor
Code:
Private Function DataFlair_Function(ByVal firstnum As Integer, ByVal secondnum As Integer) DataFlair_Function = firstnum * secondnum End Function
Code Explanation:
-
Private Function DataFlair_Function –
This declares a private function “DataFlair_Function” that accepts two integer parameters.
-
ByVal firstnum As Integer, ByVal secondnum As Integer –
This declares two parameter variables and those are ‘firstnum’ and ‘secondnum’.
-
productNum = firstnum * secondnum –
This accepts the two integer values, multiplies it and assigns the product value to the productNum.
Step 3: Calling the function from the command button click event.
- Open the code window by clicking on the view code option under the developer tab.
- This opens the code editor window.
Add the following code in the code editor for the click event of the btnDisplayProduct command button.
Code:
Private Sub btnDisplayProduct ( ) MsgBox DataFlair_Function (2,100) End Sub
Code Explanation
-
MsgBox DataFlair_Function(2,100) –
This calls the function DataFlair_Function and passes in 2 and 100 as the parameters. The function returns the product of the two numbers i.e., 200.
The first part of the code lines are the function code and the second part of the code lines are the click event code which calls the function from the command button.
Press the save button to save the changes and close the editor window.
Step 4: Testing the Application
To test the application, follow the steps:
Step 1: Go to the Developer tab.
Note: Check whether the design mode is on or off. If it is on, then the application wouldn’t work.
Step 2: Click on the Design Mode to turn it off.
Step 3: Click on the newly created button.
The following output appears.
User Defined Functions in Excel
Excel has many collections of functions. In many situations those functions are more than enough to complete the task. If not, then the user can create a own function and that is called a user defined or custom excel function. The user can access a user defined function just like any other excel function.
Now, let’s create a function called SUM_ODD that finds the sum of the odd numbers of a randomly selected range.
Code:
Function SUM_ODD(rng As Range) Dim cell As Range For Each cell In rng If cell.Value Mod 2 = 0 Then SUM_ODD= SUM_ODD + cell.Value Next cell End If End Function
Code Explanation:
Here, the code works on any cell on the worksheet and it sums up all the odd values in the cell range as we have not mentioned any specific cell range.
Note: Any function written and accessed by the user, apart from the built in functions are known as user defined functions.
Understanding the Scope of a User Defined Function in Excel
There are two scopes in a function and they are:
- Public Scope
- Private Scope
1. Public scope
In public scope, the function is available for all the sheets in the workbook as well as all the procedures across all modules in the sheet. This is useful when the user wants to call a function from a subroutine.
2. Private Scope
In private scope, the function is available only in the module in which it is provided. The user can’t use it in other modules. The user won’t see it in the list of functions in the spreadsheet. For example, if the function name is ‘ DataFlair()’, and the user type function in excel after the “=” sign, it will not show the function name. But still the user can use it if they enter the formula name.
Calling an User Defined Function from Other Workbooks
If the user has a function in a workbook, then the user can call the function in other workbooks also. There are different ways to do it and they are:
- Create an Add-In
- Save the function in Personal Macro Workbook
- Refer the function from another worksheet
1. Create an Add-in
After creating and installing an add-in, the user will have the custom function in it available in all the workbooks.
Imagine the user has created a custom function – ‘GetNumeric’ and the user wants it in all the workbooks. In order to do that the user has open a new workbook and have the function code in a module in the new workbook. Now, follow the steps to install the function in Excel.
1: Go to the File tab and click on the “Save As” option.
2: Change the file extension to .xlam.
Note: The name the user assigns to the file will be the name of the add-in. The user can also notice that the path of the file where it gets saved automatically changes.
3: Open a new Excel workbook
4: Click on the Excel Add-ins option under the Developer tab.
5: In the Add-ins dialogue box, browse and locate the file that you saved with the function.
6: After check marking on the function, click the ok button.
Note: Finally, the add-in has been activated and the user can use the custom function in all the workbooks.
2. Referencing the function from another workbook
If the user wants to refer to the function from another workbook, then that workbook has to be opened.
3. Syntax to refer a function from another workbook:
= ‘Workbook_Name’!Function_Name(Cell_number)
Here, in the workbook_name place the user has to place the workbook name of the function. Function_name refers to the name of the function and the cell number denotes the cell number.
Excel Inbuilt Functions vs VBA User Defined Function
There are few advantages of using Excel in-built functions over custom functions created in VBA and they are:
- VBA functions are quite slower than the inbuilt functions.
- When the user creates a report using VBA functions, and if they send it to another user, then they need not have to worry about whether the macros are enabled or not.
- Note: In some cases, the other users get scared by looking at the warning symbol in the yellow bar and that warning is simply a request to the user to enable the macros.
- With inbuilt Excel functions, the user doesn’t need to worry about the file extensions. If the user has macros or user-defined functions in the workbook, then they need to save it in .xlsm.
- It’s better to use a user-defined function if the inbuilt formula is huge and complicated.
- When the user has to get something specifically done then that can not be done by Excel inbuilt functions.
Volatile Functions in VBA
The user defined functions in excel are not volatile by default. When any of the arguments in the function changes, they perform the task and recalculate again. A volatile function recalculates, whenever calculation occurs in any part of the worksheet. Let’s look at a sample.
Code:
Function DATAFLAIR(cell As Range) DATAFLAIR = cell.Value + cell.Offset(1, 0).Value Application.Volatile End Function
Code Explanation:
Here, we have created a function called DATAFLAIR and this function returns the sum of the selected cell and the cell below that particular selected cell.
Note: The code is non volatile when you don’t enter the “Application. Volatile” statement in the code. The non-volatile function is not recalculated when any other cell on the sheet changes and it recalculates only when the arguments in the function changes.
Output:
The “DATAFLAIR(A2)” is called in the D8 cell and this is how we call the function generally in the sheet.
Note: If the user first creates the non-volatile function then the user needs to enter the function again to make it volatile after adding the required statements in the code.
By Ref and ByVal
The user can pass the arguments to a function procedure by reference or by value. The VBA passes arguments by reference as default. Let’s look at a sample.
Code:
Dim num As Integer num = 2 MsgBox Double(num) MsgBox num
Code Explanation:
Here, the code calls the function num. It’s the result of the second MsgBox that we are looking for and this function needs to be placed into a module. Now, open the module and add the below function code.
Function Code:
Function Double(ByRef num As Integer) As Integer num = num * num Double = num End Function
Output:
The following output appears when the user clicks on the command button.
ByVal:
Now, let’s see an example of replacing ByRef with ByVal.
Code:
Function calc(ByVal num As Integer) As Integer num = num * num calc = num End Function
Code Explanation:
The user refers to the original value when the passing arguments are by reference. The value of num is generally changed in the function and as a result the second n=message box displays the changed value. If the user passes the arguments by value then they are passing a copy to the function and hence the original value does not change. As a result, the second message box displays the original value.
Output:
The following output appears when the user clicks on the command button.
Built-in VBA Functions
VBA provides a large number of built-in functions that can be used in your code. The most popular built-in VBA functions are listed below. Select a function name to go to a full description of the function with examples of use.
1. VBA Message Functions
| Function | Description |
| InputBox | It displays a dialog box where the user can enter the input |
| MsgBox | It displays a message box |
2. VBA Text Functions
| Function | Description |
| Format | It returns the result as a string and it applies a format to an expression. |
| InStr | It returns the substring position in a string. |
| InStrRev | It returns the substring position in a string searching from right to left. |
| Left | It returns the substring from the beginning of a provided string. |
| Len | It returns the length of the provided string. |
| LCase | It converts the string elements to lower text cases. |
| LTrim | It removes the extra spaces in the string. |
| Mid | It returns the substring from the middle of a string. |
| Replace | It replaces a substring with the new string. |
| Right | It returns a substring from the end of the string. |
| RTrim | It removes the extra spaces from the right side of the string. |
| Space | It creates a string consisting of a specified number space. |
| StrComp | It compares the two strings and returns the result in an integer representing the comparison. |
| StrConv | It converts a string to a particular format mentioned. |
| String | It creates a sequence of characters in a string. |
| StrReverse | It reverses the string. |
| Trim | It removes the spaces in the beginning and in the ending of the string. |
| UCase | It converts the string to upper case characters. |
3. VBA Information Functions
| Function | Description |
| IsArray | It tests whether the provided variable is an array or not. |
| IsDate | It tests whether the provided expression is a date or not. |
| IsEmpty | It tests whether the provided variant is empty or not. |
| IsError | It tests whether the provided expression is an error or not. |
| IsMissing | It tests whether an optional argument procedure is missing or not. |
| IsNull | It tests whether the expression is null or not. |
| IsNumeric | It tests whether the expression is numeric or not. |
| IsObject | It tests whether the variable represents an object variable. |
4. VBA Error Handling Functions
| Function | Description |
| CVErr | It produces an error data type for a provided error code. |
| Error | It returns the error message corresponding to the provided error code. |
5. VBA Program Flow Functions
| Function | Description |
| Choose | It selects a value from the argument list. |
| llf | It returns one of the two values by evaluating an expression. It evaluates to true or false. |
| Switch | It returns the value associated with the first true expression by evaluating a list of boolean expressions. |
6. VBA Conversion Functions
| Function | Description |
| Asc | It returns an integer representing the code for a provided character. |
| CBool | It converts an expression to a boolean data type. |
| CByte | It converts an expression to a byte data type. |
| CCur | It converts an expression to a currency data type. |
| CDate | It converts an expression to a date data type. |
| CDbl | It converts an expression to a double data type. |
| CDec | It converts an expression to a decimal data type. |
| Chr | It returns the character corresponding to the provided character code. |
| Clnt | It converts an expression to an integer data type. |
| CLng | It converts an expression to a long data type. |
| CSng | It converts an expression to a single data type. |
| CStr | It converts an expression to a string data type. |
| CVar | It converts an expression to a variant data type. |
| FormatCurrency | It converts the expression to currency format and returns the result as a string. |
| FormatDateTime | It converts the expression to date and time format and returns the result as a string. |
| FormatNumber | It converts the expression to number format and returns the result as a string. |
| FormatPercent | It converts the expression to percentage format and returns the result as a string. |
| Hex | It converts the numeric value to hexadecimal value and returns the result as a string. |
| Oct | It converts the numeric value to octal value and returns the result as a string. |
| Str | It converts a numeric value to a string. |
| Val | It converts a string to a numeric value. |
7. VBA Date and Time Functions
| Function | Description |
| Date | It returns the current date. |
| DateAdd | It adds an interval between the date or time. |
| DateDiff | It returns the number of intervals between the two dates or time. |
| DatePart | It returns a part of a date or time |
| DateSerial | It returns the date from the provided date details. |
| DateValue | It returns a date from the string representation of a date or time. |
| Day | It represents the day number which is either from 1 to 31. |
| Hour | It returns the hour component in the time mentioned. |
| Minute | It returns the minute component in the time mentioned. |
| Month | It returns the month number which is either from 1 to 12. |
| MonthName | It returns the month name. |
| Now | It returns the current date and time. |
| Second | It returns the second component in the time mentioned. |
| Time | It returns the current time. |
| Timer | It returns the number of seconds that have elapsed. |
| TimeSerial | It returns the time from a provided hour, minute and second. |
| TimeValue | It returns the time from a string representation. |
| Weekday | It returns an integer from 1 to 7 representing the weekday. |
| WeekdayName | It returns the name of the weekday. |
| Year | It returns the year of the provided date. |
8. VBA Math and Trig Functions
| Function | Description |
| Abs | It returns the absolute value. |
| Atn | It returns the arctangent value of a provided number. |
| Cos | It evaluates the cosine value of the provided angle. |
| Exp | It calculates the value of e to the power of x for the provided value of x. |
| It rounds the negative numbers towards zero. | |
| This returns the integer. | |
| Log | It calculates the natural logarithm value. |
| Rnd | It generates a random number between 0 and 1. |
| Round | It rounds a number to a mentioned number of decimal places. |
| Sgn | It returns an integer representing the arithmetic signs. |
| Sin | It evaluates the sine value. |
| Tan | It evaluates the tangent value. |
| Sqr | It returns the square root value. |
9. VBA Financial Functions
| Function | Description |
| DDB | It calculates the depreciation of an asset using the double declining method during a mentioned period. |
| FV | It calculates the future value of an investment. |
| IPmt | It calculates the interest for a payment for a loan. |
| IRR | It calculates the internal rate of return for periodic cash flows. |
| MIRR | It calculates the modified internal rate of return for periodic cash flows. |
| NPer | It calculates the number of periods for an investment. |
| NPV | It calculates the net present value of an investment. |
| Pmt | It calculates the constant periodic payment for an investment. |
| PPmt | It calculates the principal of a payment during a mentioned period for an investment. |
| PV | It calculates the present value. |
| Rate | It calculates the interest rate for an amount. |
| SLN | It calculates the straight line depreciation of an asset. |
| SYD | It calculates the sum of years depreciation for a mentioned period in the lifetime of an asset. |
10. VBA Financial Functions
| Function | Description |
| DDB | It calculates the depreciation of an asset using the double declining method during a mentioned period. |
| FV | It calculates the future value of an investment. |
| IPmt | It calculates the interest for a payment for a loan. |
| IRR | It calculates the internal rate of return for periodic cash flows. |
| MIRR | It calculates the modified internal rate of return for periodic cash flows. |
| NPer | It calculates the number of periods for an investment. |
| NPV | It calculates the net present value of an investment. |
| Pmt | It calculates the constant periodic payment for an investment. |
| PPmt | It calculates the principal of a payment during a mentioned period for an investment. |
| PV | It calculates the present value. |
| Rate | It calculates the interest rate for an amount. |
| SLN | It calculates the straight line depreciation of an asset. |
| SYD | It calculates the sum of years depreciation for a mentioned period in the lifetime of an asset. |
11. VBA Array Functions
| Function | Description |
| Array | It creates an array containing a provided set of values. |
| Filter | It returns the subset of a provided string based on the criteria. |
| Join | It joins the number of substrings into a single string. |
| LBound | It returns the lowest subscript of an array. |
| Split | It splits a text string into a number of substrings. |
| UBound | It returns the highest subscript of an array. |
12. VBA File Management Functions
| Function | Description |
| CurDir | It returns the current path as a string. |
| Dir | It returns the directory name that matches the mentioned pattern and attributes. |
| FileAttr | It returns the mode of the file that has been opened. |
| FileDateTime | It returns the date when the file was lastly modified. |
| FileLen | It returns the length of the provided file. |
| GetAttr | It returns an integer representing the attributes of a provided file or directory. |
12. Lookup or Reference Function
| Function | Description |
| Choose | It returns a value from the list based on the position. |
13. File or Directory Functions
| Function | Description |
| ChDir | This function helps to change the current directory or folder. |
| ChDrive | This function helps to change the current drive. |
| CurDir | It returns the current path. |
| MkDir | This function helps to create a new folder or directory. |
| SetAttr | This function helps to set the attributes of a file. |
14. Logical Functions
| Function | Description |
| AND | It evaluates to true if all conditions are true or else it returns false. |
| CASE | This function has the functionality of an IF-THEN-ELSE statement |
| FOR..NEXT | This function helps to create the “For” loop. |
| IF-THEN-ELSE | It returns a value if a specified condition evaluates to true or else the other value if it evaluates to false. |
| OR | It returns true if any of the conditions is met and it returns false if no condition is met in the criteria. |
| SWITCH | This function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is “true”. |
| WHILE..WEND | This function helps to create the “while” loop. |
15. Related Page
| Function | Description |
| Vlookup in VBA | It uses the vlookup function in excel from within VBA. |
Types of Arguments
There are different types of function with or without any arguments. Let’s see some samples of it in different categories.
A. Creating a Function in VBA without Any Arguments
In the Excel worksheet, the user finds several functions that take no argument such as RAND, TODAY, NOW etc.
These functions do not depend on any input arguments. For example, the RAND function will return a random number between 0 and 1.
Note: The user can create such similar functions in VBA also.
Let’s see a sample.
Code:
Function WBName() As String Application.Volatile True WBName = ThisWorkbook.Name End Function
Code Explanation:
Here, this code will provide the name of the file and it doesn’t take any arguments as it is not dependent on any argument. The function returns the result in a string data type.
Note: If the user has saved the file then, it returns the name with the file extension or else it simply provides the name of the file.
B. Creating a Function in VBA with One Argument
Let’s create a function where it takes only one argument in it. \
Code:
Function UpperCase (CellRef As Range) UpperCase = UCase(CellRef) End Function
Code Explanation:
Here, the function would convert the referenced text into uppercase. Though we already have a function for it in Excel, this is just a function to show you how it performs with one argument. In this function, we use the UCase function in VBA to convert the value of the CellRef variable. It then assigns the value to the function UpperCase.
C. Creating a Function in VBA with Multiple Arguments
Just like other worksheet functions, the user can create functions in VBA which take multiple arguments.
Code:
Function GetDataUsingDelimiter(CellRef As Range, Delim As String) as String Dim Output As String Dim De_Position As Integer De_Position = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Output = Left(CellRef, DelimPosition) GetDataUsingDelimiter = Output End Function
Code Explanation:
Here, the code would create a function that will extract the text before the specified delimiter. It takes two arguments and those are the cell reference that has the text string, and the delimiter.
Note: When the user needs to use more than one argument in a user-defined function, the user can have all the arguments separated by a comma in the parenthesis.
D. Creating a Function in VBA with an Optional Arguments
In Excel, there are many functions where some of the arguments are not really necessary, which we call optional arguments.
For example, the vlookup function has three mandatory arguments and one optional argument.
Note: The function with optional arguments works properly even if we don’t specify the optional arguments but if we don’t enter the mandatory arguments then the function is going to give you an error. But optional arguments are also useful as they allow the user to choose from a range of options.
For example: In the VLOOKUP function, if the user doesn’t specify the fourth argument, the function does an approximate lookup and if the user specifies the last argument as FALSE or 0, then it does an exact match.
E. Function with only Optional Argument
It is not easy to create a function only with the optional arguments. Eventually, there are no built in functions like that until now. But the user can create it with VBA.
Code:
Function CurrTime(Optional frmt As Variant) Dim Result If IsMissing(frmt) Then CurrTime = Format(Time, "Hour-Minute-Second") Else CurrTime = Format(Time, "Hour:Minute:Second") End If End Function
Code Explanation:
Here in this code, the user uses the “IsMissing” to check whether the argument is missing or not. But if the user wants to use that particular statement then the argument must be of the variant data type.
The above code creates a function that shows the time in the “ Hour-Minute-Second ” format if no argument is generally supplied and in “Hour-Minute-Second” format when the argument is 1.
F. Function with Required as well as Optional Arguments
Now, we are going to create a function with the required as well as with the optional arguments in the function.
Code:
Function GetDataInText(CellRef As Range, Optional TextCase = False) As String Dim DataLength As Integer Dim Output As String DataLength = Len(CellRef) For i = 1 To DataLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Output = Output & Mid(CellRef, i, 1) Next i If TextCase = True Then Result = UCase(Output) GetDataInText = Result End Function
Code Explanation:
Here, the code creates a function that separates the text part from a string. The results appear in uppercase if the optional argument is true or if it is omitted or false then it returns the result as is.
G. Creating a Function with Indefinite Number of Arguments
If a user is creating a new function then they might not be sure about the number of arguments that has to be supplied. Hence the requirement is to create a function that can accept as many arguments that are supplied and utilise them to return the result in the worksheet.
An example of such a worksheet function is the PRODUCT function. The user can send multiple arguments to it such as
=PRODUCT(A1,A5:A10,B1:B10)
The above function would multiply the values.
Note: The keyword ‘ParamArray’ should be used before the optional argument. ‘ParamArray’ is a modifier that allows the user to accept as many arguments as they want.
Now let’s create a function that accepts an arbitrary number of arguments and would multiply all the numbers in the specified arguments:
Code:
Function MultArguments(ParamArray arglist() As Variant) For Each arg In arglist MultArguments = MultArguments + arg Next arg End Function
Code Explanation:
The above function can take any number of arguments and multiply these arguments to give the result.
H. Creating a Function that Returns an Array
The user can create a function that returns a variant that can contain an entire array of values in VBA. There are inbuilt functions to implement array formulas also.
Let’s see a sample
Code:
Function FourNumbers() As Variant Dim NumberValue(1 To 4) NumValue(1) = 1 NumValue(2) = 2 NumValue(3) = 3 NumValue(4) = 4 FourNumbers = NumValue End Function
Code Explanation:
Here, the user has specified the ‘FourNumbers’ function as a variant. This allows the user to hold an array of values. The variable ‘NumValue’ is declared as an array with four elements. It holds the four values and assigns it to the ‘FourNumbers’ function.
I. Exit Function
If the user creates a function that tests for a certain condition, and once the condition is found to be true, then they always set up a return value from the function. There are chances for them to add an Exit Function statement in the function in order to exit the function before the user has run through all the code in that particular function.
Let’s look at a sample.
Code:
Function FindNum(strSearch As String) As Integer Dim n As Integer For n = 1 To Len(strSearch) If IsNumeric(Mid(strSearch, n, 1)) Then FindNum= Mid(strSearch, n, 1) Exit Function End If Next FindNum= 0 End Function
Code Explanation:
The function will loop through the string until it finds a number and then returns the number from the provided string. The function will find only the first number in the string as it will then exit the function.
Things to remember
- The user can use the built in functions in VBA for automating the process.
- While allocating and applying multiple macros, make sure you have assigned specific unique keys to run the macros.
- To avoid the confusion, name each module in VBA.
Summary:
- A piece of code which performs some specific task is known as functions and it returns a value after execution.
- Functions help the user to break the code into smaller modules for better understanding of the code.
- The functions and subroutines can be called as many times as required and this saves a lot of time for the user from writing the same code repeatedly.