Arrays in VBA Excel – Basics

What are tables in VBA Excel – VBA Arrays

In this chapter of the VBA course you will learn what VBA tables are and how to use them in data analysis. VBA tables are containersfor storing data of the same type in an organized manner. We can refer to each element of an array using its indexed address. Arrays can also be compared to variables that store other variables . Often in programming kosks you can meet the name VBA Arrays – it’s about the boards. With their help you have access to data in VBA in an easy and quick way without having to download them from the spreadsheet. Arrays in VBA Excel can be divided into several types depending on the number of dimensions of the array. Each element of the array can store data:

  • One -dimensional boards , so-called letters
  • Two-dimensional , table-shaped arrays
  • Three-dimensional , cube-shaped boards
  • Multidimensional arrays (for more than 3 dimensions, up to 60 dimensions)

We should declare tables in Excel VBA . After the declaration, we can introduce elements to them. After entering the data into the table, the data can be downloaded from the table . Below is the display of VBA tables. Multidimensional boxes are hard to visualize, so the way they are displayed is abstract.


2. Declaring and addressing elements of the VBA table

The boards in VBA should be declared. Arrays like VBA variables  have a data type. The data type for all array elements can also be declared. If we do not do this, the data in the table will get the Variant type . It is important that all elements of the table are of the same type of data. We can make the declaration in several equivalent ways:

2.1. Declaring an array with the determination of the total number of elements:

By declaring an array in this way, you specify the total number of elements that the table can accept. The most important thing with this declaration is that you specify the index of the last element in the array in brackets. VBA tables are numbered by default from zero. So, if you want to declare an example of a one-dimensional array with a number of 10 elements, you should write it in this way Dim Name of the Table (9) . Then you refer to numbers from 0 to 9 to individual parts of the table.


'zadeklarowanie tablicy jednowymiarowej posiadającej 11 elementów, bez określenie typu danych:
Dim TabTablica1(10)
'zadeklarowanie tablicy trójwymiarowej po 11 elementów w każdym wymiarze, z określeniem typu danych:
Dim TabTablica2(10, 10, 10) as String

2.2. Declaring an array with the specification of indices of individual elements:

Declaring an array with the index specification consists in determining the number of the first and last element of each dimension of the table, eg 1 to 12 or 2000 to 2005. Declaring with the index number determines the use of elements of the table significantly. We can give the index number on the example of the number of the year, month, day of the week, etc.

'zadeklarowanie tablicy jednowymiarowej z określeniem indeksów dla 10 elementów:
Dim TabTablica1(1 To 10)
'zadeklarowanie tablicy trójwymiarowej z określeniem indeksów i typu danych:
Dim TabTablica2(2017 To 2025, 1 To 12, 1 To 7) As String

2.3. Mixed declaration of the board

We can also declare the boards in a mixed way. In the declaration, we use both previously learned ways.

'zadeklarowanie tablicy trójwymiarowej z określeniem typu danych:
Dim TabTablica2(2017 To 2025, 10) As String

2.4. Change in the number of elements in the array

In VBA Excel, you can also declare an array without specifying the number of elements and dimensions that it should have. An attempt to enter data into such a table will end with an error. To avoid the error, the number of elements in the array should be specified using the ReDim command , as in the example below. We can also enlarge our table with a few elements.

Dim TabTable()
ReDim TabTable(1 To 12, 6)
ReDim TabTable(1 To 50, 10, 2015 To 2030)

2.5. Addressing array elements

Once an array is declared, we can enter data into it and download it . To perform these activities, we should properly address theelements of the array. Elements of the array are addressed using its indexes. To the elements of declared tables, in order to write or read data, we refer to the following way:  Table_name (index_number).

3. Entering data into the Excel VBA table

To enter data into an array, you must specify the dimension, and thus the fields of the array, which should be supplemented. Depending on the accepted declaration method, we select the field of the table by numbered index.

Sub TabExample()

Dim TabTable(6) As String

TabTable(0) = "Poniedziałek"
TabTable(1) = "Wtorek"
TabTable(2) = "Środa"
TabTable(3) = "Czwartek"
TabTable(4) = "Piątek"
TabTable(5) = "Sobota"
TabTable(6) = "Niedziela"

End Sub

As you probably noticed, the addressing of tables declared with the determination of the total number of elements starts from zero. This is the number of the first index of the table. So if you would like an array to have 10 elements, you should declare an array as follows Table Name (9) . If you would like the table declared in this way to have an index of the first element starting with the number 1, you should enter Option Base 1 before the array declaration .

Sub TabExample()

Option Base 1
Dim TabTable(6) As String

TabTable(1) = "Poniedzialek"
TabTable(2) = "Wtorek"
TabTable(3) = "Sroda"
End Sub

If we want to enter data into multidimensional arrays , we proceed in a similar way by specifying the element according to its indices relative to the individual dimensions of the table.

Sub Table()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 31, 1 To 12, 2000 To 2030)
'Wprowadzenie elementu do tablicy
TabTablica(30, 9, 2017) = WeekdayName(2, False, vbMonday)
'Potwierdzenie, że pole tablicy zawiera dane
MsgBox TabTablica(30, 9, 2017)
End Sub

Data can also be entered directly from the data sheet or from the InputBox VBA window . How to do it? Below is an example:

Sub TableInputBox()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 10, 1 To 10, 1 To 10) As Variant
'Wprowadzenie elemenu do tablicy z okna inputbox
TabTablica(1, 1, 1) = InputBox("Wprowadz dane do pola 1,1,1")
'Potwierdzenie, ze pole tablicy zawiera dane
MsgBox TabTablica(1, 1, 1)

'Wprowadzenie danej do Arkusza - komórka A1
Range("A1") = "Moja dana"
'Pobranie danej do tablicy z komórki arkusza
TabTablica(2, 2, 2) = Range("A1")
'Potwierdzenie poprzez pobranie danej z tablicy
MsgBox TabTablica(2, 2, 2)
End Sub

4. Downloading data from the Excel VBA table

If you want to download data from the table, the only thing you should do is address the array field. Data from the table can be downloaded to the variable VBA, displayed in the VBA window MsgBox , enter directly into the datasheet. Below is an example:

Sub TableProgram()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 10, 1 To 10, 1 To 10) As Variant
'Wprowadzenie elemenu do tablicy z okna inputbox
TabTablica(1, 1, 1) = InputBox("Wprowadz dane do pola 1,1,1")

'Wprowadzenie danej z tablicy do komórka A1
Range("A1") = TabTablica(1, 1, 1)
'Wprowadzenie danej z tablicy do okna MsgBox
MsgBox TabTablica(1, 1, 1)
End Sub

5. Application of tables in VBA Arrays in Excel

What can we use tables in VBA? Boards primarily speed up calculations performed on Excel data. We can create complex mathematical models with their use. In the next chapter of the VBA course, you will learn additional information about VBA tables. You will learn how to create arrays using the Array function . You will learn how to perform bulk operations between arrays and data sheet. You will also create a mathematical model using a multidimensional array.

6.  Tasks (You can enter the solution in a comment)

6.1. Create a two-dimensional array, 7 and 12 elements in dimensions. Assign the names of the days of the week and the names of the months to the array elements.

6.2. Modify the above example. Enter elements from the table from task 6.1 into the datasheet.

6.3. Create a one-dimensional array. Assign it to years 2000 to 2030 using the Do While loop .

Leave a comment

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

%d bloggers like this: