Expected array ошибка vba excel

Can anyone help me?

I have been getting a compile error (…: «Expected Array») when dealing with arrays in my Excel workbook.

Basically, I have one ‘mother’ array (2D, Variant type) and four ‘baby’ arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes. Unfortunately, the final of the baby arrays craps out (giving the «Compile Error: Expected Array»). Strangely, if I remove this final baby array (‘final’ — as in the order of declaration/definition) the 2nd to last baby array starts crapping out.

Here is my code:

 Public Mother_Array() as Variant, BabyOne_Array(), BabyTwo_Array(), BabyThree_Array(), BabyFour_Array() as Double 'declare may other variables and arrays, too

Sub MainMacro()
    'do stuff

   Call SunRaySubRoutine(x, y)

    'do stuff

    Range("blah") = BabyOne_Array: Range("blahblah") = BabyTwo_Array
    Range("blahbloh" = BabyThree_Array: Range("blahblue") = BabyFour_Array

End Sub

Sub SunRaySubRoutine(x,y)
    n = x * Sheets("ABC").Range("A1").Value + 1

    ReDim Mother_Array(18, n) as Variant, BabyOne_Array(n), BabyTwo_Array(n) as Double
    ReDim BabyThree_Array(n), BabyFour_Array(n) as Double

    'do stuff

    For i = 0 to n

        BabyOne_Array(i) = Mother_Array(0,i)
        BabyTwo_Array(i) = Mother_Array(2,i)
        BabyThree_Array(i) = Mother_Array(4,i)
        BabyFour_Array(i) = Mother_Array(6,i)
    Next        

End Sub

I have tried to declare all arrays as the Variant type, but to no avail. I have tried to give BabyFour_Array() a different name, but to no avail.

What’s really strange is that even if I comment out the part which makes the BabyFour_Array(), the array still has zero values for each element.

What’s also a bit strange is that the first baby array never craps out (although, the 2nd one crapped out once (one time out of maybe 30).

BANDAID: As a temporary fix, I just publicly declared a fifth dummy array (which doesn’t get filled or Re-Dimensioned). This fifth array has no actual use besides tricking the system out of having the «Compile Error: Expected Array».

Does anyone know what’s causing this «Compile Error: Expected Array» problem with Excel VBA?

Thanks,

Elias

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