VBA Function - Call, Return Value, & Parameters - Automate Excel (2023)

In this Article

  • Creating a Function without Arguments
  • Calling a Function from a Sub Procedure
  • Creating Functions
    • Single Argument
    • Multiple Arguments
    • Optional Arguments
    • Default Argument Value
    • ByVal and ByRef
  • Exit Function
  • Using a Function from within an Excel Sheet

This tutorial will teach you to create and use functions with and without parameters in VBA

VBA contains a large amount of built-in functions for you to use, but you are also able to write your own. When you write code in VBA, you can write it in a Sub Procedure, or a Function Procedure. A Function Procedure is able to return a value to your code. This is extremely useful if you want VBA to perform a task to return a result. VBA functions can also be called from inside Excel, just like Excel’s built-in Excel functions.

Creating a Function without Arguments

To create a function you need to define the function by giving the function a name. The function can then be defined as a data type indicating the type of data you want the function to return.

You may want to create a function that returns a static value each time it is called – a bit like a constant.

Function GetValue() As Integer GetValue = 50End Function

If you were to run the function, the function would always return the value of 50.

VBA Function - Call, Return Value, & Parameters - Automate Excel (1)

You can also create functions that refer to objects in VBA but you need to use the Set Keyword to return the value from the function.

Function GetRange() as Range Set GetRange = Range("A1:G4")End Function

If you were to use the above function in your VBA code, the function would always return the range of cells A1 to G4 in whichever sheet you are working in.

Calling a Function from a Sub Procedure

Once you create a function, you can call it from anywhere else in your code by using a Sub Procedure to call the function.

VBA Function - Call, Return Value, & Parameters - Automate Excel (2)

The value of 50 would always be returned.

(Video) VBA Programming for Excel 2010 - V2.04 - Calling and Combining Sub Procedure Macros

You can also call the GetRange function from a Sub Procedure.

VBA Function - Call, Return Value, & Parameters - Automate Excel (3)

In the above example, the GetRange Function is called by the Sub Procedure to bold the cells in the range object.

Creating Functions

Single Argument

You can also assign a parameter or parameters to your function. These parameters can be referred to as Arguments.

Function ConvertKilosToPounds (dblKilo as Double) as Double ConvertKiloToPounds = dblKilo*2.2End Function

We can then call the above function from a Sub Procedure in order to work out how many pounds a specific amount of kilos are.

VBA Function - Call, Return Value, & Parameters - Automate Excel (4)

A function can be a called from multiple procedures within your VBA code if required. This is very useful in that it stops you from having to write the same code over and over again. It also enables you to divide long procedures into small manageable functions.

VBA Function - Call, Return Value, & Parameters - Automate Excel (5)

In the above example, we have 2 procedures – each of them are using the Function to calculate the pound value of the kilos passed to them in the dblKilo Argument of the function.

Multiple Arguments

You can create a Function with multiple arguments and pass the values to the Function by way of a Sub Procedure.

Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double CalculateDayDiff = Date2-Date1End Function

We can then call the function to calculate the amount of days between 2 dates.

VBA Function - Call, Return Value, & Parameters - Automate Excel (6)

Optional Arguments

You can also pass Optional arguments to a Function. In other words, sometimes you may need the argument, and sometimes you may not – depending on what code you are using the Function with .

(Video) Excel VBA Basics #11 Create your Own Custom Functions with or without Arguments

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date) as Double'check for second date and if not there, make Date2 equal to today's date. If Date2=0 then Date2 = Date'calculate difference CalculateDayDiff = Date2-Date1 End Function

VBA Function - Call, Return Value, & Parameters - Automate Excel (7)

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More

Default Argument Value

You can also set the default value of the Optional arguments when you are creating the function so that if the user omits the argument, the value that you have put as default will be used instead.

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date="06/02/2020") as Double 'calculate difference CalculateDayDiff = Date2-Date1 End Function

VBA Function - Call, Return Value, & Parameters - Automate Excel (9)

ByVal and ByRef

When you pass values to a function, you can use the ByVal or ByRef keywords. If you omit either of these, the ByRef is used as the default.

