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

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

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.noFunction NameQuick DescriptionSyntax
1SplitSplit a string using a character as a delimiter. An array of words is returnedSPLIT ( <expression> [,<delimiter>] [,<limit>] [,<compare>])
2CdblConvert expression to a double data typeCDBL ( <expression> )
3chrReturns a string (character value) that corresponds to te ascii value passed as a parameter.chr ( <ascii value> )
4MidReturns the middle part of a string when starting position and length are providedMID ( <string>, <start> [,<length>])
5LeftReturns the left portion of a string when length is specifiedLEFT ( <string>, <length> )
6RightReturns the right portion of a string when length is specifiedRIGHT ( <string>, <length> )
7TrimTrim the leading and trailing spaced of a stringTrim ( <string> )
8UcaseConverts all characters of the string to Upper caseUcase ( <string> )
9LcaseConverts all characters of the string to Lower caseLcase ( <string> )
10InstrChecks 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>])
11NowReturns the current system date and timenow()
12CstrConverts the passed parameter value to a stringcstr ( <expression> )
13ValProvides the mathematical value of the parameter.value ( <expression> )
14LtrimRemoves the leading spaces of the string and returns it.Ltrim ( <string> )
15RtrimRemoves the trailing spaces of the string and returns it.Rtrim ( <string> )
16DateReturns the current system datedate ( <expression> )
17DayReturns the day from the date parameter passed.Day (<a date in the yyyy,mm,dd format>)
18SinReturns the sin valueSin  ( < expression> )
19TanReturns the tangent valueTan  ( < expression> )
20CotReturns the Cotangent valueCot  ( < expression> )
21LogReturns the log valueLog  ( < expression> )
22CosReturns the cos valueCos  ( < expression> )
23IsnullReturns a boolean value depending on whether the parameter value is equivalent to “null” or notIsnull  ( <expression> )
24IsnumericReturns a boolean value depending on whether the parameter value is equivalent to a numeric value or notIsnumeric ( <expression> )
25RateReturns 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>] )
26CintComvert the parameter value to an integer valuecint ( <expression> )
27MkdirMake the directory i.e. create a foldermkdir ( <path_name> )
28CvarConvert the paramter value to a value of variant data typeCVAR(expression)
29CcurConvert the paramter value to a value of currency data typeccur ( <path_name> )
30cdateConvert the paramter value to a datecdate ( <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.”

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

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.

Function that works

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

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

Leave a Reply

Your email address will not be published.