Advanced Formula and Macro Creation Flashcards
6 cards from real Microsoft Excel practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Advanced Formula and Macro Creation flashcards as text
What is the difference between a VBA Sub and Function procedure?
Answer: Functions return a value while Subs do not
Functions return a value and can be used in worksheet formulas, while Subs perform actions without returning a value.
How does the LET function improve complex formulas?
Answer: It assigns names to intermediate calculations to avoid repetition
LET defines named variables within a formula so sub-expressions are calculated once and reused.
In VBA, what does 'Option Explicit' enforce?
Answer: All variables must be declared before use
Option Explicit requires every variable to be declared with Dim before use, preventing bugs from typos.
What does Application.Evaluate do in VBA?
Answer: Evaluates a string as a formula and returns the result
Application.Evaluate takes a string containing a formula and evaluates it as if entered in a cell.
What does XLOOKUP's 'search_mode' parameter control?
Answer: Search direction: first-to-last, last-to-first, or binary search
Search_mode controls direction and algorithm: 1 (first-to-last), -1 (last-to-first), 2 (binary ascending), -2 (binary descending).
How can you make a VBA macro run when a workbook opens?
Answer: Place code in the Workbook_Open event of ThisWorkbook
Code in the Workbook_Open event automatically executes whenever the workbook is opened.
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- store.certiport.com — Fees, retake fee · verified Nov 2026
- certiport.pearsonvue.com — Renewal, score validity, score release, exam format · verified Nov 2026
- learn.microsoft.com — Test delivery, time limit, eligibility · verified Nov 2026