Application.Index & Application.Match

[Guest]

I have seen the following code in a previous question:

Dim N As Variant
Dim arr As Variant
Dim arr2 As Variant
Dim strMessage As String

'Fill the array.
arr = Range("A1:B50").Value

'Return the second column of the array
arr2 = Application.Index(arr, 0, 2)



'Find the position in the array of "77"
N = Application.Match(77, arr2, 0)

I have tried to find help on Application.Index and Application.Match but
have not been successful.

Can someone point me to a help page explaining the syntax of these two
functions.

Thanks,

Antonio

 

[RichardSchollar]

Hi Antonio

They are actually WorksheetFunctions - the proper syntax would be:

Application.WorksheetFunction.Index()

Check VBA Help for WorksheetFunction.

Best regards

Richard

 

[RichardSchollar]

Antonio

I should have said that the help file you want is entitled:

Using Microsoft Excel Worksheet Functions in Visual Basic

Look for this one.

Hope this helps!

Richard

 

[Guest]

Hi Richard,

That helped fully.

Many thanks,

Antonio

 

[Bob Phillips]

Not totally correct Richard.

There are two ways to invoke worksheet functions in VBA, either using the
function as a property of the WorksheetFunction property, or as property of
the Application object. As an example, the SUM function can be called with
Application.SUM, or with Worksheetfunction.SUM (In practice, there is a
third way, using Application.Worksheetfunction.SUM, but aside from small
differences noted above, Application.Worksheetfunction and Worksheetfunction
can be assumed to be equivalent)

Application was how worksheet functions were invoked prior to Excel 97, and
has been retained for compatibility.


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Antonio,

In my testing, your code works fine for me.

What results are you getting, an error, a wrong or unexpected result?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

And an important note about the difference between application.match and
application.worksheetfunction.match.

If you use
dim res as variant
res = application.match(...)
res may return an error if there is no match.

If you use:
res = application.worksheetfunction.match(...)
and there is no match, then you'll get a runtime error.

You can test for a match using application.match() by using:
if iserror(res) then
'no match...
else
'do the work
end if

You can test for a match using application.worksheetfunction.match(...) by
using:

on error resume next
res = application.worksheetfunction.match(...)
if err.number <> 0 then
'no match
err.clear
else
'do the work
end if
on error goto 0

Application.match() looks easier to use (to me, anyway).

The same holds true for application.vlookup() and
application.worksheetfunction.vlookup().

 

[Guest]

Hi Bob,

Thank you for your interest.

No, no, the code works fine for me as well.

I just wanted to learn the syntax of those two functions.

I know now that they are just the worksheet functions.

Regards,

Antonio

 

[Dave Peterson]

res may return an error if there is no match.
should be
res will return an error if there is no match.
or
res may return an error (if there is no match).