CODEWITHSUNDEEP
excelvba
Chris Reynolds
Chris Reynolds

Reputation: 11

Overflow in read from massive spreadsheet

I'm using Excel 2007, processing large gridded datasets of atmospheric data. I regularly (every month) process flat binary files of 64000 elements per day/month, but have this atmospheric data in excel because it comes from NetCDF source files (I've got the converson add on for this data).

I open the required Excel spreadsheets using the Workbooks.Open method.

I initially used "Set OpenedWorkBook = Workbooks.Open(Filename)" to open the workbook, then read the data from the workbook using "OpenedWorkbook.Worksheets("air").Cells(Lat + 3 + (94 * DayNum), Lon + 3).Value", i.e. using the object OpenedWorkbook.

That crashes when 94 * DayNum crosses into DayNum = 349, that being 32806 (the data is in 94 rows per day). Realising that this was going over 2^15 (32768) I presumed there was something odd about the object Workbooks.Open returns, i.e. a limit on the Cells function. So I tried using the method "OpenedWorkBook.Activate" and accessing the now active opened worksheet without explicit reference to the workbook. I still get a crash at the same point for the same overflow.

The weird thing is if I use the example table I have in my code's spreadsheet, and just debug.print the values from that I can stride over the 2^15 limit and print out values from rows far past that limit. Knowing this I could convert to an intermediate flat binary by copy/paste of code into each source workbook, but there are 36 of them and I'd rather not do that if I can avoid it.

So before I start messing about converting my data to flat binaries, can anyone suggest what might be going on here?

Upvotes: 1

Views: 65

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

An integer data type in VBA has an upper limit of 32,767 as opposed to other programming languages. This would suggest that one of your variables (either Lat, DayNum or Lon) has been declared as an integer type. The number 32,768 obviously breaks this upper boundary and causes an overflow as an Integer data type in VBA has a max limit of 16 bits. If you are using whole numbers, then use a Long data type instead. A long data type in VBA can hold a value of up to 2,147,483,647 because it is allocated 32 bits of memory.

Admittedly a bit of an overkill answer, but hopefully should explain why you may want to use a Long data type instead of an Integer data type.

Upvotes: 2

Related Questions