I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I am
still experiencing a 50% slowdown. I've found that it somehow has something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone have
a reference as to how xl2007 calculates (what order) and maybe a comparison
between 2003 and 2007 calculation steps?

Thanks

You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks

I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range.

Thanks for the suggestions. I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. You've relieved a lot of frustration.

"Nick Hodge" wrote:

You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks

On May 22, 6:08*am, JNW wrote:
I have read multiple threads here regarding speed issues and xl2007. *They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? *I am
still experiencing a 50% slowdown. *I've found that it somehow has something
to do with calculation. *I have a number of circular references that are
needed in the workbook. *I have iterations on and set to the same setting as
they were in 2003. *I've tried turning calculations off and on at various
points in the code, but to no avail. *

Does anyone have any experience with this? *Alternatively, does anyone have
a reference as to how xl2007 calculates (what order) and maybe a comparison
between 2003 and 2007 calculation steps?

Thanks

I haven't found a significant difference in performance with the
different file formats, other than time for opening and saving, which
is much quicker in xlsb than xls.

On a dual core machine recalculation is quicker in 2007, but VBA
doesn't use the dual cores,and is slower, sometimes much slower. In
particular any operation that involves interaction between VB and the
spreadsheet (such as using worksheetfunction) seems to be very much
slower in 2007.

More details he
http://newtonexcelbach.wordpress.com...edback-please/
http://newtonexcelbach.wordpress.com...nction-vs-udf/

The only solution I know is to avoid using worksheetfunction, and work
on arrays inside VB, rather than reading and writing directly to
worksheet ranges.




On May 22, 8:08*am, JNW wrote:
I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range. *

Thanks for the suggestions. *I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? *Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. *You've relieved a lot of frustration.



"Nick Hodge" wrote:
You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. *They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? *I
am
still experiencing a 50% slowdown. *I've found that it somehow has
something
to do with calculation. *I have a number of circular references that are
needed in the workbook. *I have iterations on and set to the same setting
as
they were in 2003. *I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? *Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks- Hide quoted text -

- Show quoted text -

Also make sure you have uninstalled the Google Desktop Office Search COM
addins: they slow down Excel 2007 significantly.

Although there are some slow things in Excel 2007 I have not found the
calculation engine itself to be generally slower, assuming you are not using
any of the new Excel 2007 features such as referencing full columns or rows
or the new conditional formatting etc.

But I have not done much testing on circular refs with 2007.

If you can send me a zipped workbook I will be happy to take a look at it.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks

Doug-

Thanks for the information about the slow down with using worksheetfunction.

I don't use it much but when I do it's for either vlookup, match, or index.

Do you know of (or can write) an example of how to replace these
worksheetfunctions with array functionality in vba? I am not very good with
arrays yet.

Thanks

" wrote:

I haven't found a significant difference in performance with the
different file formats, other than time for opening and saving, which
is much quicker in xlsb than xls.

On a dual core machine recalculation is quicker in 2007, but VBA
doesn't use the dual cores,and is slower, sometimes much slower. In
particular any operation that involves interaction between VB and the
spreadsheet (such as using worksheetfunction) seems to be very much
slower in 2007.

More details he
http://newtonexcelbach.wordpress.com...edback-please/
http://newtonexcelbach.wordpress.com...nction-vs-udf/

The only solution I know is to avoid using worksheetfunction, and work
on arrays inside VB, rather than reading and writing directly to
worksheet ranges.




On May 22, 8:08 am, JNW wrote:
I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range.

Thanks for the suggestions. I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. You've relieved a lot of frustration.



"Nick Hodge" wrote:
You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks- Hide quoted text -

- Show quoted text -

JNW - You may find this thread from Daily Dose of Excel interesting.
It includes a simple VBA lookup function.

http://www.dailydoseofexcel.com/arch...mance-monitor/

To get the data from a worksheet range into an array simply declare
the array as a variant and use = to fill the array:

Dim myarray() as variant
myarray = Range("myrange").value

That will give you a base 1 array with the values from the spreadsheet
range; obviously you don't get the formatting or any other
information.

You access the values using the row and column index numbers:

ArrayValue1 = myarray(1,1) ' (row index, column index)

so you don't need an INDEX function.

A simple VBA lookup is given in the DDofE link, although I'm sure
there are better examples around.

Also you may find the two post on ranges and arrays from my blog
interesting:

http://newtonexcelbach.wordpress.com...es-and-arrays/
http://newtonexcelbach.wordpress.com...-and-arrays-2/




On May 23, 12:31*am, JNW wrote:
Doug-

Thanks for the information about the slow down with using worksheetfunction.

That should be:

Dim myarray As Variant

without the ()

If you use Dim myarray() you'll get a type mismatch when you get to
the myarray = Range("myrange").value line.




