Excel VBA Excel VBA 5 — Questions and Answers
Question 1: Which VBA statement pauses execution for a specified number of milliseconds?
- Application.Wait Now + TimeValue("00:00:01") (Correct answer)
- Sleep 1000
- Pause(1000)
- Wait(1000)
Correct answer: Application.Wait Now + TimeValue("00:00:01")
Application.Wait accepts a future time value; to pause for 1 second you pass Now plus a 1-second TimeValue.
Question 2: What is the purpose of the 'Option Explicit' statement at the top of a VBA module?
- Forces all variables to be declared before use (Correct answer)
- Speeds up code execution
- Enables early binding for objects
- Prevents the module from being exported
Correct answer: Forces all variables to be declared before use
Option Explicit requires every variable to be declared with Dim, preventing typos from creating unintended new variables.
Question 3: Which method deletes all content AND formatting from a range in VBA?
- Range.Clear (Correct answer)
- Range.Delete
- Range.ClearContents
- Range.Reset
Correct answer: Range.Clear
Range.Clear removes cell values, formulas, formatting, comments, and hyperlinks — a complete reset.
Question 4: In VBA, which operator is used for string concatenation?
- & (Correct answer)
- +
- ||
- ##
Correct answer: &
The & operator concatenates strings in VBA; while + can also work, & is preferred because it avoids type ambiguity.
Question 5: What does the 'ByVal' keyword mean in a VBA procedure parameter?
- A copy of the argument is passed; changes don't affect the original (Correct answer)
- The original variable is passed and can be modified
- The parameter is optional
- The argument must be a literal value
Correct answer: A copy of the argument is passed; changes don't affect the original
ByVal passes a copy of the variable, so any changes made inside the procedure do not affect the caller's variable.
Question 6: Which VBA method is used to find the last used row in a column efficiently?
- Cells(Rows.Count, 1).End(xlUp).Row (Correct answer)
- Cells.LastRow
- Range("A1").End(xlDown).Row
- UsedRange.LastRow
Correct answer: Cells(Rows.Count, 1).End(xlUp).Row
Starting from the bottom of the column and pressing End+Up finds the last non-empty cell, giving the true last used row.
Question 7: What VBA object model component would you use to open a file dialog so users can select a file?
- Application.GetOpenFilename (Correct answer)
- FileDialog.Open
- Shell.BrowseFile
- MsgBox.FileSelect
Correct answer: Application.GetOpenFilename
Application.GetOpenFilename displays a file open dialog and returns the selected file path without actually opening the file.
Excel VBA Excel VBA 5
Which VBA statement pauses execution for a specified number of milliseconds?