I have a home built ecg machine and use Excel to process the data and
display the results. The VBA code takes the incoming data stream (200
readings per second), processes the data and displays the ecg waveform
in a chart.
This works fine in Excel 2003 but recently I have installed Excel
2007. The VBA code in Excel 2007 runs 20 times slower than Excel 2003
which means that it is impossible to display the waveforms in "real
time" and incoming buffer overflow occurs.
I have increased the computer's memory from 512K to 2G without giving
any improvement. The processor is a duo-core and I am running XP.

Has anyone any idea how to improve the VBA speed?

one of the many reasons i won't use it.

--


Gary


"Ken" wrote in message
ups.com...
I have a home built ecg machine and use Excel to process the data and
display the results. The VBA code takes the incoming data stream (200
readings per second), processes the data and displays the ecg waveform
in a chart.
This works fine in Excel 2003 but recently I have installed Excel
2007. The VBA code in Excel 2007 runs 20 times slower than Excel 2003
which means that it is impossible to display the waveforms in "real
time" and incoming buffer overflow occurs.
I have increased the computer's memory from 512K to 2G without giving
any improvement. The processor is a duo-core and I am running XP.

Has anyone any idea how to improve the VBA speed?

Recently in a blog, the Microsoft Excel 2007 product manager was
begging developers to send him problem/slow workbooks.
They wanted to test them.

This link outlines some differences between old/new Excel.
The author is not happy...
http://www.add-ins.com/Excel%202003%20versus%202007.htm

Some "wisdom" that I can recall is...
"Never be first with the newest or the last with the oldest"

A long time ago in another universe, I almost died because
I ignored the above. However, I don't think anybody yet is
saying Excel is dangerous. <g

You may want to consider reinstalling Excel 2003 for a while.
--
Jim Cone
San Francisco, USA
(xl97, xl2000, xl2002, xl2003)
http://www.realezsites.com/bus/primitivesoftware



"Ken"
wrote in message
I have a home built ecg machine and use Excel to process the data and
display the results. The VBA code takes the incoming data stream (200
readings per second), processes the data and displays the ecg waveform
in a chart.
This works fine in Excel 2003 but recently I have installed Excel
2007. The VBA code in Excel 2007 runs 20 times slower than Excel 2003
which means that it is impossible to display the waveforms in "real
time" and incoming buffer overflow occurs.
I have increased the computer's memory from 512K to 2G without giving
any improvement. The processor is a duo-core and I am running XP.

Has anyone any idea how to improve the VBA speed?

This question comes up over and over.

Any time a Sub saves data into a Worksheet cell at a time instead of
row at a time you're going to get a O(n^2), hit on performance
compared to O(n).

See if you can figure out what that means :-)
Think rows. Think rows times columns.

So whenever possible do all assignments into a Worksheet a row at a
time.

To be on the safe side, each iteration should end with a few Nothing
assignments. Hopefully Excel's garbage collector can go with that.

From the many posts I see on m.p.e.p it looks like cell at a time
assignment is *very* common.

Another thing that is a real killer, even worse than data assignments
are cell at a time font assignments.

Formatting in VBA should be kept to a minium.

It's best when you put most the formatting into an Excel Template and
then fill the Template.

