17.7 Macros and VBA Basics

Q55. What is a macro?

A recorded or written set of instructions (in VBA) that automates repetitive tasks such as formatting a daily report. Macro workbooks are saved as .xlsm.

Q56. What is the difference between recording a macro and writing VBA?

The recorder converts your clicks into VBA, which is quick but produces rigid code with fixed ranges and selections. Writing VBA lets you use variables, loops, conditions, dynamic last-row detection and error handling, so the macro works on data of any size.

Q57. What are absolute and relative references in macro recording?

By default the recorder stores exact cell addresses (absolute), so the macro always acts on the same cells. With Use Relative References on, actions are recorded relative to the active cell, so the macro can run anywhere.

Q58. How do you find the last used row in VBA?

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row – it starts from the bottom of column A and moves up to the last non-empty cell. For a Table use ws.ListObjects("tblOrders").ListRows.Count.

Q59. What is the difference between a Sub and a Function in VBA?

A Sub performs actions (formatting, copying) and is run as a macro. A Function returns a value and can be used in worksheet formulas as a user-defined function, for example =DeliveryFee(G2).

Q60. How do you handle errors in VBA?

On Error GoTo ErrHandler with a labelled handler that shows Err.Description and cleans up (for example turning ScreenUpdating back on). On Error Resume Next should be used only around a single line whose failure you check immediately, then reset with On Error GoTo 0.

Q61. How do you debug a macro?

Step through with F8, set breakpoints with F9, inspect variables in the Locals and Watch windows, print values with Debug.Print to the Immediate window, and use Option Explicit to catch undeclared variables.

Q62. Why might a macro not run on someone else's computer?

Macros may be disabled by Trust Center settings, the file may be blocked because it came from the internet (Mark of the Web – unblock via file Properties if trusted), it may have been saved as .xlsx, or it may reference a sheet name or path that doesn't exist there.