Efficiency and Elegance

[jdahls]

Is there anyplace on the board where I can find some basic rules for writing VBA for Excel macros? I have made the leap from just using the macro recorder to actually typing, but the more I write the harder they are to interpret. Any tips to make them efficient and easier to maintain would be appreciated. Thanks.

 

[Richie(UK)]

Hi jdahls,

Welcome to the board.

Congratulations on stepping-up to 'proper' macros rather than just using the recorder. (remember though, if you get stuck the recorder can sometimes be very useful!).

Anyway, I don't think the board has a 'rules of VBA code' section so I'll start you off with what I'd recommend and see what the other guys add.

1. Use lots of comments in your routines. You may know what it means now but if you need to re-use a routine at a later date it may not be so clear.

2. Adopt a 'modular' approach. That is, when you have a large task to complete, try to break it down into smaller self-contained tasks. This makes it easier to check for errors.

3. Practice. If something doesn't work, try to figure out why. If you can't figure out why then post a question here on the board.

4. Look at other people's code and learn from the way that they do things. This board is a great source of knowledge and I have learned a great deal here (and am still learning!).

 

[jdahls]

Thank you Richie. For breaking up a task into smaller tasks, is it more efficient to write several subs and "call" them from within one module? Or am I better off just breaking it up visually with blank lines and comments?

 

[Al Chara]

Take a look at purchasing a book on Object Oriented Programming (OOP).

Or do a search on the internet for it.

This type of programming will make things a lot easier once you get the hang of it.

Try and stay away from On-the-Fly Programming (OTFP). Its the easiest but worst programming style. Its where you just start writing code without thinking and planning the code first.
_________________
Kind regards,<font size="5">^{<span style="text-decoration: overline">AL</span>}<u>_CHARA</u></font
This message was edited by Al Chara on 2002-09-19 13:06

 

[Legacy 98055]

Just a another bit of encouragement. The macro recorder never looses it's value, however, you can accomplish sooooo much more writing your own code. Best advice is the same as Al Chara's. It will be well worth your time to get the big picture by familiarizing yourself with the structure of object oriented/based programming. For a quick start on what you are doing now, if anything, post your code and an explanation of what your trying to do. Ask for some specific advice on how you might re-write the code for improved readability, speed, and reusability in other projects.

Tom