VBA - A little more

We now know a little bit about VBA history and what it is, thanks to an earlier post. Catching up from there, we write series of instructions in VBE to tell Excel to perform some task for us. This code is called a Macro.

Let's see more details on what you can do using VBA and if there are any disadvantages.

Why to Use Macros

You use macros because you can:

Automate Almost Everything You Do In Excel Front End

Need to apply a particular formatting to a cell? Just write macro and use it. You can write code to perform any task you do in the front end Excel.

• Perform those time consuming activities way much faster
• Automate repetitive commands
• Perform tasks with a single command or short cut or click

Automate Things Which Are Not Possible Through Excel Front End

Accept it, we all are lazy or forgetful at times. I normally protect sheets with confidential data. But when I have to change anything in those sheets, I un-protect it but often forget to re-protect it when I am done (sounds familiar?). Result, your secret data is no more secret.

Do you also have confidential data in a sheet and want to automatically protect a worksheet whenever you close an Excel file, use 'events' in Excel to do the task for you.

Record Code & Replay

Even if you don't know a bit about VBA code and stuff, you can still record your actions and repeat as many times as required in future. Yes, Excel comes with an built-in macro recorder. Recording a macro and analysing is the best way one can start stepping into the VBA world.

Develop Custom Functions

If Excel's built-in functions are not enough for you or if they are not helping in a particular situation you are in, you can create functions that works the way you want (of course following VBA structure).

For example, there is no function in Excel which gives you a sum of cell values based on their background colour or font colour. You can create a User Defined Function (UDF going forward) which just will do the this task.

Create Add-Ins

If you want the macros to be run every time you open Excel, just save the macro file as an add-in. You can even share those files with anyone.

I want my UDFs to be available in every Excel file I open, so I just created an add-in and placed all the UDFs and macros in it. Now when I open any Excel file, I get to use all these macros and UDFs.

Work With Other Office Applications

You can play with other Office applications while working with in Excel. For example: when you are working with Excel, you can put up the charts and graphs in a MS PowerPoint. You can write code in Excel to send automatic birthday wishes through Outlook.

Feel Good

You are writing code to automate things. Feel like a programmer & developer. You will notice that awesome feeling once you mastered VBA. All that praise by your colleagues around would make you addicted to more learning.

Get Help

If you are stuck or in doubt with any code related stuff, Microsoft website and countless on-line help forums are at your rescue. There is a possibility that you will instantly find what you are looking for. No? No problem. Just post a query in any on-line forum and you will get the solution at the earliest.

Simplify Front End

You can simplify appearance of Excel front end user interface by using various Forms, controls and dialogue boxes available in VBA.

It Is Within Your Application

VBA is built in Excel and the code you write is also saved with in Excel file. So you need not spend money in buying a separate application to automate your tasks. Also no worries to share the files with other Excel users.

Are There Any Disadvantages?

There are many advantages and uses of VBA as we saw above. But are there any problems using this? Let's see.

Security

Like any other application, security is a big problem with VBA. Since VBA can manipulate other applications behaviour while residing in host application, it can be used to spread virus or steal personal data or for other fraudulent purposes. Though end users can disable macros and protect themselves from the risk of running viruses, few times this may still be not so useful.

And to run the macro in other users systems, you must also share the macro code along with the file. VBA offers protection features to lock users from viewing the code but an expert can easily thwart the protection.

You Have To Learn The language

You will have learn the technical jargon of VBA. You also will have to understand the coding structure, syntax, arguments and so on. Unless you are so much interested in coding and decoding like me, this may be a bit overwhelming to cope up.

Not Standalone

This is advantage of VBA and also the disadvantage that it is not stand alone program. It requires host application (Excel for example) to reside and work. Consequently, macros written in Excel will work only in Excel and you cannot use it in anything other than Excel, like Word or PowerPoint.

Other Users May Not Understand

If you share the macro with other's, they might not be able to understand how to work with macros and what it does. You will have to provide a detailed instruction sheet or build a robust help within your macro.

Compatibility

Microsoft comes up with new versions of Excel almost once in every 2 years and might probably update VBA structure too. So macros you write in recent version may not work well in earlier versions. You will have to keep the compatibility in mind while writing a piece of code.

Infinite Looping

If you are careless in testing the code or just run a code provided by a friend for fun sake, you might end up in doing infinite loops of calculations which will crash both the macro and Excel, more often than not, your system too.

We just saw what are the benefits offered by VBA and probable disadvantages too. But the drawbacks are over-weighed by benefits. After analysing, we all decided to learn VBA. So let's go ahead and see a quick introduction on VBE and related windows appearance in next post. Till then, Adiós Amigos, hasta la vista!!