CODEWITHSUNDEEP
excelvbaobject
Gen.L
Gen.L

Reputation: 392

Excel VBA Application.Find() Method

Based on Microsoft VBA docs, I know there's Range.Find(What, After, LookIn, Lookat, SearchOrder....), but there is no Application.Find() method. And if I go to the Object Browser in VBE to inspect the Application object, I still not see any Find method attached to Application object. And it makes sense there is no intellisense when I type "Application.Find("

However, The Application.Find() method just works.

txt = "EEZ || EZE 123"
pos = Application.Find(" ", txt, 1)

the pos will have value 4. Why? ... no docs, no method in the object browser, the method can still work?

Upvotes: 4

Views: 6284

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Application.Find is an extended member of the Excel.Application interface.. it's pretty much tacked-on at runtime, that's why it's not showing anywhere. The COM type for Excel.Application is extensible, and extended with what appears to be members of the WorksheetFunction interface, but through the Excel calculation engine (or at least, consistent with how Excel would evaluate it) rather than through the VBA runtime.

In other words this:

Debug.Print Application.Find("4", "12345", 1)

Is pretty much this:

Debug.Print Application.WorksheetFunction.Find("4", "12345", 1)

The difference is more obvious when you give them invalid parameters:

Debug.Print TypeName(Application.Find("4", "12345", 10))
Debug.Print TypeName(Application.WorksheetFunction.Find("4", "12345", 10))

The first prints Error, the second throws run-time error 1004, allowing for more idiomatic error handling (i.e. an On Error statement will kick in). The Error type can't be converted to any other VBA type, so expect run-time error 13 "type mismatch" if you try to assign it to a String, or anything other than a Variant. The IsError function returns True given a Variant/Error variable/expression.

If you need to locate the position of a substring inside a String, use the VBA.Strings.InStr function:

Debug.Print InStr(1, "12345", "4", vbTextCompare)

Note the order of the parameters, and the additional VbCompareMethod parameter that makes a case-insensitive search with vbTextCompare, a case-sensitive search with vbBinaryCompare, and if you're in Access, vbDatabaseCompare matches the comparison method of the current database. If omitted, Option Compare specified at module level determines the parameter value; Option Compare Binary being the default.

Invoking early-bound VBA standard library functions should incur less run-time overhead than querying IDispatch on an Object to locate a Find function... and then get Excel to evaluate the result.

Other examples of extended interfaces include ADODB.Connection, against which you can (whether you should is another question!) invoke a stored procedure as you would a member call - note that here again, intellisense can't help, and Option Explicit will turn a blind eye, for these calls are always late-bound (i.e. resolved at run-time).

Upvotes: 5

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

This is indeed very interesting. It appears that this is a shortcut to Application.WorksheetFunction.Find. The Find method you are using is not the same as the VBA Find method for Range.Find, it is the same as the worksheet function Find.

I also tested other Worksheet Functions and they all appear to also work: Application.Sum, Application.Count. But others already have a defined Application method (such as Application.Left does not shortcut to Worksheet Function).

Upvotes: 0

Related Questions