From ICISWiki

This section of the ICIS Wiki is primarily intended for future developers of the ICISworkbook (if it remains to be in Excel of course) to have a stronger foundation on which to lay their own innovations, and continue the legacy of the application as being both user-friendly and fast. This is also intended for end-users, for them to marvel at the beauty of how their simple wish list was actually granted. And for the warring forces of MS Devotees, Mac Minions, and Open Sourcerers, to give them more reasons to fight, thereby keeping the technological balance in cyberspace… May the force be with you all!

The DMS Workbook, as it was originally named, was built on MS Excel macros for the basic reason that users have been using Excel since the day they were born. I mean… you got the idea! And so it was that the needed functions for the Workbook were one by one realized. At first, there was joy and peace, and contentment was in everyone’s heart. Until, the land and water dedicated for rice field grew smaller and smaller, and the global threat to food security became clearer and clearer, and the search for a solution grew stronger and stronger, and thus research became broader and broader, and so the data grew bigger and bigger! and the… Stop!!... inhale… exhale… until the Workbook became soo… slowww…

Thus, the Workbook must be improved! And here we are now, on a heroic journey to save the world! Ahm… maybe that was too ambitious. This is the start of putting into account the experiences of the few, the brave, the proud… (Will you just cut that out!) developers of the ICISworkbook. For many were called, but few were hired. Budget constraints, you know.

Seriously, this ICIS Wiki section aims to record the solutions taken to improve the ICISworkbook, as well as the pitfalls, as they may seem to be at the time they are first recorded here, which could probably be easily solved by any of you. Thus, there is no claim whatsoever of the superiority of any of the solutions as well as the difficulty of the pitfalls. May this be an open invitation for alternative approaches and foreseen problems for the betterment of the ICISworkbook application.

Workbook.Open vs. Workbook.OpenText

As it was quite established for some time now, moving up and down, left to right, or whatever, on the rows and columns of a worksheet has a bit of an overhead in processing time. So the recommended solution was to use a text file. And so it was that the ICISworkbook was rewritten to traverse a tab-delimited text file. And a record speed of ten times faster than threading a worksheet was achieved, blah-blah-blah… But let’s get into the details of how it was actually done shall we? Both the Description and Observation sheets are saved as a tab-delimited text file. This is a pretty simple one line of code as “ActiveWorkbook.SaveAs Filename:=zSomeFileName, FileFormat:=xlText”. Then, we access the contents of each cell by looking for the tab character Chr(9) which marks the boundaries of the columns, and the carriage return + line feed characters Chr(13) & Chr(10) marking the end of a row. But this process corresponds only to reading the worksheet or, in a more familiar term, loading a study. For retrieving a study, we have to do the reverse. That is writing to a text file then converting it to a worksheet. This will be the topic of this particular discussion. Now, the series of images below will explain the two Workbook methods that were used, and their advantages and disadvantages as well.

Given the freedom to use a carriage return inside a cell by pressing the Alt + Enter keys simultaneously, we’ll have the situation below:

Earlier versions of the ICISworkbook that started using text files and up to version 5.5.0.2 use the Workbook.Open method to view the text file in the multi-document interface of MS Excel. Again, this is a one line of code, letting Excel do the dirty work of file conversion. And it did pretty good, really. It knows what string of characters belong to which cell. And in case of special characters, double quotes where used to contain them when the file was saved. Same interpretation was done when the file was read, perfect! But the problem comes in when Excel automatically determines the data type of the cell contents. It generously converts for you every date-looking string into the equivalent date format. Oh-ow!!...

So, complaints came pouring in, "I loaded a range value of 1-2, when I retrieved it, it became a date value 2-Jan?! That's outrageous!! (sorry, exaggeration was mine :-)" So, we went back to our thinking chair... Hmm??... Hmm???... Press F1... Aha!! Workbook.OpenText allows you to declare the data type for each column. And so it was that version... Well, actually the first change started in version 5.4.1 in the Retrieve Study function but it was later propagated to the Import Data From SetGen function in version 5.5.0.3 which was used as an example in these images so just bear with me please, okay? :-D

But wait! There's more!! I mean, there's a problem. Workbook.OpenText doesn't recognize the power of double quotes to restrain the interpretation of a carriage return inside a cell. The carriage return becomes interpreted as an end-of-line. Thus, the line or row in the text file prematurely breaks into another row. Ouch!

And so, because the ROWTAG column was temporarily placed in front, it received the other part of the broken line. And you know what happens if there's a value under the ROWTAG column, the whole row becomes interpreted as a remark instead of an observation. Thus, when the code execution gets to the part pictured below, some side effect occurs which somehow hides the true error and makes you wonder, "Gosh! Why am I seeing double?? I must be hungry now, time for a coffee break." Well, errors ain't that bad after all, eh?

At this point you might be wondering what I did to solve the problem? Well, with all the time I spent writing this whole article, I did nothing but just talk about it :-D. Sorry, one task at a time, please. But realizing the problem and knowing that it all started with a carriage return inside a cell, the big question is "To fix? Or not to fix?" With not to fix, I mean just don't use a carriage return inside a cell.