VBA Arrays

VBA Arrays in Excel

A single variable can store only one value of any data type at a time. Let us see how to store group of values in a variable. It’s possible with either arrays or collections. In this following tutorial learn about arrays.

What is an Array?

An array is nothing but a group of values or elements of same data type store in a memory.

Read More…

Advantages of an Array

We have several advantages by using Arrays. let us see.

Read More…

Array Declaration

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.

Read More…

Assigning Values to an Array

An array values are assigned to an element based on an array index or subscript. Defining values for both static and dynamic array types are same.

Read More about…

Types of Arrays

There are two types of arrays.

  • Static Array: A Static array is an array of fixed size. It stores pre-defined number of elements.
  • Dynamic Array: A dynamic array is an array can be resized during run time. We can resize an array using ‘ReDim’ statement. A dynamic array does not have a pre-defined number of elements.

Read More…




Array Dimensions

There are three types of dimensions.

  • One-Dimensional Array:The One-Dimensional Arra has one dimension uses only one index(Subscript)
  • One Dimensional Array

  • Two-Dimensional Arrays:The Two-Dimensional array has two dimensions and uses two indexes(Subscriptss)
  • Two Dimensional Array

  • Three-Dimensional Arrays:The Three-Dimensional array has three dimensions and uses three indexes(Subscriptss)
  • Three Dimensional Array

  • Multi-Dimensional Arrays:The Multi-Dimensional Array has more than two dimensions and uses more than two indexes(Subscripts). We can have upto 60 dimensions in an array.

ReDim Statement

The ‘ReDim’ stament is used to initialize or resize a dynamic array.

Sub VBA_Array_ReDim()

    'Declare an Array variable
    Dim aArrayName() As String
    ReDim aArrayName(0 To 1) As String

    'Set the value of array index 0
    aArrayName(0) = "Thanks"
    'Set the value of array index 1
    aArrayName(1) = "Welcome"
    
End Sub

ReDim with Preserve Statement

We can use multiple times ReDim statement in an array. When We use many times the ReDim statement in an array, memory storage reallocates its space.

'VBA Assigning Values to an Array
Sub VBA_Array_ReDim_With_Preserve()

    'Declare an Array variable
    Dim aArrayName() As Integer
    ReDim aArrayName(0 To 2) As Integer

    'Set the value of array index 0
    aArrayName(0) = 1

    'Set the value of array index 1
    aArrayName(1) = 2

    'Set the value of array index 2
    arrMarks(2) = 3
    
    'Reallocates memory space for new elemnt in array
    ReDim Preserve aArrayName(3) As String
    'Set the value of array index 3
    arrMarks(3) = 4

End Sub

VBA Split String into Array in Excel

You can split string into array by using VBA Split function. It helps to create an array and store elements in an array.

Read More…

Read Values from Range to an Array

You can read values from range, cell, table, row, column, etc.

Read More…

Read Values from an Array to Range

Let us see the simple example how to read values from an array to range.

'VBA Read Values from an array to range
Sub VBA_Read_Values_Array_To_Range()

    'Declare an array variable
    Dim sWeekDays As Variant
    Dim iCnt As Integer
    
    'Define an Array values
    sWeekDays = Array(1, 2, 3, 4, 5, 6, 7)
    
    'Loop through each item in an array
    For iCnt = LBound(sWeekDays) To UBound(sWeekDays)
        Sheets("Sheet1").Range("A" & iCnt + 1) = sWeekDays(iCnt)
    Next
        
End Sub

Store Variant Values in an Array

We can quickly create an array using variant type data by creating dynamic array. Let us see how to store variant values to an array quickly.

Read More…

Array Built-In Functions

VBA Array Functions in Excel. These are different VBA Built-In functions. Those are LBound, UBound, IsArray, Erase, Split, Join and Filter. VBA Programmers use these functions in arrays to fasten process.

  • LBound: The LBound function represents the lower bound value of an array. It returns the smallest subscript(Index) value of specified array.
  • VBA LBound Function

  • UBound: The UBound function represents the upper bound value of an array. It returns the highest subscript(Index) value of specified array.
  • VBA UBound Function

  • IsArray: The IsArray function checks the specified input variable is an array or not. It returns a boolean value either True or False.
  • VBA IsArray Function

  • Erase: The Erase function is used to reset(reinitialise) the size of an array. It depends on type of an array.
  • VBA Erase Function

  • Split: The Split function helps us to know the number of values in an array by using delimiter.
  • VBA Split Function

  • Join : The Join function helps us to join sub strings in an array and returns a single string. It is opposite of Split function.
  • VBA Join Function




Leave a Comment

Your email address will not be published. Required fields are marked *