1. VBA tables – information for advanced users
I assume that if you are reading this article you have previously read the basic information about VBA tables , which I wrote about in this article: VBA arrays. In the article you are reading you will learn more advanced information about VBA tables. These are functions that you use on the boards, how to quickly read the data from the table and how to quickly load them into the board .
2. Functions of LBound UBound IsArray
In the case of VBA tables, we can use functions relating to the whole table. We can use them to check if our variable is an array. We can also check the subscript and subscript index of the array to check its size. To test the aforementioned functions, create a simple array and fill it with values, remembering not to fill all the elements of the array. The code creating and filling such a table is given below:
'OfficeInside.Org Sub TabliceFunkcje() Dim TabTablica(9) As String TabTablica(3) = "Robert Kubica" TabTablica(6) = "Michael Schumacher" TabTablica(8) = "Nico Resberg" Debug.Print IsArray(TabTablica) Debug.Print LBound(TabTablica) Debug.Print UBound(TabTablica) End Sub
As you can see, we created a 10-element array with indices from 0 to 9. As you can see, the Is Array function helps us to determine whether an element is an array. The LBound and Ubound functions return indexes, regardless of whether the array elements are filled. Below is an extended description:
2.1. Extended description of the functions of tables
IsArray – The function returns True if the item being examined is an array.
LBound (array [, dimension]) – The function returns the lower array index . If we work on multidimensional arrays, the second function argument is used to give the dimension in the array that we want to examine. From basic information about VBA tables , we know that dimensions can have different spans. If we are dealing with a multidimensional table, and we do not complete the argument specifying the dimension, the function returns the index for the first dimension of the array.
UBound (array [, dimension]) – The function returns the upper array index . If we work on multidimensional arrays, the second function argument is used to give the dimension in the array that we want to examine. If we are dealing with a multidimensional table, and we do not complete the argument specifying the dimension, the function returns the index for the first dimension of the array.
2.2. Example of LBound and UBound for multidimensional arrays
Below is a short example of the use of the LBound and Ubound functions for a two-dimensional and three-dimensional table. The dimension is defined by a number from 1 to the number of dimensions of the table. If you do not specify it, the VBA LBound and UBound functions will return index information for the first dimension of the array.
'OfficeInside.Org Sub TabliceFunkcje2() Dim TabTablica1(9, 1 To 5) As String Dim TabTablica2(2, 1 To 5, 18) As String Debug.Print LBound(TabTablica1, 2) Debug.Print UBound(TabTablica1) Debug.Print LBound(TabTablica2) Debug.Print UBound(TabTablica2, 3) End Sub
3. Creating an array using the Array function
Arrays can also be created using the VBA Array function . How to do it? Create a variable with the Variant type. Then assign values to it in Array arguments separated by commas. The example below:
'OfficeInside.Org Sub FunkcjaArray() Dim Panstwa As Variant Panstwa = Array("Polska", "Niemcy", "Czechy", "Węgry") Debug.Print Panstwa(0) Dim Miasta As Variant Miasta = Array("Warszawa", "Berlin", "Praga", "Budapeszt") Debug.Print Miasta(0) End Sub
In this way, the board declared and completed has one drawback. It consists of elements with Variant data type , which is not an efficient type. However, the way the board is created is fast enough to use it.
4. Fast data download from the worksheet to the VBA table
How to quickly download data to the VBA table? You can download data to the VBA table in many ways . We can of course use this loop, which will take all elements of the sheet in turn, but this is not a quick solution. The easiest way to enter data from an Excel spreadsheet into a two-dimensional array (because the sheet also has two dimensions) is a reference to the range. In order to practice the file attached to this article, I added a sheet containing data that we created using the Do Loop loop in one of the previous sections of the VBA Excel course. The following example introduces these data to the table. Our task is to enter the data table and show data from the second table record. We omit the headers when importing data from the worksheet to the table.
'OfficeInside.Org Sub ArkuszDoTablicy() Dim DaneZArkusza As Variant DaneZArkusza = Range("A2:E30") Debug.Print DaneZArkusza(2, 1) Debug.Print DaneZArkusza(2, 2) Debug.Print DaneZArkusza(2, 3) Debug.Print DaneZArkusza(2, 4) Debug.Print DaneZArkusza(2, 5) End Sub
The article is part of the Vba Excel course. The whole course can be found at this link: VBA course . Below is the file with the program code: