How to Resolve the Excel VBA “Argument Not Optional” Error (4 Solutions)

Why “Argument Not Optional” Error Occurs in Excel VBA

This error occurs when you call a procedure or function without providing all the required arguments. In VBA, each argument has a specific order and may be optional or predefined. If you omit a required argument, you’ll encounter this error. Here are four solutions to resolve it:

Solution 1 – Providing a Proper Argument

Look at the code snippet below. There’s a missing argument when calling the ExampleCode subroutine from Main.

To fix this, pass the required argument arg1 to ExampleCode:

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

Run the code (press F5 or play button) to see the result.

You can now view the output.

Solution 2 – Adding a Second Argument in the Function

In the image below, notice the missing argument in the AddNumbers function.
You need to pass two arguments (num1 and num2) instead of just one.

Paste the below 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

Run the code to see the outcome (result = 15).

Solution 3 – Input a String to Eliminate the Error

In the image below, the Split() function isn’t used correctly; it requires an input string and a delimiter.

To fix it, provide a string and a delimiter as arguments:
Then paste the following corrected code:

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

Run the code to split the sentence.

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

Solution 4 – Defining the Argument Properly

In this case, the fn_demo function is defined with three parameters (strname, strgrade, and introllno), but when calling it in the SOFTEKO subroutine, you’re passing only two arguments (Baby Lal and A+). Additionally, there’s an error in the function declaration.

Here’s how to fix it:

Provide the Third Parameter:

Input the third parameter (introllno) as 5.
Declare introllno as an integer.

Update your code 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

Run the code (press F5 or play button) to see the result.

After pasting the code correctly, you can now see the results as per the image below:

Things to Remember

Verify the number of arguments: Ensure you pass the correct number of parameters.
Check the order of arguments: The order matters in VBA.
Validate argument data types: Match the expected data types.
Declare variables: Properly declare variables within the function.
Handle optional arguments: Specify optional arguments using default values.

Frequently Asked Questions

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

To make a parameter optional in Excel VBA, 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 parameters 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.
Functions accept inputs (called arguments or parameters) and produce an output based on those inputs.
They are defined using the Function keyword, followed by the function name and a set of parentheses containing the function’s arguments.
The function code is enclosed within a pair of Function and End Function statements.

3. What is error 449?

Error code 449 occurs when a procedure or function is called without providing a required argument.
Always verify the number of arguments, their order, and data types to avoid this error.