Also, for formatting, formatting should *not* be done a row at a time
(say you're zebraing the rows or hiding rows), instead groups of 20 or
so rows should be formatted at the same time.

Header/Footer assignments take a long time. They should never occur -
the Template should take care of the header and footer.

Another thing, if you're querying a database, it's much better to do a
single query with an outer join and then parse the result set (or a
sheet with the query data) then it is to do nested queries. If you're
doing SQL, and you aren't fully up to speed on outter joining then ask
for a tip along the lines of "can I get all this data in a single
query, it's OK if I have to parse the result set. Outter joining isn't
as trivial you at first you might think, a good query might be outter
joining on 6 or more tables.

If you're *really* creating a big set of Worksheets and your sorting
Collections and you've got more than 1000 lines of code, you should
look to a tool other than Excel to do the real work. That could be C,
Perl, Python or whatever. Keep Excel for the presentation of the data,
not necessarily the organizing of it.

If it's a database application and you're using Access, it may be time
to move on to SQL Server or MySQL or Oracle. Access shouldn't be used
on databases having more a MDB file larger than 500 Megs.

Thanks guys. I am glad that I took the precaution of keeping my old
Excel.
Microsoft seems to have really stuffed up with Office 2007. I have
some fairly strait forward VBA routines in Word and they run about 10
times slower under Word 2007.
My Excel ecg program also runs perfectly satisfactorily in Excel 97/
Windows 98 on my old laptop which has a 200MHz Pentium 2 and 128M of
memory.
I will try the programming techniques suggested but there is a lot of
performance to catch up on.

It will really help if you can get the 2007 (and same thing in 2003 format)
over to the Excel team. The slowdown appears to be associated primarily with
the graphic engine (new to Office 2007). I've already sent them my own
example files from an experience I had: 400,000 rows of data, split into 50+
groups of 8800 data points, graphed. Single Core AMD 3200+ 1GB RAM Excel
2003: 1min 40 seconds to read, process and graph. Dual Core AMD 4800+ 2GB
RAM and 2nd machine Intel Core2 Duo 6600 2GB RAM both Excel 2007: over 10
minutes to create the charts; and then only after I split the data across 50+
sheets w/1 chart per sheet. Similarly a UDF that I created ran about 10x
faster on the single core, slower system with less RAM under Excel 2003 than
on the 'state of the art' hardware systems with Excel 2007.

If you have trouble getting the files to Microsoft, send them to me as email
attachments and I'll get them to them. Email to (remove spaces)
HelpFrom @ jlathamsite.com

Where heavy duty graphing is required, best to stick with 2003 for now, at
least until 2007/SP1 comes out.

"Ken" wrote:

Thanks guys. I am glad that I took the precaution of keeping my old
Excel.
Microsoft seems to have really stuffed up with Office 2007. I have
some fairly strait forward VBA routines in Word and they run about 10
times slower under Word 2007.
My Excel ecg program also runs perfectly satisfactorily in Excel 97/
Windows 98 on my old laptop which has a 200MHz Pentium 2 and 128M of
memory.
I will try the programming techniques suggested but there is a lot of
performance to catch up on.

i think whoever is writing what you're reading is grasping at straws.

i have an schedule app that keeps track of ingredients used to make blends. user
enters the code, it opens the corresponding blend file, transfers all of the
ingredients into to the schedule, formats the sheet with banding and performs
other font formatting.

right now there are 36 blends scheduled, and to clear and rebuild the schedule
in xp/2003 takes about 3 seconds over the lan. (this is the 2nd, 3rd... run time
to take into consideration caching).

in vista/excel 2007, this takes about 9 seconds.

i can watch it on the taskbar and see clearly how much slower this is in xl2007.

so, what i did, i took out all of the code and just open each blend file and
close it, without doing any data transfer or formatting to the schedule.

guess what, it was no different, 3 seconds in xp, 9 seconds in excel 2007.

so, while maybe the things you pointed out slow things down, i submit that
anything you do in vba is slower in xl2007 then it is in xl2003.

and yes, i sent my app to david gainer 6 weeks ago, he received it, but i have
heard nothing more.

hey, i am a reseller, was on the betas. i make (supposed to make) money selling
ms software. i have sold zero copies of vista and zero copies of office2k7. i
have a reputation with my clients and if i lied and told them they needed either
of these to do their business, i'd be out of business.

by this time in the xp/office2k3 cycle, i probably had sold 100 seats of each.
why, because there was a benefit to xp. i see nothing in either of these to make
my clients more productive. plus, there are a few bugs they refused to fix
before release that keep me from switching to vista. i'm hoping they're fixed
in the next release.

sorry for the rant.
--


Gary


wrote in message
oups.com...
This question comes up over and over.

Any time a Sub saves data into a Worksheet cell at a time instead of
row at a time you're going to get a O(n^2), hit on performance
compared to O(n).

See if you can figure out what that means :-)
Think rows. Think rows times columns.

So whenever possible do all assignments into a Worksheet a row at a
time.

To be on the safe side, each iteration should end with a few Nothing
assignments. Hopefully Excel's garbage collector can go with that.

From the many posts I see on m.p.e.p it looks like cell at a time
assignment is *very* common.

Another thing that is a real killer, even worse than data assignments
are cell at a time font assignments.

Formatting in VBA should be kept to a minium.

It's best when you put most the formatting into an Excel Template and
then fill the Template.

Also, for formatting, formatting should *not* be done a row at a time
(say you're zebraing the rows or hiding rows), instead groups of 20 or
so rows should be formatted at the same time.

Header/Footer assignments take a long time. They should never occur -
the Template should take care of the header and footer.

Another thing, if you're querying a database, it's much better to do a
single query with an outer join and then parse the result set (or a
sheet with the query data) then it is to do nested queries. If you're
doing SQL, and you aren't fully up to speed on outter joining then ask
for a tip along the lines of "can I get all this data in a single
query, it's OK if I have to parse the result set. Outter joining isn't
as trivial you at first you might think, a good query might be outter
joining on 6 or more tables.

If you're *really* creating a big set of Worksheets and your sorting
Collections and you've got more than 1000 lines of code, you should
look to a tool other than Excel to do the real work. That could be C,
Perl, Python or whatever. Keep Excel for the presentation of the data,
not necessarily the organizing of it.

If it's a database application and you're using Access, it may be time
to move on to SQL Server or MySQL or Oracle. Access shouldn't be used
on databases having more a MDB file larger than 500 Megs.

So what 2003 is faster. 2007 has more rows. It can do more stuff.

Not necessarily true: yes, has more rows/columns but most systems can't even
fill up a single sheet and function well (do the math, see how much RAM you'd
need just to keep a single full sheet with a single character in each cell,
not even considering overhead of Excel itself along with OS RAM needs). In
the case I cited above about the 400,000+ rows, we specifically went with
2007 because of the added rows, but the graphing errors (and it wouldn't
complete everything on 1 sheet due to errors) and system slowdowns forced a
solution that split the data across multiple sheets and still the charting
crippled the systems and in the end to get it to work in 2007 required
exactly the same processing we ended up using in 2003 because even after
reengineering the processing, 2007 was 10x slower than 2003.

Bigger is not always better. I remember the days when state troopers always
bought chase cars with the biggest engines they could find, and their 440 cid
Chrysler products were getting blown away by 426 cid Hemi's made by Chrysler
also.

" wrote:

So what 2003 is faster. 2007 has more rows. It can do more stuff.

So whenever possible do all assignments into a Worksheet a row at a
time.

Why not a whole matrix at a time (a range of N rows and M columns)? Faster
still.

To be on the safe side, each iteration should end with a few Nothing
assignments. Hopefully Excel's garbage collector can go with that.

??

I agree with your comments about formatting and database retrieval. However.
it seems that none of these are the primary problem if the OP's identical
code runs so much slower in 2007.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
oups.com...
This question comes up over and over.

Any time a Sub saves data into a Worksheet cell at a time instead of
row at a time you're going to get a O(n^2), hit on performance
compared to O(n).

See if you can figure out what that means :-)
Think rows. Think rows times columns.

