The ReDim Statement in VBA

Aug 19, 2020 by Melanie Jenkins in Arrays

The ReDim statement is used to declare the size of a Dynamic array in VBA. It can later be used to re-declare the size of the array as many times as you need. It can only be used for Dynamic VBA Arrays (where the size of the array is NOT declared in the original declaration) and not Fixed VBA Arrays (where the size of the array IS declared in the original declaration). See our article on Using VBA Arrays.

ReDim stands in contrast to the Dim statement, which is used to declare many different types of variables in VBA.

Contents

ReDim Syntax

The syntax is ReDim [Preserve] name (bound)

[Preserve]	is optional – it is used to retain the original information contained in the array when it is being re declared.
Name	is required – the name of the array
Bound	is required – the size of the array

Using ReDim in a Procedure

ReDim can only be used at a Procedure level. It cannot be used at a Module or Global level. See my article on Using Global Variables in VBA for more information. You can declare your array as Dynamic, and at a later stage in your code, you can re-size the array using the ReDim statement. This is shown in the code example below.

Sub TestReDim
'declare the string array
	Dim strNames() as string 
'resize the string array to be able to hold 3 values
	ReDim strNames(2) as string 
	strNames (0) = "Jim"
	strNames (1) = "Dan"
	strNames (2) = "Mel"
	MsgBox Join(strNames, vbCrLf)
End Sub

When you run the code, you get the following result:

We can then ReDim once again in the same procedure.

Sub TestReDim
'declare the string array
	Dim strNames() as string 
'resize the string array to be able to hold 3 values
	ReDim strNames(2) as string 
	strNames (0) = "Jim"
	strNames (1) = "Dan"
	strNames (2) = "Mel"
	MsgBox Join(strNames, vbCrLf)
'resize the string array to be able to hold 4 values
	ReDim strNames(3) as string 
	strNames (0) = "Fred"
	strNames (1) = "Hannah "
	strNames (2) = "Sue"
	strNames (3) = "Steve"
	MsgBox Join(strNames, vbCrLf)
End Sub

The second time the code is run, two message boxes will appear, the first as above, and the second as per the graphic below.

If you had declared the string array initially to have five values, it would have been a FIXED array, and you would not be able to use the ReDim command.

Using ReDim Preserve

You may want to amend your array at a later stage in your code, but keep the information that is already in the array. If that is the case, you can use ReDim Preserve.

Sub TestReDim
'declare the string array
	Dim strNames() as string 
'resize the string array to be able to hold 3 values
	ReDim strNames(2) as string 
	strNames (0) = "Jim"
	strNames (1) = "Dan"
	strNames (2) = "Mel"
	MsgBox Join(strNames, vbCrLf)
'resize the string array to be able to hold 3 values
	ReDim Preserve strNames(4) as string 
	strNames (3) = "Sue"
	strNames (4) = "Steve"
	MsgBox Join(strNames, vbCrLf)
End Sub

When you run this procedure, you will get the first message box with the 3 names.

When you click OK, the second message box appears.

The first 3 names are still in the list but you have now added 2 more names to the array while keeping your original data by using ReDim Preserve.

If you make the array bigger as in the example above, you will keep all the existing values in the array, but if you make the array smaller, you would lose parts of the array that no longer exist.

Using ReDim to Loop Through Excel Cells

Let us consider the following Excel sheet below:

We want to check if the balance in column H is positive or negative by looping through all the rows in the sheet. However, we might not know how many rows are contained in the worksheet. We can use a Dynamic Array, and then the ReDim statement to help us with the loop.

Option Explicit

Public n As Long, i As Long, Status() As String

Private Sub ()
'count the rows in the list of clients
	n = ClientList.Range("H5", ClientList.Range("H5").End(xlDown)).Rows.Count
'redim the Status to have the amount of rows we have counted in the 'array
	ReDim Status(n)
'loop through the array and get the status of the account
	For i = 0 To n-1
		If ClientList.Range("H5").Offset(i, 0) &amp;amp;lt; 0 Then
			Status(i) = "Debit"
		Else
			Status(i) = "Credit"
		End If
	Next i
End Sub

In the code above, the string array Status() is declared as a public array. The Long variable (n) is populated to give us the number or rows in column H. The Array is then redeclared using the ReDim statement where the bound number in the array becomes the value stored in the variable n. Finally, the code loops through the rows by looping through the array to find out the value that we require and populates the array.

In Summary

In this article we have learnt what a ReDim statement is used for in Arrays. Arrays are an extremely useful part of VBA coding and using the ReDim statement adds extra flexibility to the functionality of the Dynamic Array.