This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Arrays in Visual Basic for Applications macros Part four of a five-part series of blogs
You can use arrays (multiple variables) in VBA to speed up code - and they are also invaluable for processing CSV files and the like. Arrays Declaring and Using Static Arrays Dynamic Arrays using REDIM Multi-dimensional Arrays (this blog) Splitting Text into Arrays This blog is part of our Excel VBA tutorial. We also run training courses for businesses or individuals in Microsoft Excel and in VBA programming.

Arrays in Visual Basic for Applications macros

Part four of a five-part series of blogs

You can use arrays (multiple variables) in VBA to speed up code - and they are also invaluable for processing CSV files and the like.

This blog is part of our Excel VBA tutorial. We also run training courses for businesses or individuals in Microsoft Excel and in VBA programming.

Posted by Andy Brown on 24 February 2012

Multi-dimensional Arrays

I find it very hard to write enthusiastically about multi-dimensional arrays. When I first started my programming career (1986, if you must know), the first system I worked on was written in an old language called Fortran, and made extensive use of multi-dimensional arrays. The resulting code was hard to understand and write.

You can - and should - avoid ever using multi-dimensional arrays, but there will always be people who like to make life hard, and this blog page is written for them!

Avoiding Multi-dimensional Arrays

Let's start with how to avoid them. Suppose that you want to read the names and ages of the people in the spreadsheet shown below into arrays:

You might want to read the names and ages of these people into an array.

The easy way to do this is to create two arrays: one for the names, and another for the ages. Here (without any further explanation) is code which would do just that:

Option Explicit

Sub ListPeople()

'declare two arrays of unknown length

Dim PersonName() As String

Dim PersonAge() As String

'initially there are no people

Dim NumberPeople As Integer

NumberPeople = 0

'loop over all of the people cells

Dim PersonCell As Range

Dim TopCell As Range

Dim PersonRange As Range

Set TopCell = Range("A1")

Set PersonRange = Range(TopCell, _

TopCell.End(xlDown))

For Each PersonCell In PersonRange

'we've found another person!

NumberPeople = NumberPeople + 1

'for each person found, extend both arrays

ReDim Preserve PersonName(NumberPeople-1)

ReDim Preserve PersonAge(NumberPeople-1)

'now store the name and age of the new person

PersonName(NumberPeople-1) = PersonCell.Value

PersonAge(NumberPeople-1) = PersonCell.Offset(0, 1).Value

Next PersonCell

'list out contents to show worked

Dim i As Integer

For i = 1 To NumberPeople

Debug.Print PersonName(i-1), PersonAge(i-1)

Next i

End Sub

If you're wondering what you'd do if you got a 3rd column, the answer is: create a 3rd array. If you created a 4th column, maybe it would be time to think of creating a database application!

Declaring Multi-dimensional Arrays

To create an array in more than one dimension, separate the arguments with commas. For example:

'the following array has 4 x 5 x 6 elements = 120

Dim PointlessArray(3, 4, 5)

Depressingly, you can have up to 32 dimensions. IMHO, two is one too many!

For our example above, you could list out the cell contents as follows:

Option Explicit

Sub ListPeople()

'declare array which will become 2-dimensional

Dim PersonDetails() As String

'initially there are no people

Dim NumberPeople As Integer

NumberPeople = 0

'loop over all of the people cells

Dim PersonCell As Range

Dim TopCell As Range

Dim PersonRange As Range

Set TopCell = Range("A1")

Set PersonRange = Range(TopCell, _

TopCell.End(xlDown))

For Each PersonCell In PersonRange

'we've found another person!

NumberPeople = NumberPeople + 1

'for each person found, extend array dimension

ReDim Preserve PersonDetails(1, NumberPeople - 1)

'now store the name and age of the new person

PersonDetails(0, NumberPeople - 1) = PersonCell.Value

PersonDetails(1, NumberPeople - 1) = PersonCell.Offset(0, 1).Value

Next PersonCell

'list out contents to show worked

Dim i As Integer

For i = 1 To NumberPeople

Debug.Print PersonDetails(0, i - 1), PersonDetails(1, i - 1)

Next i

End Sub

There are a couple of points worth noting about this:

When you first declare an array which you will later resize, don't put any numbers as subscripts.
When you change the size of a multi-dimensional array using REDIM PRESERVE, you can only change the size of the last subscript.

There - that's over with!

Parts of this blog
Arrays Declaring and Using Static Arrays Dynamic Arrays using REDIM Multi-dimensional Arrays (this blog) Splitting Text into Arrays

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Multi-dimensional Arrays

Avoiding Multi-dimensional Arrays

Declaring Multi-dimensional Arrays

Head office

London

Manchester