Course contents · Macros and VBA

Introduction to macros and VBA

Open the practice in the trainer →

Code in Excel is called VBA (Visual Basic for Applications); “macro” is a synonym. A macro is written code that executes commands in Excel.

Why code

  1. To automate repetitive tasks (e.g. formatting a monthly report with one click).
  2. To do things the ribbon can’t (e.g. a custom function that spells out a number).

Setting up

Recording a macro

The Record Macro button (Developer → Code or on the status bar) works like a tape recorder: whatever you do is turned into VBA. Don’t forget to stop recording.

The Use Relative References button: when on, movement relative to the active cell is recorded (Offset); when off, absolute addresses (Range("A1")).

Keys

Alt+F11VBA editor
Alt+F8list of macros to run
F8 in the editorstep through the code

Sample macro

Sub FormatReport()
    Range("A1:D1").Font.Bold = True
    Range("A1:D1").Interior.Color = RGB(33, 115, 70)
    Columns("A:D").AutoFit
    ActiveCell.Offset(1, 0).Select   ' relative movement
End Sub

Tasks in the trainer

  1. Write a VBA line that makes the range A1:D1 bold.
  2. A line that autofits the width of columns A:D.
  3. Relative movement: select the cell one row below the active cell.
  4. Put the number 100 into cell B2.
  5. The first line of a macro named Report.

Solve the tasks in the trainer →

Check yourself

  1. Which format must a workbook with macros be saved in?
  2. Which shortcut opens the VBA editor?
  3. “Use Relative References” is on. How is moving one cell down recorded?
  4. Which security level does the manual recommend?