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.