Microsoft Excel VBA macro developing basic guide

Microsoft Excel is a powerful application when it comes to processing and analyzing data. With the help of hundreds of inbuilt formulas and functions you can do very powerful tasks easily by Excel. If you have some VBA knowledge (Visual Basic for Applications), you can avoid doing repetitive tasks and save your precious time. Lets say you have to process a report generated on daisy basis and the tasks you have to do with that report are same, why not you automate it. Those simple yet very powerful programs are called macros. In this blog I will start from the very beginning and will take you to the very advanced levels of macros.

I think the best point to start is macro recorder. You can achieve automated tasks for certain extent using the macro recorder.

Let's see how it is done.

Open Excel 2007 and open a new workbook. Enter the numerical values to the cells as in the figure.

Lets say you want get the sum of A1 and B1 cells to C1 cell and do this till A11 and B11 cells.

Go to View tab and the little triangle in the macro Icon. And then select Record macro option. It will open a new dialog box. You must enter a name for the new macro here. Additionally you can assign shortcut key also.

Store macro in: option is important. A macro can be stored in the workbook or a personal macro workbook. If you stored the macro in a workbook you can't access the macro for other workbooks. Best place to store your macros is personal macro workbook. So, choose personal macro workbook. And press Ok. Now, excel will start recording whatever you do withing workbook.

Now, I select the C1 and enter the following formula,

=A1+B1 

and press enter. Result of the formula will return in C1 (100)

Then for filling the other cells, drag the C1 cell down by clicking its bottom right corner until you reach C11.  now press the stop recording in the macro options (in the view tab). Now the macro is recorded. You can see the code generated by the recorder, by going to Visual basic editor. You can do this by two ways.

1. Pressing Alt+F11
2. Going to Developer tab and click the Visual Basic button.
(If you can't see the Developer tab right after the View tab, go to Excel options and tick the Show Developer tab in the ribbon option.

How Visual Basic editor looks like,

To see the recorded macro, you have to expand the Modules under VBAProject(PERSONAL.XLSB), and double click Module1. Once you double click it, you can see the code in the code window.

Sub Macro3()

'

' Macro3 Macro

'

'

    Range("C1").Select

    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"

    Range("C1").Select

    Selection.AutoFill Destination:=Range("C1:C11"), Type:=xlFillDefault

    Range("C1:C11").Select

End Sub

Note:
Look at ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]" line.
Even though we've entered the formulas in C1 as =A1+B1 whne the macro is recorded, here the formula shows as =RC[-2]+RC[-1].... This was happened due to relative references.

Here RC[-2] refers to A1. That is because A1 is two cells from C1 to the left. So as, RC[5] will be H1 cell. (5th cell to the right from C1). I know this will annoy newbies. But, don't worry, macro recorder will record any complex formulas/functions for you in relative references.

Or you can change,  ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]" line as,
 ActiveCell.Formula = "A1+B1" to get the same outcome. All you have to do is remove the R1C1 part and use the real formulas (absolute referencing)


Let's say you have to get the sum of the cells in A and B columns in C column, and your worksheets has thousands of rows in those. And what if you have to do this for hundreds of sheets everyday. You don't have to do these manually. All you have to do is adjust the cell range in the recorded macro, and run it for every sheet.

Ex: Selection.AutoFill Destination:=Range("C1:C20000"), Type:=xlFillDefault

How to run a macro?

Go to View tab and click macros button. It will open a sub window.

You can select the desired macro from this window and press Run button. That's it. What I show is a very simple example. Excel VBA is way more powerful than that. I will go step by step.