10 Ways to Make your VBA Code Better

Web & Code No Comments

1. Better way to write formulas to cells

If using the same formula over the entire row or column (sometimes even uniformly repetitive formulas), its better to update the whole column or row at the same time than each cell.

Range(E4:P4).Formula = "=A$1 "
Is faster than
For k = ActiveCell.Column To 12
ActiveCell.Formula = "=A" & j & ""
ActiveCell.Offset(0, 1).Select
Next k
2. How to effectively copy-paste cell value
Range("E135:AH174") = Range("E135:AH174").Value
Is faster than
Selection.PasteSpecial Paste:=xlPasteValues
3. Turning off screen refreshing
At the beginning and end of the subroutine to achive a faster executing code.
4. Not using VBA helps [At times]
There are some complex ways to do conditional formatting through VBA. However, I feel one can easily implement the same using he conditional format button (Format>Conditional Format) in excel main menu.

Mr. Knowledgeable to Mr. Handy Man – Defining Core Competencies

Other 2 Comments

In my recent work place, I met an engineering intern, Mr. X who has been with the company for 2 years now. He is the oldest among all interns and some of his current supervisors were interns who joined later than him but moved up the corporate ladder joining the company as full time employees.

Well, it was not always like this. Mr. X has an exemplary education with a diverse experience. When he was hired, HR considered him one of their biggest catch and he didn’t prove them wrong. His initial few months were very productive. He brought about pioneering changes and his hard work showed in his results. His personality was charming and he never said ‘no’ to a challenge.

That’s where it started going all wrong. People in the company, started realizing that Mr. X was good in most things. He could solve tricky problems, in fields ranging from manufacturing and quality to IT. And to top it all, he never really said NO. Therefore, his job input box, was always full with the odd job that no one wanted to solve or couldn’t solve. He worked harder and longer and the circle deepened, resulting in a dynamic change from Mr. Knowledgeable to Mr. Handy Man.

Why this change? Poor definition of Core Competency. Core-competency is nothing but an answer to the question “what are you good at?” With today’s globalised economy, traditional boundaries are fluid and education multi-disciplinary. This result is most graduates are failing to properly define their core-competencies. Mr. X did the same mistake. He lost out to competition because he was good in too many.

Read the rest of this entry »

© 2009, Nikhil Hullur | Entries RSS Comments RSS