VBA Interview Questions

A Sub performs actions but does not return a value; you run it as a macro. A Function returns a value and can be used in a worksheet cell or called from code. Example: Function Add(a, b) returns a + b.

Declare with Dim name As Type, e.g. Dim total As Double. Putting Option Explicit at the top of a module forces all variables to be declared, which catches typos at compile time and prevents hard-to-find bugs.

They are nested objects: a Workbook contains Worksheets, and a worksheet contains Range objects (cells). Example: Workbooks("Sales.xlsx").Worksheets("Jan").Range("A1").Value.

Press Alt+F8 to open the Macro dialog, select the macro and click Run. You can also assign a macro to a button, shape or the Quick Access Toolbar, or trigger it with a keyboard shortcut set when recording.

A standard module holds procedures (Subs/Functions) you call directly. A class module defines a custom object with its own properties, methods and events, letting you build reusable, object-oriented components.

• For...Next — a known number of iterations.
• For Each...Next — iterate a collection (cells, sheets).
• Do While/Until...Loop — loop until a condition changes.

Use For Each for objects and For when you need an index.

Use structured handling: On Error GoTo Handler, do the work, then Exit Sub, then a Handler: label that reports/logs the error. Use On Error Resume Next sparingly and reset it with On Error GoTo 0. Always clean up (e.g. re-enable screen updating) in the handler.

Turn off overhead while running: Application.ScreenUpdating = False, Application.Calculation = xlCalculationManual, and Application.EnableEvents = False — then restore them at the end. Read/write arrays in bulk instead of cell-by-cell, and avoid Select/Activate.

ThisWorkbook always refers to the workbook that contains the running code. ActiveWorkbook refers to whichever workbook is currently in focus, which can change. Prefer ThisWorkbook for reliability.

ByVal passes a copy, so changes inside the procedure do not affect the original variable. ByRef (the default) passes a reference, so changes do affect the caller's variable. Use ByVal when you want to protect the original value.

A reliable way: lastRow = Cells(Rows.Count, 1).End(xlUp).Row. This starts at the bottom of column A and jumps up to the last non-empty cell, which works even with gaps better than UsedRange in many cases.

A Collection stores a group of items you can loop through. A Dictionary (from the Scripting runtime) stores key–value pairs with fast lookups and a handy Exists method, making it ideal for de-duplication and grouping.

Selecting cells is slow and fragile — it depends on what is active and can break if the user clicks elsewhere. Instead, work with objects directly: Worksheets("Jan").Range("A1").Value = 10 rather than selecting then setting. Code becomes faster and more robust.

Events are procedures that run automatically when something happens, such as Worksheet_Change (a cell changes) or Workbook_Open (the file opens). Example: validate input whenever a cell in column B changes. Keep event code fast and disable events inside it if it writes to cells (to avoid recursion).

Load a range into a variant array in one step (arr = Range("A1:C1000").Value), process it in memory, then write it back in one step (Range("A1:C1000").Value = arr). This is dramatically faster than touching cells individually because it avoids repeated worksheet interaction.

Early binding references a library at design time (via Tools → References), giving IntelliSense and faster code but version dependencies. Late binding uses CreateObject("...") at run time — more portable across versions but no IntelliSense and slightly slower. Choose late binding when distributing to mixed environments.

Set breakpoints (F9), step through with F8, inspect values in the Immediate (?variable) and Watch windows, and use Debug.Print to log. Option Explicit plus the compiler (Debug → Compile) catches many issues before running.

Use file I/O: Open "C:\out.csv" For Output As #1, then Print #1, "a,b,c" in a loop, and Close #1. For more control (UTF-8, appending) use the FileSystemObject from the Scripting runtime.

Use Option Explicit, meaningful names, small focused procedures, constants instead of magic numbers, structured error handling, and avoid Select/Activate. Comment the "why", not the obvious, and keep configuration (paths, sheet names) in one place.