0

I've got a function which uses InStr() to locate a character in a string, but I know there is a built in function in Excel called FIND(). Can anyone advise which is faster or more cpu efficient?

hours_position = InStr(1, value, " ")

vs

hours_position = Application.WorksheetFunction.Find(" ", value, 1)
Paul
  • 21
  • 1
  • 5
  • 9
    [Race your horses](https://ericlippert.com/2012/12/17/performance-rant/) – Filburt Sep 06 '17 at 10:47
  • 2
    They have different purposes: `InStr()` is used to find a sub-string within **one** string. `Find()` is used to find a string in multiple strings (as in Cells). I use `InStr()` to make a faster version of `Find()` by transfering ranges to arrays, looping them, and using `InStr()`. So you should define your purpose and measure the difference – paul bica Sep 06 '17 at 11:13
  • @paulbica `Find()` is used for cell in a particular range. Am I correct or missing anything overhere? – nishit dey Sep 06 '17 at 11:14
  • @nishitdey - `Find()` is used to search multiple cells in a range. If you and the OP are thinking of `FindB()` there are ways to measure the difference between them as well - by using a large paragraph to find multiple sub-strings, and measure the response with a [microtimer](https://stackoverflow.com/a/7116928/4914662) – paul bica Sep 06 '17 at 11:41
  • Range.Find is very different from WorksheetFunction.Find. – D_Bester Jan 10 '18 at 13:51

1 Answers1

2

Both are same and it does the same action.

Most (but not all) worksheet functions can also be called from VBA. For example, you can use the VLOOKUP worksheet function in VBA by calling Application.WorksheetFunction.VLookup (or Application.VLookup).

Similarly, you can use Application.WorksheetFunction.Find and Application.WorksheetFunction.Search. You can use them to emulate the way the worksheet functions work in your VBA code.

These functions are only available in Excel VBA, whereas InStr is a generic VBA function, available in all Office applications (and in VB6, VB.NET etc.)

Apart from that, the Range object in Excel VBA has a Find method, and the Worksheet object has a Find object. These, however, serve a different purpose: you can't use them to search for text within a string, but to search for cells with specified content.

nishit dey
  • 458
  • 1
  • 7
  • 21
  • `WorksheetFunction.Find` does the same as `=FIND(`, witch is the formula version of InStr, returning #NA if not found rather than the 0 returned from InStr – SeanC Sep 06 '17 at 14:00