On May 23, 9:33*am, wrote:
JNW - You may find this thread from Daily Dose of Excel interesting.
It includes a simple VBA lookup function.

http://www.dailydoseofexcel.com/arch...mance-monitor/

To get the data from a worksheet range into an array simply declare
the array as a variant and use = to fill the array:

Dim myarray() as variant
myarray = Range("myrange").value

That will give you a base 1 array with the values from the spreadsheet
range; obviously you don't get the formatting or any other
information.

You access the values using the row and column index numbers:

ArrayValue1 = myarray(1,1) * ' (row index, column index)

so you don't need an INDEX function.

A simple VBA lookup is given in the DDofE link, although I'm sure
there are better examples around.

Also you may find the two post on ranges and arrays from my blog
interesting:

http://newtonexcelbach.wordpress.com...-and-arrays-2/

On May 23, 12:31*am, JNW wrote:



Doug-

Thanks for the information about the slow down with using worksheetfunction.

I don't use it much but when I do it's for either vlookup, match, or index.

Do you know of (or can write) an example of how to replace these
worksheetfunctions with array functionality in vba? *I am not very good with
arrays yet.

Thanks

" wrote:
I haven't found a significant difference in performance with the
different file formats, other than time for opening and saving, which
is much quicker in xlsb than xls.

On a dual core machine recalculation is quicker in2007, but VBA
doesn't use the dual cores,and is slower, sometimes much slower. *In
particular any operation that involves interaction between VB and the
spreadsheet (such as using worksheetfunction) seems to be very much
slower in2007.

More details he
http://newtonexcelbach.wordpress.com...007-performanc....
http://newtonexcelbach.wordpress.com...etfunction-vs-....

The only solution I know is to avoid using worksheetfunction, and work
on arrays inside VB, rather than reading and writing directly to
worksheet ranges.

On May 22, 8:08 am, JNW wrote:
I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range. *

Thanks for the suggestions. *I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? *Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. *You've relieved a lot of frustration.

"Nick Hodge" wrote:
You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. *They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? *I
am
still experiencing a 50% slowdown. *I've found that it somehow has
something
to do with calculation. *I have a number of circular references that are
needed in the workbook. *I have iterations on and set to the same setting
as
they were in 2003. *I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? *Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and2007calculation steps?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

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

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

Thanks Charles, that all makes perfect sense now!

Doug


On May 24, 12:38*am, "Charles Williams"
wrote:
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 Sitehttp://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- Hide quoted text -

- Show quoted text -

Charles

I decided your comments above (especially the conclusions) were
blogworthy:

http://newtonexcelbach.wordpress.com...tion-vs-udf-2/


Doug

I can only confirm what's said in various threads about perfs of XL2007. If
you ever used VBA, and want/need to come out with a workable application,
then you are in trouble. It may be I did something wrong, but here what I
experince:

My Excel VBA application looks into 80 rows/ 30 columns sheets and does some
computation, coloring or so. With my old PC, 600Mhz, XP and Excel 2003, it
used to take around 4-5 sec. to complete. I was very proud early this year to
buy a new PC, 2Ghz, 2Mb ram, Excel 2007 and Vista. Curiously enough, the
exact same program on the same sheet takes 45 seconds now (yes, not a typo,
it takes 10 times longer!)
Thinking it could be due to the use of €œpure€� VBA, I created an add-in,
using VSTO with VS2005. I got then even worse performances: the same file
takes now 66 seconds to complete.
I decided then to embed most of the code into a COM object. First results
were disappointing (around 20sec), but by optimizing here and there, I
eventually got a reasonable response time, around 7-8 seconds.
That COM solves my performance problem (at least, the users will accept).
Then, remains that I dont know how to deploy a solution ExcelVBA-COM, as I
dont know how to automatically insert the reference into the users Excel at
setup time. Can anyone help here?
The most natural solution would have been to create a simple DLL instead of
a COM (I dont really need to instantiate objects in my apps, just call
functions). However, I am totally unable to create, with VS2005, a DLL that
would be callable from VBA. Does anyone have an example?
Note: I know how to call from VBA a function in an existing DLL, but each
time I try to call mine, I got the error saying the entry points are not
known (though the DLL file seems to be found). I would appreciate if somebody
can tell me how to make my own DLL callable from Excel VBA.

Thanks,


"Charles Williams" wrote:

Also make sure you have uninstalled the Google Desktop Office Search COM
addins: they slow down Excel 2007 significantly.

Although there are some slow things in Excel 2007 I have not found the
calculation engine itself to be generally slower, assuming you are not using
any of the new Excel 2007 features such as referencing full columns or rows
or the new conditional formatting etc.

But I have not done much testing on circular refs with 2007.

If you can send me a zipped workbook I will be happy to take a look at it.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks