Excel VBA Functions with Examples

FREE Online Courses: Enroll Now, Thank us Later!

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:

gui dataflair functions

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.

output

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.

add ins

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:

excel volatile function

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.

calc output

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.

byref output

byval output

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

FunctionDescription
InputBoxIt displays a dialog box where the user can enter the input
MsgBoxIt displays a message box

2. VBA Text Functions

FunctionDescription
FormatIt returns the result as a string and it applies a format to an expression.
InStrIt returns the substring position in a string.
InStrRevIt returns the substring position in a string searching from right to left. 
LeftIt returns the substring from the beginning of a provided string.
LenIt returns the length of the provided string.
LCaseIt converts the string elements to lower text cases.
LTrimIt removes the extra spaces in the string. 
MidIt returns the substring from the middle of a string.
ReplaceIt replaces a substring with the new string.
RightIt returns a substring from the end of the string.
RTrimIt removes the extra spaces from the right side of the string.
SpaceIt creates a string consisting of a specified number space.
StrCompIt compares the two strings and returns the result in an integer representing the comparison. 
StrConvIt converts a string to a particular format mentioned. 
StringIt creates a sequence of characters in a string. 
StrReverseIt reverses the string.
TrimIt removes the spaces in the beginning and in the ending of the string. 
UCaseIt converts the string to upper case characters. 

3. VBA Information Functions

FunctionDescription
IsArrayIt tests whether the provided variable is an array or not.
IsDateIt tests whether the provided expression is a date or not. 
IsEmptyIt tests whether the provided variant is empty or not. 
IsErrorIt tests whether the provided expression is an error or not. 
IsMissingIt tests whether an optional argument procedure is missing or not. 
IsNullIt tests whether the expression is null or not.
IsNumericIt tests whether the expression is numeric or not. 
IsObjectIt tests whether the variable represents an object variable. 

4. VBA Error Handling Functions

FunctionDescription
CVErrIt produces an error data type for a provided error code.
ErrorIt returns the error message corresponding to the provided error code. 

5. VBA Program Flow Functions

FunctionDescription
ChooseIt selects a value from the argument list.
llfIt returns one of the two values by evaluating an expression. It evaluates to true or false. 
SwitchIt returns the value associated with the first true expression by evaluating a list of boolean expressions. 

6. VBA Conversion Functions

FunctionDescription
AscIt returns an integer representing the code for a provided character. 
CBoolIt converts an expression to a boolean data type.
CByteIt converts an expression to a byte data type.  
CCurIt converts an expression to a currency data type.
CDateIt converts an expression to a date data type.
CDblIt converts an expression to a double data type.
CDecIt converts an expression to a decimal data type.
ChrIt returns the character corresponding to the provided character code.
ClntIt converts an expression to an integer data type.
CLngIt converts an expression to a long data type.
CSngIt converts an expression to a single data type.
CStrIt converts an expression to a string data type.
CVarIt converts an expression to a variant data type. 
FormatCurrencyIt converts the expression to currency format and returns the result as a string.
FormatDateTimeIt converts the expression to date and time format and returns the result as a string.
FormatNumberIt converts the expression to number format and returns the result as a string.
FormatPercentIt converts the expression to percentage format and returns the result as a string.
HexIt converts the numeric value to hexadecimal value and returns the result as a string.
OctIt converts the numeric value to octal value and returns the result as a string.
StrIt converts a numeric value to a string.
ValIt converts a string to a numeric value.

7. VBA Date and Time Functions

FunctionDescription
DateIt returns the current date.
DateAddIt adds an interval between the date or time.
DateDiffIt returns the number of intervals between the two dates or time.
DatePartIt returns a part of a date or time
DateSerialIt returns the date from the provided date details.
DateValueIt returns a date from the string representation of a date or time. 
DayIt represents the day number which is either from 1 to 31. 
HourIt returns the hour component in the time mentioned. 
MinuteIt returns the minute component in the time mentioned. 
MonthIt returns the month number which is either from 1 to 12.
MonthNameIt returns the month name. 
NowIt returns the current date and time. 
SecondIt returns the second component in the time mentioned. 
TimeIt returns the current time. 
TimerIt returns the number of seconds that have elapsed. 
TimeSerialIt returns the time from a provided hour, minute and second.
TimeValueIt returns the time from a string representation.
WeekdayIt returns an integer from 1 to 7 representing the weekday.
WeekdayNameIt returns the name of the weekday.
YearIt returns the year of the provided date.

