VBA and Financial Modeling to the Rescue!

Over the course of time, many of juniors have put me in a position where I find that I am pretty confused. But I don’t blame them; at one point of time I put my seniors through the same thing! It deserts you in the silence of thought because you cannot possibly come up with a reply that would satisfy the person asking you the question. And the question that leads to this situation is ‘what are the brief lessons that a VBA coder must know?’

The foremost thing that comes to the mind is that you cannot sum up the whole of VBA in merely a few lessons. There is one simple thing in VBA and that is fundamental and common at all points of time – try and try till you succeed. Moreover, VBA is dynamic and keeps changing with time. So the lessons that I present today can turn out to be of no use in a matter of some time. There is only one key to VBA. That, my friends, is hard work. Put in all the effort before turning to help for it is the self-endeavor that will see you cruising through the VBA.

But then again, this is just the first thought. And when your junior keeps pushing you for concise lessons in VBA, you have to turn up with something. And here is what all the VBA coders can keep in mind.

1. Excel at Excel : Do not even think about writing about the macros if you are not familiar with excel entirely. Through the collection of macros, many times I come across a macro that is simply duplication an Excel function and boy, the macro was bad. So do have profound knowledge of the Excel before stepping into the shoes of being a macro writer.

2. Identification : This is the key for VBA. You possibly cannot imagine going forward without understanding the problem first. So my fellow coders, instead of quickly ramming your keyboards, establish a good understanding with the problem so that you can move along.

3. Research: Before you start to develop a code, it is a mandatory thing to check if the problem can be solved without the code. This undoubtedly would be big relief. However, if not then again before starting to code, check online if there already exists a solution already. You will save plenty of time and energy if you hit the target somewhere.

4. Simple and Reusable : One shall not try to carve the entire sculpture with a single stone. Go about breaking the problems into different parts and prepare targeted solutions for each problem. It would be easier to understand and revaluate. In addition to that, avoid constants and literals as far as possible. This would increase the usability and if inevitable, just use external table or pass as parameters.

5. Variables& Comments! Be careful : One must use appropriate variable and avoid using variants. Do not mind giving some time to thinking procedure. And never forget to declare all variables. It is the time to use option explicit. Include maximum comments that you can to make your code easy to understand.

6. Follow a protocol : Change is the most amazing constant. This does not apply here. It is advisable to do the things in a same way to avoid wasting time thinking things which do not demand your time. Use a definitive structure so as to decide where to declare variables, include comments or error checking. It will also help other in understanding your lines of code comprehensively.

7. End what you start : You must end all the structures that you begin. For instance, if you write IF command. Make sure after your work is complete with this command, you do use the END IF command.

8. You are no God : No matter how much skilled you maybe at VBA, never ever skip error checking to the routines. And avoid the urge to skip the test. It is sometimes tempting to skip breaking out the routines before putting into production because we are too confident to make any mistake, but guys the embarrassment that tags along in case of a fault here is very disheartening and above it, it harms the reputation in a major way.

9. Take your time : Mind works best in a state of relax. Continuously staring at the screen and endless caffeine streaks would not produce best results. With the mind focused, leave the system and take a walk maybe. The break would not only be refreshing but small things around you can sometimes be inspirational. Working out the stress can be a boon.

10. Catalogue your work : you shall not leave the macro in the file it has been created for. Instead store it at some other place which you can easily access at any point of time later. There would be instances when you will need to reuse your previous macros and you cannot possibly build the whole macro over and over again.

11. Not everyone is pro : after you are done with the macro and finalizing the file you made it for, you would most certainly forward it to someone else. It is possible that the other person is a non-programmer. So, if time allows, build a mock-up for the first time users. It avoids over thinking on someone else’s part.

So now, I am going to make sure that if anyone approaches me demanding my knowledge on VBA, I will ensure that the person leaves enlightened! There is no doubt that I cannot sum up my VBA knowledge into one or two points but then, having summarized my knowledge and experience in these points above, I do have something incredible to share.