Before we dive into the solution for the error above, let us go back to basics to make sure you understand what a function is. This will be important for fixing and troubleshooting the error. Feel free to skip ahead if you have a good grasp of this concept already.

What is a function?

A block of code that is reusable can be called a “function.” The word “reusable” tells us that the function can be called any number of times and from anywhere in the project.

If we need to make an enhancement or modification in any flow of action which is already encapsulated in a function, we only need to make the change in one place — the function. This will handle the change in flow wherever the function has been called.

The block of code with a function name that is called in several places is named the “Called Function” and the line of code that calls the function is the  “Calling function.”

Arguments in a function

Let’s say you have some data that’s required for the block of code — the function — to programmatically run. This data can be input in the same line as the function name.

These inputs are called as “Arguments” or “Parameters” in programming. We can define the data type of the arguments along with the variable names when defining the function. However, this is not mandatory.

Based on the usage of the variable arguments in the code, the data type of the arguments can be assumed too.

Return value in a function

After running the block of code programmatically, there may be some information that the function wants to pass back to the calling function. This is called the return value. Optionally, the data type of the return value can also be mentioned while defining the function.

Defining a function with arguments and a return value

Function

Let’s imagine a function called from a sub procedure of a save button on a student admission form. Let us name the function fn_SaveStudent. The function will take all the field values from the form GUI and insert them as a student record into a database.

Arguments

The field values need to be sent as arguments from the calling function to the called function.

Return Value

In turn, the database will generate a “Student ID” automatically. The function can return this value to the calling function, which in turn can display the “Student ID” on any field in the student admission form”  or in a message box.

Syntax of a function

Function <Function’s name> ([arg1 [, arg2 [, arg3 [ …… ] ] ] ] ])

<function code>

End Function

Where “arg” represents an argument.

For Example:

'Sub – procedure for the click event of a save button
Private Sub cmdSave_Click()
    strName = txtName.Value
    intAge = lstAge.Value
    strGen = lstgender.Value
    lngContact = txtcontact.Value
    Call fn_SaveStudent(strName, intAge, strGen, lngContact)
End Sub
Function fn_SaveStudent(strName As String, intAge As Integer, strGen As String, lngContact As Long) As String
    'code to insert the variable values in the parameters into the DB and get back the generated Student Id
    fnSaveStudent = strstudentId
End Function

Built-In Functions

These functions are the predefined blocks of code offered by programming languages for various purposes. In VBA, we have a wide range of built-in functions. Some examples are found below.

S.no	Function Name	Quick Description	Syntax
1	Split	Split a string using a character as a delimiter. An array of words is returned	SPLIT ( <expression> [,<delimiter>] [,<limit>] [,<compare>])
2	Cdbl	Convert expression to a double data type	CDBL ( <expression> )
3	chr	Returns a string (character value) that corresponds to te ascii value passed as a parameter.	chr ( <ascii value> )
4	Mid	Returns the middle part of a string when starting position and length are provided	MID ( <string>, <start> [,<length>])
5	Left	Returns the left portion of a string when length is specified	LEFT ( <string>, <length> )
6	Right	Returns the right portion of a string when length is specified	RIGHT ( <string>, <length> )
7	Trim	Trim the leading and trailing spaced of a string	Trim ( <string> )
8	Ucase	Converts all characters of the string to Upper case	Ucase ( <string> )
9	Lcase	Converts all characters of the string to Lower case	Lcase ( <string> )
10	Instr	Checks if one string is a substring of another string. If yes, it returns the starting position or else it returns 0.	INSTR([<start>] ,<string1> ,<string2> [,<compare>])
11	Now	Returns the current system date and time	now()
12	Cstr	Converts the passed parameter value to a string	cstr ( <expression> )
13	Val	Provides the mathematical value of the parameter.	value ( <expression> )
14	Ltrim	Removes the leading spaces of the string and returns it.	Ltrim ( <string> )
15	Rtrim	Removes the trailing spaces of the string and returns it.	Rtrim ( <string> )
16	Date	Returns the current system date	date ( <expression> )
17	Day	Returns the day from the date parameter passed.	Day (<a date in the yyyy,mm,dd format>)
18	Sin	Returns the sin value	Sin  ( < expression> )
19	Tan	Returns the tangent value	Tan  ( < expression> )
20	Cot	Returns the Cotangent value	Cot  ( < expression> )
21	Log	Returns the log value	Log  ( < expression> )
22	Cos	Returns the cos value	Cos  ( < expression> )
23	Isnull	Returns a boolean value depending on whether the parameter value is equivalent to “null” or not	Isnull  ( <expression> )
24	Isnumeric	Returns a boolean value depending on whether the parameter value is equivalent to a numeric value or not	Isnumeric ( <expression> )
25	Rate	Returns a value of double data type as the interest rate for a series of equal cash flows at regular intervals.	RATE( <number_payments>, <payment>, <PV>, [<FV>], [<Type>], [<Estimate>] )
26	Cint	Comvert the parameter value to an integer value	cint ( <expression> )
27	Mkdir	Make the directory i.e. create a folder	mkdir ( <path_name> )
28	Cvar	Convert the paramter value to a value of variant data type	CVAR(expression)
29	Ccur	Convert the paramter value to a value of currency data type	ccur ( <path_name> )
30	cdate	Convert the paramter value to a date	cdate ( <path_name> )

Optional arguments & the error “Argument not optional”

User-defined functions

For user-defined functions, the arguments are considered optional or mandatory depending on whether you used the “OPTIONAL” keyword when defining the parameters in the called function.

Example

In this called function, no argument is defined with the “Optional” keyword, so when an argument is not passed in the calling function, we see the error message “Argument not Optional.”

In order to resolve this, we need to either pass the parameter in the calling function or mark the rollno argument as optional. The below piece of code fixes the error.

Sub demo1()
    Call fn_demo1("Baby Aneesh", "A+")
End Sub
Function fn_demo1(strname, strgrade, Optional ByVal introllno As Integer)
    'Just display the values of all parameters
    MsgBox "Student name: " & strname & vbCrLf & "Grade: " & strgrade & vbCrLf & "Roll no: " & introllno
End Function

Built-in Functions

In the case of built-in functions, the syntax clearly states whether the parameters are optional or mandatory.

Example

Syntax of Split Function from the table above:

SPLIT ( <expression> [,<delimiter>] [,<limit>] [,<compare>])

Here only the expression i.e. the string is a mandatory argument. So, the code runs successfully even if the last two optional arguments are not passed.

However if the mandatory argument is not passed, it results in the compile error “Argument not optional”.

To resolve this compile error, the mandatory arguments need to be passed to the function call.

Conclusion

The compile error discussed in this article is self-explanatory if you understand the purpose and usage of functions in general. We need to pass all mandatory arguments of the expected data type in order to avoid compile errors like this.

See also:

You’ve entered too many arguments for this function