function to determine if a specific string of characters exists in an array

Question

Given an array, I would like to have a function which determines if a string of characters exists in an array. The string of characters must be EXACTLY the same in input of the function, and the array content.

Function IsInArray(ThsStrng As String, arr() As String, bnd As Integer) As Boolean
  For Z = 1 To bnd
    If arr(Z) = ThsStrng Then
      IsInArray = True
    Else
      IsInArray = False
    End If
  Next Z
End Function

At first, it seemed like the function ran correctly. After using this function a few times, I noticed that the False value (meaning that the input string does not equal to a value in the array), was not correct (there were input values that were exactly the same a value in the array).

Please help,

why not IsNumeric(Application.Match("string to look for",arrayVariable,0))? it will be true if an exact match exists and be false if not... — Dirk Reichel
– Dirk Reichel, Commented Mar 4, 2016 at 22:28

Matti Virkkunen · Accepted Answer · 2016-03-04 22:21:47Z

1

Imagine what happens when the match is found in the middle of the array. The next iteration will be a mismatch, and the return value gets set back to False. You need to stop the loop when a match is found.

I believe the VBA syntax for that is

Exit For

answered Mar 4, 2016 at 22:21

Matti Virkkunen

65.4k9 gold badges138 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

Exit Function would also have done it. But Exit For is definitely more appropriate.

Collectives™ on Stack Overflow

function to determine if a specific string of characters exists in an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related