WorksheetFunction.Match applied on an Array

[Werner Rohrmoser]

Hi,
the code below stops at the statement
"Res = WorksheetFunction.Match(ActiveWorkbook.Name, WbArray, 0)"
with Error Message: "Uable to get the Match property of the
WorksheetFunction class"
What's wrong?

WIN XP 5.1 SP1
Excel XP SP3

Regards
Werner

*****************************************************************************************************************

Public Sub Test()

Dim ArrayIndex As Integer
Dim Lastrow As Integer
Dim LoopCounter As Integer
Dim Res As Variant

Lastrow = WorksheetFunction.CountA(ArrayConstants.Range("A:A"))
ArrayIndex = 1
ReDim Preserve WbArray(Lastrow - 1)
For LoopCounter = 2 To Lastrow
WbArray(ArrayIndex) = ArrayConstants.Cells(LoopCounter, 1)
ArrayIndex = ArrayIndex + 1
Next LoopCounter

Res = WorksheetFunction.Match(ActiveWorkbook.Name, WbArray, 0)
MsgBox Test

End Sub

 

[Matt Richardson]

Hi,
the code below stops at the statement
"Res = WorksheetFunction.Match(ActiveWorkbook.Name, WbArray, 0)"
with Error Message: "Uable to get the Match property of the
WorksheetFunction class"
What's wrong?

WIN XP 5.1 SP1
Excel XP SP3

Regards
Werner

*****************************************************************************************************************

Public Sub Test()

Dim ArrayIndex As Integer
Dim Lastrow As Integer
Dim LoopCounter As Integer
Dim Res As Variant

Lastrow = WorksheetFunction.CountA(ArrayConstants.Range("A:A"))
ArrayIndex = 1
ReDim Preserve WbArray(Lastrow - 1)
For LoopCounter = 2 To Lastrow
WbArray(ArrayIndex) = ArrayConstants.Cells(LoopCounter, 1)
ArrayIndex = ArrayIndex + 1
Next LoopCounter

Res = WorksheetFunction.Match(ActiveWorkbook.Name, WbArray, 0)
MsgBox Test

End Sub

This might help you out:-

http://www.ozgrid.com/forum/showthread.php?t=38080

HTH
Matt Richardson
http://teachr.blogspot.com

 

[Charles Williams]

Hi Werner,

If you use Worksheetfunction.Match with exact match an error is raised if
there is no match, so you need to error trap it with On Error.

If you use Application.Match no error is raised but an error value is
returned instead
Note that using Worksheetfunction.Match is faster than using
Application.Match

regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Bob Phillips]

Try Application.Match

I would add error handling in case it fails

On Error Resume Next
Res = WorksheetFunction.Match(ActiveWorkbook.Name, WbArray, 0)
On Error GoTo 0
If Not Res > 0 Then
MsgBox "Not Found"
End If

--
---
HTH

Bob


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

 

[Werner Rohrmoser]

Thanks to all,

On Error... is the solution.

Another question, where can I find functions like Application.Match?
When I look into the object browser / application I can' find it.

Regards
Werner

 

[Charles Williams]

Try Visual Basic Help and look for
List of Worksheet Functions Available to Visual Basic

or you can make the object browser enumerate the WorksheetFunction


regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Jim Cone]

Charles,
Re: "Worksheetfunction.Match is faster than using Application.Match"

Does that apply to the use of all worksheet functions in VBA?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Charles Williams"
wrote in message
Hi Werner,
If you use Worksheetfunction.Match with exact match an error is raised if
there is no match, so you need to error trap it with On Error.

If you use Application.Match no error is raised but an error value is
returned instead
Note that using Worksheetfunction.Match is faster than using
Application.Match
regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Tim Zych]

Note that using Worksheetfunction.Match is faster than using

Application.Match

Wow, I in an extensive UDF solution that was dogging, I found that
Application.Match/Index in place of WorksheetFunction.Match/Index sped up
the calculation enormously.

 

[Werner Rohrmoser]

Charles,

I've found the list.
But what is the difference between Application.Match and
Application.WorksheetFunction.Match?
Or is it for downward compability, "WorksheetFunction" was introduced
some relaeses ago, I guess.

There are contrary statements about speed in this thread, does that
mean it depends on
the situation?

Regards
Werner

 

[Charles Williams]

Werner,

I dont know why worksheetfunction was introduced: AFAIK Microsoft have never
said why.

Speed: worksheetfunction has been somewhat faster in all the tests I have
done, but of course I have not tested every possible scenario.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Charles Williams]

If you got an enormous difference I would suspect it was something else that
sped it up.

Can you find the two versions of the code?

The tests I did showed Worksheetfunction was about 25% faster I think. I was
probably using range object variables, since thats generally faster than
hauling all the data across to VBA.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Charles Williams]

Jim,

Well I have not tested it so I dont know ...

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Jim Cone]

Charles,
I was afraid that would be the answer.
I certainly don't want to spend the time to do the tests.<g>
Thanks,
Jim Cone


"Charles Williams"
wrote in message
Jim,
Well I have not tested it so I dont know ...
Charles

"Jim Cone"
wrote in message

 

[Tim Zych]

I'm going to look at it again because now you have me curious, but long
story short, simply changing Worksheetfunction to Match was *the* answer to
the performance problem. There was, literally, no other factor, e.g. I
didn't switch to Match and also do something-or-other.

I'll look at it again and would be interested in following up with you with
the results, if you don't mind. Ideally, I'd like to replicate the
environments if possible. It's a complex solution and I need to pare it down
and isolate the applicable sections.