So whenever possible do all assignments into a Worksheet a row at a
time.

To be on the safe side, each iteration should end with a few Nothing
assignments. Hopefully Excel's garbage collector can go with that.

From the many posts I see on m.p.e.p it looks like cell at a time
assignment is *very* common.

Another thing that is a real killer, even worse than data assignments
are cell at a time font assignments.

Formatting in VBA should be kept to a minium.

It's best when you put most the formatting into an Excel Template and
then fill the Template.

Also, for formatting, formatting should *not* be done a row at a time
(say you're zebraing the rows or hiding rows), instead groups of 20 or
so rows should be formatted at the same time.

Header/Footer assignments take a long time. They should never occur -
the Template should take care of the header and footer.

Another thing, if you're querying a database, it's much better to do a
single query with an outer join and then parse the result set (or a
sheet with the query data) then it is to do nested queries. If you're
doing SQL, and you aren't fully up to speed on outter joining then ask
for a tip along the lines of "can I get all this data in a single
query, it's OK if I have to parse the result set. Outter joining isn't
as trivial you at first you might think, a good query might be outter
joining on 6 or more tables.

If you're *really* creating a big set of Worksheets and your sorting
Collections and you've got more than 1000 lines of code, you should
look to a tool other than Excel to do the real work. That could be C,
Perl, Python or whatever. Keep Excel for the presentation of the data,
not necessarily the organizing of it.

If it's a database application and you're using Access, it may be time
to move on to SQL Server or MySQL or Oracle. Access shouldn't be used
on databases having more a MDB file larger than 500 Megs.

Hi Jon,

Why not a whole matrix at a time (a range of N rows and M columns)? Faste still.

Yeah. Smokin!

On Excel for Mac using MacScript you can insert a matrix at a time
easily, but I haven't figured out how to do it on the PC. I recall
having done it once, but recently couldn't matrix at a time assignment
to work for me.

In Applescript it's easy, you do it like this:

set list1 to {{20, 5, 60}, {10, 13, 12}}
on doinsert(list1)
tell application "Microsoft Excel"
tell active workbook
tell sheet "Sheet1"
set value of range "A1:D4" to list1
end tell
end tell
end tell
end doinsert
doinsert(list1)

(You can whittle this down to 3 lines but this code is easy for non-
AppleScripters to understand.)

When I translated this into VBA things didn't work out ...

dim list1 as Array(Array(20,5,60),Array(10,13,12)) ' Various
permutations tried

Assigning into

Range("A1:D4").value = list1

didn't work.

Also, there was the issue of looping and dynamically building the
array of arrays. So even if I had matrix at a time assignment going -
I still had that to deal dynamically building the array of arrays,
something one doesn't have to worry about using AppleScript.

I'd be delighted if you show us how it's done.

Adding a loop where an array is added to the array of arrays on each
iteration would add an extra fine touch! Please!

To be on the safe side, each iteration should end with a few Nothing
assignments. Hopefully Excel's garbage collector can go with that.

??

My experience is that Excel objects don't automatically get removed
from memory.
The objects are removed from the scope of VBA programs - but not from
program memory space.

The memory space for Excel keeps growing and growing until it gets to
an asymptotic level which varies depending upon your system's
resources. Then the garbage collector kicks in. With VISTA and Office
13 that's probably *not* the case. But with XP it is.

However. it seems that none of these are the primary problem if the OP's identical
code runs so much slower in 2007.

Agreed. I recall somewhere in the thread where someone asked for tips,
generally, on making Excel faster. I got carried away.

In my little example the range should be A1:B3 not A1:D4 I mixed up my
examples locally. You should get the idea.