Logo
×

Excel VBA Array

Introduction to VBA Array

An array is a type of variable that holds more than one piece of data. In VBA, you can refer to a specific variable (element) of an array by using the array name and the index number. For example, to store daily reports for each day of the year, you can declare one array variable consisting of 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The specification of arrays are as follows:

  • An array index starts from ZERO, so if an array size is specified as 6, it can hold seven values in it.
  • Array Index cannot be declared with a negative number.
  • VBA Arrays can store any kind of variable in it. Hence, an array can store an integer, string, or characters in a single array variable.

In the below sections, we will briefly discuss the following arrays:

  1. One Dimensional Array
  2. Two dimensional Array
  3. Dynamic Array

One Dimensional Array

In one-dimensional arrays only one number designates the location of an element of the array. Such an array is like a single row of data, but because there can be only one row. The values are assigned to a one-dimensional array by specifying an array index against each value to be assigned.

Declaration of Array

Arrays are declared the same way a variable is declared except that the declaration of an array variable uses parenthesis. In the following example, the size of the array is mentioned in the brackets. You declare an array by adding parentheses after the array name and specifying the number of array elements in the parentheses. The following are the three ways of declaring an array:

'Method 1: Using Dim and Without Size
 Dim arr()              
 'Method 2: Mentioning the Size
 Dim arr (size)  'Declared with size 
 'Method 3: By using 'Array' function
 Dim arr
 arr = Array ("blue", "Orange", "red")

Example 1: > Write a macro to understand the working of a One-Dimensional Array and how to store the values in Array?

Sub Array_method1()
 'declaring the array
 Dim arr(9)
 'inserting values in the array
 arr(0) = "Alan"
 arr(1) = "Christopher"
 arr(2) = "Elmer" 
 arr(3) = "Frank"
 arr(4) = "Gerard"
 arr(5) = "John"
 arr(6) = "William"
 arr(7) = "Robert"
 arr(8) = "Ronald"
 arr(9) = "Thomas" 
 'running a loop to print the array
 For i = 0 To 9
     Cells(i + 2, 1).Value = arr(i)
 Next
 End Sub

Output

Name
Alan
Christopher
Elmer
Frank
Gerard
John
William
Robert
Ronald
Thomas
One-Dimensional Array

Example 2: Write a Macro using array function and print the names. 

Sub Array_method2()
 'declaring the array
 Dim arr As Variant
 'inserting values in the array using array() function
 arr = Array("Alan", "Christopher", "Elmer", "Frank", "Gerard", "John", "William", "Robert", "Ronald", "Thomas")
 'running a loop to print the array 
 For i = 0 To 9
     Cells(i + 2, 1).Value = arr(i)
 Next
 End Sub

Output

Name
Alan
Christopher
Elmer
Frank
Gerard
John
William
Robert
Ronald
Thomas

Two-Dimensional or Multidimensional Array

In some cases, a single dimension is not enough. This is where multidimensional arrays

come in. A one-dimensional array is a single row of data, a multidimensional array contains rows and columns. However, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most used ones.

Example 1: Write a macro to print the name in column 1 and the sales figure in column 2 using a two-dimensional array.

Sub TwoDimensional_Array_method1()
 'declaring the array
 Dim arr(9, 2)
 'inserting values in the array for column 1
 arr(0, 1) = "Alan"
 arr(1, 1) = "Christopher"
 arr(2, 1) = "Elmer"
 arr(3, 1) = "Frank"
 arr(4, 1) = "Gerard" 
 arr(5, 1) = "John"
 arr(6, 1) = "William"
 arr(7, 1) = "Robert"
 arr(8, 1) = "Ronald"
 arr(9, 1) = "Thomas"
 'inserting values in the array for column 2 
 arr(0, 2) = "123"
 arr(1, 2) = "309"
 arr(2, 2) = "112"
 arr(3, 2) = "211"
 arr(4, 2) = "121"
 arr(5, 2) = "211"
 arr(6, 2) = "567" 
 arr(7, 2) = "145"
 arr(8, 2) = "998"
 arr(9, 2) = "456"
 'running a loop to print the two dimensional array
 For i = 0 To 9
     For j = 1 To 2
         Cells(i + 2, j).Value = arr(i, j)
     Next
 Next
 End Sub

Output

Name Sales
Alan 123
Christopher 309
Elmer 112
Frank 211
Gerard 121
John 211
William 567
Robert 145
Ronald 998
Thomas 456
Two-Dimensional array

Example 2: Write a macro, wherein take the input from the user, store it in an array and finally print it.

Sub TwoDimensional_Array_method1()
 'declaring the array
 Dim arr(5, 2)
 For i = 0 To 5
     IB = InputBox("Enter your name")
     IB_sales = InputBox("Hey " + IB + "! Enter your sales amount")
     arr(i, 1) = IB
     arr(i, 2) = IB_sales 
 Next
 'running a loop to print the two dimensional array
 For i = 0 To 5
     For j = 1 To 2
         Cells(i + 2, j).Value = arr(i, j)
     Next
 Next
 End Sub

Output

Enter your name

array and finally print

Enter your sales figure

inserted in the excel sheet

Similarly, answer all the input boxes. You will see notice the data has been inserted in the excel sheet.

Multidimensional Array

Dynamic Array

A dynamic array is an array that does not have a set size. You can declare the dynamic array but

