In Excel
Dim arr1 As Variant
arr1 = WorksheetFunction.Index(Cells, Evaluate("row(1:100)"), Array(1, 7, 4, 3, 2))
arr1 = Application.Index(Cells, Evaluate("row(1:100)"), Array(1, 7, 4, 3, 2))
The first attempt to assign a value to arr1 fails with an error 13 (Type Mismatch), but the second attempt works fine.
However, while the WorksheetFunction object is part of the Application object, the Application object itself appears to have no "index" method. Yet the code works. Any ideas why?
For what it's worth,
arr1 = Application.WorksheetFunction.Index(Cells, Evaluate("row(1:100)"), Array(1, 7, 4, 3, 2))
fails with an error 13 as well.
Excel functions invoked through WorksheetFunction and through Application are supposed to do the same thing but they differ in many aspects, because they were written at different times and by different teams.
WorksheetFunction.Index expects the second argument to be a number. This seems to be a "mistake" in writing its prototype, because the actual INDEX function of Excel accepts arrays for the second argument. Strangely enough, the third argument of WorksheetFunction.Index accepts arrays, because it was declared as Variant in the prototype. The mistake occurred only at the second argument which was declared As Double, hence the type mismatch.
There are other known differences between WorksheetFunction.doStuff and Application.doStuff. i.e.:
The former raises a runtime error if errors occur in the calculation. The latter simply returns an error variant which you can check by code using IsError
The former functions appear in Intellisense but in a very vague, almost useless way.
I always prefer to invoke the Excel functions through the Application object.