How to Use VBA Function Procedure with Arguments in Excel

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.

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: 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:

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:

• 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.

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.

• 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.

• 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:

• 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:

• 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”):

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

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

• 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)