How to find if an array contains a string

Question

Possible Duplicate:
How to search for string in MS Access VBA array

I am currently working on an Excel macro, and I could not find a way to do like if array.contains(mystring)

I wrote the following, and it gives me the message "Invaild Qualifier" and highlights the Mainfram right after If

Dim Mainfram(4) As String

Mainfram(0) = "apple"

Mainfram(1) = "pear"

Mainfram(2) = "orange"

Mainfram(3) = "fruit"

    For Each cel In Selection
        If Mainfram.Contains(cel.Text) Then
            Row(cel.Row).Style = "Accent1"
        End If
    Next cel

The selection is a column

Anyone help?

Hi, JP I tried your suggestion, and it said Object required. And Highlightd the If IsInArray(cell.Text, Mainfram) Then Heres my full code

Sub changeRowColor()

Columns("B:B").Select

Dim cel As Excel.Range
Dim Mainfram(4) As String

Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "Banana"

For Each cel In Selection
    If IsInArray(cell.Value, Mainfram) Then
        Rows(cel.Row).Style = "Accent1"
    End If
Next cel

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)

End Function

Nevermind, I found that stupid Error... Thank you anyways

FYI, this has been asked and answered [here](http://stackoverflow.com/q/10951687/190829). — JimmyPena, Jun 20 '12 at 02:50
Please do not update your question if all you intended was to comment on my answer. Leave a comment instead. The only error I get when I run your code (other than the mismatched `cel/cell` reference is an error on the `Rows(cel.Row).Style = "Accent1"` line. — JimmyPena, Jun 20 '12 at 15:03
The accepted answer works. Look also to the answer suggesting using the Join method for a much faster solution. — Tom Collins, Feb 21 '16 at 23:55

score 140 · Accepted Answer · edited May 23 '17 at 12:18

140

Using the code from my answer to a very similar question:

Sub DoSomething()
Dim Mainfram(4) As String
Dim cell As Excel.Range

Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "fruit"

For Each cell In Selection
  If IsInArray(cell.Value, MainFram) Then
    Row(cell.Row).Style = "Accent1"
  End If
Next cell

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

edited May 23 '17 at 12:18

Community

1
1

answered Jun 20 '12 at 03:08

JimmyPena

8,694
6
43
64

I remember that one. I voted for it and I'm votin for this :). – Doug Glancy Jun 20 '12 at 03:39
1

+ 1 Nice One as usual :) – Siddharth Rout Jun 20 '12 at 03:52
I'm upvoting your old answer, and voting to close this question as it is an exact duplicate, and so is this answer! – Jean-François Corbett Jun 20 '12 at 07:53
Thank you gentlemen. I still opted to answer this one because of the sample code provided by the OP that needs updating. – JimmyPena Jun 20 '12 at 12:29
Thank you! I did searched the question but didnt find any similar ones.. Thanks! – Nicola-V Jun 20 '12 at 12:52
30

This only finds all array elements that contain the text stringToBeFound, not equal it exactly. So, IsInArray("e", arr) will return true for arrays containing key "e", "absolutely", "eventually" and so on. – berkus Feb 02 '14 at 14:42
does not work an Apple OS – Sebastian Viereck Jun 17 '14 at 07:55
10

This is not a good solution. This way, it returns TRUE if you look for "p" not the whole word. – Payam Mar 06 '15 at 03:57
3

This method is also very slow. See my comment under the Join answer to see test results. – Tom Collins Feb 21 '16 at 23:53
Wrong number of arguments or invalid property assignment – Christine Dec 02 '16 at 23:45
Another warning: if the second argument to `Filter` is the empty string (`""`), it will return the entire array! So, `IsInArray` as written will not handle empty strings safely. – Charles Wood Jun 08 '18 at 22:36
How do I find the array index of the matched value? – Si8 Apr 04 '19 at 19:32

Siddharth Rout · Answer 2 · 2012-06-20T08:09:50.947

19

Another simple way using JOIN and INSTR

Sub Sample()
    Dim Mainfram(4) As String, strg As String
    Dim cel As Range
    Dim Delim As String

    Delim = "#"

    Mainfram(0) = "apple"
    Mainfram(1) = "pear"
    Mainfram(2) = "orange"
    Mainfram(3) = "fruit"

    strg = Join(Mainfram, Delim)
    strg = Delim & strg

    For Each cel In Selection
        If InStr(1, strg, Delim & cel.Value & Delim, vbTextCompare) Then _
        Rows(cel.Row).Style = "Accent1"
    Next cel
End Sub

edited Jun 20 '12 at 08:09

answered Jun 20 '12 at 03:57

Siddharth Rout

147,039
17
206
250

2

Yeah, but this can potentially fail if the sought string happen to contain the character used as your arbitrary delimiter (`#` or whatever is chosen). – Jean-François Corbett Jun 20 '12 at 07:56
1

Hmm, Maybe I am missing something? Can you give me an example? – Siddharth Rout Jun 20 '12 at 07:57
1

`pear#orange` will be a false positive. Incidentally, `apple` will return a false negative with your current code... but you can solve that by prepending the searched string with your delimiter: `strg = "#" & strg`. – Jean-François Corbett Jun 20 '12 at 08:04
1

I see what you mean. Adding `strg = "#" & strg` after `strg = Join(Mainfram, "#")` solves the issue. Good Catch! – Siddharth Rout Jun 20 '12 at 08:08
@Jean-FrançoisCorbett: Updated! Thanks again. Wonder how did I miss that... Growing old I guess... – Siddharth Rout Jun 20 '12 at 08:10
I hear you! But `"pear" & Delim & "orange"` still gives a false positive... The only 100% certain way around that is to choose a delimiter which does not occur in `cel.Value`. You could ask the user, or automate a search, or make it *almost* error-proof by choosing as `Delim` a long and improbable string like `#This is my €*@£!%&* delimiter!#`. But then again, is it worth the effort when an error-proof method already exists... – Jean-François Corbett Jun 20 '12 at 08:16
4

Agree with you again. It depends on the choice of delimiter. BTW `#This is my €*@£!%&* delimiter!#` is a good delimiter. What are the chances that the cell will have that :-D – Siddharth Rout Jun 20 '12 at 08:31
2

Your code will fail when searching for "fruit" since there's no Delim on the end of the string. Put a Delim on both sides of your string. Otherwise, a better answer than the accepted one, as I'll explain in the next comment. – Tom Collins Feb 21 '16 at 23:48
3

This method is MUCH faster than the accepted answer of using Filter. I ran a test of both using a random 10 words, for 10,000,000 iterations each. The JOIN method took 7 seconds, while the FILTER method took 38. – Tom Collins Feb 21 '16 at 23:52
This fails in the same way as `Filter`. If a string of arrays contains, say, `ABCDE`, then the function will say that the array contains `BCD`. – Charles Wood Feb 25 '16 at 00:41
1

@CharlesWood You're wrong. The `instr` function looks for `Delim & cel.Value & Delim` so it will search for #BCD#. So #ABCDE# will not count as a result. – Fabien TheSolution Sep 16 '16 at 17:33

EkoostikMartin · Answer 3 · 2020-11-13T22:19:23.643

5

Use the Filter() method as shown here - https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function

edited Nov 13 '20 at 22:19

answered Jun 19 '12 at 21:46

EkoostikMartin

6,831
2
33
62

10

Good answer. A code example would make it even better! – Doug Glancy Jun 19 '12 at 22:42
2

[Are answers that just contain links really “good answers”?](http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers) The majority opinion seems to be "no". – Jean-François Corbett Jun 20 '12 at 08:08
2

So if i copy/pasted the example from the link my answer would have been better? The link contains an exact example which OP could use verbatim in his code to accomplish his objective. – EkoostikMartin Jun 20 '12 at 13:51
13

@EkoostikMartin Actually, I would say the answer to that is "yes". Even though it is MSDN documentation, it would be better to copy and paste the `relevant` content from that page in your answer, then include the link for reference. Even MSDN URL's have been known to break, though rarely. Also, it is nice not to need to go to another website, unless one wants more info. Similarly, it is nice to be able to compare the different answers, which is easier if they are all here, together on one webpage. – Ellie Kesselman Jul 03 '12 at 11:52
3

I agree, pasting the code would have been better. Feral Oink as a point, but also it saves everyone downstream time if they can simply look at the code in this post instead of needing to follow another link. One person spending a few extra seconds saves many people the same amount of time. Someone think of the children! – Anthony Aug 25 '14 at 22:47
The link in your answer is dead. This one works for now: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function – ARich Jul 29 '20 at 21:17

score 1 · Answer 4 · answered Jun 19 '12 at 21:45

I'm afraid I don't think there's a shortcut to do this - if only someone would write a linq wrapper for VB6!

You could write a function that does it by looping through the array and checking each entry - I don't think you'll get cleaner than that.

There's an example article that provides some details here: http://www.vb6.us/tutorials/searching-arrays-visual-basic-6

How to find if an array contains a string

4 Answers4

Linked