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).

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

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 ?

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.

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

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

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.

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.

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".

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.

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

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.

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

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

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

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.

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

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

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

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

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

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 ?

21) What is wrong with the following Function declaration ?

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