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 Site
http://www.decisionmodels.com