Functions by format

I’ve seen a few mentions recently about being able to say SUM by colour, or AVERAGE if bold etc.

Currently I don’t think format is part of the calculation chain of any spreadsheet app. So any format based function would have to be volatile. Such functions are fairly trivial to write in VBA, a bit (but not much) harder in xlls.

But I have a more fundamental question:

Should we be able to base functions on formats?

Is it reasonable that changing the look of one cell would change the value in another?

I know these requests are popular on-line, but so was a million rows.

I have had a bit of a think about it and I think we should preserve the clear separation of the value and presentation of that value. (I know that got a bit muddied by Excel percentages but still).

I think having values dependent on formats would make verifying the correctness of a model even harder.

What do you think?

cheers

Simon

Ribbon finally justified

In case anyone ever had any doubt, the justification for the ribbon was finally unleashed at the PDC today(/yesterday?). The new Office Web Applications (proper name? OWC?? ;-)) have a near identical UI to O14 (?), which is all XML goodness of course, no tatty automation in sight.

I say ‘justification’, I’m not sure preventing desktop devs from creating richly interactive systems, just so web users don’t feel they are getting the smelly end of the stick is a real ‘justification’. But hey ho, what do I know?

They had to bring out a web version to counter the Google (and Zoho and all the others) ‘threat’. And note it will work in the three main browsers (IE, Ff, and Safari (? I get more hits from nutscraper than Safari)). I’m looking forward to having a play with it, I think there is maybe a private beta coming up?

I’m sure there is a good reason for this rush to browser stuff, and I’m sure there is a rich vein of potential customers somewhere. Or maybe ‘someday’, I guess I still think there is life in the desktop market too. I wonder if .net developers will show any interest whatsoever in these new clients, or will they still be fixated on AJAX and Silverlight. I have my suspicions, what about you? (If these new Office clients are implemented in Silverlight, then maybe?) [Edit: they use all 3, HTML, AJAX, Silverlight]

I’m looking forward to hearing about all the new features for those of us focused more at the nitty gritty end of things. although I’m guessing that could be some time away?

What do you think about the web client? big thing or not?

Have you got any more (public) info about O14?

Does anyone else remember when you opened an Office doc in IE and all the menus got tangled up? Does it still do that? Or does anyone still do that? is perhaps the queston.

I do have a question though – If every desktop has MS Office on it why do they need this web stuff?

and another: if MSO won’t be on every desktop then what are they doing to make sure it is?

Because if you don’t need MS Office then you don’t need Windows, and the whole thing will come down like a house of cards. right?

[looks like I picked a good week to upskill in Linux]

cheers

Simon

Netbook experiment ends

I have had my Asus Eee (701 – 4G – Linux) for exactly 12 months (give or take).

Yesterday I completely hosed it.

I have kept my dev machine off t’intarweb for years so I don’t have to run a ton of resource sapping security crapware. I’d always used a disposable pc (Mrs Smurfs ;-)) for on-line stuff, as its not completely intertwined with the main dev work I do.

I have toyed with Linux for years too, but not at any great depth. And although I have a few successful installs under my belt, I have also had a few significant fails. Like my Sony where Fedora was crammed into a 100×75 mm (4 by 3 inch) box in the middle of the screen and no amount of faffing and swearing would make it usable.

The big draw of netbooks for me then was to get a properly working Linux to try to do my web stuff. In that it has proved stunningly successful, I have also done plenty of work in OpenOffice, which has also worked out very well.

Overall the netbook experiment has been a resounding success, 200 quid for a fit and forget web box I would rate as good value. I have mainly had it pugged into a big screen, keyboard and mouse, but it has proved useful on the road too, although really that 7 inch screen is too small, especially for spreadsheeting.

The other downer is the Linux distro it comes with, a custom version of Xandros. The Eee on-line repositories just did not have the additional software I wanted (OOo3, Ff3, Gimp, Gnumeric for example). And getting a more mainstream Linux on it looks like a pile of pain.

The final downer and the cause of the end of the experiment is that 4Gb drive. About half of that is reserved by the system, another 1Gig odd is used by some other thing, and the spare user chunk is about 400 Mb. In trying to update my Eee last night it ran out of disc space, massively, and terminally. Well, it probably would have recovered if I hadn’t turned it off because I got bored of waiting.

Poor version of Linux + small screen + inadequate hard drive = game over. I recovered it back to factory settings today. One of the smurflings will be getting a well cewl laptop from Santa – I just hope I can get all those biscuit crumbs out of the keyboard!

My netbook future? I’m going to wait around for the next few release cycles. The Acer Aspire One fixes 2 out of three of those problems. The HP mini note fixes all of them but is 100 quid (200 150 USD) too much. I think the next few months will see more adoption of mainstream disties (probably Ubuntu from what I have read) by netbook manufacturers.

My on-line future? I have just installed Ubuntu on my old dev box and will be using that:

Top Linux + big sreeen + big drive + zero cost = winner.

I mentioned a while ago that return rates for Linux netbooks were aledgedly 4 times the Windows rate. That was a quote from an MSI margeteer. The boss of Asus says for them, the return rates are identical. In related news MSI’s Linux distro got widely panned, so it sounds like it was their crappy implementation that is backfiring on them.

I could of course persevere with the Eee, use a big SD card to fix the disc size issue, and use Eeebuntu etc, and I would happily do that if I didn’t have a spare box floating around.

Of course if Asus bring out a kitty Eee I’ll be straight in there.

cheers

Simon

Simple Timer

Those nice folks at Codematic have made available their world class, best of breed, exclusive multifunctional chronography system.

(= a simple VBA Timer/Timesheet thingy, nowt special, from here.)

