Table of Contents
THE EXCEL CONTRAPTION
A beginner’s journey into the world of Excel is always filled with many “OMG it can do that too ?!? Amazing!”.
For what all it can do, it never brags; its basic grid interface is quite deceiving.
Excel for that matter is quite unlike its siblings in the MS Office family.
Whilst other tools will let you be, alone in the cold, excel will always manage to come forward with a function that could simplify any job that you have at hand.
So you could quite easily move from calculating average to calculating the mass of the sun with its features.
Excel’s Powerhouse: VBA
The source behind all this amazing power is VBA i.e. Visual Basic for Applications.
It lets you program anything you would want Excel to do for you.
Now VBA is actually universally used in Office Suite but the most benefits are enjoyed while being used in Excel and Access.
It’s always been there
What makes VBA or Excel so special is the fact that it takes absolutely no downloading/buying huge platforms, compilers, code editors etc to get started and going.
Nah no hassle at all.
All that you are going to need is right there in front of you and has always been.
The always been part is the most fascinating. We all do seriously underestimate Excel.
As an introduction to VBA, it can be easily stated (and that’s clearly no understatement) that it is quite easy to grasp and understand.
Another advantage you would always have here would be recording the actions and automatically having it translated to VBA which is probably the biggest training wheels one will need.
You are only ALT+ F11 away from your biggest most awe-inspiring discovery on the computer.
You may not be a programmer by nature or profession but it’s so damn easy to get started and remain hooked on this.
Okay, give yourself a hi5 every time you read the word Excel.
In fact, the Microsoft word application is using right now is not liking all this prejudice.
The Myths and Differences
VBA vs. VB ?
Some of us are bound to get confused between VBA and VB. Are they the same?
Well, the answer to that question is neither black nor white because both of them evolved from Basic and hence have the same background and functioning.
In fact, VBA is a subset of VB in simple terms.
What VBA lost in subsetting out was the fact that it needs the Microsoft Office suite for support in terms of the Office Object Library which it inherits.
VB, on the other hand, is capable of letting us create stand-alone applications we would be able to run from let’s say the Start Menu.
So the syntax for both the languages is quite similar but one is dependent and the other is super powerful to create applications and executables.
Is VBA OOPs ?
Another common myth with VBA is that its OOPs.
If you are not new to programming and coding you would know how OOPs is absolutely cutting edge because of its many advantages and features.
There are 4 prerequisites (or the best of the features ) for a language to be classified as OOPs.
There is polymorphism, abstraction, encapsulation, and inheritance.
In short, if a language lets you create objects where you can hide your functioning, reduce the complexity allowing all types of inputs; it even lets new objects take the properties of the old ones.
Well, folks, VBA doesn’t let you derive or reuse from the old. In fact, even VB is unable to do that.
So although VBA does all the other great things there is still a lot of scope for improvement out there.
The Common Beginner Mistakes
Coming back to Excel and VBA ; everyone starts somewhere and at that point, we all tend to do some common mistakes.
No Ctrl+Z allowed
When you screw up in Excel there is no going back.
It’s a mess.
A total train wreck.
Ok fine, that’s overly dramatic.
Cleaning up after mistakes gets typical with Macros and VBA in excel.
The reason being when you hit play or run and the code executes and gives you a result, there is no undoing.
The changes the code did to the data no matter correct or incorrect, you cannot Ctrl+Z yourself out of that mess.
Your input is lost to re-run it.
So it’s generally advised to keep multiple copies of the input file or the code you built till now when you are in the coding phase.
Sourcing to the correct input
The last and the biggest mistake is about inputs being taken from a place other than wished for.
For eg you want data on Sheet 1 to be manipulated but when before you started to run your code you were on sheet 2.
The code will work on sheet 2 data and do stuff it is not supposed to.
You can, of course, avoid this by clearly mentioning or selecting the worksheet specifically.
Sometimes VBA catches the issue and sends a message like below, other times let’s hope you backed up.
Backup your work
Yes, it is advisable to even keep a copy of the code you successfully built till now because Excel depending on its version has a limit of executions and the time taken.
So if you did your job poorly at coding a For Loop and it became infinite loop chances are the programs gonna crash. And it may or may not save your work.
Save as .xlsm
But be vary about saving and recording the work.
Most of the noobs (am sorry but really..??) save the excel as a .xls or .xlsx or god knows what instead of the .xlsm extension.
If you tell Excel to save as anything but .xlsm, am sorry to say mate you will lose the code in the modules.
The code you created with sheets will remain intact.
But let us get real it is unadvisable to code in worksheets.
Stuff happening on the worksheet is very local to the worksheet only.
You cannot expect to call other sheets or functions on other sheets.
Moreover, the data input that you use in the code should source out to the same excel worksheet.
So it is better to insert Modules and then call sheets functions whatever from around the whole workbook, makes your work more universal.
Personal Workbook and recording
Another major thing we all goof up about, in the beginning, is recording the code.
There is a personal workbook i.e. personal.xlsb which by default saves your recording.
So the modules in that workbook in your code editor will show you your code.
Generally, the workbook starts as soon as Excel starts, but in case you have been playing with it too much or your Excel crashed too many times it might not be able to open personal.xlsb on its own.
In that case, you can navigate to C:\Documents and Settings\Your Name\Application Data\Microsoft\Excel\XLSTART and open it manually.
It is again advisable to save your code in a personal workbook because it always opens with Excel, you don’t need to open a specific file to perform an action on data a piece of code can do.