Consulting

Results 1 to 3 of 3

Thread: Analyzing and REviewing VBA Code

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location

    Question 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.

  2. #2
    VBAX Regular zilpher's Avatar
    Joined
    Nov 2004
    Location
    Swindon, UK
    Posts
    30
    Location
    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.

    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

  3. #3
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •