Array Declaration in Excel VBA

Array Declaration in Excel VBA. Array Declaration in Excel VBA is very good habit. Declaring a variable tells the computer to allocate space in memory for later use. We can declare variable using Dim, Public, Private, or Static statements. We declare static array variable by specifying size of an array. Default array index value ‘0’.

When we specify Option Base statement as ‘1’ then the array index value starts from ‘1’, Otherwise index value is ‘0’. The array size value cannot be negative. An array can store any type of variable. It can be integer, string, double, or character in a single array variable. Array can also be declared as a variant type.

Declare Static Array:

Static array also known as fixed array. We declare static array variable by specifying size of an array. The size of an array specified within parenthesis.

Here is the syntax of the static array declaration.

Dim aArrayName(4) As Integer
aArrayName(0 to 4) As Integer

Explanation: Above specified array consists of 5 values and is of integer data type. The array consists of aArrayName(0), aArrayName(1), aArrayName(2), aArrayName(3), and aArrayName(4)    elements. The above specified elements contain default value is ‘0’. The array Lbound value is 0 and Ubound is 4. The integer data type occupies 2 bytes. We have 5 elements in an array, so it occupies 10bytes in memory. In the above statement 4 represents the array dimension or subscript.

Declare Dynamic Array:

We declare dynamic array variable without specifying size of an array. Dynamic arrays are declared by using an empty parenthesis. The dynamic array size can be defined during run time. It defines the size of an array by using ‘ReDim’ statement. ‘ReDim’ statement creates a new static array.

Here is the syntax of the dynamic array declaration.

Dim aArrayName() As String

ReDim aArrayName(2) As String
ReDim aArrayName(0 to 2) As String


Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Arrays in Excel VBA Tutorial VBA Functions List Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

Leave a Reply