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.
- Direct or a loop method.
- Using VBA function Array.
- VBA Split – Text string to Array
- 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.