Page 1 of 2 12 LastLast
Results 1 to 10 of 13
Like Tree1Likes

VBA: Function to search in an array

This is a discussion on VBA: Function to search in an array within the Excel Questions forums, part of the Question Forums category; Is there a function in VBA that can establish whether a particular value is part of an array, or is ...

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Posts
    57

    Default VBA: Function to search in an array

    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?

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: VBA: Function to search in an array

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

    something like:

    Code:
    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)
    Does my a$$ look big in this picture ?

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Posts
    57

    Default Re: VBA: Function to search in an array

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    287

    Default Re: VBA: Function to search in an array

    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

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Posts
    57

    Default Re: VBA: Function to search in an array

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

  6. #6
    New Member
    Join Date
    Jun 2011
    Posts
    1

    Default Re: VBA: Function to search in an array

    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:

    Code:
        'Initialise Array
        Dim myArray(2) As String
        myArray(0) = "Value1"
        myArray(1) = "Value2"
        myArray(2) = "Value3"
        
        'Initialise Search Term
        Dim searchTerm As String
        searchTerm = "Value2"
        
        'Check if a value exists in the Array
        If UBound(Filter(myArray, searchTerm)) >= 0 And searchTerm <> "" Then
            MsgBox ("Search Term SUCCESSFULLY located in the Array")
        Else
            MsgBox ("Search Term could NOT be located in the Array")
        End If
    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.
    Last edited by Console; Jun 27th, 2011 at 12:22 AM.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,873

    Default Re: VBA: Function to search in an array

    Here is a non-looping method that avoids the partial match problem that the Filter function opens one up to...
    Code:
    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.
    Gary's Student likes this.

  8. #8
    New Member
    Join Date
    Aug 2011
    Posts
    2

    Default Re: VBA: Function to search in an array

    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.

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    1

    Default Re: VBA: Function to search in an array

    Quote Originally Posted by Rick Rothstein View Post
    Here is a non-looping method that avoids the partial match problem that the Filter function opens one up to...
    Code:
    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.

  10. #10
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Default Re: VBA: Function to search in an array

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com