Microsoft Excel Advanced Formulas and Macros 2

Question 1

What is the purpose of IFERROR in Excel?

Accepted Answer

Returns a specified value if a formula evaluates to an error

Answer

Checks if a formula contains IF

Answer

Logs errors to a worksheet

Answer

Prevents incorrect formula entry

Question 2

Which VBA statement redirects execution when an error occurs?

Accepted Answer

On Error GoTo

Answer

Try...Catch

Answer

Error.Handle

Answer

Catch Exception

Question 3

What does TEXTJOIN do that CONCATENATE cannot?

Accepted Answer

Joins text with a delimiter and can ignore empty cells

Answer

Joins text with formatting

Answer

Converts numbers to text first

Answer

Joins across workbooks

Question 4

In VBA, what is the difference between 'ByRef' and 'ByVal'?

Accepted Answer

ByRef passes the reference allowing modification, ByVal passes a copy

Answer

ByRef passes a copy, ByVal passes a reference

Answer

No difference in VBA

Answer

ByRef for objects only, ByVal for primitives

Question 5

What is SUMPRODUCT commonly used for beyond multiplication?

Accepted Answer

Conditional counting and summing with multiple criteria without Ctrl+Shift+Enter

Answer

Only multiplying array elements

Answer

Calculating matrix determinants

Answer

Finding the product of a range

Question 6

How do you create a named range that automatically expands?

Accepted Answer

Use OFFSET and COUNTA in the name definition

Answer

Use Define Name with a fixed range

Answer

Set the range to 'Dynamic' in Name Manager

Answer

Named ranges cannot expand