ByVal means that you are passing a copy of the variable to the function, whereas ByRef means you are referring to the original value of the variable. When you pass a copy of the variable (ByVal), the original value of the variable is NOT changed, but when you reference the variable, the original value of the variable is changed by the function.

Function GetValue(ByRef intA As Integer) As Integer intA = intA * 4 GetValue = intAEnd Function

In the function above, the ByRef could be omitted and the function would work the same way.

Function GetValue(intA As Integer) As Integer intA = intA * 4 GetValue = intAEnd Function

To call this function, we can run a sub-procedure.

Sub TestValues() Dim intVal As Integer'populate the variable with the value 10 intVal = 10'run the GetValue function, and show the value in the immediate window Debug.Print GetValue(intVal)'show the value of the intVal variable in the immediate window Debug.Print intValEnd Sub

VBA Function - Call, Return Value, & Parameters - Automate Excel (10)

Note that the debug windows show the value 40 both times. When you pass the variable IntVal to the function – the value of 10 is passed to the function, and multiplied by 4. Using the ByRef keyword (or omitting it altogether), will AMEND the value of the IntVal variable. This is shown when you show first the result of the function in the immediate window (40), and then the value of the IntVal variable in the debug window (also 40).

If we do NOT want to change the value of the original variable, we have to use ByVal in the function.

(Video) How To Return A Result From A Function In VBA

Function GetValue(ByVal intA As Integer) As IntegerintA = intA * 4GetValue = intAEnd Function

Now if we call the function from a sub-procedure, the value of the variable IntVal will remain at 10.

VBA Function - Call, Return Value, & Parameters - Automate Excel (11)

Exit Function

If you create a function that tests for a certain condition, and once the condition is found to be true, you want return the value from the function, you may need to add an Exit Function statement in your Function in order to exit the function before you have run through all the code in that function.

Function FindNumber(strSearch As String) As Integer Dim i As Integer'loop through each letter in the string For i = 1 To Len(strSearch) 'if the letter is numeric, return the value to the function If IsNumeric(Mid(strSearch, i, 1)) Then FindNumber= Mid(strSearch, i, 1) 'then exit the function Exit Function End If Next FindNumber= 0End Function

The function above will loop through the string that is provided until it finds a number, and then return that number from the string. It will only find the first number in the string as it will then Exit the function.

The function above can be called by a Sub routine such as the one below.

Sub CheckForNumber() Dim NumIs as Integer'pass a text string to the find number function NumIs = FindNumber("Upper Floor, 8 Oak Lane, Texas")'show the result in the immediate window Debug.Print NumIsEnd Sub

VBA Function - Call, Return Value, & Parameters - Automate Excel (12)

VBA Programming | Code Generator does work for you!

Using a Function from within an Excel Sheet

In addition to calling a function from your VBA code using a sub procedure, you can also call the function from within your Excel sheet. The functions that you have created should by default appear in your function list in the User Defined section of the function list.

Click on the fx to show the Insert Function dialog box.

VBA Function - Call, Return Value, & Parameters - Automate Excel (13)

Select User Defined from the Category List

VBA Function - Call, Return Value, & Parameters - Automate Excel (14)

Select the function you require from the available User Defined Functions (UDF’s).

(Video) How To Automate VLOOKUP With Excel VBA

VBA Function - Call, Return Value, & Parameters - Automate Excel (15)

Alternatively, when you start writing your function in Excel, the function should appear in the drop down list of functions.

VBA Function - Call, Return Value, & Parameters - Automate Excel (16)

If you do not want the function to be available inside an Excel sheet, you need to put the Private word in front of the word Function when you create the function in your VBA code.

Private Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double CalculateDayDiff = Date2-Date1 End Function

It will now not appear in the drop down list showing the Excel functions available.

VBA Function - Call, Return Value, & Parameters - Automate Excel (17)

Interestingly enough, however, you can still use the function – it just will not appear in the list when looking for it!

VBA Function - Call, Return Value, & Parameters - Automate Excel (18)

If you have declared the second argument as Optional, you can omit it within the Excel sheet as well as within the VBA code.

VBA Function - Call, Return Value, & Parameters - Automate Excel (19)

