Overflow when trying to load range into a variant array

Question

Just got this error, which is really weird. The code worked fine before, now it doesn't. I tried to restart Excel but got the same error. This is the part of the code that produces the error:

Dim EmpRes as Worksheet
Set EmpRes = Worksheets("EmpRes")

Dim ER() As Variant
ER = EmpRes.Range("Q1").CurrentRegion  ' <- This line is the cause of the Overflow error

The sheet EmpRes is empty when you run the code. Data in another sheet is then autofiltered and copied into it. In total there's 518 rows and 36 columns in the sheet when the error occurs.

If I change it to:

ER = EmpRes.Range("A1:C5")

it works. But

ER = EmpRes.Range("A1:C100")

doesn't.

Anyone knows why this might be? /Jens

Maybe use Debug.Print EmpRes.Range("Q1").CurrentRegion.Address before the problem line to see what the current region is. Also, to load things into an array, you could just declare ER as a simple Variant (so just use Dim ER As Variant rather than ER()) and then just use ER = EmpRes.Range("Q1").CurrentRegion.Value (once you make sure that this range is what you think it is). — John Coleman
– John Coleman, Commented Jan 5, 2021 at 14:17
Debug.Print EmpRes.Range("Q1").CurrentRegion.Address printed $A$1:$AJ$518 So no error there. It's around 18000 cells, is that a lot? Some rows contain strings, two contain dates, some contain integers and some are empty. — Jens
– Jens, Commented Jan 5, 2021 at 15:19
Changing from ER() to ER didn't help. Tried with and without .value — Jens
– Jens, Commented Jan 5, 2021 at 15:21
I'm using Excel 365 64bit/win10_64bit and couldn't replicate your problem. What version are you using? — Ricardo Diaz
– Ricardo Diaz, Commented Jan 5, 2021 at 16:11
I'm using the same version. The thing is, this code worked before. I changed some stuff today elsewhere in the code today and the error came. But what I changed had nothing to do with this problem. — Jens
– Jens, Commented Jan 5, 2021 at 16:28

Jens · Accepted Answer · 2021-01-05 19:36:32Z

0

Ok I solved it. I have no clue why the problem emerged in the first place though.

Somehow the cells in the column AB, which contains 8 digit numbers, were formatted as dates. The data in those cells showed up as #################

Even if I changed them into integers they would change back when I ran the code again.

By adding the line EmpRes.Range("AB2:AB" & LastRow).NumberFormat = "0" before the code the problem was solved. Then the cells showed the 8 digit numbers again. And I was able to load the range into the array!

answered Jan 5, 2021 at 19:36

Jens

1951 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Overflow when trying to load range into a variant array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related