It has a couple of buttons, one to start the timer, one to end it. It records the interval and moves to a new line each time you click start.

Uses

Say for example you want to prove who can get the coffees in the fastest.

Or time how long a long procedure takes, like say some massive ETL process.

If I’d used GetTickCount instead of NOW() it could be used for some more high definition timing, maybe version 2.

Dick at DDOE did a more feature rich one recently related to measuring the amount of time spent on various categories of task during the week.

This one is just a simple timer, originally created to measure some 20 minute network based procedure to see if it could be speeded up. (Yes – work locally!). I rushed it on-line in response to a NewsGroup posting the other day, otherwise I might have spent more time tidying things up.

The code is totally open (all 10 lines of it) feel free to modify/enhance – let us know if you do something we might all benefit from.

No VBA code runs between start and stop and no variables are held in memory, so you can even time compiling your VBA.

As usual any feedback welcome

cheers

Simon

Controlling the review toolbar

A while ago I mentioned a VB6 COM add-in I had for keeping that pesky review toolbar in its place. I also discussed controlling the wordwrap/ row height auto scroll oddness that Excel does when you enter a long text string here.

I have now combined those 2 management functions into a  new COM add-in and put it in the usual place.

Its just the raw .vbp at the moment as I haven’t had chance to test the deployment properly. Don’t be under any illusions though – I haven’t tested the core operation ‘properly’. It worked OK for me the last time I tried it, it hasn’t broken anything else (that I know of – yet).

If you don’t have VB6 you can just pull the .cls and .bas files into VBA and you wont be far off. I’ll sort out a proper setup.exe in the next few days

The reason I put it in VB6 is that I have found when you are trapping Excel events in VBA and developing you can easily lose your application reference. I just find it more convenient to put this sort of stuff in a COM add-in, that also gets it out of the project explorer in the IDE.

If anyone has any enhancements or other features to include either upload them and tell us or email them to me and I’ll do it.

Any problems or other feedback either email me or leave a comment

cheers

Simon

Fluent UI Team

In case you were wondering what the FAIL UI team have been upto – looks like they got sub-contracted to ‘help’ Facebook.

Did any of us contact newsbeat about Office 2007?

Has anyone even set up a FAIL UI facebook group?

Being too old for facebook I haven’t looked into it, but did they justify it by using woefully skewed user experience stats? Or too many new features for the old UI? Users couldn’t find existing features? Or did they just fess up and admit it was just to differentiate them from MySpace?

Cheers

Simon

Tech support

Anyone seen these in the UK?

Also how hard would it be to change that ob to oss?

I’d love to put one of those on then go for one of these interviews.

Have a good weekend

cheers

Simon

Office 14 out next week

Apparently Office 14 will get an airing at the PDC next week.

Blimey – they haven’t even officially named it have they?

If O14 is the official name at least I won’t need a new notebook:

my O14 notebook

Which is lucky as this one still has spare pages. I do appear to have lost my matching pen though.

If you’re going then keep us posted on any UI repairs. (Full classic mode option (with proper automation) for competent users, is that right? ;-))

Of course with it being the _professional_ developer conference no spreadsheet (or Access) jockeys will be there right?

I got this snippet from here (linked to in the PC Pro bit above.

“We will rewrite Office to work in a browser,” he [sm: chief klaxon Ballmer] said in an interview with Computer Weekly.

What, is the point of that??

(rhetorical question – no need to answer)

cheers

Simon

Base Excel xll project

I have finally uploaded a base xll project to the traditional place.

I have put a very brief guide to it there too.

I got caught up in a bit of a quandary due to various incompatibilities.

Basically the Microsoft xll SDK for Excel 97 is simpler and easier to understand, but only works with VC6.0 or earlier (using the simple approach I use). This is generally the one I use, because there is less risk of additional run-time dependencies.

One of the SDK functions takes a string and modifies it by adding the byte count to it. You can even pass this function a fixed string as in “Hello”, and it will modify it. VC6 allowed that, VC2003 onwards don’t. There are (at least) two possible fixes

1. put the hello in a variable and then pass the variable
2. change the function to create a new string and return that (I chose this as I think it keeps the actual xll code simple)

The Microsoft xll SDK for 2007 is more complex because it covers the new features introduced in that version, and is based on VC2005 or newer.

Actually setting up Visual Studio C++ to work with the SDK is a bit of a step if you are new to it, so in the end here is what I have done:

I have changed the function in the 97 SDK that broke more recent compilers, and I have included that SDK with the base project. I have included a VC6,0 project file and a VC2008 one so it can be opened and worked with with VC2008 Express immediately, with no set up required.

I wouldn’t normally modify or redistribute someone else code but in this case I think it makes it so much easier for people to get going with this technology I think it makes sense. I did have a quick ratch around to make sure that wasn’t a breach of the SDK licensing but nothing jumped out at me. If its a problem I’ll take it down.

Of course its just barely tested, so if you have any problems then let me know

cheers

Simon

SpreadsheetHell

Should be website hell!

I think I have finally sorted out all known current kinks in the Codematic internet presence. It is still a work in progress of course. And don’t go looking for the XLAnalyst site thats ‘being refreshed’.

Nice new spreadsheet hell opening here:

(note the not insignificant personal danger involved in getting that photo (no photoshop here))

If those buttons have a grey border its because you are using an old out of date non compliant browser, inline with about 30% of visitors). If the picture tiles or does some other funny stuff then welcome to the club – we’ve given up on that.

If you get to a Codematic page that looks ugly (as in lost its style sheet, default font all over, missing pics etc) then please send me the link as I just fixed another issue today. If you find any other broken stuff then let me know that too.

cheers

Simon