VBA-Lesson 13.2. Using Arrays (Continued)

In order to store more than one column of data, we need a different dimension of the array. Example:

Saving data in a two-dimensional array:

"Declaration
Dim array_example(10, 2) '11 x 3 "defined" array

"Saving data in an array
For i = 0 To 10
      array_example(i, 0) = Range("A" & i + 2)
      array_example(i, 1) = Range("B" & i + 2)
      array_example(i, 2) = Range("C" & i + 2)
Next

The following are some examples of working with these values:

MsgBox array_example(0, 0) '=> returns: 03.11.2026
MsgBox array_example(0, 1) '=> returns: 24
MsgBox array_example(9, 2) '=> returns: NO
MsgBox array_example(10, 2) '=> returns: YES

Dynamic array

Let's imagine for a moment that we need to update the data in our array on a regular basis, and so we can't assign fixed values at declaration time...

To find out the row number of the last non-empty cell, or in other words, the last row of our database, we will use the following formula:

last_row = Range("A1").End(xlDown).Row

Excel does not accept variables in the declaration.

Instead, declare a dynamic array (using empty brackets), then define its size using Redim:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Using the following procedure, you can store all the rows of your dataset (table) in our array:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Ubound

In the previous example, the last number in our array was last_row - 2:

For i = 0 To last_row - 2

Another way to determine the last number in our array could be by using Ubound:

For i = 0 To UBound(array_example)

This function returns the largest number in the array for the selected dimension (the first dimension is the default).

The following are some examples that will clarify this in more detail:

Sub example()
      Dim array_example(10, 2)
     
      MsgBox UBound(array_example) '=> returns : 10
      MsgBox UBound(array_example, 1) '=> returns : 10
      MsgBox UBound(array_example, 2) '=> returns : 2
End Sub

Saving data in a range of array elements

It is possible to fill an array with values from a range of cells on a worksheet without even using a loop:

"Declaration
Dim array_example(10, 2) '11 x 3 "defined" array

"Saving data in an array
For i = 0 To 10
      array_example(i, 0) = Range("A" & i + 2)
      array_example(i, 1) = Range("B" & i + 2)
      array_example(i, 2) = Range("C" & i + 2)
Next

The preceding code can be effectively replaced by this:

"Declaration
Dim array_example()

"Saving data in an array
array_example = Range("A2:C12").Value

Although the second method seems more attractive than the first, beware that in most cases it may cost you more time to complete than the first.

If you store the data in your array this way, the first number will be 1 instead of 0, which can cause confusion...

Further in the process of increasing the code, if you decide to store only the data that meets certain search criteria in the array (or perform a completely different operation), you will have to completely rewrite the code using a different loop function...

But this second method is quite useful if you need to save the entire contents of a large data set, because it is faster than looping (saving about 0.2 seconds for every 15,000 records).

Array

But if you need to create an array that has "fixed" contents.

One solution could be to record the values tape by tape:

Dim en(5)

en(0) = "IF"
en(1) = "VLOOKUP"
en(2) = "SUM"
en(3) = "COUNT"
en(4) = "ISNUMBER"
en(5) = "MID"

Fortunately, you can simplify this code by using an array (Array):

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Here is a demonstration of using the Replace function (this will help you understand the following example):

Sub replace_example()
      Dim var_translate As String

      'Text ribbon for this example
      var_translate = "Hello World !"
     
      'Replace "World" with "you" in the text ribbon
      var_translate = Replace(var_translate, "World", "you")

      'Tape after replacement
      MsgBox var_translate '=> returns "Hello you !"
End Sub

Now, if we want to replace a series of values with another set of data, using arrays and the Array function will be extremely useful:

Sub translate() 'Simplified example of translating formulas from English to French
      Dim var_translate As String

      'Text ribbon for this example
      var_translate = "Formula to translate : SUM(IF(ISNUMBER(A1:E1),A1:E1,0))"
     
      'Two sets of values
      en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
      fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
     
      'Replace "SI" with "IF", "RECHERVEV" with "VLOOKUP", etc.
      For i = 0 To UBound(en)
          var_translate = Replace(var_translate, en(i), fr(i))
      Next

      'Tape after replacement
      MsgBox var_translate '=> returns "Formula to translate : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))"
End Sub

Split

The Split function allows us to convert a character string into an array.

To convert a ribbon to an array, do the following:

variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"

Use the Split function and specify the separator:

en = Split(variable, "/")

The array en will return the following values:

MsgBox en(0) '=> returns : IF
MsgBox en(1) '=> returns : VLOOKUP
MsgBox en(2) '=> returns : SUM
MsgBox en(3) '=> returns : COUNT
MsgBox en(4) '=> returns : ISNUMBER
MsgBox en(5) '=> returns : MID

The following 3 arrays will also return the same values:

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
en = Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")
en = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

The following example returns the third value in the string:

MsgBox Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")(2) '=> returns : SUM

The opposite of Split is the Join function.

This function collects the values of an array into a string.

MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> returns : 12345

Articles on the topic: