# User Defined Functions

In this chapter you will learn how to create our own function in VBA. This is called UDF or User Defined Functions.

To create our own function we have to use Function keyword and end with End Function keyword.

## Arguments

We can pass arguments to a function which need to be used for calculate the output. For example, If we are creating a function for the Area of a rectangle then we need length and width as input.

```Function Rectangle_Area(lenght As Double, width As Double)

Rectangle_Area = lenght * lenght

End Function```

## Optional Arguments

To pass an optional argument we need put Optional keyword before the argument . You can use multiple optional arguments. You should keep all the optional argument at the end.

```Function Rectangle_Area(length As Double, Optional width)

If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If

End Function```

## Access the Function from Worksheet

We can access the VBA function on the excel sheet on the same way as we use the excel function like-Sum, Average etc.

For example, copy the below formula in the module.

• Go to Visual Basic Editor (Press Alt+F11)
• Insert a Module (Alt+I+M)
• Paste the below code in the module
```Function Rectangle_Area(length As Double, Optional width)

If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If

End Function```

Not go to excel sheet and type the formula Rectangle_Area

## Passing Arguments By Value and By Reference

We can pass an argument in VBA function in two ways:

ByVal – The argument is passed by Value. This means that just the value  is passed to the function and therefore, any changes that are made to the argument inside the procedure will be lost when the procedure is exited.

ByRef – The argument is passed by Reference. This means that the address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be remembered when the procedure is exited.

Let’s see the below example for more understanding-

• Go to Visual Basic Editor (Press Alt+F11)
• Insert a Module (Alt+I+M)
• Paste the below code in the module
```Function Get_Squire(ByVal i As Integer)

i = i * i

Get_Squire = i

End Function

Sub Check()
Dim x As Integer

x = 5

MsgBox Get_Squire(x)
MsgBox x

End Sub```

Now go to the worksheet and run the Check sub procedure by pressing Alt+F8.

You will get below given message

Now put the ByRef in place of ByVal

```Function Get_Squire(ByRef i As Integer)

i = i * i

Get_Squire = i

End Function```

Now go to the worksheet and run the Check sub procedure by pressing Alt+F8.

You will get below given message

Basically, when we are passing the argument ByVal then orginal value of x is not getting changed but when we are  passing the argument ByRef then original value of x is getting changed.

## Application.Volatile

Application.Volatile is used to recalculate the function automatically whenever the worksheet recalculate.

We can use Application.Volatile in the starting of the function as given below.

```Function Rectangle_Area(length As Double, Optional width)
Application.Volatile
If IsMissing(width) Then
Rectangle_Area = length * length
Else
Rectangle_Area = length * width
End If

End Function```