How to Estimate Freelance VBA Projects

Hourglass

You already know how much to charge. That’s only half the story. You still need to figure out how many hours it will take.

First, write down all the tasks that are required. Be as specific or general as you like, but specific will get you a better estimate. For example, I might split a login userform into ‘Create login userform and controls’ and ‘Code login userform controls’ rather than have just one task for that form.

For each task, estimate the amount it would take you if everything goes perfectly and you have all the information you need to complete the task. Just walk through it in your head avoiding details and problems.

Finally, put the total estimated hours into this formula: BidHours = Estimate * (Est_factor + Info_factor + Test_factor + Integration_factor)

Est_factor is always 1. If you estimated it would take 10 hours, it will take at least 10 hours.

Info_factor is always 1. Nothing takes as little time as you estimate and you never have all the information you need.

Test_factor is always 1. For every two hours of coding, you’ll write tests for one hour.

Integration_factor is 0 if you’re coding from scratch and 1 if you’re tacking the code on to an existing project.

I made this fancy formula to make it sound cooler, but it’s just your estimate times 3 (or times 4 if you have to integrate).

7 thoughts on “How to Estimate Freelance VBA Projects

  1. I usually take a different approach. I detail out the steps just like you suggest. Then for each step I guess a low and high # of hours needed.
    I then total all lows and highs and put both of them in the estimate.
    I also inform the customer that the more I know about the project, the closer the low and high will be together.

  2. Interesting approach Dick. I discovered (the hard way) that for integration projects, documentation, original design, and code commentary are huge factors that determine the exact factor to apply.
    I came into a complex project, performed code refactoring and then an assessment: told the client it was unsalvagable. They didn’t want to hear it ! So I spent the next 3 weeks trying to change the existing code and of course, not making any progress. So, in the end, we did end up doing what I thought was needed given the circumstances: respecifying, reanalyzing, redesigning, and recoding of the entire “mess”.
    This doubled the time-line. Keep in mind, they had no comments in the code, no technical documentation, no requirements definition, and no design documents of any kind.
    The original project was managed by a large systems integration company that does work for the federal government.

  3. No technical documentation, no requirements definition, and no design documents of any kind.
    Sounds like just about every large company I’ve worked for :)

  4. “multiply by 3” is what a colleague once told me. I noted it, but didn’t believe him.
    It’s creepy how many IT projects turn out to be 3x the initial estimated duration.

  5. re: “No technical documentation, no requirements definition, and no design documents of any kind.
    Sounds like just about every large company I’ve worked for”
    Why is it that so many “MARGINAL” managers get placed into an IT technical management role ?
    My guess: the complexity of the technology can OBFUSCATE their incompetence.
    That makes sense, right ?

  6. It is fundamental to request a good and well documented explanation of the job to the client.
    If it is missing, it it is necessary to charge an extra for doing it.
    Finally estimate real hours and then multiply by 2. This method always gave me good results!

  7. Sorry, don’t like this!
    There are at least 2 better ways. I think:
    1. work out/guess how much you could possibly charge, add x% to that then bid it, the offer the x% as a reduction… this is a sales method.

    2. to actually work out the time you need, you must record how long things have taken you in the past and use this as a guide going forward, its vital that real times are used, to avoid multiplying by 1,2,3, which is not a great method on balance. There is a great pod cast from stack over flow on this floating about somewhere, worth a listen…


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.