VBA Array Initialize Values – Split string to array

How to Initialize an Array in Vba?

Different methods to assign values to array are explained here. Starting from basic to few easy methods to initialize array with multiple values are discussed in here.

Here are the list of available methods.

1. Direct or a loop method.
2. Using VBA function Array.
3. VBA Split – Text string to Array
4. Range to Array – Discussed in another topic – Click here

Lets see these steps one by one in detail with sample arrays.

1. Direct Method – Vba Array Fill values

This is the same as how we assign values to a non array field.

But, before assigning any value, we need to mention the length of the array while declaring the field using Dim or Redim. For Example:

Dim arrayFld(2) as string

In this the number 2 signifies the index of last value & not the number of items in the array. It is always n+1.

By default the array index will start from 0. So, this array can hold a total of 3 values.

Sub assignValuesToArray()
    'Define & inialize Array
    Dim strArray(1) As String
    strArray(0) = "1"
    strArray(1) = "2"

    'Use Redim & init array fields
    Dim intArray() As Integer
    Dim i As Integer
    For i = 0 To 99
        ReDim Preserve intArray(i + 1)
        intArray(i) = i
    Next
End Sub

For dynamic array sizes do not mention the array size in Dim statement.

2. Initialize using VBA Array function

This is pretty simpler one than even using the previous method. If there are only few values, this method would be preferable.

Also note that, You can have a mixed data types with single array. i.e., integer, string, date etc.,

Sub vbaArrayFunction()
    'Declare Dynamic Variant Array
    Dim vArray() As Variant
    
    'Initialized with Only String
    vArray = Array("Initialized", "With", "Only", "String")
    
    'Initialized with Only Integers
    vArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 7)
    
    'Initialized with combinatin of different data type values
    vArray = Array("1", 2, "anything", VBA.Now)
    
End Sub

This Vba array function works only on a variant data type array.  The above code works even if you declare the vArray as plain variant (Dim vArray as Variant).

3. Vba Split – Convert String to Array

This method can assign values from a long string data by slicing it into multiple pieces. Then it will return an array which can be assigned to a array variable.

Sub vbaSplitToArray()
    'Declare Dynamic array
    Dim vArray() As String
    
    'String to Array
    vArray = VBA.Split("Sun;Mon;Tue;Wed;Thu;Fri;Sat", ";", , vbTextCompare)
    
End Sub

Here is it only a small text used as sample. But in some cases, developers split a large csv or text file content to arrays.

Arrays are faster to process that using string operations on very large text data. So, most developers prefer Arrays. But, Collection data type is more flexible that arrays. Once you become familiar with collection, you will not use Arrays much.

External Reference: Click here to read more about Array from other source.