Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding a string in an array of strings

    Hi,

    Does someone knows a way to find a value or text in an array? What I mean is this: suppose you have an array of say 100 elements and each element is a string (text). Now I want to know if a specific string (e.g. the name of somebody) is present in that array, without looping through that array (or is this the only way). In a range on a spreadsheet, you have all kind of tools (vlookup, hlookup, find, ...), but does there exist an equivalent for arrays in VBA?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    Using a loop is the only way I can think of. Is there some reason for not using one?
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    Agreed.

    It's much easier to implement things like loops in VBA than in cell functions.

    But if it's something you're likely to do often, write your own function and make it globally available.

    Make sure it's implemented exactly as you want. I've had array functions in other languages which caused me trouble because I wasn't aware that it was a partial match, not a full match (eg, "John Smith" matched an entry "George John Smithers").
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    The only reason is speed. I thought that with some built-in function, it would be faster to find out the presence/absence of a value or string in an array.
    Thanks for your comments, I think that, indeed, there is no other way than looping through the array as you suggest.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    Hans,

    If you find that speed is a real problem to you, it might be possible to build a COM add-in in VB or C++. This applies only to Office 2000- and I've never been there myself.

    I would have suspected that performance is only a problem if you wanted to do the same thing thousands of times.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    Hans, Could you populate a worksheet range with the array and have a VLOOKUP or similar return the searched for member ? . May save you some coding.

    Andrew

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    The built-in functions are not always faster than VBA code that you write. The built-in functions are frequently very generalized and have to test and program for conditions that you know will not occur in your situation.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    Legare,

    That's a good point.

    Don't surmise on what may be the best- try out the alternatives, and see what actually is best.

    But, Hans, if you've only got an array of 100 elements, as you suggest in the original post, I wouldn't have thought the response time would be noticable.

    But then, I'm only surmising, and not trying out the alternatives <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a string in an array of strings

    It's my personal experience that self-made functions in Excel are slower than the built-in functions. You will not notice the difference in speed if you need it a few times in the same spreadsheet. But if you want to use it for more than 1000 records in an Excel database, then it slows down dramatically. You need to switch off the automatic calculation if you want to do something in another worksheet, because recalculation is triggered for every change you make. I experienced that this recalculation is much faster if only built-in functions are used. Now, I thought that if I could use a worksheetfunction in my VBA function, then it would be faster. The COM add-in is certainly a solution, however, I am still using Office 97, so I have to find an alternative, or live with the speed Excel offers me.
    Thanks again for your comments.

Posting Permissions

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