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.
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.
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.
'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)
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
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.
Split a string using a character as a delimiter. An array of words is returned
Convert the paramter value to a value of variant data type
Convert the paramter value to a value of currency data type
ccur ( <path_name> )
Convert the paramter value to a date
cdate ( <path_name> )
Optional arguments & the error “Argument not optional"
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.
Call fn_demo1("Baby Aneesh", "A+")
Function fn_demo1(strname, strgrade, Optional ByVal introllno As Integer)
'Just display the values of all parameters
MsgBox "Student name: " &amp; strname &amp; vbCrLf &amp; "Grade: " &amp; strgrade &amp; vbCrLf &amp; "Roll no: " &amp; introllno
In the case of built-in functions, the syntax clearly states whether the parameters are optional or mandatory.
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.