[RESOLVED] Bizarre Excel VBA Overflow error

[VBAhack]

I have a loop routine that loads a small rectangular range (36 x 12 cells) into a variant (array) for processing. This is done for every worksheet of every open workbook. For some reason, I get an Overflow (Run-time error '6') on a specific worksheet within a workbook and I can't figure out why.

Dim aV As Variant
aV = ws.Range("A1").Resize(36, 12)   '<== Run-time error '6' Overflow: for certain ws

The routine reading the cells is called once for each worksheet (meaning aV is initialized for each worksheet). The routine might process half a dozen worksheets in other workbooks but always fails on the 2nd worksheet of a specific workbook. I only get the error if the offending workbook is open. It doesn't happen with any other workbook I've tried. When I look at the offending sheet I can't visually see anything strange in any of the cells.

Has anyone seen this behavior and/or can suggest why it is happening?

[incidentals]

that is not enough code to really understand your problem...

why pass the range that is an array into an array in the first place

what do you need to do that needs an array?


here to help

[westconn1]

what do you need to do that needs an array?

much faster to process an array, than keep referencing a range

on a specific worksheet within a workbook and I can't figure out why.

can you post a sample that demonstrates the problem? or email if you do not wish to post

[VBAhack]

I've since determined there is something about the sheet itself that's the problem. Actually, upon further investigation, there are several sheets (maybe 8 out of 20) in the same workbook that result in the error. If I successively delete each sheet that causes the overflow error, I get no errors with the remaining sheets. Before I post or email anything I'll investigate further.

I was hoping the description would trigger something with someone, but I guess not (so far anyway).

[VBAhack]

Well, I've figured out what the problem is, but the workaround isn't very pleasant. I didn't know this, but when you read a cell's value with VBA, the default quantity captured is the FORMATTED value, not the raw data itself. I've since discovered that cell.Value2 is the raw data value (cell.Value is the FORMATTED value). If I process each cell within the range (instead of reading the range into a variant) and use the Value2 property, I get no error. What I found is that the data was formatted on the sheet as a date but the value was too large to be a date (thus, the overflow error) !!!!!

Therefore, the immediate workaround appears to be abandoning the preferred method of reading the range into a variant, but rather read each cell (with Value2 property instead of Value). I suppose an alternative would be to systematically reset the formatting on each sheet before reading the range, but I'd really rather not take that approach (I don't want to mess with workbooks that aren't mine).

Duh, the obvious solution is:

    Dim aV As Variant
    aV = Range("A1").Resize(4, 3).Value2

[westconn1]

What I found is that the data was formatted on the sheet as a date but the value was too large to be a date

why not fix this?

[VBAhack]

why not fix this?

The routine is used by others to look for open sheets that fit a certain data pattern. I have no control over what additional (non project related) sheets the user might have open, thus I have a strict rule of not modifying open sheets except for those that are determined to be project related. Actually, the error occurred on a non project related sheet. Using the Value2 property is an elegant solution.