Hi Charles

Yes, that is curious.

What is curiouser is that I have tested my VBAMATCH function against
your VBAMATCH2, and got the exact opposite results:

VBAMATCH: 0.06 seconds
VBAMATCH2: 20.62 seconds

I also pasted in your version of my code as VBAMATCH3. I had to
reinsert the line:
If rownext < MinRow Then rownext = MinRow
to avoid an out of bounds error. I didn't analyse if that is an
efficient way to do it, but it worked.
I also note you changed maxrow and minrow from longs to doubles.
Other than that I think the code is identical, But VBAMATCH3 came in
at about 0.05 seconds.

All times with XL2007

Here's the code for the timing:

Sub checkvbamatch()
Dim numits As Long, starttime As Double
Dim i As Long, x As Long, y As Double, j As Long
Dim datarange As Variant


datarange = Range("a1:a10000")
numits = 10000

starttime = Timer
For i = 1 To numits
y = datarange(i, 1)
x = VBAMatch(y, datarange)
Next i
[d1] = Timer - starttime


starttime = Timer
For i = 1 To numits
y = datarange(i, 1)
x = VBAMatch2(y, datarange)
Next i
[d2] = Timer - starttime

starttime = Timer
For i = 1 To numits
y = datarange(i, 1)
x = VBAMatch3(y, datarange)
Next i
[d3] = Timer - starttime

End Sub


Incidentally, you recommending switching off Google Desktop at the
Conference in Sydney. I did try that, and it didn't seem to make any
significant difference in my case. I had it running when I did the
times above.

I'd be interested to see if you can work out what is happening here.

Doug


p.s. Hope you enjoyed the rest of your stay in Australia!


On May 23, 8:13*pm, "Charles Williams"
wrote:
Hi Doug,

Curious: I tested your VBAMATCH function for 10000 function calls on a range
containing 10000 random numbers (sorted ascending since the function does
not work on unsorted data) compared to a very simple function using .MATCH
and on my system your function is substantially slower, both in XL2003 and
XL 2007.
As noted elsewhere, 2007 VBA is slower at transferring data to/from XL than
previous versions.

Function VBAMatch2(arg As Double, XRange As Variant) As Long
* * VBAMatch2 = Application.WorksheetFunction.Match(arg, XRange, 1)
End Function

Xl2003
VBAMatch 24.586 secs
VBAMatch2 0.094 secs

XL2007
VBAMatch 42 secs
VBAMatch2 0.210 secs

Below is what I used for your VBAMATCH function: have I introduced a mistake
into it somewhere?

'-----------VBA------------------------------------------------------------*--
Function VBAMatch(arg As Double, XRange As Variant) As Long
* * Dim x1 As Double, x2 As Double, xslope As Double
* * Dim MaxRow As Double, MinRow As Double
* * Dim row1 As Long, row2 As Long, rownext As Long
* * Dim Diff As Double

* * ' Convert Xrange to an array if passed as a range
* * If TypeName(XRange) = "Range" Then XRange = XRange.Value

* * MinRow = 1
* * MaxRow = UBound(XRange)

* * row1 = 1
* * row2 = MaxRow

* * Do While MaxRow - MinRow 4
* * * * x1 = XRange(row1, 1)
* * * * x2 = XRange(row2, 1)
* * * * If x2 = arg Then
* * * * * * VBAMatch = row2
* * * * * * Exit Function
* * * * End If
* * * * If x2 arg Then MaxRow = row2 Else MinRow = row2
* * * * xslope = (x2 - x1) / (row2 - row1)
* * * * rownext = row2 + Int((arg - x2) / xslope)
* * * * If rownext MaxRow Then rownext = MaxRow
* * * * row1 = row2
* * * * row2 = rownext
* * * * If row2 = row1 Then Exit Do
* * Loop

* * Diff = 1
* * row2 = MinRow

* * Do While Diff 0 And row2 < MaxRow
* * * * row2 = row2 + 1
* * * * Diff = arg - XRange(row2, 1)

* * Loop
* * If Diff < 0 Then
* * * * VBAMatch = row2 - 1
* * Else
* * * * VBAMatch = row2
* * End If
End Function
'--------------------------------------------------------------------------
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com