xl2007 speed issues
JNW
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
Nick Hodge
xlsx or xlsm
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodge...@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk
"JNW" <J...@discussions.microsoft.com> wrote in message
news:9720B23F-F422-42FB...@microsoft.com...
JNW
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.
dou...@gmail.com
dou...@gmail.com
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 here:
http://newtonexcelbach.wordpress.com/2008/05/06/excel-2007-performance-feedback-please/
http://newtonexcelbach.wordpress.com/2008/02/23/worksheetfunction-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 <J...@discussions.microsoft.com> 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
> > nick_hodgeTAKETHIS...@zen.co.ukANDTHIS
> > web:www.excelusergroup.org
> > web:www.nickhodge.co.uk
>
> > "JNW" <J...@discussions.microsoft.com> wrote in message
> >news:9720B23F-F422-42FB...@microsoft.com...
> > > 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 -
Charles Williams
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" <J...@discussions.microsoft.com> wrote in message
news:9720B23F-F422-42FB...@microsoft.com...
JNW
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
dou...@gmail.com
It includes a simple VBA lookup function.
http://www.dailydoseofexcel.com/archives/2007/09/18/performance-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/2008/03/04/ranges-and-arrays/
http://newtonexcelbach.wordpress.com/2008/03/05/ranges-and-arrays-2/
On May 23, 12:31 am, JNW <J...@discussions.microsoft.com> 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
>
>
>
> "doug...@gmail.com" 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 here:
> >http://newtonexcelbach.wordpress.com/2008/05/06/excel-2007-performanc...
> >http://newtonexcelbach.wordpress.com/2008/02/23/worksheetfunction-vs-...
> > > > > between 2003 and2007calculation steps?
>
> > > > > Thanks- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
dou...@gmail.com
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, doug...@gmail.com wrote:
> JNW - You may find this thread from Daily Dose of Excel interesting.
> It includes a simple VBA lookup function.
>
> http://www.dailydoseofexcel.com/archives/2007/09/18/performance-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:
>
Charles Williams
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
'--------------------------------------------------------------------------
dou...@gmail.com
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" <Char...@DecisionModels.com>
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--------------------------------------------------------------
Charles Williams
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
<dou...@gmail.com> wrote in message
news:15c21e08-4c11-4cc7...@z24g2000prf.googlegroups.com...
Hi Charles
Yes, that is curious.
All times with XL2007
[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
dou...@gmail.com
Doug
On May 24, 12:38 am, "Charles Williams" <Char...@DecisionModels.com>
wrote:
> Hi Doug,
>
> <doug...@gmail.com> wrote in message
> > The Excel Calculation Sitehttp://www.decisionmodels.com- Hide quoted text -
dou...@gmail.com
I decided your comments above (especially the conclusions) were
blogworthy:
http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-udf-2/
Doug
CyberPierre38
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 don’t know how to deploy a solution ExcelVBA-COM, as I
don’t know how to automatically insert the reference into the user’s 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 don’t 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,