Slow Macros in Excel 2007

[Ken]

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?

 

[Gary Keramidas]

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

 

[Jim Cone]

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 2003 versus 2007.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" <[email protected]>
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?

 

[gimme_this_gimme_that]

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.

 

[Ken]

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.

 

[Gary Keramidas]

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.

 

[gimme_this_gimme_that]

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

 

[JLatham]

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.

 

[JLatham]

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.

 

[Jon Peltier]

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

 

[gimme_this_gimme_that]

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 "A14" 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("A14").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!

??

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.

 

[gimme_this_gimme_that]

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

 

[Jon Peltier]

Sub ReadAndWriteArrays()
Dim vData As Variant

' Read Data
vData = ActiveSheet.Range("A13").Value

' Write Data
ActiveSheet.Range("F3:I5").Value = vData
End Sub

I've left our any array manipulation and other calculations you may use in
your project.

- Jon

 

[gimme_this_gimme_that]

This is a useless example.

It's only useful in the case where you *already* have the data in a
worksheet.

The point is to get the data into a worksheet in the first place.

Your example may give you the sense that you're clever.

But for do matrix at a time assignment from a database this doesn't
cut it.

If you look at the Applescript solution it's perfectly suited to
fetching and storing from a database.

Your example is useful in the sense that you're an MVP and if you
can't do it, then it's probably the case that it can't be done.

You must get something going with an array of arrays to have a useful
example.

 

[Charles Williams]

If you look at the Applescript solution it's perfectly suited to
fetching and storing from a database.

What does an Array() function have to do with reading/writing a database?

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[gimme_this_gimme_that]

Hi Charles,

The concept of using an array or an array of arrays applies went you
need to iterate through a result set and insert all the data into a
Worksheet using a *SINGLE* VBA instruction. You have an Array of rows
and each row has an array of data. This would be an alternative to
executing a VBA instruction for each iteration or row .)

Using Applescript with Excel, for example, with the example I posted,
you can do it.

In Applescript you have a list, and each element of the list is
another list with row data.

AppleScript lists translate into Arrays in VBA - but not always.

The topic came up because I explained how in optimizing Excel it's
faster to insert data a row at a time instead of a cell at a time.

Jon, suggested doing it even faster, going for an nxm matrix at a
time.

I asked him to show me.

He posed an example where data was copied from one WorkSheet to
another matrix at a time.

Which is fine. But as an example it's useless for improving the
preformance of SQL processing.

His example assumes data already exists in a WorkSheet and isn't
applicable to cases where one iterates through a result set and then
inserts the data into VBA with a single instruction.

Essentially it was a.Range("A14").value = b.Range("A14").value
where a and b are different sheets.

It may be possible. It might be possible to create a string, delimite
cells with tabs and rows with semi colons.

Something like this ..

Constant CELL_DELIMITER = chr(?) ' I don't know the value of ?
Constant ROW_DELIMITER = chr(?) ' I don't know the value of ?

'Then
Dim a as String
a = "1" + CELL_DELIMITER + "20" + ROW_DELIMITER
a = a + "30" + CELL_DELIMITER + "11" + ROW_DELIMITER
Range("A1:B2").value = a

' puts 1 in A1, 20 in A2, 30 in A2, and 11 in B2 not a in each of the
four cells

There would be a trade off with building a gigantic string and in
inserting the data with a single instruction.

It may turn out that nxm wouldn't be faster. But we can't even get nxm
going.

 

[Ken]

These theoretical discussions are all very well but my problem won't
go away. Smartening up the code only gives a marginal improvement.

As an example, before closing I restore the display settings with:

With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
End With

These instructions take 0.09 seconds in Excel 2003 and 3.78 seconds in
Excel 2007. Some 40 times longer!

I could fiddle around clearing the charts before restoring the
settings but I suspect that I will not gain much.

 

[Charles Williams]

In windows excel VBA you would generally use ADO/DAO recordsets and
..copyfromrecordset to transfer the results of a database/ODBC SQL query to a
worksheet or a pivot table cache using one statement. This is considered
reasonably efficient. You can also transfer a recordset directly to an array
if you want, but most of the time you don't need to. Recordsets have a
reasonably flexible, rich and efficient set of methods and properties (which
is not to say there is no room for improvement ...)
You can consider a recordset as an updatable and lockable
multi-user/exclusive encapsulated array of columns each of which consists of
an encapsulated array of rows similar to a RDB query result.
ADO/DAO can also access non relational data such as closed/open Excel
worksheets/workbooks, but is less efficient than the technique Jon showed
for direct read/write access to Range data in open workbooks using Variants
containing arrays.

The Array function in VBA is oriented towards constants rather than data
from some other datasource and is (IMHO) somewhat primitive by comparison.

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Charles Williams]

Ken,

I do not think there is an easy solution to speeding up Excel 2007 Charts
(other than reverting to a previous version).

Charles

 

[gimme_this_gimme_that]

In windows excel VBA you would generally use ADO/DAO recordsets and
.copyfromrecordset to transfer the results of a database/ODBC SQL query to a
worksheet or a pivot table cache using one statement. This is considered
reasonably efficient. You can also transfer a recordset directly to an array
if you want, but most of the time you don't need to.

I agree. Jon is the guy who says it's faster inserting data all at
once instead of a row at a time.

Yes, you could also transfer the data to a class.

Recordsets have a reasonably flexible, rich and efficient set of methods and properties (which
is not to say there is no room for improvement ...)
You can consider a recordset as an updatable and lockable
multi-user/exclusive encapsulated array of columns each of which consists of
an encapsulated array of rows similar to a RDB query result.

I figured most MVPs know what a record set is. What's your point?

ADO/DAO can also access non relational data such as closed/open Excel
worksheets/workbooks, but is less efficient than the technique Jon showed
for direct read/write access to Range data in open workbooks using Variants
containing arrays.

Agreed, but nothing in Jon's post shows how one would take the concept
of using a variant containing arrays.

That is what I asked him to post. And what I asked you to post. I've
tried variants having arrays and this:

The Array function in VBA is oriented towards constants rather than data
from some other datasource and is (IMHO) somewhat primitive by comparison.

I didn't say anything about Array Functions. I said Arrays. OK. We're
on board with variants and Arrays.

But both you guys are writing preteniously (with you lecturing about
what a result set is) - but you're both short on an example.

John only demonstrated reading and copying. Not writing.

A writing example would look like:

Dim v as Variant
set V = new Variant(Array(1,4),Array(3,9))
Range("A1:B2").value = v

But this syntax doesn't work.