{"id":100579,"date":"2021-10-06T09:00:41","date_gmt":"2021-10-06T03:30:41","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=100579"},"modified":"2021-10-06T11:50:56","modified_gmt":"2021-10-06T06:20:56","slug":"excel-vba-arrays","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/excel-vba-arrays\/","title":{"rendered":"Excel VBA Arrays"},"content":{"rendered":"

An array can store one or more values in memory and the values should be of the same data type. The elements of an array are the individual values in the collection of an array. They are stored in a continuous order indexing from 0 to nth value. Now, let’s see the types, advantages of the vba array in excel.<\/p>\n

Advantages of VBA array:<\/h3>\n

VBA array function\u2019s benefits:<\/p>\n

1. Grouping logically related data<\/strong><\/p>\n

If you are storing a list of groceries such as fruits, vegetables etc. Then, you can use a single array variable that has a separate location for categories i.e., vegetables, fruits.<\/p>\n

2. Easy to maintain the codes<\/strong><\/p>\n

It\u2019s easy to maintain the code because defining a single variable is enough, instead of defining more than one variable.<\/p>\n

3. Better performance<\/strong><\/p>\n

Retrieving, sorting, and modifying the data in an array are faster.<\/p>\n

Array Types in VBA:<\/h3>\n

There are two types of arrays and those are:<\/p>\n

1. Static<\/strong> \u2013 Static type array will have a fixed number of elements and it can\u2019t be changed further. These kinds of arrays are efficient when the user is working with the fixed entities. Some of the examples of static arrays are Gender, Number of months in a year, Number of days in a week etc.<\/p>\n

Static array syntax:<\/strong><\/p>\n

Dim arrayName (n) as datatype\r\n<\/pre>\n
This syntax declares an array name with the size of n variables and the data type refers to the type of data in an array.
\nExample:<\/strong><\/strong><\/p>\n
Dim ArrayGender(3) As String\r\n<\/pre>\n
2. Dynamic<\/strong> \u2013 Dynamic type of an array will not have a fixed number of elements and it can be changed further also. These kinds of arrays are useful when the user is not sure about the size of the entities. Some of the examples of dynamic array are employee_id, color_names<\/p>\n
Dynamic array syntax<\/strong><\/p>\n
Dim arrayName() as datatype\r\nReDim arrayName(4)\r\n<\/pre>\n
This \u201cDim arrayName() as datatype\u201d syntax declares an array with a name and the data type. \u201cReDim arrayName(4)\u201d declares the array size.<\/p>\n
Example:<\/strong><\/p>\n
Dim ArrayEmployee() As Variant\r\n<\/pre>\n
VBA Array Declaration<\/h3>\n
An array variable uses parenthesis while declaration but otherwise the declaration of an array variable is the same as the other variables. There are three methods to declare an array:<\/p>\n
Method 1: Variant Arrays<\/h4>\n
Dim arrEmployees()\r\n<\/pre>\n
In this method, we are declaring an array without mentioning the size. With a variant type array, the user need not define an array size.<\/p>\n
Method 2: Non – Variant Arrays<\/h4>\n
Dim arrEmployees()\r\nDim arrEmployees() A s String\r\nReDim arrEmployees(1 to 100)\r\nEnd Sub\r\n<\/pre>\n
With non-variant arrays, the user can define the array size before assigning the values to the array. Here, the user uses the ReDim command to set the size of an array.<\/p>\n
Method 3: Static Array<\/h4>\n
Dim arrEmployees(100)\r\n<\/pre>\n
In this method, we are declaring an array mentioning the array size of 100. Although the array size is 100, it can hold 101 data values as array index starts from 0.<\/p>\n
Note:<\/strong><\/p>\n