Your first VBA macro in Excel: record, read and improve it

Every Monday the same ritual: you open the export from the management system, delete two useless columns, make the header bold, widen the columns, apply a filter and sort by date. Ten minutes of identical clicks. It is the ideal case for an Excel macro, that is, a sequence of instructions written in VBA (Visual Basic for Applications) that the program carries out for you.

Enabling the Developer tab

Macro tools live on the Developer tab, which is normally hidden. You turn it on from File > Options > Customize Ribbon by ticking Developer in the list on the right. From there you can reach the macro recorder and the Visual Basic editor, which also opens with the Alt+F11 shortcut.

Recording the macro

The recorder is the quickest way to get started: it turns the actions you perform with mouse and keyboard into code.

1. Open a sample file identical to the ones you will have to process.
2. Click Record Macro, give it a name without spaces (for example FormatExport) and choose where to store it.
3. Carry out the operations calmly and without mistakes: wrong clicks will be recorded too.
4. Click Stop Recording.

One important choice is where to store it. If the macro is needed on ever-changing files, it is best saved in the Personal Macro Workbook, a hidden file that Excel opens at every start-up and that makes the macro available in any workbook.

Reading the generated code

When you open the editor you will find something like this:

Sub FormatExport()
    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Font.Bold = True
    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit
    Range("A1").Select
    Selection.AutoFilter
End Sub

The recorder is verbose: it selects something and then acts on the selection, exactly as you would by hand. It works, but it is slow and fragile. The same code can be written by acting on the objects directly:

Sub FormatExport()
    With ActiveSheet
        .Columns("C:D").Delete
        .Rows(1).Font.Bold = True
        .Columns("A:F").AutoFit
        .Range("A1").CurrentRegion.AutoFilter
    End With
End Sub

Removing the Select statements is the first improvement to make almost every time: the macro becomes faster and no longer depends on where the cursor happens to be.

The problem of fixed references

The recorder writes absolute references. If today the data runs to row 250, the code may contain Range("A1:F250"). Next month there will be 300 rows and the macro will ignore the last fifty without any warning. To find the last filled row there is a classic formula:

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:F" & lastRow).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes

The statement starts from the bottom of column A and goes up to the first non-empty cell, just like pressing Ctrl+Up Arrow from the last row of the sheet. CurrentRegion, used earlier for the filter, solves the same problem: it identifies the block of contiguous cells around the given cell.

Saving in the right format

An .xlsx file cannot contain macros: if you try to save one, Excel warns that the code will be lost. The alternatives are:

A clean solution is to keep the macro in the personal workbook or in a dedicated .xlsm file and keep saving the data as .xlsx: whoever receives the file sees no security warnings.

Macro security

Macros can do almost anything on a computer, which is why they have historically been a vehicle for malware. Excel normally disables them in files that come from the Internet or arrive by email, showing a warning bar. The practical rule is simple: enable macros only in files you wrote yourself or that come from a known internal source, never in an unexpected attachment that asks you to "enable content" in order to be read. In companies it is useful to save macro files in a folder defined as a trusted location, rather than lowering the general security level.

Making the macro easy to use

A macro nobody remembers to run is useless. You can assign it a keyboard shortcut when recording it, add it to the Quick Access Toolbar or link it to a button drawn on the sheet. Adding a final message with MsgBox "Formatting complete" is a small detail that reassures whoever uses it.

In my work I always recommend starting from the recorder and then refining the code by hand: you learn VBA by watching how Excel translates your own actions, and within a few weeks you are writing macros from scratch.

In short

• Enable the Developer tab and use the recorder to generate your first code.
• Remove the Select statements and act directly on sheets, rows and ranges.
• Replace fixed references with a last-row calculation or with CurrentRegion.
• Save as .xlsm or in the Personal Macro Workbook, not as .xlsx.
• Enable macros only in files from a trusted source.