We write the procedures to perform certain tasks and some times we may required to write another procedure with small variations. In this situation we can take advantage of the Passing Arguments.
We can pass the arguments in two different ways: 1. By Value ByVal : We will pass the actual value to the arguments 2.
By Reference ByRef : We will pass the reference address, pointers in other language to the arguments. ByRef is default passing argument type in VBA. This means, if you are not specifying any type of the argument it will consider it as ByRef type. However, it is always a good practice to specify the ByRef even if it is not mandatory. You call the user defined functions as similar to the built-in excel function.
The following picture shows how to call a user defined function to add to add two integers:. I am trying to call the below Sub but getting a syntax error. Add nPassword. Copy Range a1. Select ActiveSheet.
Save my name, email, and website in this browser for the next time I comment. This site uses Akismet to reduce spam.
Learn how your comment data is processed. Previous Next. Advanced Project Plan Excel Template. About the Author: PNRao. Related Posts. August 6th, 0 Comments. July 6th, 0 Comments. July 5th, 1 Comment. Show or Hide Unload a userform. October 2nd, 1 Comment.Before you consider creating your own VBA Functions, it is useful to know that there are a large number of built-in VBA functions that you can use in your code.
In Excel Visual Basic, a set of commands to perform a specific task is placed into a procedure, which can be a Function procedure or a Sub procedure also known as functions and subroutines. The main difference between a VBA Function procedure and a Sub procedure is that a Function procedure returns a result, whereas a Sub procedure does not.
Therefore, if you wish to perform a task that returns a result e. VBA procedures can be passed data via argumentswhich are declared in the procedure definition. For example, you could have a VBA Sub procedure that adds an Integer to every cell in the current selected range on a worksheet.
You could supply the value of the integer to the Sub via an argument, as follows:. Some procedures may not require them.
You can also define VBA procedures to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the procedure will assign a default value to them. To return to the example above, if we wanted to make the supplied integer argument optional, this would be declared as follows:. You can use multiple Optional arguments in a VBA procedure, as long the Optional arguments are all positioned at the end of the argument list.
When arguments are passed to VBA procedures, they can be passed in two ways:.
Understanding named arguments and optional arguments
You can specify whether an argument is passed to a VBA procedure by value or by reference by using the ByVal or the ByRef keyword when defining the procedure. This is shown below:. It should be noted that, by default, VBA arguments are passed by Reference. Therefore, if you do not use the ByVal or the ByRef keyword, the arguments will be passed by Reference. Before discussing further properties of VBA Function and Sub procedures, it is useful to look at the two types of procedure individually.
The VBA editor recognises a Function procedure, because the commands are positioned between the following start and end commands:. As previously mentioned, VBA function procedures unlike sub procedures return a value. The return values have the following rules:.
The following code shows an example of a simple VBA Function procedure that receives three arguments, each of which are 'Doubles' double precision floating point numbers. The Function returns a further 'Double', which is the sum of the first two arguments, minus the third argument:.
The above very simple VBA Function procedure illustrates the way in which data arguments are supplied to a procedure. It is also seen that the Function procedure return type is defined as being a 'Double' i.GeeksEngine is hosted by HostGator.
Part 1: Using By Reference argument, collection, and dictionary object VBA functions and other programming languages as well can only return one value. It's a common and useful practice that we need to return more values from a function. This article sums up some of the tips and tricks by using VBA language as an example. Approaches showed here also apply to other programming languages.
When you process data by code, sometimes the data comes in pairs or related groups. In this case, we can use various data structures to organise them.
Depending on your problem-solving situation, you may want to consider using data structures such as By Reference argument, collection object, dictionary object, array, CSV, user-defined types, database recordset, or classes.
Part 2 - Return multiple values by array, CSV, and user-defined type. Part 3 - Return multiple values by recordset and VBA class. Code used in this article is based on a simple Access form as shown below. Each button click event triggers a function that returns multiple values and displays in Immediate Window. A simple form with 8 buttons for testing use. Return multiple values by using passing arguments By Reference.
Passing arguments By Reference is probably the most common way to return multiple values from a function. It uses the ByRef keyword to tell the compiler that the variable passed to the function is only a pointer to a memory location where the actual value of the variable is stored. This way, the code inside the function itself can modify the value of the variable.
Even through the function does not explicitly return the changed value, it can be retrieved by using the same variable name within the code that calls the function. By contrast, passing arguments By Value instructs the function that the variable is read-only so the code inside the function can't change its value. In Visual Basic.This page describes how to write your own worksheet functions in VBA. While Excel provides a plethora of built-in functions, especially so if you include functions in the Analysis Took Pack in Excelthe functions that used to be in the ATP are now native Excel functions you may find it useful to create your own custom function for things that Excel cannot easily do with the built-in functions.
While it takes longer for Excel to calculate a VBA function than it does to calculate a worksheet formula, all else being equal, the flexibility of VBA often makes a VBA function the better choice. The rest of this page assumes that you are familiar with the basics of VBA programming. A UDF can only return a value to the cell s whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel.
If you attempt to change anything, the function will terminate immediately and return a VALUE error to the calling cell. This was fixed with Excel The following is an example of a simple UDF that calculates the area of a rectangle:.
This function takes as inputs two Double type variables, Height and Widthand returns a Double as its result. Because functions take inputs and return a value, they are not displayed in the list of procedures in the Macros dialog. The code for a UDF should be placed in a standard code module, not one of the Sheet modules and not in the ThisWorkbook module. This will insert a new code module into the project.
A module can contain any number functions, so you can put many functions into a single code module. You can change the name of a module from Module1 to something more meaningful by pressing the F4 key to display the Properties window and changing the Name property to whatever you want.
You can call a function from the same workbook by using just the function name. MyFunction Doing this will lead only to confusion, so just because it is possible doesn't mean you should do it. Don't do it. Do not give the same name to both a module and a function regardless of whether that module contains that function.
Doing so will cause an untrappable error. You can call a UDF that is contained in another open workbook by using the workbook name in the formula.
The function name alone is sufficient for calling a function in an Add-In. Suppose both Book1. Name End Function The function Test in each workbook simply returns the name of the workbook in which the code resides, so the function Test defined in Book1. In Book1. Test in cell A2. The functions will work properly when you first enter the formulas, but if you edit the formula in A2 e. This will almost certainly return an incorrect result. This problem occurs only when the workbook and an Add-In both have a function with the same name.
It does not occurs if two workbooks have functions with the same name. This has not been fixed in Excel As a general rule, you should pass into the function all the values it needs to properly calculate the result. That means that your UDF should not make explicit refences to other cells. If you reference other cells directly from within the function, Excel may not recalculate the function when that cell is changed. For example, a poorly written UDF is as follows:.
Value End Function. In this function, the Width is assumed to be in cell A1. The problem here is that Excel doesn't know that this function depends on cell A1 and therefore will not recalculate the formula when A1 is changed.In the previous lessonyou saw how to set up and Call a Sub. In this lesson, you'll see how to hand values over to your subroutines. So open up the code you wrote last time and we'll make a start. However, these are all hard-coded to values of True, Arial and It would be much better if we could replace these hard-coded values with variables:.
But this wouldn't make our code reusable at all. In fact, it's just a longer version of what we already have. That's where the round brackets come in at the start of the Sub. The round brackets of a Sub are not just there for show. They allow you to pass values over to your subroutines.
This means that the line that calls one Sub can pass values to another Sub. When setting up a Sub that accepts values, you place variable names between the round brackets:.
Each variable is separated by a comma. You can pass no variables, one variable, or as many as you need. Notice that you don't need the Dim word anymore. But this is just setting up the variable. They have nothing in them yet. To place something in these variables, you do so on the calling line. Each value you place between the round brackets is known as an argument or sometimes a parameter. The arguments must match.
Passing Arguments in Excel VBA
So if you have set up your Sub line to accept two arguments then you must pass two arguments in on the calling line, otherwise you'll get an error. The order of the arguments must match, as well.
If the first variable is a Boolean, then you can't pass in a value of "Arial". Likewise, if the second argument is a String, then you can't pass in a number.
Unless, that is, you set each variable up as Variants by missing off As String, As Boolean between the round brackets of the Sub line. The error is because we have the text "value" as the first argument. The Sub says that BoldValue should be a Boolean, however. You can have 1 or 0 for your Booleans, though.
Passing values to a Sub
A value of 1 means True and a value of 0 means False. You can even put the True and False in quote marks, and in any case you like: upper, lower, or a mix. In the Sub where your calling line is, you can set up variables, place values in the variables, and then type the variable names between the round brackets. Like this:. Now, we have variable names between the round brackets of the calling line. It will transfer the values to the new variables names:. So the value in boolValue will get transferred to the BoldValue variable, and the value in strText will get transferred to the NameValue variable.When you call a Sub or Function procedure, you can supply arguments positionally, in the order that they appear in the procedure's definition, or you can supply the arguments by name without regard to position.
You can call this procedure by supplying its arguments in the correct position, each delimited by a comma, as shown in the following example. You can also call this procedure by supplying named argumentsdelimiting each with a comma. Named arguments are especially useful when you are calling a procedure that has optional arguments. If you use named arguments, you don't have to include commas to denote missing positional arguments.
Using named arguments makes it easier to keep track of which arguments you passed and which you omitted. Optional arguments are preceded by the Optional keyword in the procedure definition.
You can also specify a default value for the optional argument in the procedure definition. For example:. When you call a procedure with an optional argument, you can choose whether or not to specify the optional argument.
If you don't specify the optional argument, the default value, if any, is used. If no default value is specified, the argument would be for any variable of the specified type. The following procedure includes two optional arguments, the varRegion and varCountry variables. The IsMissing function determines whether an optional Variant argument has been passed to the procedure.
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Skip to main content. Exit focus mode. For example, the following Sub procedure takes three arguments. Print strName, intAge, dteBirth End Sub You can call this procedure by supplying its arguments in the correct position, each delimited by a comma, as shown in the following example. PassArgs "Mary", 29, You can also call this procedure by supplying named argumentsdelimiting each with a comma.
End Sub When you call a procedure with an optional argument, you can choose whether or not to specify the optional argument. Print strState, varCountry Else Debug. Related Articles Is this page helpful? Yes No. Any additional feedback? Skip Submit. Is this page helpful?Declares the name, argumentsand code that form the body of a Function procedure. If not explicitly specified by using PublicPrivateor FriendFunction procedures are public by default. If Static isn't used, the value of local variables is not preserved between calls.
The Friend keyword can only be used in class modules.
However, Friend procedures can be accessed by procedures in any module of a project. A Friend procedure does not appear in the type library of its parent class, nor can a Friend procedure be late bound.
Function procedures can be recursive; that is, they can call themselves to perform a given task. However, recursion can lead to stack overflow. The Static keyword usually isn't used with recursive Function procedures. All executable code must be in procedures. You can't define a Function procedure inside another FunctionSubor Property procedure.
The Exit Function statement causes an immediate exit from a Function procedure. Program execution continues with the statement following the statement that called the Function procedure. Any number of Exit Function statements can appear anywhere in a Function procedure. Like a Sub procedure, a Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. However, unlike a Sub procedure, you can use a Function procedure on the right side of an expression in the same way you use any intrinsic function, such as SqrCosor Chrwhen you want to use the value returned by the function.
You call a Function procedure by using the function name, followed by the argument list in parentheses, in an expression.
Excel VBA Tutorial Part 4 - VBA Function & Sub Procedures
See the Call statement for specific information about how to call Function procedures. To return a value from a function, assign the value to the function name. Any number of such assignments can appear anywhere within the procedure.