ArumSoft Learning
한국어

Expert Excel · Interactive lesson 5 of 12

VBA automation

A12's recorder writes VBA for you, click by click. Writing it directly is what turns a recording into something maintainable — a loop instead of ten repeated lines, an error handler instead of a macro that just stops.

ExpertInteractive

Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.

Written tutorialAutomating Excel with VBA

Skill check

Try these first:

  • Write a Sub with a clear name and a single, obvious purpose.
  • Loop over a range instead of writing one line per cell.
  • Add an error handler that reports a failure instead of crashing silently.
  • Explain why Select and Activate are usually a sign a macro can be written more safely.

All familiar? Skip ahead to E6 — Office Scripts & Power Automate.

Concept teach

What you're learning

Procedure structure
A Sub does something; a Function returns a value. Give each one job and a name that says what that job is.
Variables & constants
Dim total As Double declares a variable with a type; Const MaxRows As Long = 10000 names a value that never changes — both beat an unexplained number buried mid-procedure.
Loops
For Each cell In rng walks every cell in a range; Do While repeats until a condition changes — either replaces ten copy-pasted lines with one pattern that scales.
Working with ranges safely
Reference ws.Range("A2:A10") directly instead of Select then ActiveCell — safer, faster, and it keeps working even if the user clicked somewhere else first.
Error handling
On Error GoTo ErrHandler catches a failure and reports it clearly, instead of the macro just stopping mid-task with no explanation.
Events
Code that runs automatically on a trigger — Workbook_Open, Worksheet_Change — instead of needing someone to remember to click a button.
User prompts
MsgBox reports something to the user; InputBox asks them for something — both keep a macro from failing silently or guessing.
Logging actions
Writing a timestamped line to a Log sheet every time a macro runs — so six months from now, someone can see exactly when it last worked and what it did.

Where to find it in Excel

Developer›Visual Basic (Alt+F11)

Swipe sideways to see the whole window.

  1. 1Developer tab (File › Options › Customize Ribbon to turn it on).
  2. 2Visual Basic (Alt+F11) opens the editor; Insert › Module adds a code module.
  3. 3Option Explicit, a loop over a Range without Select, error handling and a log call.
  4. 4Macros (Alt+F8) runs a procedure; event code goes in the sheet or ThisWorkbook module.

Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.

Guided build

Fill in the four blanks to complete a macro that refreshes data, timestamps it, exports a PDF, and reports failure clearly.

🧩 Complete the Macro
Sub RefreshAndExport()
    On Error GoTo ErrHandler

    ①______________________          ' refresh every query/table

    ②______________________          ' stamp when this ran

    ③______________________          ' export the report as PDF

    Exit Sub
ErrHandler:
    ④______________________          ' tell the user what failed
End Sub
Fill in all four lines, then check. Any equivalent VBA that uses the right object or keyword counts.

Independent extension

Write a macro that could actually ship

Write a macro that refreshes a real workbook's data, stamps a timestamp, and exports a PDF — with an error handler that reports exactly what failed instead of leaving Excel stuck mid-task. Add one logged line to a Log sheet every time it runs.

Spot a mistake or have an idea for this lesson? Send feedback