You can also use the a function that you have created without arguments in your Excel sheet.

VBA Function - Call, Return Value, & Parameters - Automate Excel (20)


How do you return a value from a VBA function? ›

To return a value using the Return statement
  1. Put a Return statement at the point where the procedure's task is completed.
  2. Follow the Return keyword with an expression that yields the value you want to return to the calling code.
  3. You can have more than one Return statement in the same procedure.
Sep 15, 2021

Can a VBA sub return a value? ›

Sub procedures DO NOT Return a value while functions may or may not return a value. Sub procedures CAN be called without a call keyword. Sub procedures are always enclosed within Sub and End Sub statements.

How to create dynamic array in VBA? ›

Create a Dynamic Array in VBA
  1. First, declare an array with its name.
  2. After that, the elements count left the parentheses empty.
  3. Now, use the ReDim statement.
  4. In the end, specify the count of elements you want to add to the array.

How do I automatically return a value in Excel? ›

Return a value if a given value exists in a certain range by using a formula. Please apply the following formula to return a value if a given value exists in a certain range in Excel. 1. Select a blank cell, enter formula =VLOOKUP(E2,A2:C8,3, TRUE) into the Formula Bar and then press the Enter key.

Can macro function return value? ›

Macros just perform textual substitution. They can't return anything - they are not functions.

How to run a macro automatically when a cell value changes? ›

Go to the VBA Editor (Alt + F11) and double-click the name of the spreadsheet that contains the cell that will change or just right-click the worksheet tab and click View Code. In the window that opens, select Worksheet from the left drop-down menu and Change from the right drop-down menu.

How do I make macros automatically update? ›

Updating Automatically When Opening Under Macro Control
  1. Choose Options from the Tools menu. Excel displays the Options dialog box.
  2. Make sure the Edit tab is selected.
  3. Clear the Ask To Update Automatic Links check box.
  4. Click on OK.
Oct 29, 2022

Can you make an IF function macro in Excel? ›

The IF-THEN-ELSE statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

What is the difference between sub and function VBA? ›

VBA Sub vs Function: Key Differences

A sub performs a task but does not return a value. A function returns a value of the tasks performed. Subs can be recalled from anywhere in the program and in multiple types. Functions are called by a variable.

How to call sub with parameters in VBA? ›

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses. Use a Sub procedure to organize other procedures so they are easier to understand and debug.

What is the difference between subroutine and function? ›

Functions are similar to subroutines, except that they return a value. A function commonly carries out some calculations and reports the result to the caller. Subroutines perform a task but do not report anything to the calling program.

What is the difference between static array and dynamic array in VBA? ›

VBA supports both dynamic as well as static arrays. Static arrays do not change the size. Dynamic arrays size can be changed any time between the runtime. The lower bound of the dynamic array remains unaffected.

What is the difference between static and dynamic array in Excel VBA? ›

Arrays, Static Vs Dynamic:

There are 2 main types of arrays you can use in VBA, Static and Dynamic. Static arrays have a fixed size. you will not be able to change the size of the array later in your code. On the other hand the size of a dynamic array can change.

How does Xlookup work in Excel? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Is an option in Excel that is used to generate a series of values automatically? ›

What is AutoFill? Excel has a feature that helps you automatically enter data. If you are entering a predictable series (e.g. 1, 2, 3…; days of the week; hours of the day) you can use the AutoFill command to automatically extend the sequence.

Can the return type of a function be auto? ›

In C++14, you can just use auto as a return type.

What function can automatically return the value? ›

The function that can automatically return the value in cell c77 is Min(). The min () function in an Excel sheet automatically returns the item with the lowest value. The min() function is also used to find the item with the lowest value in an iterable or given set of data or arrays automatically.

Can a function print and return a value? ›

The return and print statements are equally important for programming. If you just want to execute a simple function that returns a value, the return statement will be enough. In case you want to return a value but also have to print it to the terminal, you need to use the print() method.

How do I create a dynamic macro in Excel? ›

