Introduction

     VBA is the acronym for Visual Basic for Applications. It is an integration of the
Microsoft's event-driven programming language Visual Basic with Microsoft Office
applications such as Microsoft Excel, Microsoft Word, Microsoft PowerPoint and many
more. By running Visual Basic IDE (Integrated Development Environment) within the Microsoft Office applications, we can build customized solutions and programs to enhance the capabilities of Microsoft Office Applications.

     Among the Visual Basic for applications, Microsoft Excel VBA is the most popular.
There are many reasons why we should learn VBA for Microsoft Excel, among them
is you can learn the fundamentals of Visual Basic programming within the MS Excel
environment, without having to purchase a copy of Microsoft Visual Basic software.
Another reason is by learning Excel VBA; you can build custom made functions to
complement the built-in formulas and functions of Microsoft Excel. Although MS
Excel has many built-in formulas and functions, they are not enough for certain
complex calculations and applications. For example, it is very difficult to calculate
monthly payment for a loan taken using Excel's built-in formulas, but it is relatively
easier to write VBA code for such calculation.

     You can write Excel VBA code in every version of Microsoft Office, including MS Office 2003, MS Office XP , MS Office 2007 and MS Office 2010. By using VBA, you can build some very powerful tools in MS Excel, including financial and scientific applications that can perform financial calculations and programs that can perform statistical analyses. 

     But before we get started, let's begin by making sure that the tools we need are visible.

If you are using Excel version 2007 (or a higher version), click on File > Options > Customize the Ribbon and then check "Developer".

A new tab will be added:

If the version of Excel that you are using is lower than 2007, add the "Control Toolbox" and "Formulas" toolbars.

To work with VBA code, we'll need an editor, which is installed by default. You can open it by pressing the shortcut key combination "Alt F11":

How write your first Macro

The macro recorder makes it very easy to automate certain tasks.

To give just one example, we will automate the following actions:

• Delete the contents of columns A1 and C5
• Copy the contents of range E1:G5 to range A1:C5
• Delete the contents of range E1:G5

To do this, click on "Record Macro" and then "OK", carry out the actions described above without interruption (because everything you do will be recorded) and then click on "Stop Recording".

For versions of Excel lower than 2007: Tools > Macros > Record New Macro.

Excel has recorded your actions and translated them into VBA code.

To view your macro, open the editor (Alt F11) and click on "Module1":

This code represents the recorded actions perform by us.

Let's take a moment to look at the code that Excel has generated:

Sub Macro1()

'

' Macro1 Macro

'

'

    Range("A1:C5").Select

    Selection.ClearContents

    Range("E1:G5").Select

    Selection.Copy

    Range("A1").Select

    ActiveSheet.Paste

    Range("E1:G5").Select

    Application.CutCopyMode = False

    Selection.ClearContents

End Sub

Sub and End Sub mark the beginning and end of the macro, and "Macro1" is the name of this macro:

Sub Macro1()

End Sub

Let's edit the name of the macro to make it more descriptive, changing "Macro1" to "Data_Preparation" (the name of the macro cannot contain any spaces) :

Sub Data_Preparation()

The text in green (text preceded by an apostrophe) is commentary, and will be ignored when the code is executed:

'

' Macro1 Macro

'

'

This kind of commentary can be very useful for finding things when there is a lot of code, or when you want to prevent the execution of certain lines of code without deleting them.

Sub Data_Preparation()

'

' Below block of code is written to delete data from range A1:C5,

' Data from E1:G5 copied to range A1:C5 then is deletes data from range E1:G5

'

    Range("A1:C5").Select

    Selection.ClearContents

    Range("E1:G5").Select

    Selection.Copy

    Range("A1").Select

    ActiveSheet.Paste

    Range("E1:G5").Select

    Application.CutCopyMode = False

    Selection.ClearContents

End Sub

Now we want execute this macro at the click of a button.

Click on Developer > Insert > Button (Form controls) :

For versions of Excel lower than 2007: "Button" from the "Formulas" toolbar.

Insert your button and then just select the macro that you created :

When you click on the button, your macro will be executed :