Compile error argument not optional vba

I’m trying to figure this out and can’t.

I keep getting an error: «Compile error — Argument not optional». I am supplying the arguments and they are set as Optional!

Trying to pass a string and an array to a function and count occurrences of the array strings within the string passed.

Code stops running at the line:

Public Function countTextInText(Optional text As String, Optional toCountARR As Variant) As Integer

with a «Compile error: Argument not optional» message highlighting the Val in the line:

    For Each Val In toCountARR

Full code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nameR As Range
    Dim colR As Range

    Dim TKRcnt As Integer
    Dim TKRarr() As Variant
    TKRarr = Array("TKR", "THR", "Bipolar")

    Dim ORIFcnt As Integer
    Dim ORIFarr() As Variant
    TKRarr = Array("ORIF", "Ilizarov", "PFN")


    Set nameR = Range("P2:P9")
    Set colR = Range("B2:B50,G2:G50,L2:L50")

    For Each namecell In nameR
        For Each entrycell In colR
            If entrycell.text = namecell.text Then
                TKRcnt = countTextInText(entrycell.Offset(0, 2).text, TKRarr)
                ORIFcnt = countTextInText(entrycell.Offset(0, 2).text, TKRarr)
            End If
        Next entrycell

        MsgBox (namecell.text & " TKR count: " & TKRcnt & " ORIF count: " & ORIFcnt)

    Next namecell
End Sub

Public Function countTextInText(Optional text As String, Optional toCountARR As Variant) As Integer
    Dim cnt As Integer
    Dim inStrLoc As Integer

    For Each Val In toCountARR
        inStrLoc = InStr(1, text, Val)
        While inStrLoc <> 0
            inStrLoc = InStr(inStrLoc, text, Val)
            cnt = cnt + 1
        Wend
    Next Val

    Set countTextInText = cnt
End Function

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.

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.”

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: " & strname & vbCrLf & "Grade: " & strgrade & vbCrLf & "Roll no: " & 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.

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.

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

Permalink

1. 08-12-2021, 01:46 PM

   
   

   #1

   

   
   Registered User
   

   

   ---

   Compile Error: Argument not optional
   

   I am trying to auto sort a table by date,

   I am putting the sort code into a module then calling on the sheet page where the table is located.

   my call is:

   my Module code it:

   Moderator’s note: Please take the time to review our rules. There aren’t many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. —6StringJazzer

   Last edited by DropAGearN’Disappear; 08-19-2021 at 11:10 AM.

   ---

2. 08-12-2021, 01:57 PM

   
   

   #2

   Re: Compile Error: Argument not optional
   

   Sub AutoUpdater requires an argument of Target. You did not provide it when you called the sub. I am guessing you need this:

   See my note above about using code tags.

   Jeff
   | | |?| |?| |?| |?| | |:| | |?| |?|
   Read the rules
   Use code tags to [code]enclose your code![/code]

   ---

3. 08-13-2021, 12:34 PM

   
   

   #3

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Argument not optional
   

   Thank you and my apologies!

   There are no more error codes but the dates do not sort?

   In addition if I click run, the module doesnt show up in the macros window

   Last edited by DropAGearN’Disappear; 08-13-2021 at 12:47 PM.

   ---

4. 08-13-2021, 02:37 PM

   
   

   #4

   Re: Compile Error: Argument not optional
   

   You have two problems in you AutoUpdater code as it relates you your trying to do a sort…

   The following line…

   require the range passed into it to be single cell… it’s kind of hard to sort only one cell.

   If you remove the above problem, the following line…

   doesn’t make use of the Target which may or may not be a problem BUT you are specifying a sort on cell E14 which, again, is a single cell… and again, it is hard to sort a single cell.

   Last edited by Rick Rothstein; 08-13-2021 at 03:31 PM.

   ---

5. 08-13-2021, 03:14 PM

   
   

   #5

   Re: Compile Error: Argument not optional
   

   Also, not sure which macros window you mean but if you go to Developer — Macros, you won’t see it because it has an argument, and there is no way to call a sub with an argument from the worksheet interface.

   ---

6. 08-16-2021, 09:46 AM

   
   

   #6

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Argument not optional
   

   A help site gave that skeleton code as a basis for sorting a dynamic list by date. Here is the updated code in which I’ve altered it and only call it in a module. It still doesn’t work; do you all have any additional suggestions?

   ---

7. 08-16-2021, 10:35 AM

   
   

   #7

   Re: Compile Error: Argument not optional
   

   The problem with your question is you never really told use how you want this to work. After reading over everything here I think you want to call a macro from the list of macros. I assume you will always be sorting Sheet1. I think you want this in a standard module (like Module1):

   You can now call this from the list of macros, or add a button on your worksheet to call it. (Personally, I would use Autofilter on the worksheet and not even write a macro for this.)

   Here’s why your other attempts did not work:

   Your original attempt used Worksheet_Change which is automatically called when the user changes the content of any cell. Target is the current range that the user has selected. Since you are not using that, I don’t know why you would be using this sub. That is what hampered everything else you tried.

   Worksheet_SelectionChange is an event handler. It is automatically called by VBA anytime that the user changes the cell selection. It can be called from another sub but that is not the intent and I don’t recommend it. Same idea.

   ---

8. 08-16-2021, 10:50 AM

   
   

   #8

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Argument not optional
   

   Trust me I wish it was that easy. This spreadsheet has to be used by non-basic excel savvy people so I must make it as automated as possible. And thank you as I am new to VBA. How then, would I change to sort new to old as this is old to new

   ---

9. 08-16-2021, 03:19 PM

   
   

   #9

   Re: Compile Error: Argument not optional
   

   What action do you want to trigger the sort?

   This could go a lot faster if you attach your file so we can see what you’re describing.

   For new-to-old change xlAscending to xlDescending.

   ---

10. 08-17-2021, 07:32 AM

    
    

    #10

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Argument not optional
    

    Unfortunately the spreadsheet contains trade secreted information. That worked for the correct order (I assumed I needed to change orientation as well). The trigger needs to be when a date is entered or altered.

    ---

11. 08-17-2021, 08:17 AM

    
    

    #11

    Re: Compile Error: Argument not optional
    

    Where are the dates located?

    ---

12. 08-17-2021, 08:22 AM

    
    

    #12

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Argument not optional
    

    Sheet 1

    in dynamic table

    header — E14
    First date — E15

    ---