We’re now going to write a macro (not record it!) that will carry out the following task:

Put the value 10 into cell A1.
Put the value 20 into cell A2.
Put the value 30 into cell A3.
Put the formula =SUM(A1:A3) into cell A4.

Start with a blank workbook. Go into the Visual Basic Editor (Alt F11 will take you there fairly quickly) and click Insert, Module from the menu. This will insert a standard module into your workbook. If you record a macro then Excel will add this module automatically but if you’re writing your own code then you need to do this yourself.
Remember that a macro should start with the word Sub followed by the name of your macro. Type this: Sub NumbersInCell followed by the Enter key.

VBA will automatically put parentheses at the end of the procedure name and the words End Sub underneath. This is the bare skeleton of your procedure.

OK, you’re now going to type your first line of code (I hope you’re excited!)

Type Range(“A1″) followed by a full stop character. VBA automatically displays a drop-down box.
The drop-down box contains a list of all properties and methods available for the object type you’ve specified.

Properties can be identified as having this icon Property Icon next to the property name.
Methods can be identified as having this icon Method Icon next to the method name.

You can search the list by using the cursor keys or page up/page down keys. Alternatively when you’re more experienced and know which property or method you want to use, you can just begin to type it and VBA will locate it for you. From the drop-down list you need to locate the Value property. You can do this by either scrolling through the list until you find the word “value” or you can press the V key to immediately take you down to where all of the “V” properties are located. Find the “Value” property and press TAB (not Enter). Type =10 and press Enter to move to the next line.

At this stage your code should look like this:

Sub NumbersInCells()
Range(“A1″).Value = 10

End Sub

Congratulations! You have written your very first macro! At this stage it is quite possible to run the macro to see if it works so let’s do that.

You already know how to run a macro from within the Excel environment. It is also possible to run your macros without leaving the Visual Basic Editor. You may have noticed that the Visual Basic Editor (VBE) has a toolbar with various buttons and menu items.
Some of these menu items will look familiar as there are similarly named items within Excel i.e. File, Edit, View, Insert, Format, Tools, Window and Help. There are also Debug and Run menu items. Unsurprisingly, the Run menu item is the one that will allow us to run a macro.

Make sure that the cursor is resting somewhere within the NumbersInCell macro and click Run, Run Sub/Userform – this will run the NumbersInCell macro.

Go back into Excel. If the macro has worked successfully then you should see the number 10 happily sitting in cell A1.
Value in A1

WOW! If anything convinces you of the power of macros then this surely will! Seriously, this simple piece of code is the foundation of almost any VBA code – no matter how experienced you get, you will nearly always be writing data to cells in this manner.

Now, the macro is not yet complete. We also want to write the values 20 into cell A2 and 30 into cell A3. See if you can write the code to do these two things yourself. If not, then carry on reading.

The macro to place the 3 values in the first 3 cells in column A should look something like this:

Sub NumbersInCells()
Range(“A1″).Value = 10
Range(“A2″).Value = 20
Range(“A3″).Value = 30

End Sub

Let’s run this macro again to see if it works. This time use one of the alternative methods for running a procedure from within the VBE. Make sure that your cursor is resting somewhere within the NumbersInCells procedure then either press F5 (which is the shortcut for Run, Run Sub/Userform) or click the Run Button button.

If you now go back into Excel you should see the values 10, 20 and 30 in cells A1, A2 and A3 respectively. Now we’re cooking!

So far, you’ve only used the Value property of the Range object. The final stage in writing our macro is to put a formula in cell A4 which will sum the 3 values above it, and it requires us to use another of the Range object’s properties – the Formula property. This property allows you to specify a formula for whichever cell you’re referencing. The formula we need is =SUM(A1:A3) and we need it to be placed in cell A4. Start typing the words Range(“A4″) followed by a full-stop and then select the Formula propery from the drop-down list. Remember that the easiest way to choose a property when you know what you’re looking for is to type the first few characters i.e. “formu” as in this screenshot:
Formula Property

Type an = sign and then type “=SUM(A1:A3)”. Your finished code should look like this:

Sub NumbersInCells()
Range(“A1″).Value = 10
Range(“A2″).Value = 20
Range(“A3″).Value = 30
Range(“A4″).Formula = “=SUM(A1:A3)”

End Sub

Run your macro using any of the methods you’ve learnt so far. If you’re not already in Excel then switch back to the main Excel window. If all has gone according to plan then you should be looking at something like this:

Save your workbook as Tutorial 2.xls.

I found this tutorial here: http://www.danielklann.com/tutorials/2/introducing_vba_language.htm Unfortunately his site’s no longer available.

Incoming search terms:

• excel macro object list
• write macro to automatically place formulas in cells
• how to write a formula for a group of cells in excel using macro
• macro put a formula in a range of cells
• macro using ie object in excel
• macro window to type in value for formula
• object as value of formula macro
• Write a macro to find a value in a range
• write a value in cellthrough a macro excel
• write excel::writer macro perl