Microsoft Excel Advanced Formula and Macro Creation 2 — Questions and Answers
Question 1: What does the VBA keyword 'Dim' stand for?
- Dimension (Correct answer)
- Diminish
- Direct Input Method
- Data In Memory
Correct answer: Dimension
Dim stands for 'Dimension' and is used to declare variables in VBA.
Dim (Dimension) declares variables with optional data types. If no type is specified, the variable defaults to Variant.
Question 2: Which Excel function returns a reference to a range specified by a text string?
- OFFSET
- INDIRECT (Correct answer)
- INDEX
- ADDRESS
Correct answer: INDIRECT
INDIRECT converts a text string into an actual cell reference for dynamic reference building.
INDIRECT(ref_text) takes a text string like 'A1' or 'Sheet2!B5' and returns the value at that reference. It is volatile and recalculates constantly.
Question 3: In VBA, what is the purpose of 'With...End With'?
- Creating a loop
- Executing statements on a single object without re-qualifying it (Correct answer)
- Defining a class module
- Handling runtime errors
Correct answer: Executing statements on a single object without re-qualifying it
With...End With performs multiple operations on the same object without repeating the object reference.
Inside the block, properties and methods are accessed with just a dot prefix, improving both readability and execution speed.
Question 4: What does the FORMULATEXT function do?
- Converts a formula result to text
- Returns the formula in a cell as a text string (Correct answer)
- Creates a formula from text
- Evaluates text as a formula
Correct answer: Returns the formula in a cell as a text string
FORMULATEXT returns the formula in a referenced cell as text, useful for documentation and auditing.
FORMULATEXT(reference) returns the formula as text. It returns #N/A if the cell doesn't contain a formula.
Question 5: How do you create a spilling array formula in Excel 365?
- Press Ctrl+Shift+Enter
- Simply press Enter — dynamic arrays spill automatically (Correct answer)
- Wrap in ARRAYFORMULA()
- Select the output range first
Correct answer: Simply press Enter — dynamic arrays spill automatically
In Excel 365, dynamic array formulas automatically spill results into adjacent cells when you press Enter.
Dynamic arrays spill automatically. Functions like SORT, UNIQUE, FILTER, SEQUENCE are designed for this. Reference spill ranges with the # operator (e.g., A1#).
Question 6: What is the purpose of the LAMBDA function in Excel?
- Creating anonymous functions in VBA
- Defining custom reusable functions without VBA (Correct answer)
- Generating random numbers
- Creating lambda expressions for Power Query
Correct answer: Defining custom reusable functions without VBA
LAMBDA lets you create custom reusable functions using Excel formulas alone, without VBA.
LAMBDA(parameter1, parameter2, ..., calculation) can be assigned to a name via Name Manager. LAMBDA functions can even be recursive using LET.
Microsoft Excel Advanced Formula and Macro Creation 2
What does the VBA keyword 'Dim' stand for?