How different is VBA from other programming languages?

THE EXCEL CONTRAPTION

A beginners 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 which could simplify any job that you have at hand.

So you could quite easily move from calculating average to calculating the mass of 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 the 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.

Read More: How to create VBA Macros in Excel using Macro Recorder

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.

Select Developer option in the Customize Ribbon window in the Excel Options dialog box.

To show the Developer Tab in the tab list, select the check box of Developer in the Customize Ribbon window in the Excel Options dialog box.

Visual basic editor and ActiveX Controls

1) To open the Visual Basic editor, click on the Visual Basic command on the left side. 2) To insert ActiveX controls, click on the Insert drop down.

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 am using right now is not liking all this prejudice.

Read More: For Next Loop in VBA Excel (How to Step and Exit Loop)

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 or 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 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 the polymorphism, abstraction, encapsulation, and inheritance.

In short, if a language lets you creates 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.

OOP Example

OOP Example

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 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 overtly 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.

Read More: Copy your macros to a Personal Macro Workbook

Sourcing to the correct input

The last and the biggest mistake is about inputs being taken from a place another 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.

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.

Run time error

Run time error

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 an infinite loop chances are the programs gonna crash. And it may or may not save your work.

Excel-dialog-box

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 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.

Read More…

Learn Excel VBA Programming & Macros [Free Tutorials, Download PDF & Course]

How to Count Frequency of Text in Excel (Using VBA)!


Hi, my name is Priya and I am a VBA expert. I self-learned this while working to a telecom giant We used to have a lot of data in form of subscriber info services call etc. To handle the same efficiently I came across this very interesting and helpful feature about excel and other office products.

We will be happy to hear your thoughts

      Leave a reply