Creating and examining macro in Excel VBA

We’re going to create a very simple macro that will help introduce the language to you. Follow these steps:

  1. Create a new blank workbook in Excel.
  2. Turn on the Macro Recorder (Tools, Macros, Record New Macro). Make sure that you record the macro in ThisWorkbook. Name the macro ValueInCell.
  3. Using the mouse select cell F5. In this cell type the word “Hello” and press Enter.
  4. Stop the macro recorder.

Now take a look at the VBA code behind this macro. Open the Visual Basic Editor, make sure the project explorer is visible (Ctrl-R will show it), locate your project, expand the Modules folder and double-click Module1.

From the first tutorial you should recognise the structure of what you can see. The first 4 lines of the procedure are comments (which you will recognise as starting with an apostrophe and being green). The first line of actual code is this:

Range(“F5”).Select

This instruction tells VBA to select the cell F5. The next line is slightly more cryptic.

ActiveCell.FormulaR1C1 = “Hello”

ActiveCell refers to exactly that – the active cell i.e. the one that is currently selected. FormulaR1C1 can be used to assign a formula or a value to a cell. For example, this code would also work:

ActiveCell.FormulaR1C1 = “=SUM(R1C10:R10C10)”

If you’re not familiar with the R1C1 style of referencing cells then this may seem confusing (suffice to say that it will put the formula SUM($J$1:$J$10) into the active cell). You can also specify a constant e.g. “Hello” as we have done in our macro.

The final line of is similar to the first:

Range(“F6”).Select

Can you guess what it does? That’s right, it selects cell F6!

The usual syntax of VBA is to write the name of the object that we’re working with e.g. Range(“A1”) followed by a full-stop/period and then the name of the attribute we want to change or the command we want to carry out. If we’re changing the value of an attribute then we use an = sign followed by the new value e.g.

ActiveCell.FormulaR1C1 = “Hello”

If we want to carry out a command then we specify the object we’re working with followed by the name of the command that we want to carry out e.g.

Range(“F5”).Select

OK, you can close the workbook now. Don’t bother saving it, unless you really want to.