VBA - what are arrays?

Arrays are practical tools when programming in Excel VBA. In this practical tip, we explain what is hidden behind the term.

What are arrays in Excel?

Arrays are data fields in which groups of values ​​are stored for further processing. Arrays can read larger text files line by line and then quickly access the individual elements. There are two types of arrays.

• One speaks of an array with a fixed size if the number of elements to be stored is specified in the declaration. An example of the spelling is "Dim myArray (4) As String". In this case, the array can hold five elements.
• In the case of a dynamic array, however, the number is not specified in the declaration; this is done at runtime. In our example, this corresponds to "Dim myArray () As String".

Why you need arrays when programming

Arrays collect data of the same type. In addition to the clarity of the code, arrays bring other advantages.

• If you push the data into an array, they are preloaded and are immediately available when called up - this means speed.
• Arrays are actually always used when you don't know how many variables you need at the start of your work.