Easy - Getting Started With Excel VBA(Free Tutorial)

This is a brief introduction to VBA which stands for Visual Basic for Applications. It is a standard programming language for Microsoft applications such as Word and PowerPoint and you can use VBA to automate tasks within Excel or within other applications. You can also use VBA if you want these applications to talk to each other. So you can extract a report from excel and put it in PowerPoint or in word or you can do a lot of automation within Excel. You can also use VBA to do a lot of stuff that you cannot do in Excel by itself. So you can create many new functions, you can use a lot of new functionalities which Excel does not provide in its basic form.

What can you do with VBA?

• You can automate a series of tasks if for example you get a report from a database or you get many reports from colleagues and you have to apply a certain formatting each and every time you get that report then you can automate this task. You can write a code in the backend and with a single click or with a single shortcut all these tasks can be done within a twinkling of an eye because you have automated it using VBA.
• You can also create custom functions that do not exist in Excel. Say for example you want your VLOOKUP( ) function to extract all the values that are occurring for a particular block of values and put it in a single cell then you can do that but you cannot do that with the VLOOKUP function that provided within excel. You can create custom functions.
• Create add-ins that can do a lot of work within Excel.
  
• Create applications. Now these applications that are built using VBA may not even look like a spreadsheet but its so powerful that you can create these applications and do a lot of stuff.

What are the building blocks of VBA?

VBA is a very simple language. All you need to know is how its building blocks come together to make this language and you also need to know the syntax. Here are a couple of tings that make up VBA.

• Sub/Procedure - This is the place where you put your code. It means you write your code within the sub.
• Functions - You can use VBA functions or create custom functions.
• Events- This is where you can ask VBA to execute a code when an event happens. For example when you double click on a cell, you want something to happen so that's an event based VBA procedure.
• Objects - This means everything in Excel is an object. Whether its a chart or worksheet or a workbook.
• Variables

These will be used continuously to build code and get the work done.

Object Oriented Programming

VBA is one of the object oriented programming languages. If you have not heard this terminology before here's a simple example that will help you understand the concept behind object oriented programming. Sometimes written as OOP. 

Assuming I would like to apply a green background colour to cell B2 in the worksheet(Sheet1), in workbook(example.xlsx) in the application which is Microsoft Excel. 

I would start from the outside going in and that would be with the application(Excel) and tell VBA that I would like to do something within Excel. 

Within Excel I would have to go to the Workbook(example.xlsx). 

Within the workbook, I have a worksheet whose name is Sheet1. A workbook can have many worksheets but I am only interested in Sheet1. 

Within the worksheet(Sheet1) I have to locate cell B2 within which I change the colour. 

This is a simple illustration of how Object Oriented Programming works.

Microsoft Excel -> Workbook(example.xlsx)-> Worksheet(Sheet1)-> Cell B2-> Change background colour.

This means you need to know the cell address in order to make any changes to it such as changing the colour, copying, deleting etc.and that requires knowing how to qualify that object. To understand OOP better, you can read more about it on Wikipedia.

I would love to hear from you, so leave any questions or comments below and I will get back to you as soon as possible.

Cheers!