The array of arrays, also known as a Jagged Array, is a two-dimensional array where each element of the first dimension is an array. You can access the elements of the inner arrays using two sets of indexes, one for the outer array and one for the inner array. In this article, we will show you different examples of Excel VBA array of arrays.
The above video shows the overview of array of arrays in Excel VBA using some products as examples.
Download Practice Workbook
You can download the practice Excel workbook from the download button below.
How to Open VBA Macro Editor in Excel
VBA is a programming language that may be used for various tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to perform Vlookup with multiple criteria in Excel. Therefore, you can follow the simple steps accordingly to open the VBA editor.
Steps:
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic tab.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write the VBA code.
Excel VBA Array of Arrays: 3 Examples
The array of arrays in Excel VBA helps to store and access data in a better and more efficient way. Now we will show you 3 examples of the array of arrays in Excel VBA.
Example 1: Finding Data in Excel from InputBox and Showing Results in MsgBox
In the above code, we have some product names and some popular brands of products. We will make an array of arrays of brand names for each product. This will help us find the respective brands of the product items using an array of arrays in Excel VBA.
Sub ProductBrands()
products = Array("Car", "Smartphone", "Camera", "Laptop")
CarBrands = Array("Audi", "Porsche", "BMW", "Tesla")
SmartphoneBrands = Array("Apple", "Samsung")
CameraBrands = Array("Sony", "Canon", "Nikon")
LaptopBrands = Array("HP", "Dell", "Asus")
Brands = Array(CarBrands, SmartphoneBrands, CameraBrands, LaptopBrands)
user_input = InputBox("Please write the name of a product. Car/Smartphone/Camera/Laptop")
For i = 0 To UBound(products)
If LCase(user_input) = LCase(products(i)) Then
MyMsg = ""
For j = 0 To UBound(Brands(i))
MyMsg = MyMsg & Brands(i)(j) & ","
Next j
Exit For
End If
Next i
MsgBox "The available brands of " & user_input & " are " & Left(MyMsg, Len(MyMsg) - 1)
End SubVBA Breakdown
Sub ProductBrands()The code begins with a sub procedure called “ProductBrands“.
products = Array("Car", "Smartphone", "Camera", "Laptop")
CarBrands = Array("Audi", "Porsche", "BMW", "Tesla")
SmartphoneBrands = Array("Apple", "Samsung")
CameraBrands = Array("Sony", "Canon", "Nikon")
LaptopBrands = Array("HP", "Dell", "Asus")Now, we declare four arrays – “products“, “CarBrands“, “SmartphoneBrands“, “CameraBrands“, and “LaptopBrands” – each containing strings of the names of various products and their corresponding brands.
Brands = Array(CarBrands, SmartphoneBrands, CameraBrands, LaptopBrands)Here the “Brands” array contains the other four arrays defined earlier.
user_input = InputBox("Please write the name of a product. Car/Smartphone/Camera/Laptop")This will display an InputBox to the user, prompting them to enter the name of a product and store their input in the “user_input” variable.
For i = 0 To UBound(products)The “For” loop will iterate over each element of the “products” array.
If LCase(user_input) = LCase(products(i)) ThenNow, Lcase(user_input) checks if the user’s input matches the current element of the “products” array (ignoring case).
MyMsg = ""
For j = 0 To UBound(Brands(i))
MyMsg = MyMsg & Brands(i)(j) & ","
Next j
Exit ForIf the user’s input matches an element of the “products” array, this code block sets the “MyMsg” variable to an empty string, then iterates over each element of the corresponding brand array (using the index “i”) and appends each brand name, followed by a comma, to the “MyMsg” variable. The “Exit For” statement will exit the loop once a match has been found.
End If
Next iIt ends the “If” statement and moves on to the next element of the “products” array.
MsgBox "The available brands of " & user_input & " are " & Left(MyMsg, Len(MyMsg) - 1)This code displays the user a message box that indicates the available brands for the product they entered. It concatenates the user’s input, the string “The available brands of “, and the contents of the “MyMsg” variable (minus the final comma) using the “&” operator.
If you run the code then an input box will appear. Then insert the name of any product mentioned in the code. The output box will return their brand names like the output image above.
Read More: Excel VBA Array of Strings (4 Examples)
Similar Readings
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
- How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
- Excel VBA: Remove Duplicates from an Array (2 Examples)
- VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
- Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
Example 2: Storing Data with Varying Lengths of Array Element and Showing Output in Immediate Window
This code contains three products, their price, and any discounts offered for the products. The arrays contain different-sized data. We will use the array of arrays in Excel VBA to organize the data.
Sub JaggedArrayExample1()
' Define a jagged array of prices and discounts for 3 products
Dim products(1 To 3) As Variant
products(1) = Array(10, 0.1, 0.2)
products(2) = Array(20, 0.15)
products(3) = Array(15, 0.1, 0.3, 0.4)
' Print the prices and discounts for each product
Dim i As Integer
For i = 1 To 3
Debug.Print "Product " & i
Dim j As Integer
For j = 0 To UBound(products(i))
Debug.Print " " & products(i)(j)
Next j
Next i
End SubVBA Breakdown
Dim products(1 To 3) As VariantThe Dim command declares a jagged array named “products” of data type “Variant” with 3 elements. Since the nested arrays may have different lengths, “Variant” accommodates any data type.
products(1) = Array(10, 0.1, 0.2)
products(2) = Array(20, 0.15)
products(3) = Array(15, 0.1, 0.3, 0.4)These three lines assign nested arrays to each element of the “products” array. The first element is assigned the nested array “Array(10, 0.1, 0.2)”, the second element is assigned “Array(20, 0.15)”, and the third element is assigned “Array(15, 0.1, 0.3, 0.4)”.
Dim i As Integer
For i = 1 To 3The “For” loop iterates over each element of the “products” array. The variable “i” is declared as an integer and is used to keep track of the current element being processed.
Debug.Print "Product " & iThe Debug.Print method displays a message in the Immediate window. It indicates the current product being processed. The message concatenates the string “Product ” with the current value of “i“.
Dim j As Integer
For j = 0 To UBound(products(i))This “For” loop iterates over each element of the nested array of the current product. The variable “j” is declared as an integer and is used to keep track of the current element being processed. The UBound function returns the upper bound of the current nested array.
Next jThe Next command terminates the inner “For” loop after nested array completion.
In the immediate window, we can see that the three products have different numbers of discounted offers. The vba code placed them under each product name.
Read More: Excel VBA 2 Dimensional Array Initialization
Example 3: Removing Non-Zero Array Elements in Excel with VBA and Showing in Immediate Window
The above code contains some values, there are some zeros in the arrays. We will use vba code to eliminate the zero elements. We will write code to remove the zeros and organize the data in array of arrays in Excel VBA.
Sub JaggedArrayExample3()
' Define a jagged array of non-zero elements in a matrix
Dim matrix(1 To 3) As Variant
matrix(1) = Array(1, 2, 0, 0)
matrix(2) = Array(0, 0, 3, 4)
matrix(3) = Array(0, 0, 0, 5)
' Print the non-zero elements in the matrix
Dim i As Integer
For i = 1 To 3
Debug.Print "Row " & i
Dim j As Integer
For j = 0 To UBound(matrix(i))
If matrix(i)(j) <> 0 Then
Debug.Print " " & matrix(i)(j)
End If
Next j
Next i
End Sub
Here, we can see the output of the VBA code. All the zero elements are removed from the list. The immediate window is showing only the non-zero elements.
VBA Breakdown
Dim matrix(1 To 3) As Variant
matrix(1) = Array(1, 2, 0, 0)
matrix(2) = Array(0, 0, 3, 4)
matrix(3) = Array(0, 0, 0, 5)First, the code declares a jagged array matrix with 3 elements. Each element is an array of integers representing a row of a matrix. The integers represent the non-zero elements in the matrix. The zeros represent the missing elements.
Dim i As Integer
For i = 1 To 3
Debug.Print "Row " & i
Dim j As Integer
For j = 0 To UBound(matrix(i))
If matrix(i)(j) <> 0 Then
Debug.Print " " & matrix(i)(j)
End If
Next j
Next iHere, this block of code loops over each row of the matrix and prints the non-zero elements. It does this by using nested loops – an outer loop over the rows, and an inner loop over the elements in each row. If an element is non-zero, it is printed using the Debug.Print statement.
Read More: How to Create an Array in Excel VBA (4 Ideal Methods)
Things to Remember
- Please make sure that you do not try to access elements that are outside the bounds of the array. If you access elements outside the array then it can cause runtime errors or unexpected behavior.
- Be mindful of clearing the array when it is no longer needed by setting it to Nothing. It will free up memory space.
Conclusion
In this article, we showed some examples of Excel VBA array of arrays. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- How to Use UBound on Multidimensional Array with VBA in Excel
- Excel VBA Multidimensional Arrays
- How to Redim 2D Array with VBA in Excel (3 Examples)
- Excel VBA Multidimensional Array for Assigning Values (6 Ways)
- How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)
- Excel VBA to Declare Multidimensional Array of Unknown Size
- How to Declare Array in Excel VBA (3 Easy Ways)
