Skip to main content

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.
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
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.


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!!!


Popular posts from this blog

TreeView Structure in Excel VBA

TreeView is extreamly useful in specific cases but it can be bit tricky sometimes to implement.  Last Few days I was working on a TreeView Structure and thought I will share the knowledge I gained...  This post takes you through the basic operations to create and operate a TreeView. It will be like the Folder tree window of the windows explorer.  Moreover, you can have it dynamically updated based on the data in excel.... Here we go..

Controlling LED through Octolapse for 3D print timelapse

Today's post is about one of the 3D Printing adaptation that I learned. This is specifically about how to control an LED light automatically through OctoLapse so that it is switched on only when the timelapse photo is taken. Just as a background, I have been learning to use 3D printer and its customizations options for a few months now and was a lot of learning since then. The best thing was OctoPi, a platform for controlling the 3D printer and the many plugin options that are developed by the community.  Most of them are like open source. Today we will discuss specifically about an adaptation to one such plugin, OctoLapse. OctoLapse is the plugin for taking timelapse photos, with many options for further customisations. One of the best timelapse method is taking photo after each layer is complete, by moving the head out of the way. You can use standard Pi Camera or a DSLR, which is great to get good resolution videos. One small flash back before we dive into the topic.  I keep the

PTC Creo | my Mapkeys for free

I have created a list of frequently used mapkey shortcuts for the PTC Pro Engineer Creo. This is the macro equivalent in creo.  I am copying them below.If you need them, copy paste the content to the "" file in your startup folder. My favourites are highlited and greatly improves the workflow.. For ex, to reach MEASURE. need to go to another menu and click.  Instead, maypkey from any selected menu on the ribbon will work.. Thats wonderful to me... Also, Edit Sketch (ES) is overloaded and will work for Extrusion, Revelution, Sweep etc.. So is aa, pp, zz..  really helps me a lot.. Hope you will start using them as well and get benefited! Let me know in comments, your feedback and issues.... Sketch View           > sv Show and Erase        > se Working Directory     > wd Hiddel Line View      > hi Close (quit) Window   > qw Measure               > mm Erase Session         > ee Insert Mode @ Sel     > is Cancel Insert Mode    > ic edit Sketch