Overflow error when attempting to assign a range to a variant array in Excel; VBA

I have two ranges containing data that I am assigning to two variant arrays called "ImportedData" & "TestData". The ranges are exactly the same size and contain the same data. I am importing this data from a spreadsheet i download from our ERP system. The only difference in the data in the two ranges is that I import the data and format it. Otherwise it is identical. This is part of a larger spreadsheet and I have done this import many, many times with no problem. For some reason I am now getting an "Overflow" error when attempting to assign the range to a variant array. I have attached a spreadsheet containing the two ranges (one on each worksheet). I have some simple code in a module to test this. The same issue occurs. Range "ImportedData" assigns to MITS with no problem. Range "TestData" results in the error.
Test.xlsmTest.xlsm
Please help!!!

Public Sub Test()

Dim MITS As Variant

MITS = Range("ImportedData")
MITS = Range("TestData")

End Sub

Open in new window

mikef715Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
No attachment I'm afraid.
0
mikef715Author Commented:
I just tried to upload again
0
Roy CoxGroup Finance ManagerCommented:
Norie the attachments are in the body of question.

the code doesn't make sense, what are you trying to do with MITS - assign the range address, it's value or what?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martin LissOlder than dirtCommented:
That's a really strange problem but this works.

Dim MITS As Range

Set MITS = Range("ImportedData")
Set MITS = Range("TestData")

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Martin

If I add a line then the Overflow returns

Public Sub Test()

Dim MITS As Range

Set MITS = Range("ImportedData")
Set MITS = Range("TestData")
MsgBox MITS
End Sub

Open in new window


Even this has the same error

Public Sub Test()

Dim MITS As Range

Set MITS = Range("ImportedData")
Set MITS = Nothing
Set MITS = Range("TestData")
MsgBox MITS
End Sub

Open in new window


It makes no sense to declare a variable assign a value then assign a different value. I
0
NorieAnalyst Assistant Commented:
I seem to recall that there is a limit to how many values you can populate an array from a range with.

When you exceed the limit you don't normally get an overflow error but you get a whole bunch of !N/A# values in the resultant array.
0
Roy CoxGroup Finance ManagerCommented:
It would be useful to see the full code and maybe what it is supposed to be doing. There is over 1.5 million cells in that range.
0
Fabrice LambertFabrice LambertCommented:
That's a really strange problem but this works.
Dim MITS As Range

Set MITS = Range("ImportedData")
Set MITS = Range("TestData")

Open in new window

Cept you obtain a range object instead of an array.

In theory, you should declare your variable as a dynamic array of variants:
Public Sub Test()
    Dim MITS() As Variant
    
    MITS = Range("ImportedData").Value
    MITS = Range("TestData").Value
End Sub

Open in new window

But there are still some limits, depend on the amount of data, an overflow error can still happen.

By looking at the files you provided, an alternative solution can be to query your workbook with an SQL query, and retrieve your data in an ADO recordset.
Public Sub test2()
    Const adOpenStatic = 3
    
    Dim cn As Object        '// ADODB.Connection
    Set cn = CreateObject("ADODB.Connection")
    
    cn.ConnectionString = getConnectionString(ThisWorkbook.FullName)
    cn.Open
    
    Dim SQL As String
    SQL = "SELECT *" & vbCrLf
    SQL = SQL & "FROM ImportedData;"
    
    Dim rs As Object        '// ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open SQL, cn, adOpenStatic
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Public Function getConnectionString(ByVal path As String) As String
    getConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
    getConnectionString = getConnectionString & "Data Source=" & path & ";"
    getConnectionString = getConnectionString & "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
End Function

Open in new window

NOTE:
If you refer to a a worksheet in your SQL query, the name must be enclosed in braquets and followed by a "$" in your WHERE clause.
SELECT *
FROM [Sheet1$];

Open in new window

0
mikef715Author Commented:
Sorry for the delay, I've been on the phone. Okay, let me try to answer several of the questions.

1) In my example code, I'm not trying to assign it twice. I comment out one or the other lines. I am simply asking why one assigns where the other one won't?

2) It cannot be a size issue because, as I have said, I have been doing this import for quite some time now. The previous import was actually 500 rows more than this one.

3) The reason I am assigning the range to an array is to process a report. It would take an extremely long time to do this given the size of the range if I don't use an array to do it.

4) if the array is too large, how did it import and be reformatted in the first place?

thanks,
0
Fabrice LambertFabrice LambertCommented:
4) if the array is too large, how did it import and be reformatted in the first place?
Can't answer this question.
Many techniques are available, with many frameworks written in many languages.

PS: Little mistake in my previosu answer, the worksheet name is in the FROM clause (of course).
0
Martin LissOlder than dirtCommented:
@Roy. This works.

Dim MITS As Range

Set MITS = Range("ImportedData")
Set MITS = Range("TestData")
MsgBox MITS.Address

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Martin, I added address as well as you have and I can see how that works. Once address is added Excel realises it's a String and changes it from one value to the next with no error because it's  not very large. If in the original Excel is interpreting it as  a Value then it will become extra large and the overflow occurs.
0
Martin LissOlder than dirtCommented:
I also think that the problem may have something to do with the calculation mode because after I close the asker's workbook I have to manually set calculation to xlCalculationAutomatic when I open one of my workbooks in order for my workbook to behave normally. I have to admit that I've only tested this with one of my workbooks so it could be my workbook that has the problem.
0
Roy CoxGroup Finance ManagerCommented:
Agreed, also Calculation mode wouldn't matter in a String.
0
mikef715Author Commented:
Hey folks,

If anyone can find why this is happening I would appreciate it. In any case, I have solved the problem by reducing the size of the range from 59 to 32 columns. Works fine. I didn't need the data in the other 27 columns for the particular report i was creating. I was just simply using an already named range to assign to the array. Thing is, this has been working for quite some time now. Wonder why it started all of a sudden?

Thanks,
0
Roy CoxGroup Finance ManagerCommented:
I think it was sheer size, you've reduced the range by about half. You could try saving the workbook as .xlsb which is designed for larger amounts of data.
0
Fabrice LambertFabrice LambertCommented:
I can only guess: Total data length (do not confuse with total cells).

Plus, due to the fact that you don't need all columns, I strongly suggest that you work with another method to import your data.
No need to handle uneeded columns.
0
Roy CoxGroup Finance ManagerCommented:
I think it was sheer size, you've reduced the range by about half. You could try saving the workbook as .xlsb which is designed for larger amounts of data.
0
mikef715Author Commented:
Thanks,

The workbook that I am having the issue in is, and has always been, saved as a .xlsb file.
0
mikef715Author Commented:
Also, I am having this issue after I import the file. Which is why it is a curious thing. I import the file, format some of the data inside an array. Write the data to a range and name it. I then in another macro try to assign the range to the array and get an error trying to assign a range of the same size that just came from an array?
0
Martin LissOlder than dirtCommented:
It may be your use of the Variant type. Where possible you should always explicitly define a variable to something other than Variant, since Variant is the slowest and largest of all the variable types.
0
Fabrice LambertFabrice LambertCommented:
It may be your use of the Variant type. Where possible you should always explicitly define a variable to something other than Variant, since Variant is the slowest and largest of all the variable types.
Worksheets can hold any data type.
What about cells in error ? (values arn't implicitly convertible)
What about null values ?
No other choice but variant array with this technique.

@mikef715:
I think we've been struggling about the "why" long enough already. Transfering data into an array work within limits internally managed by Excel, in your case, those limits are exceeded.

Time to work on a fix.
0
Martin LissOlder than dirtCommented:
Worksheets can hold any data type.
Yes, of course but what I'm saying is that when working with a range you usually should use the Range variable type and not Variant.
0
Fabrice LambertFabrice LambertCommented:
Anyway, the variant array technique does not work in OP's case (sad bc it is insanly fast).

I suggest either:
- Import via copy / paste.
- Import via recordset.
0
Martin LissOlder than dirtCommented:
Oh, I see now what you are talking about Fabrice, but when working with the Variant range you should set the variant to the the range's Values as in this example.
Dim DataRange As Variant 
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
' This loads DateRange with the sheet's values. Note the use of ".Value"
DataRange = Range("A1:C10000").Value ' read all the values at once from the Excel grid, put into an array
' rather than this which would (incorrectly for this example) make DatRange equivalent to the sheet. In other words updating a value in DateRange would directly update the sheet.
'Set DataRange = Range("A1:C10000").
For Irow = 1 To Ubound(DataRange)
    For Icol = 1 To 3
    MyVar = DataRange(Irow, Icol)
        If MyVar > 0 Then
        MyVar = MyVar * MyVar ' Change the values in the array
        DataRange(Irow, Icol) = MyVar
    End If
Next Icol
Next Irow
Range("A1:C10000").Value = DataRange ' writes all the results back to the range at once

Open in new window

0
Ejgil HedegaardCommented:
Cell BE618 on sheet Test Data with the value 3486111 are formatted as date.
Maximum for a date is 2958465, so Excel displays a lot of # in the cell.
It is the display value that is loaded into the array, and that is causing the overflow.

Change the cell format to General and it works.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikef715Author Commented:
Thanks Ejgil!

You are correct. That is what is causing the overflow error. The column is formatted as "general" and I haven't changed it. So, wonder why Excel changed the format on that one cell? Curious thing is why the cell below it BE619 which is the number 3467401 didn't get changed?

Anyway, thanks for figuring it out!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.