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

calculating area of rectangle

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.

converting text to speech to illustrate how to Use VBA Function Procedure with Arguments in Excel

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.

inserting module

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

type the function to illustrate how to Use VBA Function Procedure with Arguments in Excel

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.

calculating commission to illustrate how to Use VBA Function Procedure with Arguments in Excel

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

using nested if function

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

using vlookup function

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

apply custom function to illustrate how to Use VBA Function Procedure with Arguments in Excel

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

show the output to illustrate how to Use VBA Function Procedure with Arguments in Excel


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.

calculating area to illustrate how to Use VBA Function Procedure with Arguments in Excel

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.

open VBA tab

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

calculating area to illustrate how to Use VBA Function Procedure with Arguments in Excel


Similar Readings


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.

inserting module to illustrate how to Use VBA Function Procedure with Arguments in Excel

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

calculating average to illustrate how to Use VBA Function Procedure with Arguments in Excel

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.

creating sub procedure with arguments


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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment

Leave a reply

ExcelDemy
Logo