User FAQs

If you have a question, please send it to us.

1) How would you declare a Fixed Array of Integers ?
This array contains 11 items: arValues(0) to arValues(10).

Dim aValues1(10) As Integer 
Dim aValues2(0 To 10) As Integer 

Debug.Print LBound(aValues1) & "-" & UBound(aValues1)  '0-10  
Debug.Print LBound(aValues2) & "-" & UBound(aValues2)  '0-10  

2) How would you declare a Dynamic Array of Integers ?

Dim aValues() As Integer 

3) What is the difference between a Fixed Array and a Dynamic Array ?
A dynamic array can be resized multiple times at run-time using the ReDim statement.
A fixed array cannot be resized and the size is defined when it is declared.

4) What is the difference between the following two lines of code ?

Dim aValues() As Variant 
Dim aValues As Variant 

The first line declares an array of the data type Variant.
The second line declares a Variant data type which can hold any data type (including an array).
Variant Data Type vs Array of Variants.

5) Is it possible to resize a Dynamic Array ?
Yes. You can use the ReDim statement.
This can only change the size of the upper bound (not the lower bound).
You can only change the size of the last dimension if the array has multiple dimensions.

Dim aIntegers() As Integer 
ReDim aIntegers(2) 
aIntegers(1) = 10 
aIntegers(2) = 20 
ReDim aIntegers(6) 

6) Is it possible to resize a Dynamic Array without losing the current values ?
Yes. You can use the keyword Preserve.

ReDim Preserve aValues(6) 

7) How would you initialise an Array of Integers ?
You can either initialise one by one or inside a loop.

Dim aIntegers(3) As Integer 
aIntegers(0) = 10 
aIntegers(1) = 20 
aIntegers(2) = 30 
aIntegers(3) = 40 

8) Is it possible to use the ARRAY function to initialise an array ?
Yes. The Array must be declared with a Variant data type.
ARRAY - Returns an array containing the given values.

Dim avNumbers() As Variant 
avNumbers = VBA.Array(10,20,30,40) 

9) Is it possible to initialise an Array without looping ?
Yes. This code will initialise the array to contain the value 8.
In Excel VBA you can use a combination of the Evaluate method and the IF and ISERROR functions.

Dim aIntegers(3) As Integer 
aIntegers = Application.Evaluate("IF(ISERROR(A1:D1), 8, 8)") 

10) What function would you use to quickly filter an array based on a substring ?
FILTER - Returns an array containing a subset of values that contain a substring (Variant).
The filtered array contains all the items that contain the letter "e".

Dim aStrings(2) As String 
aStrings(0) = "mon" 
aStrings(1) = "tue" 
aStrings(2) = "wed" 

Dim arFiltered As Variant 
aFiltered = VBA.Filter(aStrings, "e") 
aFiltered(0) = "tue" 
aFiltered(1) = "wed" 

11) Is it possible to start an array at index position 1 (instead of 0) ?
Yes. There are two ways this can be done.
You can explicitly define the base of the array.

Option Base 0  'default  
Dim aIntegers(1 To 10) As Integer 
Debug.Print LBound(aIntegers) & "-" & UBound(aIntegers)  '1-10  

You can place Option Base 1 at the top of a module.
The Option Base statement only affects the module it is declared in.

Option Base 1 
Dim aIntegers(10) As Integer 
Debug.Print LBound(aIntegers) & "-" & UBound(aIntegers)  '1-10  

12) How can you tell if an Array is 0 or 1 based ?
You can use the LBOUND function to return the lower bound of the array.

If (VBA.LBound(aIntegers) = 0) Then 
If (VBA.LBound(aIntegers) = 1) Then 

13) How many items are there in this Fixed Array ?

Option Base 0 
Dim aIntegers(3) As Integer 

There are four items: aIntegers(0), aIntegers(1), aIntegers(2) and aIntegers(3).

14) How many items are there in this Fixed Array ?

Option Base 1 
Dim aIntegers(3) As Integer 

There are three items: aIntegers(1), aIntegers(2) and aIntegers(3).

15) Should I use the UBOUND function to return the size of an array ?
No. The UBOUND function will return the upper limit which is not the same thing as the size.
This array contains 6 items but the upper limit is 10.

Dim aIntegers(5 To 10) As Integer 
Debug.Print UBound(aIntegers)    '= 10, although array only has 6 items (5,6,7,8,9,10)  

Dim aIntegers(10) As Integer 
Debug.Print UBound(aIntegers)    '= 10, array has 11 items (0,1,2,3,4,5,6,7,8,9,10)  

If you want to return the size of an array you should always use the following formula:

Dim iArraySize As Integer 
iArraySize = UBound(aIntegers) - LBound(aIntegers) + 1 

16) How would you declare a 2-Dimensional Fixed Array ?

Dim aStrings(1 to 10, 1 to 4) As String 

17) How would you declare a 2-Dimensional Dynamic Array ?

Dim aStrings( , ) As String 

18) What would the syntax be for passing in a Fixed Array into a subroutine ?

Public Sub MySubroutine(ByRef aStrings() As String) 
Public Sub MySubroutine(ByRef avNumbers As Variant) 
Public Sub MySubroutine(ByVal avNumbers As Variant) 

19) Is there any difference between the following two lines of code ?

Public Sub MySubroutine(ByRef aIntegers() As Integer) 
Public Sub MySubroutine(ByVal aIntegers() As Integer)   'compile error  

The first line will pass in an array by reference.
The second line will generate a compile error because arrays cannot be passed by value. They must always be passed by reference.

20) What would the syntax be for returning a Fixed Array from a function ?

Public Function MyFunction() As String() 
Public Function MyFunction() As Long() 
Public Function MyFunction() As Variant 

21) What is wrong with the following Function declaration ?

Public Function Array_Find(ByVal arValues() As Integer, _ 
                           ByVal iValue As Integer) _ 
                           As Boolean 
   Array_Find = False 
End Sub 

When you pass in an array, you must pass the "aValues" as ByRef and not ByVal.