We knew how to create custom VBA functions and use them in the worksheet. If you are looking for special tricks to know how to use VBA function procedure arguments in Excel, you’ve come to the right place. This article focuses on various kinds of arguments you can use in VBA functions. Function arguments can be variables (including arrays), constants, literals, or expressions. Keep in mind the following about function procedure arguments:

- Some functions do not have any arguments.
- Some functions have a fixed number of required arguments (from 1 to 60).
- Some functions have a combination of required and optional arguments.

The following sections demonstrate how to use the VBA function procedure with arguments in Excel.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.

## Function vs Sub Procedure in VBA

**Function:**

Functions are programs that perform statements and return their results. The purpose of functions is to automate repetitive tasks.

Using functions in a large program primarily avoids redundancy and ensures reusability. Functions are normally used to return values.

Here is an example of a function.

```
Function Area(Length, Width)
'Finding out the Area of the Rectangle.
Area = Length * Width
MsgBox "The Area of your Rectangle is: " & Area
End Function
```

**Sub Procedure:**

An argument to a sub-procedure specifies a set of statements that perform the specified actions, but the result will not be returned from the sub-procedure. As shown below, Sub does not have a return type-like function. Mainly used to simplify maintenance of large programs by dividing them into smaller parts. Between Sub and End Sub statements, a sub procedure contains a series of statements.

Here is an example of Sub Procedure.

```
Sub GetRowNumber()
rowNumber = Range("B4").row
MsgBox "Here,Row Number is: " & rowNumber
End Sub
```

## How to Write a Function in Excel VBA

The syntax of a function is given value:

**Function Name of function [(arglist)] [As type]**

**[statements]**

**End Function**

**Function**: It is necessary to use the term “function” as a keyword.:

**Name of Function:** You are free to use any name you like when referring to a function. It is necessary to adhere to specific naming norms.

**argList:** At the time of a function call, variables are passed to it.

**Type**: The function’s return type is its data type.

**Statements:** Function-specific actions.

Here is an example of a function.

```
Function Area(Length, Width)
Area = Length * Width
MsgBox "The Area of your Rectangle is: " & Area
End Function
```

By using the above function we will calculate the area of a rectangle in Excel.

To do this, after inserting the code in the VBA module, you have to use the following formula in cell D5:

`=Area(100,40)`

The area of a rectangle can then be calculated by pressing **Enter** after that. This function displays the area of the rectangle in a message box after the user provides it with two arguments (Length and Width).

## What Are Arguments in VBA?

During procedure definition, arguments are declared, which can be used to pass data to VBA procedures. Here, the sub-procedure takes more than one argument.

```
Sub calculation()
Call house_1_calculation(380955, 49505)
End Sub
Sub house_1_calculation(price As Single, wage As Single)
If 2.5 * wage <= 0.8 * price Then
MsgBox "This place is not affordable."
Else
MsgBox "This place is suitable for you."
End If
End Sub
```

Keep in mind that arguments are not required for VBA Functions or Subs. They may not be necessary for all procedures.

You can also specify optional arguments for VBA procedures.

There are two ways that arguments can be supplied to VBA procedures: ByRef, ByVal

## 3 Examples of VBA Function Procedure with Arguments in Excel

In the following section, we will use three effective and tricky examples of VBA function procedure with arguments in Excel. This section provides extensive details on these examples. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the **Microsoft Office 365** version here, but you can utilize any other version according to your preference.

### 1. A Function with One Argument

Here we will demonstrate two examples of a function with the argument: converting text to speech and calculating commission. The examples are discussed in detail in this section.

#### 1.1 Converting Text to Speech

Here, we will demonstrate the first example of a function with one argument. For demonstration purposes, we will use the following dataset, where we will convert the text into speech.

Here, we will convert the text “Good Achievement” into speech. Let’s walk through the following steps to do the task.

**📌 Steps:**

- VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the
**Developer**tab on your ribbon. Then select**Visual Basic**from the**Code**group.

- VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the
**Insert**tab on the VBA editor. Then click on**Module**from the drop-down.

- As a result, a new module will be created.
- Now select the module if it isn’t already selected. Then write down the following code in it.

```
Function SpeakTheText(text)
Application.Speech.Speak (text)
End Function
```

- Next, save the code.
- Then, if you want to use the SpeakTheText() function to hear the content of cell
**B5**, you have to use the following formula in cell**C5**:

