• 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 improve code writing time

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi experts,

Need your help on below.

Could you please advise, share your experience about improving code writing time.

I am trying to improve my speed, however not sure, what exactly I should follow (each time) to save my time and improve efficiency.

Can anyone please help me in this. Have a nice day ahead. :)
 
Hi ,

Code writing is not a matter of productivity unless you are being paid , by the hour , for it !

Code writing is a matter of effectiveness and efficiency , writing the appropriate code correctly the first time , even if it takes a longer time to do it ; writing code , finding out errors , debugging them and revising your code , even if you do it very well and very fast , is not necessarily the right way to write code.

To improve effectiveness , the best way is to master the individual writing blocks and to have a comprehensive arsenal of utility routines ; when having to write a sort routine , very few coders try to find a new sorting algorithm , especially if the overall application does not really depend on the efficiency of the sort routine that you will use.

Try to build up the utilities to do with the basic Excel objects such as workbooks , worksheets , ranges , rows , columns , tables e.g. adding , deleting , copying and pasting , renaming where applicable ,...

There are enough such routines on the Internet ; Chip Pearson's site is a valuable resource. Ron de Bruin's site is a valuable resource for anything to do with Excel's integration with email.

Narayan
 
This is such a hard question to answer!

But I personally don't think it is code writing time that is an issue as your typing speed will be good ;). Most of the time it is unclear scope which adds to reworking of code which is expensive.

Several examples can be seen (also) here on the forum. Generally, if a thread has 15~20 or more exchanges then you will see that OP has shifted or added new requirements and not supplied correct details.

If this is taken care of then as a coder you need to take care of pitfalls e.g. user input is involved so you may have to add validation there.

As for syntax learning there's no other way of knowing it than practice and reading associated books. I will recommend you read VBA Developer's Handbook written by Ken Getz and Mike Gilbert.
 
Hi Sachin ,

If you can go through the following links , and adopt wisely all that is mentioned there , you should go far in your quest.

https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

http://www.spreadsheet1.com/vba-development-best-practices.html

http://www.codematic.net/excel-development/excel-vba/excel-vba-best-practice.htm

http://www.techrepublic.com/blog/so...-follow-best-practices-when-writing-vba-code/

http://www.kubiszyn.co.uk/best-practices.html

http://www.exceluser.com/formulas/vbastds.htm

http://dailydoseofexcel.com/archives/2013/07/02/vba-best-practices-that-ill-never-do/

http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html

http://www.automateexcel.com/vba/best-practices

A general thread in all will be :

Write code that is easy to read and understand ; we are not in the era of machine language programming where everything had to be compressed within 64 KB ; nor are we writing code that has to deal with a trillion records of data and spit out the result in a few seconds ; improving the time of output from 2 seconds to 0.33 seconds means nothing when almost everyone of us wastes time by the hour every day !

Narayan
 
Last edited by a moderator:
The others have covered the main things, so I'll hit the little things. I love having the drop down menu, which appears when dealing with well defined variables. Then you only have to type a few letters and you can hit either period or tab, and VBE will auto fill the rest.

upload_2016-6-15_13-43-40.png

hit the period, and it becomes:
upload_2016-6-15_13-44-3.png

Again, just a minor tip, but using it helps me type things out faster (and fewer spelling mistakes).
 
Hi @NARAYANK991 sir, glancing through all the web links you have provided.
These will be pretty helpful for me. Thanks.

I agree 100% with you
we are not in the era of machine language programming where everything had to be compressed within 64 KB
Click to expand...

Couple of weeks ago, an experienced developer told me the same.
According to him, this was okay when we had space constraint. Now we have machines with huge storage capacity. So need not bother much about this aspect.

The very reason for seeking help regarding this was, around me, we always have fantasy discussions about how a particular code runs within XXXX seconds etc.
improving the time of output from 2 seconds to 0.33
Click to expand...

At present, I have multiple snippets of codes stored in my personal workbook.
These includes from as simple as creating a range, searching in string, to extracting Pivot table, or sending e-mails through excel. These codes help me save time. I just Copy and Paste them as required.

However, reading through all the links you have provided will certainly help me further. Good night. :)
 
Two things I have started doing now are:

1) Use of Option explicit, earlier I used to think, all my variables are declared.
Since using this option, I realized, I was missing on many. So this is very helpful.
This helped me to assign each variables wisely.

2) Usually I write codes in different modules, I used to write screen update true, false in every module. But after suggestion by Narayan sir, using true false in first module only. Skipping other modules. Code running time improved.
 
My Top Tips:

1) Public Variables

Define a variable once & you never need to define it again

2) Application.Calculation = xlCalculationManual

Use it to set the workbooks calculation mode to manual. Since your formulae stop processing, macro runs faster. Reset to xlCalculationAutomatic once done.

3) Application.WorksheetFunction

Basically its a formula calculated within VBA at runtime, output can be stored & used in a Variable

4) Application.ScreenUpdating = False

Stop the screen from refreshing. Once done, set it to True

5) Application.CutCopMode = False

Clear your clipboard after you've copy/pasted something

6) Define Variables

Ensure all your variables are defined correctly
 
You must log in or register to reply here.
Back
Top