What is "You've Entered too Many Arguments"? - VBA and VB.Net Tutorials, Education and Programming Services

What is “You’ve Entered too Many Arguments”?

Dec 04, 2020 by Lakshmi Ramakrishnan in Error

To fully understand why you’re seeing this error, you have to first understand what a function is and how it works.

Contents

Basics of a function

A function is a block of code which can do any repetitive task. It can be run any number of times and anywhere in the project without repeating identical code over and over again.

For example, you may want to develop a function to:

Format an Excel sheet based on some conditions
Use or display user information
Perform mathematical calculations

By facilitating code reuse, functions make your code free from redundancy and also cut down the maintenance overhead. There is no need to make changes to the same code in several different areas. Instead, the function can be enhanced / updated as required. It saves time and effort and keeps your code organized.

Parameters in a Function

The input data that may be required for any action the function does is passed into the function in the form of parameters or arguments. Functions can also return some value after performing an action. Such a value is called a return value.

Syntax for arguments:

Function <Function name> ([argument1 [, argument2 [, argument3 [ …… ] ] ] ] ])

<function code>

End Function

For example:

This function prints whether the customer is a senior citizen or not by just validating the customer’s age — which is passed to the function as an argument. In the sub procedure below, this function is called by passing the age value received from the user as an input argument.

'called function
Function typeofcustomer(age)
    If age &amp;amp;gt; 60 Then
        Debug.Print "Customer is a senior citizen"
    Else
        Debug.Print "Customer is not a senior citizen"
    End If

End Function
Sub findout()
    custage = InputBox("Enter the age of the customer")
'calling function
    typeofcustomer (custage)
End Sub

Built-in functions

Built-in functions are readymade functions predefined and offered by the programming language. They can be widely used in our code to made it work efficiently.

VBA Built-in Functions

The following are some built-in functions offered by VBA

S.no	Function name	Description	Syntax	Example	Output of the example
1	Lcase()	Converts the word in the argument into a lowercase word and returns it	Lcase (<string>)	Lcase(“ApplE”)	“apple”
2	Trim()	Removes the leading and trailing spaces of the argument	Trim (<string>)	Trim(” Santa Claus “)	“Santa Claus”
3	IsDate()	This function returns a boolean value to state if te string passed as an argument in a valid Date	IsDate (<string>)	IsDate( “20-11-2020” )	TRUE
4	CurDir()	This funciton returns the path in which the current file resides.	CurDir	CurDir	“C:\Users\JANANI\Documents”

MS Excel Built-in Functions

Similarly, Microsoft Excel ( the host application) also facilitates the users with a wide range of in-built functions. Some of those are furnished below.

If
Vlookup
Hlookup
Count
Countif
Sum
Sumif
Average
Trim
Min
Max
And
Or
Not
Iferror
Xor

To see the functions and their descriptions directly in Excel , you can select a cell and click on the “fx” symbol as shown in the image below. This will open up a dialog with a list of functions under each category to choose from. Depending on what you’re trying to do, a function can be chosen and used.

Select the category to see the list of functions under it.

Common causes for the error

Very often, you’ll find that typos are the cause of the error “You’ve entered too many arguments.”

In the above list, there are several functions which can be nested and used in conjunction with one another.

For example, the syntax for If function is

IF( condition1, <what is to be done or displayed if condition is true>,<what is to be done or displayed if condition is false>)

Now this can lead to a nested if condition if another condition needs to be tested in the place of the second or third parameter.

Scenario:

Here is a formula with nested if conditions to calculate tax amount for salaries.

If any of the brackets or commas as not closed, i.e not as per the syntax requirements, we get the error below.

If we add unnecessary arguments accidentally, we see the same error again.

For the scenario, we are trying to find the total marks of a specific student using the Vlookup formula.

In this case, we have provided an extra argument by mistake and the error below shows up.

Solution

So to fix the error “You’ve entered too many arguments for this function” you need to go through the content of the cell, character-by-character, to ensure that there are no syntax errors. In other words, you need to check whether all opened brackets are closed and all commas are properly in place. Also, the number of arguments you provide has to match the function’s syntax.

Note: Functions like Average, Sum, Min and Max have no limitations in the number of arguments.

But what about in VBA?

If we try to write the same formula in Excel using VBA, the code looks like the example below. It also works well.

Sub arg_error()

' select a cell in which the formula needs to be inserted
    Range("B3").Select

' insert the formula in the cell
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]&amp;amp;gt;250000,IF(RC[-1]&amp;amp;lt;700000,(RC[-1]* 10)/100,""to be calculated later""),""0"")"

End Sub

But if similar mistakes are made in this VBA code — i.e omission of a parentheses, or of a comma, or the existence of an additional parameter, the error that is thrown is actually very generic. This might confuse the user.

You end up getting the more generic “Run-time error ‘1004’: Application-defined or object defined error.”

Conclusion:

The error “You’ve entered too many arguments for this function” is not specific to the “If” condition or function in MS. Excel. It can be thrown when the syntax is incomplete for any of the in-built functions used in Excel.

A tip to avoid this kind of typo and careless error

When you open a bracket to insert a new condition or data, it is better to close the bracket before inserting that data. So as soon as you type a “(“ , you should immediate type a “)” and then move the cursor between these brackets to insert the required data. This can avoid syntax errors that eat up your time.