How to Start with VB Programming (VBA) in MS Excel

Excel is like a vast ocean.... 

The possibilities of using VB (VBA to be precise) is almost endless 

But it’s not too complicated (to do simple stuff) if you put bit of an effort and with a small programming background.  Let me try to help you to get started....

Step 1. Security Settings

Ensure that the security setting is appropriate.
Go to TOOLS -> MACRO -> SECURITY
Select either MEDIUM or LOW. If you select MEDIUM, make sure that you select the option ENABLE MACROS when you open the excel with VBA.




Step 2. Set Excel ToolBars

In Excel window, it’s better to have the following ToolBars selected. (You can do that at VIEW -> TOOLBARS)

Visual Basic
Control ToolBox




Step 3. VB Editor

Press and you will see the VB Editor.
In that window, make sure that the following are visible. (In View Menu.)

Project Explorer
Properties Window




Step 4. Create a macro.

Enter 10 in Cell A1
Enter 20 in Cell A2
Enter 30 in Cell A10

Click the Cell B1.
Click RECORD MACRO (in Visual Basic Toolbar).
Click OK in the pop up window. Now it will start recoding your actions.
Enter the formula, =A1*10 to Cell B1
Click STOP RECORDING.
Recording is finished.

How to use the macro.

Click on cell B2. Click the RUN MACRO (in Visual Basic Toolbar). Select the macro you have recorded and click RUN. Click on cell B10 and run the same macro.

You can see that the same relation getting pasted in all selected cells.

Caution: This is an example where the macro was applicable for any selected cell. Not all macros are that direct. For most of the macros, you need to manually edit if you want that to use in different places.

To show that effect, you try this. Start recording a macro. Select a cell, apply a Fill Color. Stop recording. Try using the macro for other cells. It fails. That is because the selection is also getting recorded. Now we will see how to modify the macros.




Step 5. Edit the macro.

Click the RUM MACRO. Click EDIT.

Here you would be seeing the subs (functions) for each macro. In the sub for the first macro you would see the following command.

ActiveCell.FormulaR1C1 = "=RC[-1]*10"

Note: Ignore lines starting with "'". Those are comments and are not evaluated.

In the second Sub, you would see commands starting with following line or similar.

Range("B1").Select

After that you would see few lines starting with "WITH" and ending with "END WITH". We need that. That is what is setting the color. The number represents the color you have selected.

Copy the lines from "WITH" to "END WITH".
Put it in the first macro so that the sub will look like this.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded ---------- by -------
'

'
ActiveCell.FormulaR1C1 = "=RC[-1]*10"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End Sub

Now close the VB editor.

In Excel delete the column B.
Click on Cell B1 and run the first Macro.
You should find that the Formula is placed and Cell is colored.





Step 6: Link the macro to a Button.

From the Control ToolBox, click and place a Button on the sheet. Once it is placed, you are in Design mode. Double click the button and place the following line in that Sub.
Call Macro1 'or with the correct name.

Close the VB Editor.
On the Visual Basic ToolBar, click EXIT DESIGN MODE.

Now verify it by clicking a cell and clicking the button.







Now you have a rough idea how it works. Play with The macro like changing color index. Say put a number in the cell D1. Modify the Sub so that the colorindex is taken from that cell like the following.
.ColorIndex = Range("D1")

Record more macros to understand how to code is doing things..

Have fun!!!