CODEWITHSUNDEEP
excelvba
Ismail Laabdi
Ismail Laabdi

Reputation: 25

VBA : Overflow error in multidimensionnal array init

I got a bizzare overflow message error with this code and I can't understand why. Could you please help ? I am using exactly the same code in another vba module and it works.

Dim tab_base As Variant
tab_base= Worksheets("Test").Range("A1:AL1492").Value

Thanks,

Ismail

Upvotes: 1

Views: 352

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You must have some invalid cells in the range. I suspect mostly some cells formatted as Date with a value that is either too large or negative.

To be on the safe side and get the numeric values independent of the format, use the recommended .Value2. So try

tab_base= Worksheets("Test").Range("A1:AL1492").Value2

Then you get everything in the array as either strings or numbers (for date: number). You can later convert numbers to dates if necessary, but you can also check them for errors before.

For example, before converting some cell to a date, something like this:

Dim d As Date
On Error Resume Next
d = tab_base(i, j)
if Err.Number <> 0 Then ' invalid date, this was a cell causing overflow

p.s.: check for example if Err.Number = 6, it means you really had a date overflow (thx Mat's)

Upvotes: 3

Related Questions