What is the point of arrays in Excel VBA?

Question

I haven't done any programming for about 20 years and have started writing macros in Excel in the last 6 months. I understand what an array is and people seem to extol the virtues of them. I can't see what the advantages are. What am I missing?

Answer 1

Basically, Excel VBA treats an array as a single variable. The only real difference between an array variable and a single variable is the array has an index. Arrays would be useful when a task requires looping through variables. It could reduce the amount of code required to perform the task.

Answer 2

An Excel worksheet is an array. So in VBA, an array is a convenient way to represent a whole worksheet in one variable.

Answer 3

Mainly, people aren't computers with meticulously maintained variable table, they are not going to make up IDs everytime they need to store some scalar value especially related to a group. So there's the array.



The technical reason I doubt it is of any significance today.