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

Question

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 :

• Is called with named parameters and

• Its return value is assigned to a variable

  'Call without named parameters
  
   Call InStr("AB", "B")                   'No compiler error  
   i = InStr("AB", "B")                    'No compiler error
  
  'Call with named parameters
  
   Call InStr(String1:="AB", String2:="B") 'No compiler error
   i = InStr(String1:="AB", String2:="B")  'Compiler error : "Expected: list separator"
  

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.

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

Answer 1

InStr is overloaded by way of Variant parameters

The InStr function has 4 optional parameters at design-time, but at least 2 arguments must be provided at run-time. The first 3 parameters to InStr are all Variant, which allows InStr to support two different syntaxes and effectively mimic an overloaded function. That's one of the reasons that String1 and String2 are defined as Variant types and not as String types. Start could be a Long, but it is a Variant type too.

In the following 4 examples, x is always assigned the value 4

Option 1 - Using the defined parameter order or name-meanings

The Function signature behaves as it is defined:

Function InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])

x = VBA.InStr(1, "food", "D", vbTextCompare)                                   '4
x = VBA.InStr(Start:=1, String1:="food", String2:="D", Compare:=vbTextCompare) '4

Option 2 - Using the alternate order or name-meanings

The Function signature behaves as though it was defined like:

Function InStr([String1], [String2], , [Compare As VbCompareMethod = vbBinaryCompare])

Which in effect means that Start should be used as if it is String1 and String1 should be used as if it is String2. The String2 argument must be omitted, or you get a Type Mismatch error.

x = VBA.InStr("food", "D", , vbTextCompare)                        '4
x = VBA.InStr(Start:="food", String1:="D", Compare:=vbTextCompare) '4

Using Named Parameters

But as you've discovered, the InStr function suffers from Syntax and/or Compilation errors when using named parameters:

Syntax Error: Expected List Separator

When all of the parameters are named:

x = InStr(Start:=1, String1:="foo", String1:="foo", Compare:=vbBinaryCompare)

You get:

Syntax Error: Expected List Separator

Compile error: Object doesn't support named arguments

When some of the parameters are named:

x = InStr(1, String1:="foo", String2:="foo", Compare:=vbBinaryCompare)

You get:

Compile error: Object doesn't support named arguments

Same errors with StrComp function

The StrComp function doesn't appear to have any overloaded-type functionality, but it has the same problems with Syntax and Compilation errors:

x = StrComp(String1:="foo", String2:="foo", Compare:=vbBinaryCompare) 'Syntax Error: Expected List Separator???
x = StrComp("foo", String2:="foo", Compare:=vbBinaryCompare) 'Compile error: Object doesn't support named arguments

But as the OP has discovered, the error doesn't occur with InStrRev.

So, what do InStr and StrComp have in common that is different to InStrRev and seemingly all other VBA functions?

Well, InStr and StrComp both share these features:

• The functions are defined in the first referenced Type Library
• The functions are defined in a TLB/COM module
• All parameters except the last are Variant type.
• The last parameter is an Enum with a default value
• The return value is a Variant

I can't find any other functions in the VBA library that share those characteristics, so I suspect there's a compilation bug related to those characteristics.

Qualifying the function fixes the problem!?!?

Both InStrRev and StrComp can be used with all/some named parameters, if the function is qualified by the library name or module name:

'InStr Vanilla usage:
x = Strings.InStr(Start:=1, String1:="food", String2:="D", Compare:=vbTextCompare) '4
x = VBA.InStr(Start:=1, String1:="food", String2:="D", Compare:=vbTextCompare)     '4

'InStr Alternate usage:
x = Strings.InStr(Start:="food", String1:="D", Compare:=vbTextCompare) '4
x = VBA.InStr(Start:="food", String1:="D", Compare:=vbTextCompare)     '4

'StrComp usage
x = Strings.StrComp(String1:="food", String2:="D", Compare:=vbTextCompare)         '1
x = VBA.StrComp(String1:="food", String2:="D", Compare:=vbTextCompare)             '1

Answer 2

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)