`=SpeakTheText(B5)`

- Next, press
**Enter**. - Therefore, you will hear the voice saying
**“Good Achievement”**. To hear the voice, your system must be set up to play sound.

**Overview of Speak() Method:**

We will introduce you to the method** Speak()**. This method has the following syntax:

**expression.Speak (Text, [SpeakAsync], [SpeakXML], [Purge])**

The expression represents a Speech object.

Though **Speak()** method can take three arguments, but Text argument is the required one, the rest are optional. We shall use the **Speak()** function by passing only the Text argument. So, for this case, it is a function with one argument. This method uses an Excel text-to-speech generator to “speak” the text passed to it through Text argument.

When you use the** SpeakTheText function** in a worksheet formula, the function always returns 0, because this function is returning nothing. In other words, I can say, there is no value assigned to the function’s name (SpeakTheText) variable.

#### 1.2 Calculating Commission

Here, we will demonstrate the second example of a function with one argument. Now we are going to deal with a more complex function. This function is designed for a sales manager who calculates the monthly commission that he has to pay to his salespersons. To boost up the sales of the company, the commission rate structure is based on the amount sold— those who will sell more will earn a higher commission rate. The function takes the sales amount through the only argument (Sales) and returns the commission earned by a salesperson. The function will calculate the commission according to the following dataset. For demonstration purposes, we will use the following dataset, where we will calculate commission in three different ways. First, we will use the **nested IF **function, then the **VLOOKUP** function, and finally we will use the VBA code to calculate the commission value.

- First of all, select the cell and type the following formula.

`=IF(AND(B6>=0,B6<=9999.99),B6*0.08,IF(AND(B6>=10000,B6<=19999.99),B6*0.105,IF(AND(B6>=20000,B6<=39999.99),B6*0.12,IF(B6>=40000,B6*0.14,0))))`

- Next, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. Therefore, you will get the following commission calculated by the nested**IF**function. - Then, select the cell and type the following formula.

`=VLOOKUP(B6, $G$6:$H$9,2)*B6`

- Next, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. Therefore, you will get the following commission calculated by the**VLOOKUP**function.

Now, we are going to calculate the commission by using the VBA code. Let’s walk through the following steps to do the task.

**📌 Steps:**

- VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the
**Developer**tab on your ribbon. Then select**Visual Basic**from the**Code**group.

- VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the
**Insert**tab on the VBA editor. Then click on**Module**from the drop-down.

- As a result, a new module will be created.
- Now select the module if it isn’t already selected. Then write down the following code in it.

```
Function CommissionEarning(Sales)
'Calculates sales commissions
Rate1 = 0.08
Rate2 = 0.105
Rate3 = 0.12
Rate4 = 0.14
Select Case Sales
Case 0 To 9999.99
CommissionEarning = Sales * Rate1
Case 10000 To 19999.99
CommissionEarning = Sales * Rate2
Case 20000 To 39999.99
CommissionEarning = Sales * Rate3
Case Is >= 40000
CommissionEarning = Sales * Rate4
End Select
End Function
```

- Next, save the code.
- Then, you have to use the following formula in cell
**E5**:

`=CommissionEarning(B5)`

- Next, press
**Enter**. - Therefore, you will; be able to calculate the commission by using the VBA code as shown below.

- Then copy this function for other cells. Therefore, you will get the following output.

### 2. A Function with Two Arguments

In this example, we are going to write a VBA function that will calculate the area of a rectangle. We know the area of a rectangle= Length ☓ Width.

Here, we will use the following length and width to calculate the Area of a rectangle.

Let’s walk through the following steps to do the task.

**📌 Steps:**

- VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the
**Developer**tab on your ribbon. Then select**Visual Basic**from the**Code**group.

- VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the
**Insert**tab on the VBA editor. Then click on**Module**from the drop-down.

- As a result, a new module will be created.
- Now select the module if it isn’t already selected. Then write down the following code in it.

```
Function Area(Length, Width)
'Finding out the Area of the Rectangle.
Area = Length * Width
MsgBox "The Area of your Rectangle is: " & Area
End Function
```

- Next, save the code.
- Then, you have to use the following formula in cell
**D5**:

`=Area(100,40)`

- Next, press
**Enter**. - Therefore, you will be able to calculate the area of a rectangle. This function takes two arguments(Length and Width) from the user and shows the area of the rectangle in a message box.

