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

Question

Background . In VBA, the "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 the error "Expected: List Separator" if the function "InStr":

• Called with named parameters and

• Its return value is assigned to the 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 a VBA compiler error occur when the "Instr" function is used with named parameters and its return value is assigned to a variable? Is this a language restriction or a compiler error?

Link : A screenshot of the VBA editor for tool tips "InstrRev" and "Instr". Differences are highlighted in red.

Note : "String1" and "String2" are optional arguments for the "InStr" function according to the square brackets of the screenshot tooltip. However, they are required, as indicated in the answers in Visual Basic: https://msdn.microsoft.com/EN-US/library/office/gg264811.aspx

Answer 1

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

In particular, it is odd because VBA forbids it; the rule is that optional arguments cannot follow optional arguments, which makes sense since there are times when the compiler could not match the arguments with the expected function. This also leads to the fact that all his arguments are options.

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

Curious that his full name

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

performs parsing, but generates a run-time error if Start not provided:

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

which works as expected.

One of the reasons that the Call form can work is because it does not work and can be optimized.

---

VBA.X () vs X ()

It's fine:

 ptr = VBA.CLng(AddressOf someFunc) 

This generates parsing time. Expected expression error:

 ptr = CLng(AddressOf someFunc) 

Answer 2

InStr reloads with Variant options

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 for InStr are all Variant , which allows InStr support two different syntaxes and effectively simulate an overloaded function. One of the reasons String1 and String2 is defined as Variant and not as String types. Start may be Long , but it is also a Variant type.

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

Option 1 - Using the specified order of parameters or name values

The signature of the function behaves as it is defined:

InStr Function ([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 - Use an alternate order or name value

The signature of the function behaves as if it were defined as:

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

This means that Start should be used as if it were String1 and String1 should be used as if it were String2 . 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 discovered, the InStr function suffers from syntax and / or compilation errors when using named parameters:

Syntax error: expected list delimiter

When all parameters are named:

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

You are getting:

Syntax Error: Expected List Segmenter

Compilation error: object does not support named arguments

When some of the parameters are called:

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

You are getting:

Compilation error: Object does not support named arguments

The same errors with the StrComp function

The StrComp function StrComp not have functions of an overloaded type, 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 OP discovered, an error does not occur with InStrRev .

So, what is common between InStr and StrComp , which is different from InStrRev and, apparently, all other VBA functions?

Well, InStr and StrComp both use these functions:

• Functions defined in the first type reference library
• Functions defined in the TLB / COM module
• All but the latest options are Variant .
• The final parameter is Enum with a default value.
• The return value is an option.

I cannot find any other functions in the VBA library that share these characteristics, so I suspect there is a compilation error associated with these characteristics.

Qualification of the function fixes the problem!?!?

Both InStrRev and StrComp can be used with all / some named parameters if the function is defined 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