Excel VBA Technology & Digital Applications 5 — Questions and Answers
Question 1: Which VBA function pauses code execution for a specified number of seconds?
- Application.Wait Now + TimeValue("00:00:05") (Correct answer)
- Sleep(5000)
- Pause(5)
- Wait 5
Correct answer: Application.Wait Now + TimeValue("00:00:05")
Application.Wait accepts a time value and pauses VBA execution until that time is reached, commonly used with Now + TimeValue for a delay.
Question 2: What is the correct way to reference a named range called 'SalesData' in a VBA formula assigned to a cell?
- Range("A1").Formula = "=SUM(SalesData)" (Correct answer)
- Range("A1").Value = Application.Sum(Names("SalesData"))
- Range("A1").FormulaR1C1 = "=SUM([SalesData])"
- Range("A1").Formula = "=SUM(Range.SalesData)"
Correct answer: Range("A1").Formula = "=SUM(SalesData)"
Named ranges can be referenced directly by name inside formula strings assigned to the Formula property, just as they are in worksheet formulas.
Question 3: When declaring a dynamic array in VBA, which statement resizes it while preserving existing data?
- ReDim Preserve arr(newSize) (Correct answer)
- ReDim arr(newSize)
- Resize arr(newSize)
- Expand arr(newSize)
Correct answer: ReDim Preserve arr(newSize)
ReDim Preserve changes the array's upper bound while keeping existing element values; ReDim alone would erase all data.
Question 4: Which VBA property determines whether formula calculation in Excel is automatic or manual?
- Application.Calculation (Correct answer)
- Application.CalcMode
- Workbook.CalculationMode
- Application.AutoCalc
Correct answer: Application.Calculation
Application.Calculation can be set to xlCalculationAutomatic or xlCalculationManual to control when Excel recalculates formulas.
Question 5: In VBA, which built-in constant represents a carriage return and line feed combination when building multi-line strings?
- vbCrLf (Correct answer)
- vbNewLine
- Chr(13)
- vbReturn
Correct answer: vbCrLf
vbCrLf is the VBA constant equal to Chr(13) & Chr(10), representing Windows-style line endings used in strings and message boxes.
Question 6: Which approach correctly prevents an Excel Add-in macro from appearing in the user-facing Macro dialog box?
- Declare the Sub as Private (Correct answer)
- Prefix the Sub name with an underscore
- Store it in a Class Module
- Use the Hidden attribute in the VBA project
Correct answer: Declare the Sub as Private
Private procedures are not listed in the Macro dialog, effectively hiding internal helper routines from end users.
Question 7: What VBA method on the Range object fills a selection with a series of values based on a pattern, equivalent to Excel's Fill Series feature?
- Range.DataSeries (Correct answer)
- Range.AutoFill
- Range.FillDown
- Range.Series
Correct answer: Range.DataSeries
The DataSeries method fills a range with a linear, growth, or date series, mirroring the Home > Fill > Series dialog functionality.
Excel VBA Technology & Digital Applications 5
Which VBA function pauses code execution for a specified number of seconds?