What Are Excel Function Arguments (A Detailed Discussion)

Excel has many functions for different operations. Inside the parentheses of the functions, we put some inputs for the desired operation that we want to perform. These inputs inside the parentheses are called function arguments. In this article, we will get to know what function arguments are in Excel. Also, we will be familiarized with different types of arguments.

How to Show Function Arguments in Excel

We can show function arguments in two ways.

1. Show Function Arguments While Typing a Function

When you type a function name after placing an equal sign and then type the first bracket, Excel will automatically show the respective arguments. Look at the following image.

When you type =IF(, the arguments of the IF function appears automatically.

2. Show Function Arguments Using Keyboard Shortcut Ctrl+A

In the previous way, you can just see the arguments, but you can both see and place input using this way. After typing a valid function name followed by an equal sign into the formula bar/any cell, the following shortcut presents the function arguments dialog box.

The window appears. Now you can see the arguments and can input numbers in the boxes too.

Read More: How to Use VBA Input Function in Excel (2 Examples)

How Many Arguments Can Function Have?

Different Excel functions have a different number of arguments. Even, some of them have no arguments at all. Let’s see.

1. Functions with a Single Argument

Arguments are mostly referred to individual cells but it also includes cell ranges. Below is an example of a function with a single argument.

• Here we used the UPPER function which takes text-string as an argument.
• It accepts a single argument as input and converts the lowercase letter into the uppercase letter.
• The formula used in cell B4 is:

Here, the argument is a text string placed in cell B2.

2. Functions with Multiple Arguments

If you want to use multiple arguments in a function you need to use a comma in between them.

Example:

• In some cases, you need to calculate the average function and sum function of two columns. Either, you can use the single argument with a range or you can use two arguments where you can define the two ranges separately like,

&

Here, (C5:C14,D5:D14) are the arguments for AVERAGE and SUM functions. As there are multiple arguments, they are separated by commas in between them.

• You can see the below example which uses three arguments. The formula is as follows.

In this example, the function is the TIME function and it uses hours, minutes, and seconds as arguments.

Read More: How to Put Comma After 2 Digits in Excel (9 Quick Methods)

3. Functions with No Arguments

Though most functions use arguments, Excel has some predefined functions that don’t use any arguments. Such as RAND(), TODAY(), and NOW().

Read More: How to Use VBA User Defined Function (4 Suitable Examples)

3 Types of Arguments in Excel Functions

1. Arguments of Required Type

Every Excel function that has arguments, has at least one required argument. A function must have all its required arguments to return a valid answer. As an example, let’s see the NETWORKDAYS function.

This is the syntax of the NETWORKDAYS function is as follows.

The inputs in the parenthesis of a function without a square bracket are the required arguments here. In the following examples, the function NETWORKDAYS has two types of arguments: a required type and an optional one. With the required arguments the function has returned 86 days as a result.

2. Arguments of Optional Type

There are some functions that use some arguments as optional. Like the below image, after typing INDEX function, Excel automatically shows the required and optional arguments of that function.

Read More: How to Use INDEX MATCH with Excel VBA

3. Nested Functions Used as Arguments of Another Function in an Excel Formula

Functions can be used as arguments under a different function. This process is known as the nesting function. For example, we want to add 5 years to a specific date to find out the resultant date. we will be using the formula-

Here the main function is DATE. YEAR, MONTH, and DAY are the other functions that are nested in the DATE function. These additional functions are accepted as arguments for the DATE function. Like the YEAR(A2)+B2 has been used as the first argument of the DATE function.

Finding the Value of Nested Functions:

• To find the values of the nested functions which are used as arguments for a different function you just need to select the nested formula within and press F9.

• After that, press the argument of the main function, and follow the pictures below to see what exactly is done.

Here firstly we select the first argument of the main function. In the second picture, we just press the F9 of the keyboard button. It showed the specific result of that argument. You can even use this procedure for single functions also.

Similar Readings

• 22 Macro Examples in Excel VBA
• 20 Practical Coding Tips to Master Excel VBA
• How to Write VBA Code in Excel (With Easy Steps)
• Types of VBA Macros in Excel (A Quick Guide)
• What You Can Do with VBA (6 Practical Uses)

2 Ways to Insert Arguments in Excel

There are two ways in which you can insert arguments in Excel.

• Typing the function directly in a cell
• Using Excel function Argument dialogue box.

1. Typing Function Directly in a Cell

In this way, if you want to use any function in any specific cell, select that cell and start writing the function name with a “=” sign in the beginning. While you write the function name in Excel you will see what kind of arguments it can accept within the parenthesis. The picture below shows how to write a function with arguments in Excel

2. Using Excel Function Argument Dialog Box

It`s a good practice to use the Excel function argument dialogue box to write the function name with arguments. In order to find the function argument dialogue box, press on the formulas tab on top and select any formula from there you will see a box will appear. Now, in this example, I showed the NETWORKDAYS function with its available arguments.

The best part of using the function argument dialogue box is that you can insert the arguments by knowing them properly. So, before inserting any arguments, you actually know what you are performing.

Value Types of Arguments in a Function

Excel accepts many different kinds of inputs as arguments depending on the functions you want to use. Most of the arguments in Excel are numeric data as people vastly use Excel for numeric calculations. But it also accepts other data types. The types of arguments that are used in Excel are given below.

• Numeric data (=SUM(5,10))
• Text-string data (=UPPER(“Thomson”))
• Boolean values (=OR(1+1=2))
• Error Values (=ISERR(#VALUE!))

Arguments in Excel VBA Function

Excel VBA has three types of procedures. Sub, Function, and Property. Of these, Function has the following syntax.

Here we notice that it has [(arglist)] which refers to arguments of Function statement in Excel VBA. The [] around arglist indicates that this part is optional for the Function procedure. Now let’s see the parts of a Function argument list.

Function arglist has the following syntax.

We will discuss each of these parts with examples.

• Optional:

This indicates that an argument is optional if you use it for an argument. next arguments must be optional too, and you have to declare them with the optional keyword.

• ByVal:

This indicates that the arguments are passed by value instead of reference. This is also an optional type of argument.

• ByRef:

This is by default argument. If you d not specify anything, Excel will consider that you are passing reference of variables instead values. Using this ensures that the procedure through which it is being passed can change it.

• ParamArray:

It is the last argument in the list when used. You cannot use Optional, ByVal or ByRef with it. It is also an optional type of argument. It allows us to use an arbitrary number of arguments.

• varname:

This is a required type of argument. With this, you have to give names to variables with standard conventional rules.

• type:

This is also an optional argument. You can set the data type with this. If not optional, you can set any user-defined data type.

• defaultvalue:

any constant or expression of a constant. Only applies to optional parameters. An explicit default value can only be Nothing if the type is an Object.

Example 1:

Function CalculateNum_Difference_Optional(Number1 As Integer, Optional Number2 As Integer) As Double
If Number2 = 0 Then Number2 = 100
CalculateNum_Difference_Optional = Number2 - Number1
End Function
Sub Number_Difference_Optional()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Num_Diff_Opt As Double
Number1 = "5"
Num_Diff_Opt = CalculateNum_Difference_Optional(Number1)
Debug.Print Num_Diff_Opt
End Sub

Observe the following line:

Function CalculateNum_Difference_Optional(Number1 As Integer, Optional Number2 As Integer) As Double


Here,

CalculateNum_Difference_Optional is the function name, Number1, Number 2 are varname, Integer is type declared.

Example 2: Use of Default Value
We can set a default argument for a function, which means we’ll never select that argument, a default value will be chosen always.

Sub Number_Difference_Default()
Dim NumberX As Integer
NumberX = CalculateNum_Difference_Default(Number1)
MsgBox NumberX
End Sub
Function CalculateNum_Difference_Default(Number1 As Integer, Optional Number2 As Integer = "100") As Double
CalculateNum_Difference_Default = Number2 - Number1
End Function

Example 3: Use of ByRef

Sub Using_ByRef()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByRef n As Long)
n = 100
End Sub

Example 4: Use of ByVal

Sub Using_ByVal()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByVal n As Long)
n = 100
End Sub

Read More: List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)

Excel VBA Functions with No Arguments

In Excel VBA, you can write a function with as many arguments as you need basis. But it is also possible to write a function without an argument.

See the following procedure:

Choose File ➪ Options ➪ General to see this section. After that, the following is a simple example of a custom function that has no arguments. This function returns the UserName property of the Application object. This name appears in the Personalize your copy of the Microsoft Office section of the Excel Options dialog box. This function is very simple, but it is the only way you can get the username to use in a worksheet cell or formula.

Function OfficeUserName() 
'Returns the name of the current user 
OfficeUserName = Application.UserName 
End Function

When you enter the following formula into a worksheet cell, the cell displays the name of the current user:

When you use a function with no arguments, you must include a set of empty parentheses.

Conclusion

So we have discussed Excel function arguments in this article. If you find the discussion useful, please let us know in the comment box. And for more Excel-related articles, please visit our blog ExcelDemy.

Further Readings

• Introduction to VBA Features and Applications
• How to Use Select Case Statement in Excel VBA (2 Examples)
• Excel VBA to Populate Array with Cell Values (4 Suitable Examples)
• 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
• How Different Is VBA from Other Programming Languages
• Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)