Spreadsheets with Rulers

In a recent post I mentioned that I think that Excel should have better support for panes.

The backdrop to this is that you can only split Excel windows on the same workbook.

The backdrop to this is that when you set a column width, or hide a column, or set a background colour for a column, that setting applies to the whole column!

That was bad when a sheet could have  8K rows. It was worse when a sheet could have 64K rows. It is even worse now when a sheet can have 1M rows!

This limitation of columns is another thing which hasn't changed much since the far off days of VisiCalc! Another stark example of a lack of innovation.

Can you imaging a Word Processor where you could only have one ruler for a whole document? It's unthinkable!

So if we combine these two ideas, why couldn't we insert a new ruler at any point in a spreadsheet?

Then you could control columns widths, and hiding, and colours independently every time you have a new ruler.

The mock-up below shows how this might look. Notice that:

• It's a single worksheet: the rows go from 1 to 19.
• The background colour changes with each new ruler
• The column widths change with each new ruler
• Column B is hidden in the third section without affecting the first two sections.

Another Excel Limitation

Wouldn't it be great if you could easily arrange "panes" of content within Excel, each pane being taken from a different sheet.

And you could lock some of those panes so that they don't scroll.

So you have one pane which where the user enters data, and other panes where the effects of those changes are displayed.

Here's a simple mock-up of what I would like to achieve:

And when you reopen the workbook, the layout which you defined is redisplayed.

And if you resize the overall window, the panes resize as you prescribe.

And maybe you can even have a different "zoom" setting for different panes.

Basically it helps you to create an application within Excel.

Sounds reasonable?

Unbelievably after all these years of spreadsheets, the limitations of what you can do in terms of splitting a window seem to be more or less the same as was the case with VisiCalc!

BTW: I have played with "Windows" and "Workspaces" within Excel and have been unable to achieve the result I desire. Additionally "Save a Workspace" has been discontinued in Excel 2013.

Triumph of the Nerds

I mentioned VisiCalc and Dan Bricklin in my last few posts.

Anyone wanting to know more about the birth of the personal computer industry should watch Robert Cringely's excellent three part documentary "Triumph of the Nerds":

Dan Bricklin

I mentioned Dan Bricklin (inventor of VisiCalc with Bob Frankston) in my last post.

Included below is a video where Dan discussed the rise and fall of VisiCalc along with lots of photos of his company and other people in the industry at that time. Obviously Lotus (where I used to work) is mentioned a number of times.

It's a nice walk-through of those times.

Excel - Using IF with multiple conditions

I mentioned Visicalc in my last post.

The Visicalc manual from 1981 has the following to say about the @IF function:

@IF takes three arguments . The first must be a logical value ; the second and third can be any value . The function evaluates to the value of the second or third argument, depending on the value of the first.

So fast forward to Microsoft Excel in 2015.

I tried to enter an IF function with multiple conditions into Excel today. Here is something similar to what I entered:

