How To Create An Array In Excel Vba

Creating an Array in Excel VBA

In Excel VBA, you can create an array to store multiple values of the same data type. Arrays can be useful when you want to work with a group of related data elements.

1. Declaring an Array

To declare an array in VBA, you first need to specify the data type of the elements it will hold. The general syntax for declaring an array is:

    Dim arrayName() As dataType
  

For example, to declare an array named myArray that will hold integers, you would use the following statement:

    Dim myArray() As Integer
  

2. Initializing an Array

Once you have declared an array, you can initialize it with values using the Array function or by assigning values directly to each element. Let’s see both approaches:

a) Using the Array Function

The Array function creates an array and assigns values to it. The general syntax is:

    arrayName = Array(value1, value2, ..., valueN)
  

For example:

    
Dim myArray() As Integer
myArray = Array(10, 20, 30, 40, 50)
    
  

b) Assigning Values Directly

You can also assign values directly to each element of the array. The general syntax is:

    arrayName(index) = value
  

For example:

    
Dim myArray(4) As Integer
myArray(0) = 10
myArray(1) = 20
myArray(2) = 30
myArray(3) = 40
myArray(4) = 50
    
  

3. Accessing Array Elements

You can access individual elements of an array using their index. The index ranges from 0 to the size of the array minus 1. To access an element, use the following syntax:

    arrayName(index)
  

For example:

    MsgBox myArray(2)
  

This will display the value 30, as it is the element at index 2 of the myArray.

4. Using Loops with Arrays

Loops can be used to iterate through the elements of an array and perform certain actions. For example, you can use a For loop to display all the elements of an array:

    
Dim i As Integer
For i = 0 To UBound(myArray)
    MsgBox myArray(i)
Next i
    
  

This code will display each element of the myArray in separate message boxes.

5. Redimensioning an Array

If you want to change the size of an array dynamically, you can use the ReDim statement. The general syntax is:

    ReDim Preserve arrayName(newSize)
  

The Preserve keyword is used to preserve the values of the existing array elements when you change the size. For example:

    ReDim Preserve myArray(9)
  

This resizes the myArray to have 10 elements while preserving the values of the existing elements.

Example:

Here’s a complete example that demonstrates the creation and usage of an array in Excel VBA:

    
Sub ExampleArray()
    Dim myArray() As Integer
    myArray = Array(10, 20, 30, 40, 50)
    
    Dim total As Integer
    total = 0
    
    Dim i As Integer
    For i = 0 To UBound(myArray)
        total = total + myArray(i)
    Next i
    
    MsgBox "The total is: " & total
End Sub
    
  

This example initializes an array myArray with some values, calculates their sum using a loop, and displays the result in a message box.