Excel VBA Application.Find() Method

Question

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?

Answer 1

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).