8. VBA Math and Trig Functions

FunctionDescription
AbsIt returns the absolute value.
AtnIt returns the arctangent value of a provided number.
CosIt evaluates the cosine value of the provided angle.
ExpIt 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.
LogIt calculates the natural logarithm value.
RndIt generates a random number between 0 and 1.
RoundIt rounds a number to a mentioned number of decimal places.
SgnIt returns an integer representing the arithmetic signs.
SinIt evaluates the sine value. 
TanIt evaluates the tangent value. 
SqrIt returns the square root value.

9. VBA Financial Functions

FunctionDescription
DDBIt calculates the depreciation of an asset using the double declining method during a mentioned period.
FVIt calculates the future value of an investment.
IPmtIt calculates the interest for a payment for a loan.
IRRIt calculates the internal rate of return for periodic cash flows.
MIRRIt calculates the modified internal rate of return for periodic cash flows.
NPerIt calculates the number of periods for an investment.
NPVIt calculates the net present value of an investment.
PmtIt calculates the constant periodic payment for an investment.
PPmtIt calculates the principal of a payment during a mentioned period for an investment.
PVIt calculates the present value.
RateIt calculates the interest rate for an amount.
SLNIt calculates the straight line depreciation of an asset.
SYDIt calculates the sum of years depreciation for a mentioned period in the lifetime of an asset.

10. VBA Financial Functions

FunctionDescription
DDBIt calculates the depreciation of an asset using the double declining method during a mentioned period.
FVIt calculates the future value of an investment.
IPmtIt calculates the interest for a payment for a loan.
IRRIt calculates the internal rate of return for periodic cash flows.
MIRRIt calculates the modified internal rate of return for periodic cash flows.
NPerIt calculates the number of periods for an investment.
NPVIt calculates the net present value of an investment.
PmtIt calculates the constant periodic payment for an investment.
PPmtIt calculates the principal of a payment during a mentioned period for an investment.
PVIt calculates the present value.
RateIt calculates the interest rate for an amount.
SLNIt calculates the straight line depreciation of an asset. 
SYDIt calculates the sum of years depreciation for a mentioned period in the lifetime of an asset.

11. VBA Array Functions

FunctionDescription
ArrayIt creates an array containing a provided set of values. 
FilterIt returns the subset of a provided string based on the criteria.
JoinIt joins the number of substrings into a single string.
LBoundIt returns the lowest subscript of an array. 
SplitIt splits a text string into a number of substrings. 
UBoundIt returns the highest subscript of an array. 

12. VBA File Management Functions

FunctionDescription
CurDirIt returns the current path as a string. 
DirIt returns the directory  name that matches the mentioned pattern and attributes. 
FileAttrIt returns the mode of the file that has been opened. 
FileDateTimeIt returns the date when the file was lastly modified. 
FileLenIt returns the length of the provided file. 
GetAttrIt returns an integer representing the attributes of a provided file or directory.

12. Lookup or Reference Function

FunctionDescription
ChooseIt returns a value from the list based on the position.

13. File or Directory Functions

FunctionDescription
ChDirThis function helps to change the current directory or folder.
ChDriveThis function helps to change the current drive.
CurDirIt returns the current path.
MkDirThis function helps to create a new folder or directory.
SetAttrThis function helps to set the attributes of a file.

14. Logical Functions

FunctionDescription
ANDIt evaluates to true if all conditions are true or else it returns false.
CASEThis function has the functionality of an IF-THEN-ELSE statement
FOR..NEXTThis function helps to create the “For” loop.
IF-THEN-ELSEIt returns a value if a specified condition evaluates to true or else the other value if it evaluates to false.
ORIt returns true if any of the conditions is met and it returns false if no condition is met in the criteria.
SWITCHThis function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is “true”.
WHILE..WENDThis function helps to create the “while” loop.

15. Related Page

FunctionDescription
Vlookup in VBAIt 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.

We work very hard to provide you quality material
Could you take 15 seconds and share your happy experience on Google

follow dataflair on YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *