A table is nothing more than a collection of items of the same type of data. Tables store your data in an organized way for easy use and maintenance. In VBA the tables can be static or dynamic. Static arrays have a predefined size and number of elements – and they cannot be adjusted. On the other hand, dynamic networks make it possible to adjust the size of the devices as needed.

First a few sample tables….

To understand how to correct this error, you must first understand the basics of using arrays in VBA.

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

  1. Table for saving the list of car brands

Sub array_cars()

Explain variable
Dim arr_cars() Variant

Determine the size of array
ReDim arr_cars(5)

Initialization of the elements of array
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

Last Subsection

  • A table for storing student records and their total grades in a two-dimensional table.

Sub array_2d_demo()

Specify and define the size of table
. 3 means 4 rows from 0 to 3 and 1 means 2 columns from 0 to 1
Dim arr_stu(3, 1) Optional

Initialize elements of array
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 marker: & arr_stu(i, 1)
Debug.Print end entry
Next

Last Subsection

Get out of here:

Name of the student: Jackson

Plates: 89

End of registration

Name of the student: Vincent

Plates: 57

End of registration

Name of the student: Sathyan

Plates: 90

End of registration

Name of the student: Viji

Plates: 67

End of registration

Compilation error: Matrix reasons cannot be allocated

alt= compilation error : The table cannot be assigned to data-original width=287 data-original height=206 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/01/How-to-Fix-the-Error-Cant-assign-to-array-in.png data-ez= />

alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Facebook share data-ezsrc=/utilcave_com/social/fb_share.png />

This is a compilation error that occurs when you try to assign incorrect or irrelevant data to a table. Here are some causes that may lead to this error. We will examine these reasons and the associated solutions one by one.

Assigning a string to an array or array element

Assuming we have defined a table and want to initialize it with data, we have to use one of the following methods:

  1. The values shall be assigned to each element of the table separately or separately by means of table indices.
  2. The assignment of one table to another can be made if the table to be assigned is a dynamically adjustable table and if both tables have the same data type.

If the above rules are not followed, a compilation error occurs that cannot be assigned.

Let’s look at a few examples to get a better understanding.

Sub array_allocation()

Explain variable
Dim arr_cars1() Variant

Initialize the elements of a table. Incorrect assignment
arr_cars1() = abcd

Last Subsection

In this example, the string arr_cars1() = abcd does not indicate to which element of the array the value of the string abcd should be assigned. Hence the compilation error Impossible to assign an array.

alt=undefined element and a data-orig-width=960 data-orig-height=540 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/01/1609906249_230_How-to-Fix-the-Error-Cant-assign-to-array-in.png data-ez= /> error.

alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Facebook share data-ezsrc=/utilcave_com/social/fb_share.png />

To solve this problem, in the following code we define the size of the array and assign a value to each element in the array.

Sub array_allocation()

Explain variable
Dim arr_cars1() Variant

Determine the size of array
ReDim arr_cars1(3)

Initialize the elements of a table. Invalid assignment
arr_cars1(0) = abcd

Last Subsection

Assignment of a value of another data type to a table element

The following example tries to assign values from a series of strings to a series of integers. This in turn causes the compilation error Cannot be assigned to array.

Sub array_allocation()

Explain the variable
Dim arr_cars1() a string
Dim arr_cars2() a whole number

ReDim arr_cars1(5)

The elements of an array initialize
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 the first array
For i = LBound(arr_cars1) To UBound(arr_cars1)
Debug.Print arr_cars1(i)
Next

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

Last Subsection

The solution is that the table does not have to have a predefined size, as I will show in the following example.

Sub split_array()

Explain an array and set its size in
Dim arr_days as variant.

Initialize the value of the string
strdays = Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

Divide and distribute table
arr_days = Split(strdays, ,)

Try printing all values
For i = LBound(arr_days) To UBound(arr_days)
Debug.Print arr_days(i)
Next

Last Subsection

Get out of here:

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Here is another example where we try to assign the return value of the split function to a series of strings with a predefined size.

Sub split_array()

declare an array and set its size in
Dim arr_num(3) As a character string

Initialize the value of the string
strdays = 1,2,3

Split and assign array
arr_num() = Split(strdays, ,)

Try printing all values
For i = LBound(arr_num) To UBound(arr_num)
Debug.Print arr_num(i)
Next

Last Subsection

In the code nipple, removing the 3 in the dimming operator can correct this compilation error.

alt= compilation error : Cannot be assigned to an array — occurs because the array size is set to 3 data-orig-width=790 data-orig-height=557 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/01/1609906250_443_How-to-Fix-the-Error-Cant-assign-to-array-in.png data-ez= />.

alt=Pin it! data-ezsrc=/utilcave_com/social/pin_it.png />

alt=Facebook share data-ezsrc=/utilcave_com/social/fb_share.png />

Completion

Compilation error The lack of assignment of values to an array can easily be corrected or avoided if the rules for assigning values to an array are followed correctly.

In order to solve this problem, we need to think about it:

  1. What should be the data type for the table to the left of the command manager?
  2. How big does it have to be?
  3. The size can be preset or not

Note that the data type of the two tables must be the same to make the job succeed.

excel vba function can t assign to array,vba can't assign to array split,vba function as array,excel vba return array in function,excel vba function as string array,vba assign range to array,vba return array from function,vba array length,redim vba,vba object required,byref argument type mismatch,vba array practice,excel vba hardcode array,vba array square brackets,excel 2016 vba max array size,excel macro code snippets,excel vba populate array with cell values,vba copy part of array to another array,vba add one array to another,copying array vba,move data from one array to another,vbscript copy array to another,vba set array equal to another array,vba function return array can't assign to array,vba redim,vba array,how to reference an array vba

You May Also Like

Chromecast with Google TV – Price, Specs, & Setup Guide

In September 2020, Google officially unveiled its new streaming device Chromecast with…

Apex Legends Crashing PC Fix 2020

Various problems can occur during gambling. If you’re playing the new royal…

🥇 Create Geolocated Groups in Telegram  Step by Step Guide ▷ 2021

Discover groups in your area and start communicating without having to exchange…

How to initialize and get biases in a keras model?

In this tutorial, we will see how to initialize Keras’ model and…