Use Dynamic Ranges in Pivot Tables
  1. Select a cell in the database.
  2. Choose Data | PivotTable and PivotChart Report.
  3. Select 'Microsoft Excel List or Database', click Next.
  4. For the range, type myData , then click Next.
  5. Click the Layout button.
  6. Drag field buttons to the row, column and data areas. ...
  7. Click OK, then click Finish.
Jan 22, 2023

Which event runs a macro automatically in Excel VBA? ›

Excel Event code makes this possible and it is easier than you think. Event code can run a macro based on a drop-down list, event code is VBA code stored in a worksheet module or workbook module.

How do I auto populate a cell based on a value entered in another cell using a table? ›

Fill data automatically in worksheet cells
  1. Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
  2. Drag the fill handle .
  3. If needed, click Auto Fill Options. and choose the option you want.

How do I create a trigger macro? ›

Adding a trigger in the properties of the macro
  1. In Macro Manager, select the macro, then click the button Properties on the toolbar.
  2. Switch to the tab Triggers.
  3. Click the button Add.
  4. Follow the instructions in the Schedule Wizard.

How do I run VBA code in Excel automatically without opening? ›

You can't run a Excel VBA Macro without opening the File that contains the macro. If you want you can launch the excel application in hidden mode and then run the macro after opening the file in hidden mode from a VBS file.

Can Excel automatically update data? ›

Automatically refresh data at regular intervals

Click a cell in the external data range. On the Data tab, in the Connections group, click Refresh All, and then click Connection Properties. Click the Usage tab. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

Are macros faster than functions Excel? ›

Speed versus size The main benefit of using macros is faster execution time. During preprocessing, a macro is expanded (replaced by its definition) inline each time it's used. A function definition occurs only once regardless of how many times it's called.

How do I use multiple conditions in VBA? ›

You can use the OR operator with the VBA IF statement to test multiple conditions. When you use it, it allows you to test two or more conditions simultaneously and returns true if any of those conditions are true. But if all the conditions are false only then it returns false in the result.

Can we pass a macro as an argument to a function? ›

To assign a macro that you pass arguments to a button, shape, image, or any object, you first right-click that object and click Assign Macro and then type the name of the macro and the argument, following the pattern described in the above examples, and then click OK. 'show_msg "I clicked a button!"'

Can a subroutine return a value? ›

A subroutine does not have to return a value, but when it does, it sends back the value with the RETURN instruction. The calling program receives the value in the REXX special variable named RESULT. A function must return a value.

Are macros the same as functions? ›

A macro is defined with the pre-processor directive. Macros are pre-processed which means that all the macros would be processed before your program compiles. However, functions are not preprocessed but compiled.

Are a subroutine and a macro the same thing? ›

Any logic may use both macros and subroutines. The main difference is in the way they are used. Only subroutines can be used when you need to pass arguments, get a return value, or activate the independent execution of logic. Only macros can be used when defining run-time interface parameters.

How do you call a method using parameters? ›

To call a method in Java, simply write the method's name followed by two parentheses () and a semicolon(;). If the method has parameters in the declaration, those parameters are passed within the parentheses () but this time without their datatypes specified.

How do you dynamically name a range in VBA? ›

To create a named range using VBA, you need to use the “Names” property further with the “Add” method. In add method, you have arguments to define the name that you wish to give to the range and specify the address of the range (make sure to use the dollar sign with the address to freeze the range).

How do you pass parameters in VBA? ›

You can pass an argument by value if you include the ByVal keyword in the procedure's declaration. Arguments passed by value consume from 2–16 bytes within the procedure, depending on the argument's data type.

What are the disadvantages of subroutines? ›

The only drawback to using subroutines is that the overhead associated with them can at times hamper the performance of a time critical application.

Why is using a subroutine better? ›

Subroutines make programs shorter as well as easier to read and understand, because they break program code into smaller sections. You can test procedures or functions separately, rather than having to test the whole program. This makes programs easier to debug.

What are the two types of subroutines? ›

There are two types of subroutine:
  • procedures.
  • functions.

What is the disadvantage of the static array? ›

Disadvantages of Static Array

In case more static data space is declared than needed, there is waste of space. In case less static space is declared than needed, then it becomes impossible to expand this fixed size during run time.

Is dynamic array faster than linked list? ›

