• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to guess, assess time required for preparation of a macro

ThrottleWorks

ThrottleWorks

Excel Ninja
I am sure most of you will find this as silly doubt however I am able to decide how to finalize the time required.

For example, the user tells us 10 manual steps to prepare a table but when need to code, we might require 15 or 30 different coding steps for desired result.

At present I cannot guess how much time I will require to complete a particular report.

I am sure this will come only with experience, i.e. improvement in self coding standard and coding experience.

But are there any benchmarks or trends, rules we need to follow before giving any estimation.

Kindly help with your suggestions.
 
Hi Throttle,

Good question, and one that I frequently deal with when doing paid consulting jobs. As you said, it can vary based on your personal level of experience. When I get a task, I try to at least write out a rough draft of how I'll tackle the project. So, for coding it might be "build loop over cells, check range". I then review my notes, and try to give my best guess as to how long I think it will take me.

When I'm all done, multiply result by about 1.5 (safety margin, your own margin may vary :p). Especially when coding, there's things that come up that need more work than you anticipated. Other factors that can come into play is if macro needs to have error checking, work with other applications, etc. Also, if user requires all code to be independently developed so they have intellectual proprietary, or can you use common code from other sources?

In short, expect it to take longer than you initially anticipate.

In going forward, I'd suggest keeping a time log for yourself. Write down what you were working on, and the start/stop time. Here's an example from my own log:
upload_2015-9-22_13-16-5.png

It's just a simple, separate file where I can track dates, a little note of what I was doing, and my times. Col F is a formula that calculates hours. Note that col B is name of customer, which I've hidden for this example.

Benefit here is that as I get more tasks, I can review how long it takes me to build a dashboard/chart/etc., and I get better at giving out quotes.

Hopefully that helps, and let me know if you have any follow-up questions. :)
 
Well, there are several factors. Of course as Albert wrote (Einstein) :
« The only source of knowledge is experience. »
​
It depends also of the data design : for example with merged cells
it's a bit more tricky than without …
Thinking about all possible errors is time consuming.

I'm not aware of any tool but is there any tool
for the confidence placed by a customer ? …

As on wall in Albert's office at Princeton :
« Not everything that counts can be counted,
and not everything that can be counted counts. »
​
 
What Luke M said about merged cells. I've hosted few Lunch & Learn session at my company to educate employees on data layout to make their (and my) life easier.

As for estimating time, it comes with experience. As Luke M suggested, keep track of time spent on each project. I tend to break it out a bit more, categorizing by components within the project: Data Cleaning, Research, Proposal, Coding, Check & Balance, and finally Audit and Approval/Hook-Off.

I treat each report build as PRJ management piece.
 
Hi Sachin ,

If you are into either professional development for which you will be paid , or you are developing applications within your organization , there are the following areas you need to focus on , whether you like it or not , and whether the end user is expecting it or not.

1. Developing user specifications or a functional description ; if one is already available , you need to go through it minutely , and fill in the gaps where ever you find any. No code should be developed without this.

2. Developing a user manual ; this is not merely to describe the keystrokes that they have to use to operate the application , but also to describe the logic you have used in coding , and even more important , the assumptions , if any , that you have made. Very frequently , it is the assumptions made which will create problems for software maintenance later on , since if the wrong assumptions are made , a complete rewrite will have to be done. As a rule , assumptions should be made only if the end user cannot give any confirmation on them.

A manual like this is not merely for the end user ; in a lot of cases , it will be for the benefit of the developer who has to maintain the application , sometimes even after years.

Narayan
 
Hi @Luke M Sir, good morning. Yes, you are correct, lot of time is taken by
if macro needs to have error checking
Click to expand...

Cause at any point of time of time we do not want macro to throw 'Run Time Error'. Till date, the only error handling I have used to check if the file selected is correct, does it have valid data, are particular worksheets present in file, checking for Column headers.

Hi @NARAYANK991 Sir, thanks a lot for the help.
Could you please guide more on below if possible.
Developing user specifications or a functional description
Click to expand...
Does it mean the details steps required to prepare the macro, such as select File A, go to worksheet B, find Column C, filter with today's date.

Sorry I was not able to understand this point. Have a nice day ahead. :)
 
Hi Sachin ,

A Functional Description ( FD ) is a description of what the end user wants from the application that you are going to develop for them. For example , suppose a store keeper wants you to develop an inventory control system , the FD will give a complete description of what the inventory control system will do for the store keeper ; thus , included in this will be a description of how the end user will run the application , what kind of menus and other dialogs will be displayed , what kind of keystrokes will be required within the menus and other dialogs , what reports / printouts will be generated by the system ...

For more information , go through these links :

http://www.bridging-the-gap.com/functional-specification/

http://www.its-all-design.com/what-actually-goes-in-a-functional-specification/

http://www.toptal.com/freelance/why-design-documents-matter

Narayan
 
You must log in or register to reply here.
Back
Top