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 :
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
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)