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

Suspensory

Good question, same behaviour in VB6. – Capybara

Answer 1

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)

Capybara

Thanks for the clarification.　Still one thing is not clear for me. How do you explain the following ?　No compiler error for ① 【i = VBA.Strings.InStr(String1:="AB", String2:="B", Start:=1)】, ② 【i = Strings.InStr(String1:="AB", String2:="B", Start:=1)】, ③ 【i = .InStr(String1:="AB", String2:="B", Start:=1)】. But compiler error for ④ 【i = InStr(String1:="AB", String2:="B", Start:=1)】. – Suspensory

.Anything on its own is not valid syntax – Capybara

Thank you for the remark for ③. Although code runs fine with ① and ②, compiler returns "Expected: list separator" error for ④. However, the optional 'Start' argument is passed after the non-optional 'String1' and 'String2' arguments as you kindly suggested. Why is that ? – Suspensory

Probably because providing all 3 arguments removes any ambiguity and lets the compiler know exactly which argument is which – Capybara

3 arguments are provided in cases ①, ② and ④. However, compiler returns error only for ④. Would that mean that compiler has a parsing logic when Instr is called with its library prefix 'Strings' (cases ① and ②) different from the parsing logic when Instr is called without its library prefix (case ④) ? – Suspensory

Yes, there are differences, added example above. – Capybara

Compile error "Expected Expression" you added in example above would be less confusing than "Expected: list separator" for case ④. This looks like an incorrect compiler error report to the user in case ④... Don't you think so ? Anyway, thanks a lot for your clarification ! – Suspensory

Answer 2

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

Bosporus

Wow, this is so broken. – Clamber

Answer 3

The two answers are amazing and so much interesting, but I'd love that the question also asks "how to solve" rather than just "why" because I guess many people are interested more to solve (fast). Sorry to divert the question.

Question: how to solve the weird Compile error: Expected: list separator or ) of this code:

i = InStr(String1:="AB", String2:="B")

Answer (stolen from the other answers): use both the "fully qualified name", i.e. prefix with VBA. and use Start:=... which is mandatory otherwise you'd get the error 5 Invalid procedure call or argument at runtime:

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

Why: see the other answers.

Galatia