Complete error argument not optional

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

1. 05-07-2018, 06:43 AM

   
   

   #1

   Compile error argument not optional
   

   I have a VBA in Excel that check Word files for a certain keyword and then should copy the table following the keyword to an Excel worksheet. Using input from other forums I had separate codes for both; searching the keyword and table, and copying the table to Excel. However I fail to combine the two. Everytime I get a compile error in the bold line that the ‘argument is not optional’ but I fail to see where an argument is missing. Stand alone both scripts work as expected. If anyone is able to spot what is wrong and how it can be solved, that would be much appreciated.

   Sub ImpTable()Dim oWdApp As New Word.Application
   Dim oWdDoc As Word.Document
   Dim oWdTable As Word.Table
   Dim oWS As Worksheet
   Dim lLastRow$, lLastColumn$
   With ThisWorkbook
   
   
   Set oWdDoc = oWdApp.Documents.Open("path")
   'oWdDoc.Activate
   'Application.ScreenUpdating = False
   Dim StrFnd As String, Rng As Range, i As Long
   StrFnd = "keyword"
   With ActiveDocument.Range
     With .Find
       .ClearFormatting
       .Replacement.ClearFormatting
       .Text = StrFnd
       .Replacement.Text = ""
       .Forward = True
       .Wrap = wdFindStop
       .Format = False
       .MatchCase = False
       .MatchWholeWord = False
       .MatchWildcards = False
       .MatchSoundsLike = False
       .MatchAllWordForms = False
       .Execute
     End With
     Do While .Find.Found
       i = .Information(wdActiveEndAdjustedPageNumber)
       Set Rng = ActiveDocument.GoTo(What:=wdGoToPage, Name:=i)
       Set Rng = Rng.GoTo(What:=wdGoToBookmark, Name:="page")
       If Rng.Tables.Count > 0 Then
         With Rng.Tables(1)
         Set oWdTable = Rng.Tables(1)
         
           oWdTable.Range.Copy
           oWS.Range("A1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
          End With
         
       Else
         MsgBox "no table."
       End If
       .Start = Rng.End
       .Find.Execute
     Loop
   End With
   'Application.ScreenUpdating = True
   oWdDoc.Close savechanges:=False
   oWdApp.Quit
   End With
   End Sub

   ---

2. 05-07-2018, 08:10 AM

   
   

   #2

   Show us the two Stand-Alone Codes.

   I expect the student to do their homework and find all the errrors I leeve in.

   ---

3. 05-07-2018, 08:10 AM

   
   

   #3

   I only dabble in Word VBA, but I’m fairly sure that is an illegal assignment. Your code defines ActiveDocument.Range as a Word range, while Rng is defined as an Excel range. The data types are not compatible. Perhaps if you redefined rng as a word range?

   ---

4. 05-07-2018, 11:55 PM

   
   

   #4

   Originally Posted by SamT
   

   Show us the two Stand-Alone Codes.

   Here are the codes for respectively copying the Word table to Excel and analysing the Word file to find the table. I tried to combine these but this failed.

   Sub CopyTableFromWordDoc(ByVal oFile As file)Dim oWdApp AsNew Word.Application                      ' Requires "Microsoft Word .. Object Library" reference
       Dim oWdDoc As Word.Document
       Dim oWdTable As Word.Table
       Dim oWS As Worksheet
       Dim lLastRow$, lLastColumn$
   
       ' Code to copy table from word document to this workbook in a new worksheet
       With ThisWorkbook
   
           
           ' Set oWdTable
           ' Copy the table to new worksheet
           oWdTable.Range.Copy
           oWS.Range("A1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
   
           ' Close the Word document
           oWdDoc.Close False
   
           ' Close word app
           oWdApp.Quit
   
       EndWith
    EndSub

   Thanks to Zac.

   and in Word VBA;

   Sub Demo()Application.ScreenUpdating =False
   Dim StrFnd AsString, Rng As Range, i AsLong
   StrFnd = "keyword"
   With ActiveDocument.Range
     With.Find
       .ClearFormatting
       .Replacement.ClearFormatting
       .Text = StrFnd
       .Replacement.Text =""
       .Forward =True
       .Wrap = wdFindStop
       .Format =False
       .MatchCase =False
       .MatchWholeWord =False
       .MatchWildcards =False
       .MatchSoundsLike =False
       .MatchAllWordForms =False
       .Execute
     EndWith
     DoWhile.Find.Found
       i =.Information(wdActiveEndAdjustedPageNumber)
       Set Rng = ActiveDocument.GoTo(What:=wdGoToPage, Name:=i)
       Set Rng = Rng.GoTo(What:=wdGoToBookmark, Name:="page")
       If Rng.Tables.Count >0Then
         With Rng.Tables(1)
           'here the table should be copied to the Excel sheet
         EndWith
       Else
         MsgBox "No table."
       EndIf
       .Start = Rng.End
       .Find.Execute
     Loop
   EndWith
   Application.ScreenUpdating =TrueEndSub

   Thanks to Macropod.

   ---

5. 05-08-2018, 12:21 AM

   
   

   #5

   Originally Posted by rlv
   

   I only dabble in Word VBA, but I’m fairly sure that is an illegal assignment. Your code defines ActiveDocument.Range as a Word range, while Rng is defined as an Excel range. The data types are not compatible. Perhaps if you redefined rng as a word range?

   Dohh that makes perfect sense. I corrected that.

   Last edited by plaem; 05-08-2018 at 01:12 AM.

   ---

6. 05-08-2018, 02:01 AM

   
   

   #6

   So bit by bit I have combined codes to get my desired script. The goal is to analyze a folder of various Word files, extract a certain table from each Word file and paste it into a separate Excel worksheet which has the name of the doc file:

   Sub LookForWordDocs()Dim FolderName As String
   With Application.FileDialog(msoFileDialogFolderPicker)
     .AllowMultiSelect = False
     .Show
     On Error Resume Next
     FolderName = .SelectedItems(1)
     Err.Clear
     On Error GoTo 0
   End With
       Dim sFoldPath As String: sFoldPath = FolderName     ' Change the path. Ensure that your have "" at the end of your path
       Dim oFSO As New FileSystemObject                    ' Requires "Microsoft Scripting Runtime" reference
       Dim oFile As File
       ' Loop to go through all files in specified folder
       For Each oFile In oFSO.GetFolder(sFoldPath).Files
           ' Check if file is a word document. (Also added a check to ensure that we don't pick up a temp Word file)
           If ((InStr(1, LCase(oFSO.GetExtensionName(oFile.Path)), "doc", vbTextCompare) > 0) Or _
            (InStr(1, LCase(oFSO.GetExtensionName(oFile.Path)), "docx", vbTextCompare) > 0)) And _
                   (InStr(1, oFile.Name, "~$") = 0) And _
                   ((InStr(1, oFile.Name, "k") = 1) Or (InStr(1, oFile.Name, "K") = 1)) Then
               ' Call the UDF to copy from word document
               ImpTable oFile
           End If
       Next
   End Sub
   
   
   Sub ImpTable(ByVal oFile As File)
   Dim oWdApp As New Word.Application
   Dim oWdDoc As Word.Document
   Dim oWdTable As Word.Table
   Dim oWS As Excel.Worksheet
   Dim lLastRow$, lLastColumn$
   Dim s As String
   s = "No correct table found"
   With ThisWorkbook
   Set oWS = Excel.Worksheets.Add
   On Error Resume Next
   oWS.Name = oFile.Name
   On Error GoTo 0
   Set sht = oWS.Range("A1")
   
   
   Set oWdDoc = oWdApp.Documents.Open(oFile.Path)
   oWdDoc.Activate
   'Application.ScreenUpdating = False
   Dim StrFnd As String, Rng As Word.Range, i As Long, j As Long
   StrFnd = "keyword"
   With Word.ActiveDocument.Range
     With .Find
       .ClearFormatting
       .Replacement.ClearFormatting
       .Text = StrFnd
       .Replacement.Text = ""
       .Forward = True
       .Wrap = wdFindStop
       .Format = False
       .MatchCase = False
       .MatchWholeWord = False
       .MatchWildcards = False
       .MatchSoundsLike = False
       .MatchAllWordForms = False
       .Execute
     End With
     Do While .Find.Found
       i = .Information(wdActiveEndAdjustedPageNumber)
       Set Rng = ActiveDocument.Goto(What:=wdGoToPage, Name:=i)
       Set Rng = Rng.Goto(What:=wdGoToBookmark, Name:="page")
       If Rng.Tables.Count > 0 Then
         With Rng.Tables(1)
         Set oWdTable = Rng.Tables(1)
           oWdTable.Range.Copy
           sht.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
           j = 1
          End With
       End If
       .Start = Rng.End
       .Find.Execute
     Loop
   End With
   If j = 0 Then sht.Value = s
   'Application.ScreenUpdating = True
   oWdDoc.Close savechanges:=False
   oWdApp.Quit
   End With
   
   
   Set oWS = Nothing
   Set sht = Nothing
   Set oWdDoc = Nothing
   Set oWdTable = Nothing
   Set Rng = Nothing
   
   
   End Sub

   With Word.ActiveDocument.Range
   The first table copies fine but then I get a “Run-time error 462 : The remote server machine does not exist or is unavailable” on line «With Word.ActiveDocument.Range». Any idea what may cause this and how it can be solved?

   Last edited by plaem; 05-08-2018 at 02:37 AM.

   ---

7. 05-08-2018, 06:32 AM

   
   

   #7

   Option Explicit
   
   
   Sub test()
       Dim wd As Word.Application
       Dim StrFnd As String
       Dim r As Word.Range, rr As Word.Range, t As Word.Table
       Dim p As String, f As String
       Dim wb As Workbook
       Dim ws As Worksheet
       Dim n As Long
       Dim i As Long
       
        StrFnd = "keyword"
       
       With Application.FileDialog(msoFileDialogFolderPicker)
           .Title = "Plesse choose the folder"
           If Not .Show Then Exit Sub
           p = .SelectedItems(1) & ""
       End With
       
       Set wd = New Word.Application
       wd.Visible = True
       
       Set wb = Workbooks.Add(xlWBATWorksheet)
   
   
       f = Dir(p & "*.docx")
       
       Do While f <> ""
           With wd.Documents.Open(p & f)
               Set r = .Range
               With r.Find
                   .Text = StrFnd
                   Do While .Execute
                       Set t = Nothing
                       On Error Resume Next
                       Set t = r.GoTo(What:=wdGoToBookmark, Name:="page").Tables(1)
                       On Error GoTo 0
                       Application.Wait [Now() + "0:00:00.1"]
   
   
                       If Not t Is Nothing Then
                           If ws Is Nothing Then
                               Set ws = wb.Worksheets.Add
                               ws.Name = f
                           End If
                           t.Range.Copy
                           ws.Range("a1").Offset(n).PasteSpecial xlPasteValues
                           Application.CutCopyMode = False
                           n = n + t.Rows.Count + 2
                       End If
                   Loop
               End With
               .Close False
               Set ws = Nothing
               n = 0
           End With
           f = Dir()
       Loop
   
       wd.Quit
       Set wd = Nothing
       
   End Sub

   マナ

   ---

8. 05-08-2018, 03:37 PM

   
   

   #8

   So bit by bit I have combined codes to get my desired script.

   I try to break my code into many small Procedures, so I can Troubleshoot each one by itself. This also makes it easy to reuse Procedures

   I expect the student to do their homework and find all the errrors I leeve in.

   ---