Course contents · Macros and VBA

Your first macro: Range and Cells

Open the practice in the trainer →

A macro is a Sub procedure: from Sub Name() to End Sub. Inside are Excel commands written through objects: workbook → sheet → range → its properties.

Referring to cells

Range("B2"), Range("A1:D1")by address — as in formulas
Cells(2, 3)by numbers: row 2, column 3 (C2). Handy in loops where the number is a variable
ActiveCell, Selectionthe active cell and the selection
Range("A1").Offset(1, 2)shift: 1 row down and 2 columns right (C2)

Properties and methods

.Valuethe cell value; Range("B2").Value = 100
.Formulaa formula: Range("D2").Formula = "=B2*C2". Written into a range, the references shift as when copying
.Font.Bold, .Interior.Colorbold font and fill colour: RGB(198, 239, 206)
.ClearContents, .Selectmethods are actions: clear, select

A With … End With block saves repetition: inside it, lines starting with a dot refer to the same object.

Sub Header()
    With Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(198, 239, 206)
    End With
End Sub

Using the editor below. Write code, pick a macro in the list and click Run (F5). The result appears on the sheet below the editor, messages in the Output box. One module can hold several macros. Reset sheet restores the original data.

Tasks in the VBA editor

  1. Finish the Headers macro: A1:D1 get "Item", "Price", "Qty", "Amount". Run it.
  2. Make the headers A1:D1 bold and fill them with RGB(198, 239, 206).
  3. Write the formula =B2*C2 into D2 using the Formula property.
  4. Fill the whole range D3:D6 with a formula in one line — Excel shifts the references itself.
  5. Using Cells (row and column numbers), write "Checked" into F1.

Solve the tasks in the trainer →

Check yourself

  1. Which cell is Cells(3, 2)?
  2. What does Range("D2:D6").Formula = "=B2*C2" do?