Excel vba Compile error - Argument not optional,

Question

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

(a) which line is giving the "Argument not optional" error? (b) You can't Set countTextInText = cnt, it should be Let countTextInText = cnt, which would normally be written as just countTextInText = cnt. — YowE3K
– YowE3K, Commented Sep 30, 2017 at 9:48
Error line is: Public Function countTextInText(Optional text As String, Optional toCountARR As Variant) As Integer — Shlomi Kalfa
– Shlomi Kalfa, Commented Sep 30, 2017 at 9:50
Ignore my previous (a) comment, I see you get a compile error on Val. Val is an Excel function that requires a mandatory argument to be passed to it. If you wish to use that as a variable name, you will need to declare it as a variable. But it is a better idea to choose a more appropriate variable name. (But declaring your variables is a very good idea anyway!) — YowE3K
– YowE3K, Commented Sep 30, 2017 at 9:52
I think you need a default value for optional arguments, if I remember correctly. What value do you expect when the argument is not supplied? — Stefan
– Stefan, Commented Sep 30, 2017 at 9:56
@YowE3K Thanks mate, this helped me solved that annoying issue! how do I rate / vote for your reply? :) — Shlomi Kalfa
– Shlomi Kalfa, Commented Sep 30, 2017 at 10:04

YowE3K · Accepted Answer · 2017-09-30 10:14:28Z

3

Val is a VBA function which requires a single, mandatory, argument - therefore the compiler generates the message saying "Argument not optional" if you don't provide that argument. (MSDN documentation of Val)

It is a bad idea to use VBA function names as variable names, so I would recommend you don't use Val as a variable name - use myVal or anything else that VBA hasn't already used.

If you really want to use Val (and you are sure that you won't be needing to access the Val function at all), you can use it as a variable name if you simply declare it as such, e.g.

Dim Val As Variant

You will also have problems with your line saying

Set countTextInText = cnt

as countTextInText has been declared to be an Integer, and Set should only be used when setting a variable to be a reference to an object. So that line should be

countTextInText = cnt

edited Sep 30, 2017 at 10:14

answered Sep 30, 2017 at 10:03

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hadron Over a year ago

Nice! For me the problem was I forgot Set on a ByRef parameter, it was confusing though because it was the first line of the Sub so the error message threw me off.

cssyphus · Accepted Answer · 2018-07-10 02:46:27Z

For those coming late to this question because of the question's title, as I did, having received this error while using the .Find method -

In my case, the problem was that the variable I was Seting was not Dimd at top of function.

My Example

Sub MyTest()
    Dim tst, rngAll
    rngAll = [a1].CurrentRegion
    tst = fnFix1Plus1InValues(ByVal rngAll As Range)
End Sub

Public Function fnFix1Plus1InValues(ByVal rngAll As Range) As Boolean
    Dim t1, t2, arr, Loc '<=== Needed Loc added here
    Set Loc = rngAll.Find(What:="+", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
    If Not Loc Is Nothing Then
        Do Until Loc Is Nothing
            t1 = Loc.Value
            If fnContains(t1, "+") Then
                'Do my stuff
            End If
            Set Loc = rngAll.FindNext(Loc)
        Loop
    End If
End Function 'fnFix1Plus1InValues

Collectives™ on Stack Overflow

Excel vba Compile error - Argument not optional,

2 Answers 2

1 Comment

My Example

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

My Example

Comments

Your Answer

Sign up or log in

Post as a guest

Related