Function Procedure In Vba Excel #08

What are function procedures?

After discussing about Sub Procedures and how to call them, this VBA Excel tutorial series will invite you to learn and understand how to create a function procedure or function procedure in VBA Excel.

Definition of Function Procedure

In the discussion of Macro VBA Excel, Procedure is a block of program code that contains certain command lines between the opening statement line and the closing statement of the procedure. You can read more details in the following series: Excel VBA Module and Procedure .

A function procedure in VBA Excel is one type of procedure that is marked with an opening statement ” Function ” and ends with a ” End Function ” statement.

In the process of working, the function procedure is actually the same as the Sub Procedure that we discussed earlier. The main difference between these two types of procedures is that Function Procedures can generate a return value from their work processes that are stored in the procedure name.

The way a function procedure works is very similar to the default excel functions such as the SUM function , AVERAGE function , MIN–MAX function and so on, where we can enter certain arguments into a procedure function, then the function performs calculations and then returns a result of that calculation.

Function procedures declared with the Public keyword can be used as excel formulas in an excel cell just like Excel’s built-in functions. For this reason, function procedures are also known as Custom Functions and User Defined Functions (UDF) .

And for the same reason, in this blog I prefer to refer to the default worksheet function as a function and not an excel formula.

How to Create a Function Procedure

Perhaps you are asking how to make your own function or formula in excel?

If the default functions or the built-in functions that excel provides are not sufficient, you can create an excel function yourself. The trick is to create a custom function or user defined function (UDF) by creating a public function through VBA Excel.

The syntax or how to create a function procedure in a VBA module is as follows:

[Private|Public|Friend][Static] Function Function_Name ([List_Argument]) As [Return_Data_Type]

[Instructions/Program Code]

[Exit Function]

[Instructions/Program Code]

[Function_Name = Return_Value]

End Function

Each element that is in square brackets “[…]” in the syntax of the function procedure above is optional , meaning that it can be used and can be ignored or not used.

A simple example of a function procedure is as follows:

Function areaSquare(length As Integer) As Long

     Dim wide As Long

     area = length * length

     areaSquare = area

End Function

Element [Private|Public|Friend][Static]

Because it has been explained in several previous series, this element will not be discussed again. To learn what it means, please read the previous VBA Excel macro series materials.

Element “Function”

You must use this ” Function ” text or element if you intend to create a Function procedure .

This element distinguishes the preparation of function procedures from other types of procedures.

Element “Procedure_Name”

This section shows the name of the procedure function that you created and that you should use. This function name will be used to call or be used on the worksheet. So name the function procedure that you created as wisely as possible. Ideally, please follow the example of how Office Excel names the default functions that are already available.

As explained in the discussion about VBA procedures, the name of this function also follows the following rules:

1. The first character must be a letter of the alphabet (AZ, az).
2. Subsequent characters can be letters, numbers or certain punctuation characters (not all punctuation marks can be used).
3. You can’t use periods (.) and spaces ( ) and you can’t use the following characters: #, $,%, &, @, ^, * and !.
4. There is no difference between uppercase and lowercase letters.
5. The maximum number of characters that can be used is 255.

Element [List_Argument]

This section shows a list or list of variables that represent the arguments used in the function procedure when it is called or used.

About List Arguments This function will be discussed separately at the bottom later.

Wow, why not just now? I like it donk ekekekeke….

Element “As [Return_Data_Type]”

This element is filled with the VBA data type starting with the keyword “As” as when declaring a variable.

This section shows the type of data that will be generated by the function we are creating. Writing for example: As Integer, As Long, As String, and so forth.

What types or types of data can be used? Please read the following series: Data Types in Excel VBA

Element [Instructions/Program Code]

Instructions / Program Codes are lines of command code that will be carried out by the Function procedure when it is executed or called.

The content of course adjusts to your needs or desires, which in essence is a certain calculation process by the related procedure when it is called.

Element [Exit Function]

Ideally a line of code will end when it reaches ” End Function “. However, under certain conditions you can exit the function procedure by using the ” Exit Function ” command .

The line of code ” Exit Function ” is a command to exit the related Function Procedure. ” Exit Function ” indicates that the function procedure must be terminated and not continued to the next program lines.

Element “Function_Name = Return_Value”

It has been explained above that a function procedure can produce a return value ( Return Value ) from its work process.

So how do we tell this return value to a function procedure?

To tell a function procedure what value to return, the trick is to assign that value to the function name.

Writing the line of code is to write the name of the function followed by equals (=) and the value you want to generate or return.

Consider again the following example function procedure:

Function areaSquare(length As Integer) As Long

     'Wide variable declaration

     Dim wide As Long

     'Calculates the area of a square according to the given length argument

     area = length * length

     'Assign return value to function areaSquare

     areaSquare = area

End Function

In the example above the line of code ” areaSquare = area ” tells the areaSquare function that the value it should return is the value of the area variable that is obtained by multiplying the length * length argument .

So in this case the function name also works as a variable.

Usually the line of code that tells the value to be returned by this kind of function is written at the end of the function before ” End Function ” or ” Exit Function “.

As an additional note that you can also summarize the area of ​​the Square function above as follows

