Book on how to write VBA code in a fast/efficient manner?

  • Thread starter Thread starter J S
  • Start date Start date
J

J S

Does anybody know of any books/websites that deal with how to write very
fast VBA code? Particularly in excel? I have just written some code in Excel
that has reached close to 3000 lines and it seems like I will be writing a
great deal more in the future, yet I know very little about how to write
efficient code! I'm not really talking about fancy sort algorithms and the
such but the more mundane basic stuff like avoiding unnecessary select
statements.

-J
 
Very few books advocate using select statements beyond perhaps a short
introduction on using the macro recorder.

The usual recommendations are books by John Walkenbach (power programming
VBA series) and John Green/Stephen Bullen (Excel 2000 VBA Programmer's
Reference or replace 2000 with 2002).

John Walkenbach's site
http://www.j-walk.com/ss/excel
(also has a links page)


Stephen Bullen's Site:
http://www.bmsltd.co.uk/Excel/Default.htm

Both have links to pages describing their books.

A free resource:

http://support.microsoft.com/support/excel/content/vba101/default.asp

Also, the Microsoft VBA Programmer's guide is on their MSDN website
http://msdn.microsoft.com

Some links at Dave McRitchies site
http://www.mvps.org/dmcritchie/excel/excel.htm

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials

VBA tutorials are after the excel tutorials.

Also Chip Pearson has page after page of sample code for specific
activities:
http://www.cpearson.com/excel.htm
go to his pages index or his topics index.

The microsoft. knowledge base:

http://support.microsoft.com go to Advanced search and select excel for
windows.
 
All the previously mentioned sites and books are excellent references.
Another resource is the following webpage on VBA code optimisation. There
are a few more like it out there and maybe some that are more comprehensive
but this was helpful for me when I was doing the same thing you are.
http://www.microsoft.com/officedev/articles/movs101.htm

By the way, I went to painstaking lengths to avoid select statements(as an
example) in my Excel's VBA code and I found it had two positive effects. The
first was that my code ran unquestionably faster. The second was that it
forced me to learn more about the Excel object model and I think that made
me a much better developer. You can take a lot of shortcuts in Excel
programming but they are rarely the most efficient ways to get the job done.

HTH

Chris
 
Back
Top