Excel VBA (Visual BASIC for Applications)

Excel VBA also known as Macros.

Now a day’s office programs are playing very vital part in our work and personal life.

We do use almost all office programs in our daily routine like Work, Excel, PowerPoint, and Outlook.

So by using these programs we prepare lots of documents, presentations, huge excel files with so complicated formulas to achieve what we want.

However, still sometimes we are unable to achieve few things even by using complex Excel Formulas and to achieve those we spend lots of our time.

Those are actually pretty much quick and easy to achieve by using macros.

What is macro?

A macro is a set of commands that can be run in the back to perform a given task with a single click. These tasks may be something as simple as calculating 2 numbers into a cell or more complex.

So you can automate almost everything in office program, even you can perform such task which you might not even know they are possible.

So when we speak about the macros, people thing that it’s a programming and I am not from programming background or a developer to write fine macros.

Well pal you are not 100% right, but yes you are partially right MACROS are programming, but you do not need to be from programming background or a developer to use them.

Macros can be created in office programs and they are written in a language called Microsoft Visual Basic for Applications, usually know as VBA.

When and why we should use macros?

Macros save time and extend capabilities of the programs you use every day. You can use them to automate document production tasks, simplify bulky tasks. When you will be well-versed with VBA can use macros to create your own add-ins that can include templates, dialog boxes, and even store information for frequent use.

Let’s take an example: For formatting multiple tables in a document. Let’s say that there are 50 tables in your document that need to be reformatted.

Even as a proficient user will take more than four hours for that one task but you can take just five minutes to format each table, that is. If you record a macro to format the tables and then edit that macro to repeat the changes throughout the document, you can complete that task in minutes rather than hours.

How to created macros?

In many Office programs, you can create a macro either by recording a series of actions or by writing the macro by yourself.

Record Macro

This is the simplest way to create macro, just turn tape recorder on (or off) while you are performing your actions.

When you use macro recorder, a macro is automatically written for you based on the actions you take.

In Office, you can record macros in Word, Excel, PowerPoint, Visio, and Project. I know it is exiting for you are you are ready to kick start.

For example, let’s take a look at recording a macro for the first task as I mentioned at the beginning of article - formatting several tables in a Word document. You can apply a table style to accomplish a lot of table formatting, but you might also need to apply formatting that cannot be part of a style, such as the width of the tables or the height of table rows.

Let’s begin J

To record this macro, start with your insertion point in the first table you want to format, and then do the following:

• Office 2003 - On the Tools menu; click Macro and then click Record New Macro.
• Office 2007 onwards – ON Developer tab click Record New Macro.
• In the Record Macro dialog box, as shown here, you can name the macro, assign it to a toolbar or keyboard shortcut for easy access, customize where the macro is saved, and add a description of the macro for later reference. Or, if you prefer, you can skip all of those steps for now and just click OK to begin recording.

1. You can rename the automatically assigned numeric name for your macro. Macro names can include letters and numbers, but no spaces.
2. By default, new macros you record are saved in the global template named Normal.dot. You can also save the macro in the active document or template, or in another custom template.
3. You can assign a macro to be accessible from a toolbar or with a keyboard shortcut. If you skip this part, you can make these assignments anytime after creating the macro.
4. The macro description includes the date and name of the user who is recording the macro. You can edit this description as needed.

After you click OK, in office 2003 a small toolbar will open containing two buttons, Stop Recording  and Pause Recording.

In office 2007 onwards we will get both these button in Developer tab

• Take each of the steps you need to format your table. For example, you might apply a table style, set the width of the table to be 50% of the available page width, select the table, and then remove row height settings from all table rows. When you have finished applying any formatting that will be the same for all of the tables that you need to format, click the Stop Recording button.
• You can now click into any table in your document and then run this macro to automatically repeat all of the actions you took in the first table.

Note we have not assigned the macro to a toolbar or keyboard shortcut, you can access it through the Macros dialog box.

To do this

In office 2003 on the Tools menu, click Macro and then click Macros. Select your macro from the Macro name list and then click Run.

In office 2007 onwards, on Developer tab click Macros. Select your macro from the Macro name list and then click Run.

That’s it you have created your first macro and its quick a simple and fun too.

So let’s now start creating macro by other way, writing our own macro.

Tip A good way to begin learning VBA is to record a macro and then look at the macro in the Visual Basic Editor.

To do this, in office 2003 on the Tools menu click Macro, and then click Macros. Select your recorded macro from the Macro name list and then click Edit.

In 2007 onwards do to Developer tab and click on Visual Basic button.

Well before we start writing macro just security information as macros can be harmful to you system in case in case they are written by someone else and you do not know the use of it.

What about macro security?

It is a fact that, while most macros are both harmless and helpful, macros are an important security issue. When created with malicious intentions, macros can contain destructive code that causes harm to your documents or your system.

To protect your system and your files, do not enable macros from unknown sources. In order to have the option to enable or disable macros, but still have access to any macros you want to use, set macro security in your Office programs to Medium. This will provide you with the option to enable or disable macros anytime you open a file that contains a macro, but will allow you to run any macros you choose.

To set macro security in any Office program that offers VBA macros follow below steps

In Office 2003

• On the Tools menu click Macro
• Click Security
• Select your preferred Security Level and then click OK.

Note that setting security to Low is not recommended.

In Office 2007 onwards

• On the Developer tab click Macro Security
• Select your preferred Security Level and then click OK.

So to begin with simple macro, we can open excel and write below code in macro editor (Visual Basic Editor).

To open macro

In office 2003 – Click on Tools, then select Visual Basic.

In office 2007 onwards – go to Developer tab and click Visual Basic.

This example will show Hello World text in cell A1.

Sub Macro1()     'Showing “hello world” text in A1 cell     Range("A1").Value = "Hello World :)" End Sub

Another very simple addition of 2 numbers calculation using macros

Sub Macro1()     'Adding 2 numbers to cell C1     Range("C1").Value = Range("A1").Value + Range("B1").Value End Sub

So similarly you can write your own macros and run them, you can also assign them to buttons and then you are just a click away from a magic.

The simplest way is to add buttons from the Forms Toolbar - display this from the View Menu > Toolbars & click Forms.

1. Click on the Button icon on the menu & the cursor will hang to +, click on the sheet to add a button
2. The Assign Macro Dialog will show listing available macros, in the "Macros in:" box select This Workbook for ease.
3. Now simply click on a macro to assign it.

I will show you more complex macros and more tips in my next article.

Till then enjoy MACROS.