Solved: Problem with "Instr" when called with an Empty Thread

[Mackeral]

If you call "Instr" with a null argument as in "Instr("Whatever","")" you always get back a 1.

It should return a 0 indicateing that it didn't match anything.

My solution:

Function In_Str(Look_In, For_Text)    ' Correcting "Instr" function.
    ' 7/3/18 Created. WML
    
    Prog = "In_Str"
    
    If For_Text = "" Then
        In_Str = 0
    Else
        In_Str = InStr(Look_In, For_Text)
    End If
    
End Function ' In_Str()

 

[shg]

Or

Function In_Str(Look_In, For_Text) As Long
  If Len(For_Text) Then In_Str = InStr(Look_In, For_Text)
End Function

 

[Mackeral]

Much shorter code I'll admit. But when I write, I like to do it structured because when I come back later and try to re- figure out what it does it, it makes it easier.


The much more serious ramification of this is that I think Microsoft really missed this.

Anyway, I also appreciate hearing back from people when I post stuff. Thanks Shg.
Mac

 

[Scott Huish]

This functionality actually matches their documentation:

Return Values

If InStr returns
string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match is found
start > string2 0

You would have to put vbNull in as your second value to function as you want.

Try this in the Immediate Window:

debug.Print Instr("Whatever",vbNull)

 

[Mackeral]

Scott,

Never seen that before. Going to have to remember to lookup stuff like this.

But with your having pointed that out to me and my philosophy of handing errors, I've reprogrammed my routine to report "Look_For" being either null or empty as an error. After all, you would not have called a routine like this without an argument here.

My recoded code:

Function In_Str(Look_In, Look_For)

    ' Returns InStr(Look_In, Look_for) after checking that "Look_For" _
      is neither Null or Empty.
    
    Prog = "In_Str"
    
    If IsNull(Look_for) Or IsEmpty(Look_for) Then
        Msg = "Called with a Null String"
        Call Msg_Err(Prog, Msg)
    Else
        In_Str = InStr(Look_In, Look_for)
    End If


End Function ' In_Str()

 

[shg]

The value Null never appears in Excel (that's why there is no ISNULL function), and never appears in VBA unless you explicitly assign Null to a Variant.

 

[Mackeral]

In VBA there is a function "IsNull", apparently I'm guessing to test for this condition.

To read about it click here.

But you raise the question of exactly what to the IsNull and IsEmpty functions do. Scott showed me a definition of what Instr returns, and I should find the same for these two functions.

Mac

 

[mikerickson]

Function In_Str(Look_In, For_Text)    ' Correcting "Instr" function.
   In_Str = Sgn(Len(For_Text)) * Instr(Look_In, For_Text)
End Fuction

 

[shg]

In VBA there is a function "IsNull", apparently I'm guessing to test for this condition.

Right. You would just never see it in automating Excel. VBA automates other applications that do use Null.

 

[RoryA]

Right. You would just never see it in automating Excel.

That's not entirely true. If you try and return properties (other than Value/Value2) of multiple cell ranges, you'll often get Null back if the property value isn't the same for every cell in that range.