How to Fix the Error “Can’t assign to array” in VBA
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.
Contents
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:
- We should assign values to each element of an array separately or one-by-one using the array’s indexes
- 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:
- What the datatype should be for the array on the left side of the assignment statement
- What should be the size of the array
- 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.