Function areaSquare(length As Integer) As Long

     'Calculating area at once Assign return value to function areaSquare

     areaSquare = length * length

End Function

Make sure that when passing this return value to a function, the value given is the final value of a calculation process which may be quite lengthy.

“End Function” Element

This section marks the end of a Function Procedure.

When you press Enter to finish writing the opening declaration when creating a new procedure, usually the closing statement of this function procedure will be automatically created in VBE (Visual Basic Editor) .

Arguments in Function Procedures

I’m sure you are very familiar with the term argument in an excel function.

In general, this argument is more often used in function procedures, although basically you can use this argument list in Sub (Subroutine) type procedures that we discussed in the previous series.

The argument list is written between brackets (…) after the procedure name. The way of writing is similar to variable declaration in Excel VBA Macros , but without including the scope.

Writing arguments begins with the name of the argument followed by the data type and can also be accompanied by other keywords such as ByVal or ByRef and Optional statements, while the variable declaration does not exist.

If there is more than one argument, they are separated by a comma (,).

Consider the following example of a function procedure with the name DISCOUNT :

Function DISCOUNT (Amount of As Long, price of As Currency, Optional percent As Double = 0.01)

     DISCOUNT = quantity * price * percent

End Function

The function procedure with the name DISCOUNT above uses 3 arguments, each of which is: amount , price and percent .

Argument Procedure

When a procedure is accompanied by arguments, then to call the procedure must also provide input values ​​for the arguments. For example, to use the DISCOUNT function above on a cell as an excel formula you can write the following formula to use it:

= DISCOUNT(5;10000,0,1)

The value of the number 5 is the amount , 10000 is the price and 0.1 which is equal to 10% is for the percent argument .

Optional Keywords

As in Excel’s built-in functions, you can also create arguments as optional arguments that don’t have to be filled when called. To set an optional argument add the keyword ” Optional ” before the argument name.

You can also set this optional argument by default when the calling procedure does not include it. In the DISCOUNT function example above, the optional argument is percent with a default value of 0.01 (1%).

Optional percent As Double = 0.01

This default value of 0.01 will be ignored if when calling the DISCOUNT function you include the percent value as in the previous example. If not filled then this default value will be used.

In an excel cell you can call the DISCOUNT function like this:

= DISCOUNT(5;10000)

The excel formula above will produce the number 500 which is obtained from the calculation of 5 * 10000 and multiplied by 0.01 or 1% as the default percent value because you did not include the percent value when calling the DISCOUNT function.

To create an optional argument, start writing the argument with the keyword ” Optional ” followed by the name of the argument and if needed, set the default value by writing equal to (=) followed by the default value you want to set.

ByVal and ByRef Key keywords

In the preparation of procedure arguments, either function or sub types, you can also include ByVal or ByRef keywords .

ByRef keyword is the default keyword, so in the preparation of the DISKON function arguments above you can also write it like this:

Function DISCOUNT(ByRef amount As Long, ByRef price As Currency, Optional ByRef percent As Double = 0.01)

     DISCOUNT = quantity * price * percent

End Function

What is the meaning of using ByRef and ByVal keywords?

The ByVal and ByRef keywords basically indicate how an argument value supplied by the caller will be used by the called procedure. Is the value of the argument passed taken its value ( Value ) or treated as a reference ( Reference ).

• Keyword ByRef : Passes the memory location of a given value as an argument. The argument will be filled with the data form directly, therefore the value of the variable can be changed in the sub procedure or function.
• ByVal keyword : The argument will be filled with a copy of the data value only, in other words ByVal sends a given value into an argument and then the argument copies that value to be used. The argument procedure will only change the copy of the value and the original value will not change at all

I’m at a loss for words to explain about this ByRef and ByVal. For more details, we just practice.

Please create the following sub procedures and functions in a Standard module:

Option Explicit

Function VOLUMECUBE(ByRef length As Long) As Long

     length = length ^ 3

     VOLUMECUBE = length

End Function

Sub testVolume()

    Dim length  As Long

     length = 10

     'Call the VOLUMECUBE function and display the result value in a MesSage Box

     MsgBox "Cube Volume = " & VOLUMECUBE(length)

     'Display variable length value in a Message Box

     MsgBox "Variable Length = " & length

End Sub

If you have tried running the testVolume procedure. The result is the following 2 message boxes:

Pay attention to the number value that appears in the 2 message boxes. The first message box is the result of the VOLUMECUBE function , while the second is the value of the variable length .

Next, change the ByRef keyword with ByVal in the long argument, then run the testVolume procedure again. The result is the following 2 message boxes:

Notice the difference in the 2nd message box in the two exercises above.

When passing an argument by reference (ByRef), we are referring to the original value. The length value is changed in the function ( length = length ^ 3 ). Then this long variable is called again via the 2nd message box(Long MsgBox). As a result, the second MsgBox returns the value 1000.

When passing an argument by value (ByVal), we pass a copy of the value to the function (VOLUMECUBE(10)). The original length value does not change. As a result, the second MsgBox returns a value of 10 (the original value).

In the next series we will learn about how to use or call function procedures, but before that, please share this guide so that more people can benefit from it.

Are you getting confused? If you come across this page by accident I suggest reading some of the previous Excel VBA macro guide series so you will get a more complete understanding context.