An array is a type of variable which differs from a ‘normal’ variable in that it can hold multiple values rather than just one value at a time.  There can be a few reasons why you would receive an “Expected array” error.

Let’s look at some code that we have to loop through a range of cells on an Excel worksheet.

Option Explicit
Public n As Long, i As Long, Status As String
Private Sub GetStatus()
'count the rows in the list of clients
	n = ClientList.Range("G5", ClientList.Range("G5").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 = 1 To n
		If ClientList.Range("G5").Offset(i, 0) < 0 Then
			Status(i) = "Debit"
		Else
			Status(i) = "Credit"
		End If
	Next i
End Sub

The code above looks perfect, but when we run it – this error will occur:

The code is written to loop through the following rows in Excel:

The first line of the code will count how many rows are in the list, and then it will ReDim the Status variable to be able to contain that amount of rows.   The Status variable has been declared to hold multiple values – and the code will loop through the cells from G5 to the last cell, and store either Debit or Credit as the status for that cell, depending on what is in the appropriate cell.

At a glance, the code looks fine, but there clearly is a problem as we get a compile error.   This type of error can be hard to find.

If we look closely at the code, and at the error – the error says ‘expected array’ – and we have re-dimmed the variable Status in line 2 of the code.   However, in order to ReDim an Array, we first have to actually declare an Array – and there lies our problem.  We have declared the Status variable as a String – but we have NOT declared it as a String ARRAY. The solution is annoyingly simple – which is why so many people make the same error.  The correct code is below – can you spot the amendment?

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

Private Sub GetStatus()
'count the rows in the list of clients
	n = ClientList.Range("G5", ClientList.Range("G5").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 = 1 To n
		If ClientList.Range("G5").Offset(i, 0) < 0 Then
			Status(i) = "Debit"
		Else
			Status(i) = "Credit"
		End If
	Next i
End Sub

Look at the Public variables at the top of the module – just under Option Explicit.

In the first code snippet – the variables look like this:

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

But in the second code snipped, the variables now look like this:

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

The Status has now been declared as an ARRAY – simply by adding the brackets behind the word STATUS.  This now enables the variable Status to hold multiple values, and not just one value.

So in summary, if you get this message, there is a variable in your code that is being expecting to be populated with multiple values, but your declaration of the variable is indicating that only a single value can go into that variable.   You need to check the syntax of your code carefully – it may be that you have an error in your code and you DON’T actually want an array, or you may have declared the variable incorrectly.

See also: Can’t Assign to Array