Overflow in read from massive spreadsheet

1
votes

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?

1
excelvba
I cannot answer your question, but I recommend that if you want to look at data that large you use some other piece of software (Excel is not good for data that is actually large, and you are getting close to large). You might look into MATLAB (not free) or Octave (open source similar to MATLAB). Even things like Python have built in functionality for parsing CSV files and will be able to work on much larger datasets. You may find a work around that will get you what you want for a while, but you'll probably be better off in the long run going another route. (Just my opinion.) - TravisJ
There is somthing I do not understand here: the expression "OpenedWorkbook.Worksheets("air").Cells(Lat + 3 + (94 * DayNum), Lon + 3).Value" refers to just one cell. What is it that I am missing? - Gene Skuratovsky
Hi Travis, one of my colleagues keeps trying to get me into R for the same reason. But I'm very busy and balk at the prospect of the time involved. Also once I have the data in excel, I'm very proficient and the analysis flows. - Chris Reynolds
Hi Gene, the variables lat and Lon cycle through every grid box in order to execute a calculation and produce the results in excel. The global data is presented in gridded format, a grid for each day, and a new file for each year. - Chris Reynolds
Any variable that is declared somevar As Integer should be changed to somevar As Long. - user4039065

1 Answers

2
votes

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.