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 three 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 (this blog) Multi-dimensional Arrays 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 three 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

Dynamic Arrays using REDIM

Nearly every array I've ever used in VBA has been dynamic: that is, I haven't known how big the array would end up being when I first declared it.

A Typical Example - Reading in Cell Values

Suppose that you want to read the values of a column of cells into an array:

Suppose that you want to read all of the names in column A into an array, but you're not sure how many there will be.

To do this, you need to dimension the array initially with no specific number of items, then redimension it for each new person found. The program shown below would produce the following output:

The VBA code below would show a message containing the people found.

The code which produced this message box was as follows (it's broken down below into its different parts):

Sub ListPeople()

'declare array of unknown length

Dim PersonName() 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

'for each person found, extend array

NumberPeople = NumberPeople + 1

ReDim Preserve PersonName(NumberPeople-1)

PersonName(NumberPeople-1) = PersonCell.Value

Next PersonCell

'list out contents to show worked

Dim msg As String

Dim i As Integer

msg = "People in array: " & vbCrLf

For i = 1 To NumberPeople

msg = msg & vbCrLf & PersonName(i-1)

Next i

MsgBox msg

End Sub

Note the use of the keyword Preserve. Without this, Excel would create a new array containing one more value than the predecessor - but lose any previous contents in the process.

Redimensioning an array like this takes a copy of the old array and puts it into a new one. For 6 names this will run like lightning, but for 6,000 you'll start noticing speed issues.

Breaking Down the Code

Here's how the code above works. The first thing to do is to declare an array:

Sub ListPeople()

'declare array of unknown length

Dim PersonName() As String

'initially there are no people

Dim NumberPeople As Integer

NumberPeople = 0

Initially, then, we don't know how big the array is. The code then creates a variable called PersonRange to refer to the column of names:

'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))

Now that we've got a reference to the range of cells from A1 down to the bottom of the list, we can loop over them:

For Each PersonCell In PersonRange

For each cell, we now:

Increment the number of people found to one more than it used to be.
Increase the size of the array, being careful to preserve any existing values.
Store the name just found in the last array entry (the one we've just created by extending the array).

Here is the code to do this (including the end of the loop):

'for each person found, extend array

NumberPeople = NumberPeople + 1

ReDim Preserve PersonName(NumberPeople-1)

PersonName(NumberPeople-1) = PersonCell.Value

Next PersonCell

We now want to check if this has worked, so we first create a string variable to hold the start of a message:

'list out contents to show worked

Dim msg As String

Dim i As Integer

msg = "People in array: " & vbCrLf

Finally, we add each name stored in our array to the end of this message (with a carriage return code before each), then display the results:

For i = 1 To NumberPeople

msg = msg & vbCrLf & PersonName(i-1)

Next i

MsgBox msg

And just for completeness:

End Sub

This is a good example of when we gained nothing by using an array - we could have just read in each cell's value and then printed it out directly to the Immediate window.

Parts of this blog
Arrays Declaring and Using Static Arrays Dynamic Arrays using REDIM (this blog) Multi-dimensional Arrays 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

Dynamic Arrays using REDIM

A Typical Example - Reading in Cell Values

Breaking Down the Code

Head office

London

Manchester