Excel VBA Excel VBA Data Manipulation & Arrays 1 — Questions and Answers
Question 1: How do you declare a dynamic array in VBA?
- Dim arr(10) As Integer
- Dim arr() As Integer (Correct answer)
- Dim arr As Array
- Dim arr[10] As Integer
Correct answer: Dim arr() As Integer
A dynamic array is declared with empty parentheses (Dim arr() As Integer) and resized later with ReDim.
Question 2: Which VBA statement resizes a dynamic array while preserving existing data?
- ReDim arr(20)
- Resize arr(20)
- ReDim Preserve arr(20) (Correct answer)
- Expand arr(20)
Correct answer: ReDim Preserve arr(20)
ReDim Preserve resizes the array to the new size while keeping all previously stored values intact.
Question 3: What does the LBound() function return for an array?
- The total number of elements
- The last valid index
- The first valid index (Correct answer)
- The data type of elements
Correct answer: The first valid index
LBound() returns the lowest subscript (first valid index) of the specified array dimension.
Question 4: In VBA, how do you declare a two-dimensional array with 3 rows and 4 columns?
- Dim arr(3, 4) As Integer
- Dim arr(2, 3) As Integer (Correct answer)
- Dim arr[3][4] As Integer
- Dim arr(3)(4) As Integer
Correct answer: Dim arr(2, 3) As Integer
Since VBA arrays are zero-based by default, Dim arr(2, 3) creates indices 0–2 and 0–3, giving 3 rows and 4 columns.
Question 5: Which built-in VBA function splits a string into an array using a delimiter?
- Slice()
- Divide()
- Split() (Correct answer)
- Tokenize()
Correct answer: Split()
The Split() function divides a string by a specified delimiter and returns a zero-based string array.
Question 6: What is the default lower bound for VBA arrays?
- 1
- 0 (Correct answer)
- -1
- It depends on the array type
Correct answer: 0
VBA arrays are zero-based by default, meaning index 0 is the first element unless Option Base 1 is set.
Excel VBA Excel VBA Data Manipulation & Arrays 1
How do you declare a dynamic array in VBA?