VBA: Function to search in an array

[rajat_magic]

Is there a function in VBA that can establish whether a particular value is part of an array, or is it necessary to actually cycle through all of the values and check individually?

 

[DonkeyOte]

can't find anything - are you looping the values in the array - what code are you using to find a match...

something like:

n = 0
test = 10
Do Until n > z
    If varData(n) = test Then
        MsgBox "Match in Row " & n + 2
        Exit Sub
    End If
n = n + 1
Loop

(I had some code to create the array - z matches the number of values in the array)

 

[rajat_magic]

Yes, I'm using something very similar - just looping through the values and if I find a match I set a boolean variable to true and exit the loop. Its working fine - I was just wondering if there was a more efficient VBA function that does it in one line.

Anyway, thanks for the time...

Cheers,
Rajat

 

[Tommy Bak]

Hi
If it's a onedimensional array the Filter-Function could be used

dim z as variant
z = Filter(MyArray, Testvalue)
If UBound(z) < 0 Then MsgBox ("empty")

regards
Tommy

 

[rajat_magic]

Thanks Tommy, I wrote that out in one line (Ubound(filter....)), and now my code really does look slim. Great tip... thanks.

 

[Console]

Thanks for the tip Tommy.
However, I noticed that you need to be careful if passing a blank string as the search term.

Ordinarily, you'd expect a blank string to return false (that it does not exist in the array), unfortunately due to the way the filter function works, passing a blank string returns the entire array, which is then incorrectly reported as a successful search.

Instead consider the slight alteration:

[COLOR=Green]    'Initialise Array[/COLOR]
    [COLOR=RoyalBlue]Dim [/COLOR]myArray(2) [COLOR=RoyalBlue]As String[/COLOR]
    myArray(0) = "Value1"
    myArray(1) = "Value2"
    myArray(2) = "Value3"
    
   [COLOR=SeaGreen] 'Initialise Search Term[/COLOR]
    [COLOR=RoyalBlue]Dim [/COLOR]searchTerm [COLOR=RoyalBlue]As String[/COLOR]
    searchTerm = "Value2"
    
    [COLOR=SeaGreen]'Check if a value exists in the Array[/COLOR]
    [COLOR=RoyalBlue]If UBound[/COLOR](Filter(myArray, searchTerm)) >= 0 [COLOR=RoyalBlue]And [/COLOR]searchTerm <> ""[COLOR=RoyalBlue] Then[/COLOR]
        MsgBox ("Search Term SUCCESSFULLY located in the Array")
    [COLOR=RoyalBlue]Else[/COLOR]
        MsgBox ("Search Term could NOT be located in the Array")
    [COLOR=RoyalBlue]End If[/COLOR]

This works and corrects the issue of passing a blank string (this may be necessary if, for example, you are comparing the value a user typed into a cell against an array of valid values, within a manual validation function).

However, be aware that the Filter function also accepts partial matches.
For example, typing "Value" as the search term in the above example (with no number on the end) reports a valid match, even though no element in the array exactly matches this string.

Nevertheless, still should be a good one line alternative to using a loop and manually comparing each value in the array, assuming you can handle partial matches.

 

[Rick Rothstein]

Here is a non-looping method that avoids the partial match problem that the Filter function opens one up to...

Dim C As String, FindMe As String, myArray() As Variant
Dim ItemFound As Boolean, MatchCase As Boolean
' Load dynamic Variant array in a single line of code
myArray = Array("Value1", "Value2", "Value3")
' Initialize search options
FindMe = "value2"
MatchCase = False
' Perform search
C = Chr$(1)
ItemFound = InStr(1, C & Join(myArray, C) & C, C & FindMe & C, 1 + MatchCase)
' Display result
If ItemFound Then
    MsgBox "Found it!"
Else
    MsgBox "Could NOT find it."
End If

The above code only finds whole array element matches; that is, if you searched for "Val", it would not match any elements in the array (the Filter function method would produce a match). I also provided a MatchCase Boolean variable to control whether the letter casing must be exact or not when performing the search.

 

[destabilizator]

Simple Application.Match(value, array, 0) won't help? That is if the array element you are searching for is exactly same - eg. looking for "john" will find "john" but not "john doe".

Don't use WorkSheetFunction.Match, because when it doesn't find a match, it will throw runtime error.

 

[bhilleli]

Here is a non-looping method that avoids the partial match problem that the Filter function opens one up to...

Dim C As String, FindMe As String, myArray() As Variant
Dim ItemFound As Boolean, MatchCase As Boolean
' Load dynamic Variant array in a single line of code
myArray = Array("Value1", "Value2", "Value3")
' Initialize search options
FindMe = "value2"
MatchCase = False
' Perform search
C = Chr$(1)
ItemFound = InStr(1, C & Join(myArray, C) & C, C & FindMe & C, 1 + MatchCase)
' Display result
If ItemFound Then
    MsgBox "Found it!"
Else
    MsgBox "Could NOT find it."
End If

The above code only finds whole array element matches; that is, if you searched for "Val", it would not match any elements in the array (the Filter function method would produce a match). I also provided a MatchCase Boolean variable to control whether the letter casing must be exact or not when performing the search.

Isn't calling it 'non-looping' misleading?
If InStr loop as I'd expect, 'letter by letter', then it's actually a less efficient solution than the original. In the original you loop for as many elements as there are in the array, with InStr you are effectively loop for as many letters as there are in all of the elements combined.

It would be interesting to time this on an appropriately large data-set.

 

[ffirat]

Hello,
I have a problem in a macro. I have a function and function input is an array. I used another function in the function whhich I prepared before and input of this function coming from the main function. I code is given below. I don't know how to pass input of one function to anaother function. Could you please help me.

Function G_TP(ParamArray Comp() As Variant) As Variant
G_TP = H_TP(Comp()) - Comp(0) * S_TP(Comp())
End Function

Comp() in the function H_TP() must be an array. But in this form only first term of the Comp() ig cominh to H_TP() other terms are not coming how can I solve it. Please help me.