Funcognito — Explore and learn anything, you got this!

6.3 VBA when you need it

Checked against Microsoft's Excel for Windows keyboard references, August 2026

తెలుగులో చదవండి

What you'll be able to do

Open an inherited macro workbook without fear. Find the code, read it, fix the small thing that broke, and write a modest macro of your own. Every organisation has .xlsm files older than its employees, and somebody has to maintain them.

Before you start

Open the automation monthly workbook in desktop Excel. VBA runs on a desktop only.

The work

  1. Same either way: open the Visual Basic Editor with AltF11. The Project Explorer on the left lists every open workbook, and the code sits in modules beneath them. This window is where all inherited macros hide, so knowing the keystroke is half the search.
  2. Same either way: record a starting point, exactly as with Office Scripts. AltF8 opens the Macro dialog, and the recorder is on the View tab's Macros menu, or on the Developer tab once you have enabled it. Record the same tidy-up routine, stop, then press AltF11 and read what it wrote: Range("A1"), Selection.Font.Bold = True. Those are your actions written down. Use the recorder to learn the words: record a thing to see its syntax, then write it properly yourself.
  3. Same either way: here is the core vocabulary, which is enough to read most inherited code. Sub name() … End Sub marks the start and end of a macro. Dim total As Double announces a variable. Range("B2:B100") and Cells(row, col) give addresses. For i = 2 To lastRow … Next repeats. If … Then … End If decides. With those five shapes and the recorder, the average inherited module can be read in an evening.
  4. Same either way: two habits mark competent VBA, so use them and recognise them. Find the true last row with Cells(Rows.Count, 1).End(xlUp).Row. A recorded row count is fixed in place, which is the same fault the Office Scripts recorder has. And avoid .Select: the recorder writes select-then-act, while written code acts on ranges directly, which is shorter, faster and does not make the screen flicker.
  5. Same either way: run macros in three ways. Press AltF8 and Run. Or make a button: insert a shape, open its context menu with ShiftF10, and choose Assign Macro. Or use an event, which is code in a sheet's module that runs when the workbook opens or when a cell changes. Events are how inherited workbooks do things by themselves, so when a workbook acts on its own, look in ThisWorkbook and in the sheet modules.
  6. Same either way: the security facts are not optional knowledge. Macros must be saved in an .xlsm file, because saving as .xlsx deletes the code silently, which is how departments lose a decade of work. Excel opens downloaded macro files with the macros blocked and a warning bar, because macros are a real route for malware. Many organisations remove .xlsm from mail entirely. Enable macros only in files whose origin you trust, and never talk a colleague through overriding a block on a file you have not seen.

Try it

Record the tidy-up as a VBA macro and read it in the editor. Replace its fixed last row with the End(xlUp) habit, and delete the Selects. Wire it to a button and save as .xlsm. Then save it once as .xlsx and reopen it. See the warning about losing the code now, so that it never surprises you on a file that matters.

Check yourself

In modules, and in the sheet and ThisWorkbook modules, inside the VBA editor. AltF11 opens it, and the Project Explorer lists the contents of every open workbook.

The code is removed, with only one warning. It is the classic way a department loses a ten-year-old macro, and the answer is backups and the right file type.

The last genuinely used row of column A. It starts at the bottom of the sheet and does the equivalent of pressing End and the up arrow. It replaces fixed row counts, which break the first month the data grows.

VBA can do anything the person at the keyboard can do, which makes it a way to deliver malware. An .xlsm file arriving by mail is a standard attack. Enable macros only in files whose origin you trust, and never override a block on an unknown file.

In an event procedure: the Open event in ThisWorkbook, or one in a sheet's module. Events are how a workbook acts without anyone pressing a button.

Go deeper

We haven't checked most of these for screen reader use yet.

Back to VBA when you need it: work through the checklist