The Excel Function Arguments

How to Show the Function Arguments in Excel

1. Show the Function Arguments While entering a Function

When you enter the function name and the first bracket after the equal sign, Excel will automatically show the arguments:

2. Show the Function Arguments Using the Keyboard Shortcut Ctrl+A

Enter a valid function and press Ctrl+A to see the arguments:

How Many Arguments Can a Function Have?

1. Functions with a Single Argument

Below is an example of a function with a single argument:

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

The argument is the text string in B2.

2. Functions with Multiple Arguments

To use multiple arguments in a function, you need to use a comma between them.

Example:

• To use the average function and sum function in two columns, you can either use a single argument with a range or two arguments, defining the two ranges separately:

&

C5:C14,D5:D14 are the arguments of the AVERAGE and SUM functions.

• In the example below there are three arguments. The formula is:

The TIME function uses hours, minutes, and seconds as arguments.

Read More: 51 Mostly Used Math and Trig Functions in Excel

3. Functions with No Arguments

Thee RAND(), TODAY(), and NOW() Functions don’t use any arguments.

3 Types of Arguments in Excel Functions

1. Required Arguments

Take the NETWORKDAYS function as an example.

Its syntax is:

The inputs inside parentheses without a square bracket are the required arguments.

In the following examples, the NETWORKDAYS function has two types of arguments: a required and an optional one.

2. Optional Arguments

In the example below, after entering the INDEX function, Excel automatically shows the required and optional arguments.

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

Functions can be used as arguments in a different function.

• To add 5 years to a specific date, use the formula:

The main function is DATE. YEAR, MONTH, and DAY are nested in the DATE function. They are accepted as arguments.

YEAR(A2)+B2 is used as the first argument of the DATE function.

Finding the Value of Nested Functions:

• To find the values of the nested functions, select the nested formula within the function and press F9.

• Click the argument of the main function to see the nested arguments.

2 Ways to Insert Arguments in Excel

1. Entering the Function Directly in a Cell

Select that cell, enter the “=” sign and add the function name to see the arguments it can accept within the parentheses.

2. Using Excel Function Argument Dialog Box

• Select the formulas tab.
• Choose a formula. Here, the NETWORKDAYS function.

Types of Arguments in a Function

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

Read More: How to Use Format Function in Excel

Arguments in an Excel VBA Function

Excel VBA has three types of procedures. Sub, Function, and Property.

Function has the following syntax:

[(arglist)] refers to arguments of the Function in VBA. The [] around arglist indicate that this part is optional.

The arglist function has the following syntax:

• Optional:

The argument is optional and the next arguments are also optional.

• ByVal:

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

• ByRef:

This is an argument used by default. If you do not specify it, Excel will consider that you are passing reference to variables instead of values.

• ParamArray:

It cannot be used with Optional, ByVal or ByRef. It is also an optional argument that allows you to use an arbitrary number of arguments.

• varname:

This is a required argument. It names the variables.

• type:

It is an optional argument. You can set the data type. When use as required, it allows you to set a user-defined data type.

• defaultvalue:

Any constant or expression of a constant. It 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


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

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

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

Excel VBA Functions with No Arguments

Observe the following image:

• Choose File ➪ Options ➪ General.
• Enter the custom function that has no arguments.

This function returns the UserName property of the Application object. This name can be entered in the Personalize your copy of the Microsoft Office section in the Excel Options dialog box.

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

When you enter the following formula, 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.

Further Readings

• Most Useful and Advanced Excel Functions List
• Top Excel Functions and Features for Management Consultants
• Compatibility Function in Excel

<< Go Back to Excel Functions | Learn Excel