VBA Array Declaration in Excel

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’. If 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 use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen

VBA Arrays other articles

You may also like the related VBA Array articles.

VBA Arrays in Excel

Leave a Reply