How Do You Fix “Compile Error: Argument not optional”?

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


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.


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. 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.


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: " &amp;amp; strname &amp;amp; vbCrLf &amp;amp; "Grade: " &amp;amp; strgrade &amp;amp; vbCrLf &amp;amp; "Roll no: " &amp;amp; introllno
End Function

Built-in Functions

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


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.


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

Leave a Reply

Your email address will not be published. Required fields are marked *