Hi Doug,
Thanks: I see what the difference is.
I created 10000 UDF VBAMATCH formulae on the worksheet and timed the
calculation of the 10000 formulae using RangeCalc, so that I was timing
passing the 10000 numbers to each UDF call as a range
Your timing routine has as its first executable statement:
datarange=Range("a1:A10000")
This converts the range to a variant array of values before doing any
timing, and then passes datarange to the UDFs as a variant array rather than
a range.
So for your timing run of VBAMatch there is no data transfer between Excel
and VBA or VBA and Excel at all, but for VBAMATCH2 the whole array gets
passed from VBA to Excel 10000 times.
Since the vast majority of the execution time is taken by the data transfer
that explains the differences.
Conclusion:
If you want to develop a MATCH routine to process a sorted VBA array then a
VBA binary search routine (or your equivalent) will be fast because the data
is already in VBA, but if you want to develop a UDF MATCH routine to use as
a worksheet UDF function its better to use Worksheetfunction.MATCH because
then the data never has to be passed from Excel to VBA.
Since the Google Office COM Addins tend to affect the VBA<--Excel transfer
time you would not see any effect on your VBAMATCH timimg.
(Australia and New Zealand were great: I really enjoyed both speaking at the
XLEUC conference and the rest of the trip (5 weeks in total!))
regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
wrote in message
...
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
[f1] = Timer - starttime
starttime = Timer
For i = 1 To numits
y = datarange(i, 1)
x = VBAMatch2(y, datarange)
Next i
[f2] = Timer - starttime
starttime = Timer
For i = 1 To numits
y = datarange(i, 1)
x = VBAMatch3(y, datarange)
Next i
[f3] = 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