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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview image of Excel VBA argument not optional


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.

Showing “Argument not optional” because of missing argument

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.

Providing a proper argument in the VBA code

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

Showing output after correcting the code

Read More: VBA Object Variable or with Block Variable Not Set in Excel


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.

Improper VBA code which includes a VBA function

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

Showing “Argument not optional” because of applying 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

Correction of the wrong code by adding a second argument

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

Showing output after inputting the correct code

Read More: [Fixed!] Subscript Out of Range Error in Excel VBA


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.

Showing “Argument not optional” because wrong use of the Split function

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.

Correction of the Split function by inputting a string

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

The output of the VBA code

Read More: Reasons And Solutions for Excel Object Required Error in VBA


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.

Showing “Argument not optional” because of not defining argument properly

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.

Properly defined argument

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

Showing output after defining the argument properly

Read More: [Fixed!] Excel Application-Defined or Object-Defined Error in VBA


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.


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.


Related Articles:

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo