vbaargumentsnamedlist-separator

VBA compile error if Instr function used with named parameter and return value assigned to variable


Background : In VBA, 'InStrRev' function can be called without or with named parameters.

    'Call without named parameters

     Call InStrRev("AB", "B")                   'No compiler error  
     i = InStrRev("AB", "B")                    'No compiler error

    'Call with named parameters

     Call InStrRev(StringCheck:="AB", StringMatch:="B") 'No compiler error
     i = InStrRev(StringCheck:="AB", StringMatch:="B")  'No compiler error

Concern : In VBA, the compiler returns "Expected: list separator" error if 'InStr' function :

Question : Why does VBA compiler error occur when 'Instr' function is used with named parameters and its return value is assigned to a variable ? Is it a limitation of the language or a compiler bug ?

Reference : VBA editor screenshot for 'InstrRev' and 'Instr' functions tool tips. Differences are highlighted in red.

Comparison of '*InstrRev*' and '*Instr*' functions tips in VBA editor

Remark : 'String1' & 'String2' are optional arguments for 'InStr' function according to above screenshot tooltip square brackets. However, they are required, as mentioned in below answer and in Visual Basic language reference : https://msdn.microsoft.com/EN-US/library/office/gg264811.aspx


Solution

  • InStr is odd in that its first argument (Start) is optional, but its subsequent String1/String2 arguments are not (despite the [] in the tooltip) - If they were optional InStr(1) would parse but it does not and generates the same error you see.

    Specifically its odd because VBA disallows this; the rule there is that non-optional arguments cannot follow optional arguments, which makes sense as there would be cases when the compiler could not match up the arguments to what the function expected. This also forces all of its arguments to be variants.

    VB6/A has a lot of baggage carried over from QBASIC, and that language (which iirc did not allow user defined optional arguments) has exactly the same signature for its INSTR() so I assume the behaviour you see is an artifact of the special parsing rules that must exist for calls to InStr.

    Curiously its fully qualified name

     i = VBA.Strings.InStr(String1:="AB", String2:="B")` 
    

    does parse, but produces an error at runtime unless Start is provided:

    i = VBA.Strings.InStr(String1:="AB", String2:="B", Start:=1)` 
    

    which works as expected.

    One reason the Call form may appear to work is thats its a no-op and may be optimised away.


    VBA.X() vs X()

    This is perfectly fine:

    ptr = VBA.CLng(AddressOf someFunc)
    

    This generates a parse time Expected Expression error:

    ptr = CLng(AddressOf someFunc)