Excel VBA Excel VBA 4 — Questions and Answers
Question 1: Which VBA collection object contains all open workbooks in the current Excel instance?
- Workbooks (Correct answer)
- OpenFiles
- ExcelFiles
- ActiveWorkbooks
Correct answer: Workbooks
The Workbooks collection is a property of the Application object and holds all currently open Workbook objects.
Question 2: What does the MsgBox function return when the user clicks the Cancel button?
- vbCancel (2) (Correct answer)
- vbNo (7)
- False
- 0
Correct answer: vbCancel (2)
MsgBox returns the VBA constant vbCancel, which has an integer value of 2, when Cancel is clicked.
Question 3: Which statement is used to define a reusable block of code that does NOT return a value in VBA?
- Sub (Correct answer)
- Function
- Procedure
- Macro
Correct answer: Sub
A Sub procedure performs actions but does not return a value, unlike a Function which returns a result.
Question 4: How do you copy a range in VBA without including the clipboard (direct value copy)?
- destRange.Value = sourceRange.Value (Correct answer)
- sourceRange.Copy destRange
- sourceRange.Paste destRange
- destRange.Assign sourceRange
Correct answer: destRange.Value = sourceRange.Value
Assigning .Value directly between ranges transfers data without using the clipboard or triggering a copy animation.
Question 5: What does the Cells property return when called as Cells(2, 3) on a worksheet?
- The cell in row 2, column 3 (C2) (Correct answer)
- The cell at column 2, row 3 (B3)
- A range of 2 rows and 3 columns
- The 2nd cell in the 3rd row
Correct answer: The cell in row 2, column 3 (C2)
Cells(row, column) uses numeric row and column indices, so Cells(2,3) refers to row 2, column C — cell C2.
Question 6: Which VBA function extracts a substring from the right side of a string?
- Right (Correct answer)
- RightStr
- SubRight
- Extract
Correct answer: Right
Right(string, length) returns the specified number of characters from the rightmost end of the string.
Question 7: What happens when you use 'ReDim Preserve' on a dynamic array in VBA?
- The array is resized while keeping existing data (Correct answer)
- The array is cleared and resized
- The array dimensions are locked permanently
- An error is thrown if new size is smaller
Correct answer: The array is resized while keeping existing data
ReDim Preserve resizes a dynamic array and retains the values already stored in it, unlike plain ReDim which resets them.
Excel VBA Excel VBA 4
Which VBA collection object contains all open workbooks in the current Excel instance?