must leave the parentheses empty, as shown below:

Dim myArray()

ReDim keyword is used if the developer wants to set the size of the array. It is used to declare a dynamic array and allocate or reallocate storage space. Using ReDim, the developer can reinitialize the array.

But, if you use ReDim too many times, such as in a loop, you lose all the data it holds. To prevent the data from losing, the Preserve keyword is used. This keyword enables you to resize the last array dimension, but you cannot use it to change the number of dimensions.

Syntax

ReDim [Preserve] varname(subscripts) [,varname(subscripts)]

Parameters

Preserve (optional)- This parameter is used to preserve the elements of the data in an existing array when you change the size of the last dimension.

Varname (required)- This parameter represents the name of the variable (it should follow the standard variable naming conventions).

Subscripts (requited)- It represents the size of the array.

Example 1: Write a macro to demonstrate the example of Dynamic array and explain the concept of Dim, ReDim and Preserve.

Private Sub Dynamic_Array_Example()
     'declare the array but leave the parentheses empty
    Dim arr() As Variant
    i = 0
    'set the size of the array
    ReDim arr(6)
    arr(0) = "Hello"
    arr(1) = 14
    arr(2) = 22.987
    arr(3) = "@VBA@"
     'resize the last array dimension
    ReDim Preserve arr(8)
    For i = 4 To 8 
    arr(i) = i
    Next
    'to Fetch the output
    For i = 0 To UBound(arr)
       Cells(i + 1, 1).Value = arr(i)
    Next
 End Sub

Output

Hello
14
22.987
@VBA@
4
5
6
7
8
Dynamic array

Related Topics

VBA ListBox

What is a ListBox? ListBox refers to a permanently displayed control (usually box-shaped) which contains a list of objects (or attribute, or elements) from which the user can select single or multiple attributes....

8 minutes read.

Excel VBA StrReverse Function

The StrReverse function returns a String after reversing the given String. Syntax StrReverse (Expression) Parameter Expression (required) – This parameter represents the String that you want to reverse. Return This function returns a String after reversing the given String. Example...

1 minute read.

Excel VBA Str Function

VBA Str Function: The Str function in VBA converts the given number into a string representation of that number. Syntax Str (Number) Parameter Number (required) – This parameter represents the numeric value that you want...

1 minute read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

Excel VBA Replace Function

VBA Replace Function: The Replace function in VBA searches for a substring within the specified string and replaces its occurrences with a second substring. Syntax Replace (Expression, Find, Replace, [Start], [Count], [Compare]) Parameter Expression (required) – This parameter represents...

2 minutes read.

Excel VBA CDbl Function

VBA CDbl Function: The CDbl function in VBA converts an expression into a Double data type. Syntax CDbl (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

VBA Dim

What is Dim? DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.)...

7 minutes read.

Excel VBA: Select … Case Statement

Select … Case Statement When a group of statements is executed, depending upon the value of an Expression, then Switch Case is used.  If you have several conditions to check, then the If condition...

4 minutes read.

Looping in VBA

Looping in VBA There are many situations where a programmer needs to execute a block of the repetitive code number of times. Writing the same statement will make the program tedious and monotonous....

3 minutes read.

Excel VBA IsError Function

VBA IsError Function: The IsError function in VBA returns a Boolean value showing whether the specified Expression represents an error or not. Syntax IsError (Expression) Parameter Expression (required)- This parameter represents the variant that you...

1 minute read.

VBA TimeSerial Function

The TimeSerial function in VBA returns a Time for the specified hour, minute, and second. Syntax TimeSerial (Hour, Minute, Second) Parameter Hour (required) – This parameter represents an integer (0 to 23), signifying the hour of the time. Minute...

2 minutes read.

VBA Not Equal Operator

What is VBA Not Equal Operator? VBA Not Equal binary operator (“<>”) is a logical function that is used to check if the specified values are not equal or not. This...

5 minutes read.

Excel VBA IsNumeric Function

VBA IsNumeric Function: The IsNumeric function in VBA returns a Boolean value showing whether the specified Expression contains a numeric value or not. Syntax IsNumeric (Expression) Parameter Expression (required)- This parameter represents the variant that...

1 minute read.

Excel VBA FormatCurrency Function

VBA FormatCurrency Function: The FormatCurrency function in VBA is used to apply a currency format to a numeric expression and returns the result as a string. Syntax FormatCurrency (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

For Next loop in VBA

For Next Loop The ”For Next” loop is used for a fixed number of times. It works by implementing the loop for the specified number of times. In this, the user specifies how...

3 minutes read.

Excel VBA String Function

The String function in VBA creates a String, consisting of several repeated characters. Syntax String (Number, Character) Parameter Number (required) – This parameter represents the number of characters in the returned String. Character (required) – This parameter...

1 minute read.

CodeIgniter Architecture

Here we will understand the architecture and working of the CodeIgniter application, which helps you to elaborate all steps in simple ways. As the above image represents that whenever a request comes from the...

2 minutes read.

Miscellaneous Exercise of conditional statements and Loop

We have the already worked with syntax and examples of conditional statements and loops in the previous tutorials. In this tutorial, we will learn how to work with both together.  We will explain...

3 minutes read.

Introduction to Visual Basic Editor Window

How to enable the Developer Ribbon Tab? In order to work with VBA, users need to make a small change in Excel to display a new tab (Developer) at the top of the...

5 minutes read.