Compared to linked lists, dynamic arrays have faster indexing (constant time versus linear time) and typically faster iteration due to improved locality of reference; however, dynamic arrays require linear time to insert or delete at an arbitrary location, since all following elements must be moved, while linked lists ...

How to loop through dynamic array in VBA? ›

In VBA, to loop through an array you can use the For Loop (For Next). And to write this loop code you need to know the size of the array (upper and lower bound) so that you can use both as the counter for the loop. Basically, for loop will start from the first element of the array and loop up to the last.

What is a dynamic array function in Excel? ›

Dynamic Arrays are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell. Through over 30 years of history, Microsoft Excel has undergone many changes, but one thing remained constant - one formula, one cell.

Why do we use dynamic array? ›

A dynamic array is a random access, variable-size list data structure that allows elements to be added or removed. It is supplied with standard libraries in many modern programming languages. Dynamic arrays overcome a limit of static arrays, which have a fixed capacity that needs to be specified at allocation.

What is the difference between a static and dynamic array? ›

A static array variable holds a value of type, array. A dynamic array variable holds a pointer to an array value. Thanks to automatic pointer dereferencing and automatic index padding, there is very little difference in the code that you write to use either type of array.

Can I dynamically allocate array? ›

dynamically allocated arrays

To dynamically allocate space, use calls to malloc passing in the total number of bytes to allocate (always use the sizeof to get the size of a specific type). A single call to malloc allocates a contiguous chunk of heap space of the passed size.

How do you dynamically input an array? ›

We shall have to make an array of size n dynamically and take n numbers one by one, then find the sum. To make the array we can use malloc() or calloc() function which is present inside the stdlib. h header file. The value of n is also provided as input through stdin.

How can you return a value from a function? ›

To return a value from a function, you must include a return statement, followed by the value to be returned, before the function's end statement. If you do not include a return statement or if you do not specify a value after the keyword return, the value returned by the function is unpredictable.

How do I create a return in VBA? ›

In VBA, there are three different (constants) to add a line break.
  1. vbNewLine.
  2. vbCrLf.
  3. vbLf.

How do you return a value from another function? ›

function firstFunction() { // do something return "testing 123"; } var test = firstFunction(); // this will grab you the return value from firstFunction(); alert(test); You can make this call from another function too, as long as both functions have same scope. Save this answer. Show activity on this post.

Can functions have return values? ›

Some functions don't return a significant value, but others do. It's important to understand what their values are, how to use them in your code, and how to make functions return useful values.

Can a function return a value by reference? ›

Functions in C++ can return a reference as it's returns a pointer. When function returns a reference it means it returns a implicit pointer.

What is it called when a function returns a value? ›

Return statements in many programming languages allow a function to specify a return value to be passed back to the code that called the function.

How are functions called How does it return a value? ›

A return is a value that a function returns to the calling script or function when it completes its task. A return value can be any one of the four variable types: handle, integer, object, or string. The type of value your function returns depends largely on the task it performs.


1. 5 Tips for Passing Values to Macros (VBA) - Beginner to Advanced
2. Excel VBA IF THEN Statement (with ELSEIF & Looping in cells)
(Leila Gharani)
3. Functions with Array parameter in VBA
4. Excel VBA Topic 4.1.1 - Example, Calling a Sub Procedure
5. Excel VBA FIND Function (& how to handle if value NOT found)
(Leila Gharani)
6. Use VBA To Run a Powershell Command and Get Return Value
Top Articles
Latest Posts
Article information

Author: Sen. Ignacio Ratke

Last Updated: 05/21/2023

Views: 6082

Rating: 4.6 / 5 (76 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Sen. Ignacio Ratke

Birthday: 1999-05-27

Address: Apt. 171 8116 Bailey Via, Roberthaven, GA 58289

Phone: +2585395768220

Job: Lead Liaison

Hobby: Lockpicking, LARPing, Lego building, Lapidary, Macrame, Book restoration, Bodybuilding

Introduction: My name is Sen. Ignacio Ratke, I am a adventurous, zealous, outstanding, agreeable, precious, excited, gifted person who loves writing and wants to share my knowledge and understanding with you.