=if(a1="y","day",a2="y","week",a3="y","month,"year")

You get the idea:

IF cond1 THEN res1 ELSEIF cond2 THEN res2 ELSEIF cond3 THEN res3 ELSE res4

What could be simpler?

Guess what: after all of these years, the Excel IF function still only takes 3 arguments!!

So people have to resort to "nested ifs":

=if(a1="y","day",if(a2="y","week",if(a3="y","month,"year"))) 

Unbelievable! Talk about a lack of innovation!!

Visicalc

I have been planning to do a post on Visicalc pretty much since I started blogging.

I have mentioned it in a couple of posts before, including one on the "Most Influential Software Programs".

In my opinion Visicalc was the most innovative software program of all time. It did not reproduce anything (although there may be some similarities to old ledgers). It did not build upon anything (as most software does). It was simply invented.

And then it was copied: 1-2-3, SuperPlan, Quattro, Multiplan, Excel, and others. And of these Excel is still in incredibly wide use today.

I have huge admiration for the accomplishment of Dan Bricklin and Bob Frankston. In all of the years since they invented Visicalc in 1979, nobody has created anything in software which (IMHO) has that level of creativity and innovation. Truly remarkable.

The reason I mention it today is that I came across this video on the two guys and their achievement.

Microsoft Excel - Performance Opening and Saving

I recently discovered a way to help people who are experiencing performance problems opening and saving large Excel workbooks over a WAN connection.

The solution is to switch to a more efficient (smaller) file format. So instead of saving the workbooks as XLS or XLSX, you need to save them in XLSB format.

The "B" stands for binary, and it means that the file is saved in an optimal binary format, as it was before the trend to use XML to make file data more "open". So the files would be harder to read in other applications, but much faster to read by Excel.

XLSB format supports all of the functionality of the other file formats, including macro and VBA code.

And because the files are much smaller (down from 10 MB to 2 MB in one case I tested), they can be read/written over a WAN connection much more quickly.

One thing to watch out for, of course, is that you will be changing your filenames. So if you're using external links they you will have to reconnect them to the new filename. External links work faster too, of course.

Note: They may also be a problem with "Custom Ribbons" and "Personal.XLSB" which you need to watch out for but I have not experienced this.

What I still miss from Lotus 1-2-3

I use Microsoft Excel practically every day. It's running right now on this machine as I write this posting.

But there are a couple of things that I miss from Lotus 1-2-3 right to this day.

Slash Commands

The first is the ability to use slash commands. With 1-2-3 all commands were initiated by pressing the slash key. "/FO", for example, was used for File Open.

The great things about this was that 1-2-3 was designed to be used with a keyboard rather than a mouse. Originally this was because 1-2-3 was a DOS application rather than a Windows application. But the combination of the slash commands and the matrix of cells which could be navigated using cursor keys meant that using the keyboard was fast. Much faster than using a mouse.

The benefit of Excel (based on Windows and the mouse) is that it is easier to learn. But it is slow.

1-2-3 was used by power users. Power users were easily able to learn the slash commands. But as everyone migrated from 1-2-3 to Excel that speed was lost for power users.

Deleting Rows

The second thing I still miss was that in 1-2-3 you could define a source (input) table, a target (output) table, and a criteria table and you could, for example, copy all rows from the source table to the target table which met the criteria. And the target table did not need to have all of the columns from the source table. All very useful and powerful. Now Excel does have some of those capabilities (filter and advanced filter). But what was even more powerful and is missing from Excel was that you could delete the rows from the source table which met the criteria in the criteria table. "/ Data Query Delete". Genius!

Farewell Lotus 1-2-3

I worked in Lotus Development in Dublin between 1989 and 1993. When I joined the company, the most important product by far was Lotus 1-2-3. When I left it would be possible to argue that the most important product was Lotus Notes (in 1995 IBM acquired Lotus primarily to get its hands on Lotus Notes).

A few days ago (on 30th September to be exact) the end of an era came when IBM discontinued support for Lotus 1-2-3. See the image below for the announcement from the IBM website.

1-2-3 was released originally in 1983. So it was a supported software product for over 30 years. Not a bad record!

I worked in the "Advanced Support Group" when I was in Lotus and was there at the time we launched "Release 3.0". As I recall, there were two fairly serious issues with this release:

• It needed a lot of memory. This meant that it needed "extended" or "expanded" memory. These were new requirements for the market and caused lots of confusion.
• There was a problem at one stage in relation to specific calculations returning the wrong results. The passage of time has dimmed my recollection of this, but it may have been related to problems with the Pentium processor, or a floating-point coprocessor. But it certainly was alarming at the time.

The other problem with Release 3 was that it didn't have on-screen WYSIWYG capabilities (while the Release 2 product had the Allways add-in). This was fixed in Release 3.1 but by then there had been so much loss of momentum for 1-2-3 that it was difficult to recover.

In the end, of course, Excel went on to dominate the spreadsheet market and products like 1-2-3,  Borland's Quattro even Microsoft's own Multiplan joined VisiCalc in the software history books.

BASIC is 50 years old

The BASIC programming language turned 50 on the 1st of May this year.

BASIC was first successfully used to run programs on the school’s General Electric computer system at 4 a.m. on May 1, 1964. It was invented by John G. Kemeny and Thomas E. Kurtz of Dartmouth College in Hanover, New Hampshire. They are shown in the photo below with one of their students.

BASIC is an acronym, standing for "Beginner’s All-Purpose Symbolic Instruction Code".

You can see the manual for this first version of BASIC here.I particularly like the section on the use of "teletype typewriters"! That first version had a very limited number of commands, including the infamous GOTO!

But, in my opinion, BASIC played a critical role in the Personal Computer revolution. Every computer came with some form of BASIC. My own first computer, an Apple II, came with "Integer BASIC" in ROM (written by the genius who is Steve Wozniak) and "Applesoft BASIC" (written my Microsoft) on cassette tape. And everyone who bought a person computer entered programs in BASIC (often transcribing them from books and magazines).

Without BASIC, what would have been fuelling this desire to write programs on your own computer? COBOL? Pascal? FORTRAN? Forth? Logo? ALGOL? Lisp? It's hard to imagine. (Java, by the way, wasn't to make an appearance until many years later in 1995)

And of course Assembler would be out of the question (too complex, and no practical ability to share code across different computers).

There is a nice video on this part of computer history on the Dartmouth website here.

Scratch 2 - Renaming a Sprite

I have forgotten this a few times so I'm going to blog about it to remind myself!

In Scratch 2, renaming a Sprite (or changing its other properties) is no longer intuitive.

To do it, you need to:

1. Select the sprite in the Sprites pane
2. Click on the little "i" in the top left hand corner
3. Make whatever changes you need
4. Click on the arrow head to return to the normal Sprites pane

No idea what they were thinking when they made this design decision.

Design Oversight in Scratch 2

There is a problem in Scratch 2 where you cannot easily differentiate between variables that are global (shared by all sprites) and those that are local to the current sprite.

In the old version these were separated by a dividing line.

The screenshot below shows the three types of variable that are available in the online version.

Cloud variables have a cloud icon to differentiate them. Local and global variables look the same.

The workaround which can be seen in the screenshot is to make the variables visible temporarily on the screen. Then local variables are prefixed by the name of the sprite of which they are members.

Scratch 2 Offline Editor

I mentioned in a posting back in May that Scratch 2 had been released without the offline editor.

Well this has been rectified now and the editor can be download from here.

It is still described as "Beta Version".

The only thing I have spotted that you can do in the Online version and not in Offline version is to declare and access "Cloud variables".

Here is an image of the offline client:

Steve Ballmer Resigns

On the August 23, Steve Ballmer announced that he will step down as Microsoft CEO within 12 months.

There are a number of surprising things about this announcement.

The main one is that he wasn't forced to step down years ago. Anyone who say the video below would agree with me. Imagine having to treat that person as your boss!

The second is that it's going to take up to 12 months to find his successor! Why?! Couldn't they just walk down the corridor until they find someone who's acting bizarrely!

The one thing that wasn't surprising about the announcement (widely covered in the media) is that the stock price jumped 7.3% on the announcement. Why should that surprise anyone?

Fanfold Paper

I mentioned fanfold paper in a previous post.

Wikipedia tells me that it is also known as continuous stationery, continuous form paper fan-fold paper, sprocket feed paper, burst paper, tractor-feed paper, and pin feed paper.

I thought is was consigned to the history books.

Imagine my surprise then when I was on a client site the other day and I saw the invoice below on someone's desk. I just had to take a photo!

The photo wasn't great to begin with (not sure why) but I have blurred some parts of it in order to avoid breaking any confidentiality.

Kindle Paperwhite Case

I was lucky enough to receive a Kindle Paperwhite for Christmas.

Recently I started bringing it to work on the train and I quickly decided that I needed a case to protect it.

After some searching on the web I decided on the Fintie Flip Folio case. One of the reviews is here.

Unfortunately I couldn't source it locally and Amazon.co.uk don't stock it (although Amazon.com do). The official Amazon cover from the former would have cost me over $36 euro.

In the end I bought it directly from the Fintie website (€13.57 plus shipping).

I'm very happy with it. I especially like the fact that the Kindle switches on and off automatically as you open/close the case.

Even though there's a range of colours, I chose the black version like the picture below.

Turbo C Memory-Resident Utilities, Screen I/O and Programming techniques by Al Stevens

My last post talked about Borland Sidekick and Terminate and Stay Resident (TSR) programs.

In the late eighties I bought a book by Al Stevens which provides some of the information you would need to write your own version of Sidekick.

Specifically it dealt with writing programs which could generate on-screen windows (stacked, layered, etc,) and also how to create TSR programs.

I liked the book so much that I still have it after all these years. I learned a lot about writing C code from this book.

I searched on the web and I couldn't find an image for it so I scanned the front and back covers of my own copy and you can see them below.

I did see on the web that you can but the book from Amazon. The price which is printed on the back of the book is $24.99. One of the listings gave the condition as " Used - Very Good. Very nice Copy - almost no wear". The price quoted was $369.98 plus shipping!

So if anybody would like to send me $400 or so then I would be happy to give you my copy, which is also in very good condition!

Borland Sidekick

I mentioned Borland Sidekick in my last post. Sidekick was an early (1983) Personal Information Manager (PIM) application. It had a phonebook, a calendar, a notepad, a calculator and various other utilities.

The genius of sidekick was that you could access it instantly by holding down two keys on your keyboard: I liked to use the two shift keys.

This doesn't sound like much these days but you must remember that we are talking about the time of DOS, when you had to stop running one program if you wanted to run another one!

Sidekick's secret was that it was a "Terminate and Stay Resident" (TSR) program. So when you ran it, it appeared to finish and you were returned to the command prompt but it fact it was still running and could be brought up instantly using the 2-key combination.

I thought TSR programs were so cool. I got one of my team to write one which allowed us to send printouts to a printer in another building (and you could still use the PC attached to the printer for other things of course). I wrote one myself later when I worked in Lotus Development. It addressed a problem which Lotus Freelance had on some platforms.

I would include Sidekick in the list of the programs which influenced me most because it showed that PCs were not just "one thing at a time".

The 10 Most Influential Software Programs

A blogger took it upon himself to try to name the 10 most influential software programs ever.

Here is his list:

1. SABRE (IBM, 1964)
2. Maze War (1973)
3. Adventure (1975)
4. VisiCalc (VisiCorp, 1979)
5. WordStar (MicroPro, 1979)
6. Hypercard (Apple, 1987)
7. Photoshop (Adobe Systems, 1988)
8. Lotus Notes (IBM, 1989)
9. Mosaic (NCSA, 1993)
10. Minecraft (Mojang, 2009)

I think it's a really good list.

I am particularly impressed by his inclusion of Hypercard and Lotus Notes.

The omissions as I see it are:

• Sidekick (Borland, 1983)
• Turbo Pascal (Borland, 1983)
• PageMaker (Aldus, 1985)

PageMaker was the product that made companies want to buy a Mac. I was working in just such a company. I fell in love with the Macs, of course (I had an Apple II at home by then) and spent the rest of my time there trying (without success) to convince my manager than I should switch from an IBM clone to a Mac.

I discovered Hypercard on the Mac and (even though I only ever played with it) I thought was very exciting product with huge potential.

Turbo Pascal (which I never used although I did use Turbo Basic) brought tremendous innovation in the area of the Integrated Development Environment (IDE): the forerunner of today's Visual Studio and Eclipse.

Scratch 2.0 - Where I think they have gone wrong

I have a number of issues and reservations about the new version of Scratch - Scratch 2.0.

1) Use of Adobe Flash

The first issue is that the development environment is now online and is based on Adobe Flash.

This continues a trend which we saw with the previous version of Scratch where the online environment moved from using a Java Applet to view games to using Flash. You could still switch to the Java Applet but the default was to use the Flash player. In 2.0 the support for the Java Applet on the Scratch website has disappeared completely.

The Flash player is a technology which is on its last legs, IMO. The beginning of the end was when Apple release the iPhone and there was no flash support. YouTube responded by providing an alternative to Flash for iPhone users. The middle of the end was when Steve Jobs wrote his "Thoughts on Flash" open letter in April 2010 and then Adobe announced in November 2011 that it will no longer develop Flash for mobile platforms. Mobile is everything these days. The end of the end cannot be far off.

Interestingly, the  University of California at Berkeley has created SNAP! (formerly known as BYOB for "Build Your Own Blocks") which is described as an "extended reimplementation of Scratch". It doesn't use Flash. In fact it just uses JavaScript. The amazing thing is that it actually runs in iOS. I have used it on a iPad (even though Apple try to limit people's ability to develop code on an iPad). To be frank I think that UCB have outsmarted MIT. You can access SNAP! here.

2) Lack of "offline client"

The Scratch site says that an offline client will be available "early in the summer". This is a little vague for my liking. I think being able to work online is great, but being able to work offline is essential. That's how IDEs are supposed to work, IMHO.

When we teach Scratch in CoderDojo, we like everyone to be able to run Scratch independently. We don't want to have to give all of the kids Internet access.

As if that weren't enough, Scratch 2.0 uses a different file format. So while you can still develop in Scratch 1.4 and upload programs to the Scratch website, you can no longer download them to work offline.

So pending the release of the new offline client, you can no longer download and remix other people's programs using the offline client.

In summary, I think the Scratch team has lost its way a little and they have made a couple of pretty serious strategic and tactical errors with the current release. How damaging they will be remains to be seen.