Instr vba ошибка

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

Your understanding of the InStr function is correct. Your assumption about the Str function, however, is incorrect

The Str function adds a leading space if there is no sign, so you are comparing a single character to two characters: " 0" which will always be false.

When numbers are converted to strings, a leading space is always reserved for the sign of Number. If Number is positive, the returned string contains a leading space, and the plus sign is implied.

Use Cstr(i) to avoid this,

If InStr(cell.Text, CStr(i)) <> 0 Then

or nest your Str function within a Trim:

If InStr(cell.Text, Trim(Str(i))) <> 0 Then

Note also that 0 = "0" in VBA, so you technically don’t need to cast explicitly to string, though it’s probably a good habit.

If Instr(cell.Text, i) <> 0 Then ' <-- this should also work

Есть скрипт, примитивный, грузит массив с словами и каждое из слов ищет в подстроке s, но загвоздка в том, что InStr всегда возвращает 0 вне зависимости есть ли подстрока в s или нет. Вопрос, почему это может быть?

Dim A As Variant
Const strFileNameA = "C:UsersvpuhoffDesktopCatACatAbinDebugasdA.inf
Open strFileNameA For Input As #1
A = Split(Input$(LOF(1), #1), vbLf)
Close #1
 Dim s2 As String
    s2 = Trim(A(i))
    If (InStr(1, s, s2) > 0) Then

Hi,

I’m working on a project that tracks daily changes in the prices and kinds of books. I get a data dump each day, which I extract into an Excel sheet and compare to the previous day’s. To track the changes, I start by creating a unique ID I call «primaryKey»
which is a concatenation of the book number, edition, and ISBN, each separated by a vertical line «|» symbol. I sort the data using this key and compare the data using VLookup (with 4th parameter True). Then, I use InStr to see if the book number is contained
in a primaryKey in yesterday’s data; if it is not, it must be a new book. However, there are times when InStr returns 0 even when the book number is present in the primaryKey. The issue is not spaces/empty characters, I checked this using the Len function
in a MsgBox.

Here’s a code snippet:

For j = 2 To jRow
        primaryKey = tData.Range("A" & j).Value
        If StrComp(book, CStr(tData.Range("F" & j).Value), vbTextCompare) <> 0 Then
            book = tData.Range("F" & j).Value
            If InStr(1, Application.VLookup(primaryKey, yData.Range("A:A"), 1, True), book & "|", vbTextCompare) > 0 Then
                'MsgBox (primaryKey & "   " & Len(primaryKey) & "   " & book & "|   " & Len(book) & "    " & InStr(1, Application.VLookup(primaryKey, yData.Range("A:A"), 1, True), book & "|", vbTextCompare) & "  Not New")
                newBook = False
            Else
                'MsgBox (primaryKey & "   " & Len(primaryKey) & "   " & book & "|   " & Len(book) & "    " & InStr(1, Application.VLookup(primaryKey, yData.Range("A:A"), 1, True), book & "|", vbTextCompare))
                Do
                    tData.Range("A" & j & ":O" & j).Copy Destination:=newDelCons.Range("A" & newDelRow)
                    newDelCons.Range("P" & newDelRow).Value = "New Book"
                    newDelRow = newDelRow + 1
                    j = j + 1
                Loop While InStr(1, tData.Range("A" & j).Value, book & "|", vbTextCompare) > 0
                newBook = True
                j = j - 1
            End If
        End If

Sometimes the InStr function correctly identifies when the book number is found in the primaryKey, other times it does not. Any ideas why?