An array is nothing but a collection of elements of the same data type. Arrays keep your data organized for easy usage and maintenance. In VBA, Arrays can either be static or dynamic. Static arrays have a pre-determined size and number of elements — and they are not re-sizable. On the other hand, dynamic arrays offer re-sizing depending on need.

First, a couple sample arrays…

To understand how to fix this error, you need to first understand the basics of how to use arrays in VBA.

Here are some code snippets that demonstrate how to declare, initialize and use simple arrays.

• An array to hold a list of car brands

Sub array_cars()

'declaring variable
Dim arr_cars() As Variant

'define the size of the array
ReDim arr_cars(5)

'initialize array elements
 arr_cars(0) = "Benz"
 arr_cars(1) = "Maruthi"
 arr_cars(2) = "Hyundai"
 arr_cars(3) = "Ford"
 arr_cars(4) = "Nissan"

'print all elements
For i = LBound(arr_cars) To UBound(arr_cars)
    Debug.Print arr_cars(i)
Next

End Sub

• An array to hold the details student records and their total grades in a 2-dimensional array.

Sub array_2d_demo()

'declaring and defining size of an array
'3 means 4 rows starting from 0 to 3 and 1 means 2 columns starting from 0 to 1
Dim arr_stu(3, 1) As Variant

'initialize array elements
 arr_stu(0, 0) = "Jackson"
 arr_stu(0, 1) = 89
 arr_stu(1, 0) = "Vincent"
 arr_stu(1, 1) = 57
 arr_stu(2, 0) = "Sathyan"
 arr_stu(2, 1) = 90
 arr_stu(3, 0) = "Viji"
 arr_stu(3, 1) = 67
 
'print all elements
For i = LBound(arr_stu) To UBound(arr_stu)
    
    Debug.Print "Student Name : " & arr_stu(i, 0)
    Debug.Print "Marks : " & arr_stu(i, 1)
    Debug.Print "End of record"
Next

End Sub

Output :

Student Name : Jackson

Marks : 89

End of record

Student Name : Vincent

Marks : 57

End of record

Student Name : Sathyan

Marks : 90

End of record

Student Name : Viji

Marks : 67

End of record

Compile error: “Can’t assign to array” causes

This is a compile time error that occurs when you try to assign improper or irrelevant data to an array. Below are some reasons that can cause this error. We’ll have a look at these causes and their corresponding solutions one-by-one.

Assigning a string to an array or an element of an array

Suppose we have an array defined and we want to initialize the array with data, then we should follow either of these methods:

1. We should assign values to each element of an array separately or one-by-one using the array’s indexes
2. Assigning one array to another array can be done if the array to which data has to be assigned is a dynamic resizable array and the two arrays are the same datatype.

If the above rules are not followed then the compile time error  “Can’t assign to array” will occur.

Let’s take a look at a couple examples to understand better.

Sub array_assign()

'declaring variable
Dim arr_cars1() As Variant

'initialize array elements for one array. Invalid assignment
arr_cars1() = "abcd"

End Sub

In this example  the line arr_cars1() = "abcd" does not specify to which element of the array , we need to assign the string value “abcd”. Hence it generates the compile error “Can’t assign to array.”

To address this, in the code below we define the size of the array and assign the value to any one element of the array.

Sub array_assign()

'declaring variable
Dim arr_cars1() As Variant

'define the size of the array
ReDim arr_cars1(3)

'initialize array elements for one array. Invalid assignment
arr_cars1(0) = "abcd"

End Sub

Assigning a value of different datatype to an element of an array

In the following example, we try to assign that values of a string array to an integer array. This again leads to the compile error “Can’t assign to array.”

Sub array_assign()

'declaring variable
Dim arr_cars1() As String
Dim arr_cars2() As Integer

ReDim arr_cars1(5)

'initialize array elements for one array
 arr_cars1(0) = "Benz"
 arr_cars1(1) = "Maruthi"
 arr_cars1(2) = "Hyundai"
 arr_cars1(3) = "Ford"
 arr_cars1(4) = "Nissan"

arr_cars2() = arr_cars1()

'print all elements of first array
For i = LBound(arr_cars1) To UBound(arr_cars1)
    Debug.Print arr_cars1(i)
Next

'print all elements  of second array
For i = LBound(arr_cars2) To UBound(arr_cars2)
    Debug.Print arr_cars2(i)
Next

End Sub

This can be solved if the datatype of the array arr_cars2() is set to the same datatype as arr_cars1().

Note: This compile error will occur even if the array on the left side of the assignment is of the variant datatype.

Split a string to an array with a pre-defined size

The result of a split function is an array. This result cannot be assigned to an array for which size is already set. Doing so will cause — you guessed it — the compile error “Can’t assign to array.”

Sub split_array()

    'declare an array and define it's size
    Dim arr_days(7) As Variant
    
    'initialize the value of string
    strdays = "sunday,monday,tuesday,wednesday,thursday,friday,saturday"
    
    'split and assign to an array
    arr_days = Split(strdays, ",")
    
End Sub

The solution is that the array should not have a pre-defined size as I demonstrate in the example below.

Sub split_array()

    'declare an array and define it's size
    Dim arr_days As Variant
    
    'initialize the value of string
    strdays = "sunday,monday,tuesday,wednesday,thursday,friday,saturday"
    
    'split and assign to an array
    arr_days = Split(strdays, ",")
    
    'try printing all values
    For i = LBound(arr_days) To UBound(arr_days)
        Debug.Print arr_days(i)
    Next
    
End Sub

Output:

sunday

monday

tuesday

wednesday

thursday

friday

saturday

Below is another example where we try to assign the return value of a split function to a string array whose size is pre-defined.

Sub split_array()

    'declare an array and define it's size
    Dim arr_num(3) As String
    
    'initialize the value of string
    strdays = "1,2,3"
    
    'split and assign to an array
    arr_num() = Split(strdays, ",")
    
    'try printing all values
    For i = LBound(arr_num) To UBound(arr_num)
        Debug.Print arr_num(i)
    Next
    
End Sub

In the code snippet, removing that “3” in the dim statement can resolve this compile error.

Conclusion

The compile error “Can’t assign to array” can be easily resolved or avoided by properly following the rules for assignment of values to an array.

To solve this problem, you have to consider:

1. What the datatype should be for the array on the left side of the assignment statement
2. What should be the size of the array
3. Can the size be pre-defined or not

Remember, the datatype of the two arrays should be of the same datatype for assignment to happen successfully.