[Solved!] Excel VBA “Argument Not Optional” Error

Excel VBA (Visual Basic for Applications) is a powerful tool that allows users to automate tasks, create custom functions, and enhance the functionality of Excel spreadsheets. However, when working with VBA code, you could come into runtime issues that are difficult to fix. One such error is the “Argument Not Optional” error in Excel VBA, which occurs when a required argument is not provided when calling a procedure or function.

In this article, we will delve into the “Argument Not Optional” error in Excel VBA and explore four possible fixes to resolve it. Understanding the causes of this error and knowing how to address it is crucial for ensuring the smooth execution of your VBA code and achieving your desired automation or data processing tasks in Excel.

We will explore common scenarios where this error may arise, discuss the reasons behind it, and provide practical solutions to rectify the issue. By implementing the suggested fixes, you will be able to overcome the “Argument Not Optional” error and continue your VBA programming journey with confidence.

So, let’s dive into this article and apply some effective strategies to solve this issue.

Why “Argument Not Optional” Error Occurs in Excel VBA

The “Argument not optional” error in Excel VBA occurs when you call a procedure or function without providing all the required arguments. In VBA, every argument of a procedure or function has a specific order and may have a predefined value or be marked as optional. You will encounter this error if you omit a required argument when calling the procedure or function.

To fix this error, you must provide all the required arguments correctly when calling the procedure or function. You can refer to the documentation or the function/procedure declaration to determine the required arguments and their order.

Excel VBA “Argument Not Optional” Error: 4 Possible Fixes

Solution 1: Providing a Proper Argument

If you look closely at the code in the image below, you can see that there is a missing argument when calling the ExampleCode subroutine from the Main subroutine. That’s why after running the code a popup window appears and shows Argument not optional.

To fix the issue, you need to pass the required argument arg1 to the ExampleCode subroutine and follow the below instructions:

Just paste the following code. Here’s the corrected code:

Sub ExampleCode(arg1 As String)
    ' Some code here
End Sub
Sub Main()
    Dim value As String
    value = "Hello"
    ExampleCode value ' Providing the required argument
    MsgBox value
End Sub

Now run the code by pressing the F5 key or by clicking on the play button to see the result.

You can now view the output after correctly inputting the code.

Solution 2: Adding a Second Argument in Function

If you pay close attention to the code in the below image, you’ll see there is an argument missing, In the AddNumbers function, you need to pass two arguments (num1 and num2) instead of one. The AddNumbers function is defined with two parameters num1 and num2, but when calling it in the Example subroutine, only one argument 10 is provided.

Showing “Argument not optional” after running the wrong code.

So by adding another number, we can easily fix this issue. We are taking another number “5”

Now,

Paste the below code. Here’s the corrected code:

Sub Example()
    Dim result As Integer
    result = AddNumbers(10, 5)
    MsgBox "The result is: " & result
End Sub
Function AddNumbers(num1 As Integer, num2 As Integer) As Integer
    AddNumbers = num1 + num2
End Function

To see the outcome, run the code by using the F5 key or the play

After entering the code correctly, you may now see the results.
We entered 5 as num2, So the outcome is 15.

Solution 3: Input a String to Eliminate the Error

Here, in the picture below the Split() function is not properly used as it requires an input string and a delimiter to split the string into an array.

After running the code it shows Argument not optional.

To use the Split function, you need to provide a string and a delimiter as arguments. and we provide a string in the Split function to fix this code.

Now please follow the following instructions:

Then paste the following code. Here’s the corrected code:

Sub ExcelDemy()
word = Split(" I love Cricket")
For I = 0 To UBound(word)
Debug.Print word(I)
Next
End Sub

You can run the code by pressing the F5 key or by clicking on the play button to see the result.

After applying the code the sentence is split.
The outcome will look like the image below.

Solution 4: Defining the Argument Properly

First, when calling the fn_demo function in the SOFTEKO subroutine, you are passing two arguments (“Baby” and “A+”), but the fn_demo function is defined with three parameters (strname, strgrade, and introllno).

There is an error also declaring the function.

As a result, after running the code it will show Argument not optional.

Follow the below instructions to fix this problem:

We input the third parameter of the fn_demo function as 5.
We also declare introllno as an integer
Then paste the code below. The updated code is as follows:

Sub SOFTEKO()
    Call fn_demo("Baby Lal", "A+", 5)
End Sub
Function fn_demo(strname As String, strgrade As String, 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

Now run the code by pressing the F5 key or by clicking on the play button to see the result.

After pasting the code correctly, you can now see the results like the image below.

Things to Remember

Verify the number of arguments: When using a procedure or function, make sure the appropriate number of parameters is given. Ensure you are passing the necessary number of arguments, together with any additional ones that are optional.
Check the order of arguments: Ensure that the arguments passed in the correct order match the parameters defined in the procedure or function. The order of arguments matters in VBA.
Validate argument data types: Make sure the data types of the arguments passed match the data types expected by the procedure or function. For example, passing a string when an integer is expected can result in an error.
Declare variables: Ensure that all variables used within the procedure or function are properly declared with appropriate data types.
Handle optional arguments: If the procedure or function has optional arguments, ensure that you provide the required arguments or specify them as optional using default values when calling the procedure or function.

Frequently Asked Questions

1. How do I make an optional parameter in Excel VBA?

To make a parameter optional in Excel VBA, you can use the Optional keyword in the parameter declaration. This allows you to call the subroutine or function with or without providing a value for that parameter. If the parameter is omitted, it will use a default value (if specified) or a system default value. Making a parameter optional provides flexibility and allows for more versatile use of the subroutine or function.

2. What is a function in VBA?

In VBA (Visual Basic for Applications), a function is a reusable block of code that performs a specific task and returns a value. It can accept inputs (called arguments or parameters) and produce an output based on those inputs.

Functions are defined using the Function keyword followed by the function name and a set of parentheses that may contain the function’s arguments. The function code is enclosed within a pair of Function and End Function statements.

3. What is error 449?

It actually means an incorrect number of arguments. In VBA, error code 449 refers to the runtime error “Argument not optional.” This error occurs when a procedure or function is called without providing a required argument.

Download Practice Workbook

If you want a free copy of the illustrated workbook we discussed during the presentation, please click the link below this section.

VBA Argument Not Optional.xlsm

Conclusion

In this article, we explored a common error that occurred in VBA programming. We discussed four potential solutions to address the issue. By implementing these solutions, you can effectively overcome the “Argument Not Optional” error in Excel VBA. Hope this will ensure the smooth execution of your VBA code. If you have any queries or suggestions, please let us know in the comments section below.

Get FREE Advanced Excel Exercises with Solutions!