What is Excel VBA Array Function?
VBA Arrays function in Excel can be defined as a variable which stores multiple values of a similar data type. Nine different array functions are available in VBA Excel. These include ARRAY, ISARRAY, JOIN, REDIM, SPLIT, ERASE, FILTER, LBOUND, and UBOUND.
We write the VBA arrays function as follows:
Array(arglist)
Here, arglist indicates a comma-delimited list of elements. If no arguments are passed, an array of zero length is created.
Table of contents
Key Takeaways
- VBA Array function in Excel is a simple way of storing values of the same data type in a variable.
- VBA Array function is mainly of two types: Static and Dynamic.
- Static Array: The length of the array is pre-determined. For example, no. of days in a week, months, seasons of India, etc.
- Dynamic Array: The length of the Array is not fixed and is not pre-determined.
- We can create a customized Excel function using the VBA Array function. It simplifies the writing and maintenance of code.
How to use VBA Array Functions?
Let us take a simple example to understand VBA Arrays Function in Excel. Here, we look at how to store String values using the VBA Arrays function. Open the VBA module and write the below VBA code:
Step 1: Here, we declare a variable Arr to assign our array and use the Array function to store values of a variant data type into it. We then print the elements of the array, including the length, using a message box.
Sub VBA_Array_Example()
Dim Arr As Variant
Arr = Array(“VBA”, “Programming”, “Lanaguage”, “Excel”)
MsgBox Arr(0) & ” ” & Arr(1) & ” ” & Arr(2) & ” ” & Arr(3)
MsgBox “Array Length: ” & UBound(Arr) – LBound(Arr) + 1
End Sub
Step 2: Run the code; see the result below. When you click OK after the first result, you can see the result of the length of the string.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Let us look at some examples of how to use the VBA Arrays Function in Excel in different ways.
#1 – Insert Serial Numbers Using Static Array
In a static array, the length of the array is pre-determined. We can insert serial numbers easily into a static array.
Step 1: Write the below code:
Sub StaticArray_Ex()
Dim x(1 To 10) As Integer
x(1) = 5
x(2) = 10
x(3) = 15
x(4) = 20
x(5) = 25
x(6) = 30
x(7) = 35
x(8) = 40
x(9) = 45
x(10) = 50
Range(“A1”).Value = x(1)
Range(“A2”).Value = x(2)
Range(“A3”).Value = x(3)
Range(“A4”).Value = x(4)
Range(“A5”).Value = x(5)
Range(“A6”).Value = x(6)
Range(“A7”).Value = x(7)
Range(“A8”).Value = x(8)
Range(“A9”).Value = x(9)
Range(“A10”).Value = x(10)
End Sub
Explanation:
- Here, we declare an array variable x to store ten values.
- Each element of the array variable can store a value of the VBA data type Integer.
- We assign values to each array element and insert them into the Excel cells ranging from A1 to A10.
Step 2: Run the code and we can see the values are shown in A1-A10 cells.
#2 – Insert Serial Numbers Using Dynamic Array
In a dynamic array, the length of the array is not pre-determined. Let us take the above example for a dynamic array.
Step 1: Write the below code:
Sub DynamicArray_Ex()
Dim x() As Integer
ReDim x(10)
x(1) = 5
x(2) = 10
x(3) = 15
x(4) = 20
x(5) = 25
x(6) = 30
x(7) = 35
x(8) = 40
x(9) = 45
x(10) = 50
Range(“A1”).Value = x(1)
Range(“A2”).Value = x(2)
Range(“A3”).Value = x(3)
Range(“A4”).Value = x(4)
Range(“A5”).Value = x(5)
Range(“A6”).Value = x(6)
Range(“A7”).Value = x(7)
Range(“A8”).Value = x(8)
Range(“A9”).Value = x(9)
Range(“A10”).Value = x(10)
End Sub
Explanation:
In this code, the Array length is not pre-determined. Thus, we have used VBA ReDim Function to assign the length.
Dim x() As Integer
ReDim x(10)
Step 2: Run the code and we will see the result from cells A1-A10.
#3 – Create a Function Insert Month Names Using Array
Here we will see how to create a VBA function “Month names” using VBA Arrays Function in Excel.
Step 1: Write the below code in the VBA Editor.
Function Name_Of_Months()
Name_Of_Months = Array(“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)
End Function
Explanation:
The Name_of_Months function in VBA is created, and an array of elements is assigned to it. Here, the Array function holds the name of the months from January to December.
Step 2: Save the code in .xlsm format and run the code.
Step 3: Now, we will go to our current active Excel sheet and type =name of months in cell A1, and we will see the function we created. Choose the function and press Enter. We will see the result as January only.
Thus, to obtain all the names of months created, we need to select 12 cells in one row. Then, use the Name of Functions in A1 cells and press Ctrl+Shift+Enter.
Now we will have all the months from January to December in A1-L1 cells.
Important Things To Note
- VBA Arrays function in Excel is used to store/hold multiple values of a particular data type in the variable.
- VBA Arrays Function in Excel has nine different functions namely, VBA ARRAY, ERASE, FILTER, ISARRAY, JOIN, LBOUND, REDIM, SPLIT, and UBOUND.
- ARRAY: Returns an array with the given values.ERASE: It reinitializes the elements of arrays.
- FILTER: Returns an array having a subset of values (Variant)
- ISARRAY: It returns the value as a Boolean expression (True or False)
- JOIN: It returns a String (Text) having all the elements in an array.
- LBOUND: It returns the smallest subscript of a Long data type array.
- ReDim: It assigns the length of an array in a dynamic array.
- SPLIT: It returns an array with a specified no. of variant.
- VBA UNBOUND: It returns the largest subscript of a Long data type array.
- There are two more VBA Arrays Function in Excel available, namely, a two-dimensional array (length is determined in two dimensions only) and multi-dimensional array (length is determined in multiple dimensions).
- An array begins the count of values from zero, Array (0,0) signifies the first-row first column.
- VBA Arrays Function in Excel not working happens when we access a location that does not exist.
In the VBA Arrays Function in Excel, an Excel file must be saved as a macro-enabled workbook (.xlsm).
Frequently Asked Questions (FAQs)
Arrays are a “memory location” that stores more than one value of a particular data type in a variable. Arrays function in Excel VBA makes it easier to write and maintain code.
If an array has been defined, we can retrieve, sort and modify data in a shorter duration.
• To create an Array in VBA using Array function, please follow the steps below:
• Open a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (.xlsm)
• To open a VBA editor, click on Developer option and select Visual basic application (VBA), or we can directly use shortcut keys Alt+F11.
• In the VB editor window, select Insert and click on a new module in the Ribbon tab to open a new module.
• In the module, we can write our code to create an array.
For example:
Sub VBA_Array_Example1()
Here, the variable is declared to store our array.
Dim Arr As Variant
Write all the desirable elements of the Array.
End Sub
• Run the code by clicking on the Run option in VBA ribbon or F5 key and see the result in the active workbook in cells or in the message box.
Yes, the VBA Array function returns a variant with a particular data type array.
Download Template
This article must be helpful to understand the VBA Arrays Function in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Arrays Function in Excel. Here we explain how to use the VBA Arrays Function in Excel in different ways. You may learn more from the following articles –
Leave a Reply