**Similar Readings**

**How to Use VBA Rnd in Excel (4 Methods)****Use the VBA Environ Function (4 Examples)****How to Use VBA Int Function in Excel ( 3 Examples)****Use VBA IsEmpty Function (5 Relevant Examples)****How to Use the VBA Weekday Function (2 Suitable Examples)**

### 3. A Function with a Range of Arguments

Here, we will demonstrate the last example of using the VBA function procedure with arguments in Excel. Let’s make some assumptions. Say, you have some values. The number of values may be 100, 1000, or any. Your boss has told you to give him the average of the largest 5 values of this data. What can you do? First, you’ll think that whether there is any predefined function in Excel to solve it efficiently. Yes, it has a** LARGE function**. The LARGE function does not give the result directly. You have to use it trickily. You can use the** LARGE** function in the following way (assuming that you have 20 values and the data range is named “DataAvg”):

`=(LARGE(DataAvg, 1)+LARGE(DataAvg, 2)+LARGE(DataAvg, 3)+ LARGE(DataAvg, 4)+LARGE(DataAvg, 5))/5`

The above formula will return the average of the largest 5 values of your data. The formula works fine, but you will face a problem when next time your boss will ask you to average of 100 values from a data range of say 2000 values. This time you have to rewrite this formula in a new way and it is really toilsome. Here comes the custom VBA function, TopAverage (DatAvg, Num).

The dataset will look like this.

Let’s walk through the following steps to do the task.

**📌 Steps:**

- VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the
**Developer**tab on your ribbon. Then select**Visual Basic**from the**Code**group.

- VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the
**Insert**tab on the VBA editor. Then click on**Module**from the drop-down.

- As a result, a new module will be created.
- Now select the module if it isn’t already selected. Then write down the following code in it.

```
Function TopAverage(DataAvg, Num)
'Returns the average of the highest Num values in Data
Sum = 0
For i = 1 To Num
Sum = Sum + WorksheetFunction.Large(DataAvg, i)
'Large(Data, i) returns the ith largest value from the Data.
Next i
TopAverage = Sum / Num
MsgBox "The average is: " & TopAverage
End Function
```

The code initializes the Sum variable to 0. Then it uses For- Next loop to calculate the sum of the Num number largest values. Notice that we have used the built-in LARGE function of Excel in the code. You can use any worksheet function in the custom VBA function using WorksheetFunction and a period before the function name (WorksheetFunction.LARGE).

Finally, the average is found just by dividing the Sum by the Num (Sum/Num). We showed the result in a message box with this text: “The average is: “.

- Next, save the code.
- Then, you have to use the following formula in cell C5:

`=TopAverage(DataAvg,5)`

- Next, press
**Enter**. - Therefore, you will get the following output.

This situation exemplifies how a custom function can make things easier for you. This function takes two arguments:** DataAvg** and **Num**. **DataAvg** represents the range of data that you’re working with. Num is the number of largest values you want to average from the data range.

## How to Create Sub Procedure with Argument in Excel

A Sub procedure is a collection of Visual Basic statements that are delimited by the Sub and End Sub statements and that carry out operations without producing a result. Constants, variables, and expressions that are supplied by a calling procedure are examples of arguments that a sub-procedure may accept. There must be an empty parenthesis in the Sub statement if the Sub procedure has no arguments.

Here, the sub-procedure takes more than one argument.

```
Sub calculation()
Call house_1_calculation(380955, 49505)
End Sub
Sub house_1_calculation(price As Single, wage As Single)
If 2.5 * wage <= 0.8 * price Then
MsgBox "This place is not affordable."
Else
MsgBox "This place is suitable for you."
End If
End Sub
```

Therefore, using the aforementioned code, we can quickly determine whether the house is a good fit for us based on our wage and the asking price of the house. We will receive the following output after running the aforementioned code that was inserted into the VBA module.

## Conclusion

That’s the end of today’s session. I strongly believe that from now, you may be able to VBA function procedure with arguments in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**VBA Date Function (12 Uses of Macros with Examples)****How to Use VBA InstRev Function (7 Suitable Examples)****Use the VBA Chr Function (2 Examples)****How to Use the Left Function in VBA in Excel (2 Examples)****Call a Sub in VBA in Excel (4 Examples)****How to Return a Value in VBA Function (Both Array and Non-Array Values)**