Analyzing and REviewing VBA Code

Printable View

12-21-2004, 02:23 PM
Cosmos75

Analyzing and REviewing VBA Code

(Not sure where I should have asked this questions. Moderators, please feel free to move this thread if I asked this in the wrong place)

How do you go about reviewing and writing code?

I have a project to automate a tedious process at work that involves controlling another program from Excel. This other programs has it?s own programming language that is very similar to VBA.

I am close to finishing this project but am having a major headache trying to review my code for lines that are redundant, not used, where I performing the same or similiar procedures repeatedly and so on.

I am certain that part of the problem is that I just jumped into writing the code with not much of a plan of attack other than trying to break the code into chucks that perform a particular task and then one ?all-in-one? that calls the separate functions.

My question is what tools do you use to review your code?

Here are some tools that I have found

Printing VBA Code
http://www.starprint2000.com/
http://www.prettycode.com/

Code Flowcharting (Big differences in prices but I haven?t looked into all the features!)
http://www.kunzlertech.com/ ($17.95)
http://www.fatesoft.com/s2f/ ($99.95)
http://www.aivosto.com/visustin.html ($249)

Code Analyzer?
http://www.aivosto.com/project/project.html

Do any of you use tools or does it just boil down to programming practices?

I am not a VB or VBA programmer. I just picked up VBA on my own with help from website such as this on and kept on learning. My division isn't even an IT or programming division, just that I've found ways to better utilize MS Office and/or to automate certain tedious things we do around the office that could be automated.
12-26-2004, 07:22 AM
zilpher

Hi Cosmos

I have never found a tool that will tell me how efficient my code is, but there is one that will inform you of redundant lines, it's called mztools (http://mztools.com), is incredibly useful and will help you tidy up all those things you declared but never used.

Quote:

I am certain that part of the problem is that I just jumped into writing the code with not much of a plan of attack other than trying to break the code into chucks that perform a particular task and then one ?all-in-one? that calls the separate functions.

If you actually have problems, then I'd agree with your sentiment here. It's really hard to resist the urge to get coding when a new project comes along, which is typically when you end up with procedural and inefficient code, but it all depends on the project, monolithic and procedural code might be the right way to go for one project but a disaster for another.

I'd recommend that once the requirements are understood, a Functional Design is carried out next, no code, just a "when this happens, do this, this and this" type of document, once that is agreed a detailed design should be done with a list of all functions, objects, properties and methods etc that will need to be created.

I follow this approach at work, but that's because I work on large multi developer applications and documentation becomes a useful tool; it has to be said though that I loathe this part of the process and only do it where necessary which is a very subjective argument at times.

Finally, having another developer to do a code review can be really useful too, it's something we do at work and can help a lot. I don't know how many lines of code you have, but you could try stepping through the code in debug and manually reviewing it yourself.

HTH
12-28-2004, 04:27 PM
Cosmos75

zilpher,

THank you for your input!

Quote:

Originally Posted by zilpher

I have never found a tool that will tell me how efficient my code is, but there is one that will inform you of redundant lines, it's called mztools (http://mztools.com), is incredibly useful and will help you tidy up all those things you declared but never used.

Yes, I found that one and the Procedure Caller feature is WONDERFUL!

Quote:

Originally Posted by zilpher

I'd recommend that once the requirements are understood, a Functional Design is carried out next, no code, just a "when this happens, do this, this and this" type of document, once that is agreed a detailed design should be done with a list of all functions, objects, properties and methods etc that will need to be created.

This particular project was more of, First X, then do Y, then do Z. So, that's kind of how I started writing the code.

Quote:

Originally Posted by zilpher

Finally, having another developer to do a code review can be really useful too, it's something we do at work and can help a lot. I don't know how many lines of code you have, but you could try stepping through the code in debug and manually reviewing it yourself.

I wish I had that luxury of another developer to review my code but it's just little ol' me with what scattered VBA knowledge I know!

I found more free tools for VB but not too many for VBA. It was the same even when it came to tools you had to pay for.
:bawl