To fully understand why you’re seeing this error, you have to first understand what a function is and how it works.
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 [ …… ] ] ] ] ])
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.
If age &gt; 60 Then
Debug.Print "Customer is a senior citizen"
Debug.Print "Customer is not a senior citizen"
custage = InputBox("Enter the age of the customer")
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
||Output of the example
||Converts the word in the argument into a lowercase word and returns it
||Removes the leading and trailing spaces of the argument
||Trim(” Santa Claus “)
This function returns a boolean value to state if te string passed as an argument in a valid Date
||IsDate( “20-11-2020” )
||This funciton returns the path in which the current file resides.
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.
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.
- 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.
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.
' select a cell in which the formula needs to be inserted
' insert the formula in the cell
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]&gt;250000,IF(RC[-1]&lt;700000,(RC[-1]* 10)/100,""to be calculated later""),""0"")"
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.”
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.
For a very closely related error, read the article Argument Not Optional.