Disappointing Excel 2004 VBA performance and stability
Edwin Tam
for many years, for both PC and Mac. I was also a beta tester of Excel 2001,
Excel X and Excel 2004 for Mac. And I was given the MS MVP (Mac) in 2002.
After working with the Mac Excel 2004 for some time, I couldn't help posting
a message here expressing my dissatifaction about the bad Stability and
Performance of VBA in Excel 2004. They're simply disappointing, and below the
expected standard.
Honestly, Excel X is much much much more stable than Excel 2004 in terms of
VBA.
Here are some examples to show why I think Excel 2004 VBA stability is
unacceptable. They may not be always reproducible. I raise that here, so that
in case your Excel 2004 quits unexpectedly on loading of an UserForm, check
these.
1) Dropdown control with lots of listitems
When I say "lots of items", I mean, for example, 150 or more. (I don't have
the exact figure which can guarantee a software shutdown.) If a userform
contains some other controls and code, and a dropdown list, and the list
contains ralatively more items, on LOAD/SHOW of that form, there is
relatively high probablity that Excel 2004 will quit unexpectedly.
2) TransitionEffect property of MultiPage
MultiPage has a property called “TransitionEffect”. In Windows Excel, you
can happily use that property. This property is there on the Mac, but DO NOT
USE IT! There is VERY HIGH probability that it’ll crash Excel 2004.
3) Multi-Column Listbox
Many Excel developers used to assign arrays to listboxes. This is a fast and
convenient way to popular listboxes. But in Excel 2004, this can sometimes
cause Excel to quit unexpectedly. Therefore, a safer approach will be to use
a loop to pupulate the listbox items one-by-one.
4) More Multipage annoyance…
A Multipage with a few pages, each has some controls and some code attached
(maybe a pretty complex userform), it could simply be impossible to load that
form, as Excel 2004 will quit unexpectedly. A way to solve this is to, before
SHOW of that form, set some of the pages’ visible property to False. This
problem is strange, as the form can LOAD, but will crash Excel when SHOW the
form.
5) The END statement
Normally, the END statement will conveniently terminate all running macros.
But the following macro (sometimes) won’t dismiss the UserForm and has no
effect at all on the macros. This may not happen always. But if you have a
large project with many files and userforms, there is very high chance that
you’ll experience this in some occassions.
Private Sub CommandButton1_Click()
End
End Sub
A way to resolve it is to Hide the UserForm first:
Private Sub CommandButton1_Click()
Me.Hide
End
End Sub
Other Annoyance:
6) Font display on UserForms
Very often, fonts are displayed strangely on UserForms. For example, for the
same font, some characters may look normal, some look bold. A way to resolve
it is the use Repaint before form is shown on sreen:
Me.Repaint
7) The font size and control dimensions torture
The favorable font for UserForm design is Tahoma 11 pt. Also, Height of
Commandbutton should best be 27. For unknown reasons, the MS Development Team
want to torture users by leaving them to “hunt for their favorite font and
font size” and “try out their most suitable control dimensions”. The Windows
team is more sensible in this aspect.
8) Super Ugly MultiPage
Any human being won’t believe they’re looking at the Mac screen when they
see the Multipage control in Excel 2004. It’s simply unbelivable and is an
insult to the asethetic standard of Mac users.
9) The Mouse-click delay in UserForm design
When designing the UserForm, when you click anywhere on anything a the Form,
after each mouse-click, you need to wait for a bit more than a second, before
you can make the second mouse-click. Otherwise, the software will think
you’re double-clicking on a control and will open up the macro screen (for
macro editing). This is SUPER annoying, and make development in Excel 2004
very inefficient.
In fact, there are more annoying thing in Excel 2004 VBA, and there are many
more ways to crash the software. I saw no improvement in the most recently
Office 2004 update. I am very disappointed.
Maybe, the number one most annoying thing about Excel 2004 is, when it
crashed, it’ll ask you whether you want to submit a report to MS. To me, it
just look like flash animation. There is no effect at all!!
But despite my dissatifaction, I love the Mac system, and I appreciate
Microsoft’s effort in developing MS Office for Mac. Very soon, I’ll release a
freeware which is a powerful automation toolbox for Excel 2004 and Excel X.
It’ll be called “Mac Excel Expander”. The Windows version was released a few
months ago and has been very popular. But the Window version is a shareware
not a freeware. I make the Mac version a freeware, as I wish more people can
start to experience the potential of the Mac as a number crunching machine.
To read more about my software, please feel free to visit my web site.
Regards,
Edwin Tam
edwi...@vonixx.com
http://www.vonixx.com