Archive for August, 2007

Desktop deployment environment

Thursday, 16th August, 2007

Read a great quote the other day, the irony made me smile for ages.

“Initially we thought we’d be using Java, but we ended up not using it because we concluded that there would never be a stable runtime environment that we could count on on all desktop PCs.” Ray Ozzie, Microsoft Chief Software Architect, ex CEO of Groove, talking about why Groove was written in C++. (Founders at Work, Livingston 2007).

My biggest complaint about .net? same thing – it just can’t be relied on to be there in the right version. Maybe thats going to change going forward??

Or maybe we will just start targeting servers and have thin-client browser apps?



Excel CALL()

Thursday, 16th August, 2007

I had a great product idea the other day. Xlls are fairly hard to write, and not many people do them. So I thought I could write an xll that would expose other peoples code to Excel as worksheet functions. I thought it through a bit, wrote half a paragragh of description, then thought – Hang on, isn’t this CALL()? (This has been in Excel since V4 at least, so those clever Excel folks beat me to it by what, 15 years? maybe more)

The CALL() function allows you to point to a function defined in a dll and then call that from a worksheet cell. Unfortunately CALL is not a worksheet function, its a macro sheet one. Well its not a worksheet function anymore if you applied the ‘Disable CALL worksheet function’ security patch, which I think is probably rolled into the recent versions of Excel. I suspect there are very few of us left still using XLM, which is a shame as it has some fantastic features. Speed being one of them, doing stuff in XLM can be loads faster to run than VBA.

So anyway I fired up VC++ and created a win32 dll project and selected the one that exports some symbols. This would have been enough except for C++ name mangling, so I then added a .def file to export their boiler plate function. This is named “fn” + whatever the VC++ project is called, takes no arguments and returns an int (the number 42). I compiled a release version and copied the dll somewhere easy to find.

I then added a macro sheet to a test workbook put these 3 lines in, defined it as a name (of type ‘function’) and then called it from a worksheet.

  • =RESULT(7)
  • =CALL(“E:\add2.dll”,”fnAdd2″,”J”)
  • =RETURN(B2)

What these mean

  1. Says the result will be of type number, text or logical
  2. calls the dll and holds the result, the J tells Excel it will be of data type long
  3. returns the result held in the cell above (these last 2 could be combined as RETURN(CALL(.., but I thought this was clearer)

Once I’d finally worked out/remembered that CALL is case sensitive everything worked fine.

I have not really seen much about this approach, apart from all the security advisories (have you?). It seems to me that this gives you most of the benefits of xlls without the hassle of learning the Excel C API. It means you can focus on getting your functions right in your dll (must be a native win32 one , not a Mickey Mouse VB or .net one). You could of course use VBA and Declare the dll functions, but that takes your worksheet function through the oh so slow COM interface.

If you want to interact with internal Excel stuff then you probably need to go the xll/C API route.

The test dlls, the VC project, and the test workbook are all here. Let us know what you think. (codo.dll beeps when the cell is calculated – just a warning so you can choose where/when to look at this ;-))

Does anyone already use this on a regular basis?



OpenOffice VBA

Tuesday, 14th August, 2007

I’ve just downloaded OxygenOffice 2.2.0, based on OO2.2. This version has VBA support.

The recommended way to check if your version has VBA support is to open an .xls with code in and look at it in the basic editor. If it is all rem’d out (remember ‘rem’? (for comments (remarks))) then you don’t have support. If it isn’t you probably do. Its a long way from complete at this stage, and realistically isn’t going to run anything too complex just yet. But its a start, and its interesting to see an organisation recognising the importance of VBA.

I don’t think there is much support for writing VBA, it looks like the initial aim is to run existing VBA. You can write some VBA:

sub VBAMain
ActiveCell.Value = "Hello VBA style"
end sub

Or you can record it in Basic:

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")
rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "StringName"
args1(0).Value = "Hello"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())
end sub

I think there is some way to go for sure, but its pretty neat to see what we know as VBA running fine in OpenOffice. Comparing the 2 examples above, I think VBA is going to be more approachable than Star Basic.

I’m not a total VBA fan boy, there are other technologies I would rather use for many things. But I have a lot of respect for the designers of VB6/VBA, I really think they hit the sweet spot between too simple to do anything useful, and too rigid/complex to be beyond the use of business/non IT people. I’m not convinced MS are really targetting this user group with their current offerings, and I think they should be.

Another thing is that all this is open source so even if the VBA IDE continues to stagnate, there may be the chance to use the OO Basic IDE to write VBA for Excel.

I’m also wondering about Python, that seems to be getting a lot of attention as a ‘user’ macro language. This is some way down my list of things to look at at the moment though.

One other thing I registered the other day is that VBA has been multithreaded (to some extent) since O2k I think (VB6). I have no plans to look into it in depth, but if you have a multithreaded app and you want to add VBA to it then thats possible. If VBA is thread aware I wonder why it hasn’t been revamped as part of the multithreaded calc? I guess its part of the MS philosophy – if its not managed code, its pants. Or maybe VBA has attracted the wrong crowd? ;-)

Have you got any plans to check out the SO/OO VBA features? (I believe you can also send them .bas files etc so they can prioritise the stuff people are using).




Tuesday, 14th August, 2007

Ages ago I claimed I was going to learn a new language, or something. I can’t remember just what I said I was going to do, but I’m pretty sure I havent done it, and certainly not as far as I thought I would. I think it might have been Flash, which I havent looked at since.

But someone (Tom?) suggested I took a look at Ruby. So I have, just a little look, I’m meant to be doing something else. But you can try too – zero install, low pain. Go here and try the on-line Ruby demo – neat it just runs in your browser so an excellent way to try before committing. (I have a total fear of installing anything on my dev machine in case it quietly changes some component I don’t want changed). And this is easier than firing up a VPC.

Let us know what you think.



Google pack

Monday, 13th August, 2007

Those nice ‘do no evil’ folks over at Google have made a rather interesting addition to their Google Pack. Google Pack is a bunch of software Google gives away, most of it is free or open source. The idea is that for someone who just picked up a new pc with Windows and little else, this set of software will get them up and running with some useful stuff. (once they have cleared off all the pre-installed crapware of course!).

So whats new? They added StarOffice from Sun. StarOffice is what OpenOffice is based on, and is a complete Office suite similar to Microsoft Office. Star Office usually costs 70 USD or so, and has some proprietary stuff that OpenOffice doesn’t. (Google Pack link here). A few people (including me) are curious why they didn’t include OpenOffice instead – could it be the MS Office migration tools that are in Star Office but not OO?. I also wonder why there is not a Linux Google Pack. I have no idea if Google Pack is widely used or not – do you? (how many downloads?)

The big thing about including SO, is that there is already talk of connecting up StarOffice Calc to Google Spreadsheets. That basically gives you Excel Services type functionality for nowt. And potentially for non Windows OS’s. Funnily enough this was something I was thinking about only the other day (although I was thinking Excel/VBA-Google s/s). I think there is already an Essbase add-in for OpenOffice, so the connectivity features where Excel was always so strong, are appearing gradually in the competition. With this (free) deal you now have a fully featured desktop s/s editing tool, and a simple way to share key parts of your spreadsheets. The limitations of Google spreadsheets on-line editing suddenly go away.

I don’t think StarOffice has VBA though still. In fact I suspect MS have some pretty strong legal protection in this area, as VB is a proprietary flavour of BASIC, not a standards based language like C/C++/C#. (You’ll notice no-one else has a Visual Basic editor, there is REALBasic which is probably similar, but is not VB. Contrast all the C/C++ editors and compilers and the Borland/Code Gear C# IDE – these are ECMA or ISO standards). BTW anyone who released a decent VB(ish) IDE that produced native win32 apps (zero install) would find a willing audience, maybe I should check out Real Basic – they have been doing well since the retirement of VB6.

I’m sure I must be missing something obvious (pls tell me!). To me the biggest unique selling point of MS Office is VBA, none of the competition have got anything as widely used or as ‘good’ (good, as in gets the job done), or even compatible. I know there is limited love at MS for VB and those that use it (Morts right?), but I really think a major VBA IDE overhaul would add sales and protect their market position. (and if they could spin off a non .net VB IDE that would be handy).

Do you think VBA is that big a deal? If not why not? and if not then what, if anything do you think is/are the key benefits of MS Office over the others? Do you think StarOffice/Google spreadsheets is significant or am I reading too much into it?

Do you think there is that much inertia and tradition around using MS Office that nothing these ‘competitors’ could do would have any real impact?



Pop stars

Sunday, 12th August, 2007

Then has been a trend in pop music recently to hype tracks well before they are released. The idea being that will build up a backlog of pent-up demand and loads of people will buy on release day, thereby potentially improving the chart entry position. That could maybe be excused when the world was physical media (remember 7″ singles?- never mind that, some you probably remember 78’s! ;-) ). But in these days of download music its hard to understand why radio stations can access a track a month before the buying public, except to deliberately manipulate the market.

Anyway, obviously this post isn’t about the music industry, but its just that I see a lot of similarities in IT.

For example there are lots of great new features in Excel 2007 (widely and well promoted since beta 1), but I am still not seeing the market. VS2008, again lots of great new features (did I mention I still mainly use VC6 and VB6?), but no clients have framework 3.5 yet (its not released, like VS2008 isn’t). VSTO is beginning to mature, but still a very limited opportunity. I’m not even going to mention Sharepoint hype, its easy to believe that will bring world peace, or better. Of course once you have tried these products its a challenge to go back to your previous set-up. Hence the value of Virtual PC and VMWare. Its not just Microsoft, its been hard to get past System 9 fluff from Hyperion.

There seems to be more and more pre-release hype around many of the products I am involved with. To be frank, I find a lot of it distracting. I’m trying to run a business based on the software people are actually using, but I keep getting dragged away by this exciting shiny new stuff. Its great but totally unusable as it is not distributed to my target customers. (And I don’t think I can influence that distribution in many cases).

Is anyone else seeing this push for early adoption? (/Attempts to create a ‘buzz’?). One thing that shocked me was the way many of the Office blogs were dropped the day Office 2007 was released. In fairness, Dave Gainer and the Excel team blogged more frequently, and at a higher quality, and they kept it going for longer (and its back in use regularly now). The Access team and the UI blogs look a little unloved in comparison.

Back to music: the thing is, I can easily download a music track for less than a quid, it is almost certain not to break anything, and if it doesn’t work for me I can simply delete it, again not breaking anything. Much of this hyped software really isn’t like that, keeping up with the hype is a significant effort.

Of course I’m still looking forward to going to Seattle next month to discuss Office 14 – I love new stuff, me. Just need to find a way to balance that with a viable real world business.




Saturday, 11th August, 2007

I finally made the move to firefox from IE. Driven in part by the abilty of my favourite news site (El Reg) to regularly moff up their style sheets in such a way as to make the site often unpleasant from IE but fine from Firefox.

I didn’t really think I would be bothered either way, but I do actually prefer Firefox. Not really delved into it too far yet.

Just installed the add-blocker – chuffin’ ‘ell what a difference. Those sites that used to give me headaches are now usable. Those irritating floating obstructive ads that stop you reading the story, gone. Neat.

I’ll have to spend a bit of time and see whats hot and whats not in the world of plugins – there seems to be a pretty lively community out there.
What are you using? and why?



Sub-prime mortgage market

Friday, 10th August, 2007

Anyone following this?

I heard a rumour this might be a big deal, and it seems to be panning out that way.

In summary there seem to be a lot of defaults in the non status (ie risky clients) mortgage market in the US. That is causing ripples throughout the markets as many countries were involved.

Who knows the impact on spreadsheet services?

Looks like overly optimistic modelling is partly to blame, reminds me of the endowment debacle we are just coming out of.



Servers and spreadsheets

Friday, 10th August, 2007

Somebody made a comment, I can’t remember who (or where!) about Excel 97 users. Basically saying, for most purposes this group probably do not represent a rich vein of opportunity for either add-on product or service sales. And actually they probably dont make good upgrade prospect either. Having already managed without anything from the last 10 years of spreadsheet development, its hard to imagine what would make them upgrade now.

I did have someone persuade me to release an E97 version of XLAnalyst, but thats a free tool and the work was a freebie as well. So I would have to agree that I do not expect to generate much revenue from E97 users going forward.

Extending that principle I wonder if, regardless of the proportion of users that adopt Excel services type products, might the most attractive opportunities be with this group?

I have worked for clients who had limited infrastructure control and they were all truly in spreadsheet hell. I worked places where the client can and does control servers, with Analysis Services, Oracle, Essbase and SQL Server etc, and I can honestly say these clients were in much better shape and the work was more productive and more enjoyable. And in general they understood the cost/benefits of investing in effective tools and services at all levels.

Maybe its just me but I would much prefer to write tools to connect a clients spreadsheets to their server data than sort through 20 levels of linked tangled spreadsheet mess.

So I’m thinking things like Excel services probably won’t take over the world, and they sure have some drawbacks, but maybe as a developer and as a business person that still might make a more attractive market. I am still not currently planning to invest much time in Office 2007 for 12m or so, so really I guess I’m thinking of other servers actually.
I’m not sure of the overhead of implementing Excel Services, and I am concerned that many of these products seem to be getting more and more tangled up such that you can’t pick and choose the ones most useful for your org. It seems you have to get a whole heap of stuff to pick up the thing you want. I could be wrong there, I have purposely avoided looking at MOSS as it looks like a bit of a tar pit.

I do think that I really want to be working with organisations that recognise the value of decent infrastructure, be that Excel Services, Essbase or whatever. Maybe only those with .net 2.0? (maybe that is too limiting to be a viable business? especially as 3.0 is out and 3.5 will be out early 2008).

I am not planning on abandoning my current potential client set, more trying to encourage some more than others. (Maybe I should take ‘loves unravelling other peoples interlinked spreadsheet disasters’ off my CV?)(Replace with ‘implemented serveral succesful Excel Services projects internally at Codematic (eg shopping list and CD catalogue ;-) )’?)

What do you think? do your clients sort of get filtered out by virtue of the products and services you offer?



Spreadsheet – desktop or server?

Wednesday, 8th August, 2007

I currently see spreadsheets as a desktop based application. But the more I think about it the more things are quietly moving to the server. And have been for a while.

Pretty much all the spreadsheets I have written in the past few years have pulled some data from a server somewhere. Apart from one app I remember where all the ‘data’ was made up by someone else in another spreadsheet. (There was no real data anywhere).

The add-in type stuff I have written has gradually moved to a more centralised deployment approach, generally network file based rather than pure ‘server’, but getting there.

I was thinking that we need the processing power of a decent desktop, I have some horrible spreadsheets that take minutes to calc, and VBA that takes hours to run. I was thinking that couldn’t be done over a network because there is too much data there would be too much latency. I am used to pitiful client networks where it can take 5-10 minutes to open and save large models (which means that Autosave ‘virus’ basically locks you out of the app almost all the time – well it would if I didn’t always disable it).

When I thought about it though, if the spreadsheet was living on the server, the network traffic would/could be small – just transmitting commands. This is just how Citrix works for example.

So we have data gradually tending to come from a server, we have command based add-in logic from a server, if we put the spreadsheet logic on a server too, it can do all the hard work in the cloud before it lands on our desktop. This would stop us being locked out of doing shopping lists just because the billion dollar deal evaluation model is calcing.

Harlan made a good point that spreadsheets aren’t especially the best way to implement black box business logic. But I can see the appeal if its something the business users can work with themselves. And with it being server based it can be migrated over time to some other technology that may be more appropriate.

Personally I love the autonomy of having everything installed on my pc so I can work when/where I want. But I also love the simple update deployment process of server based apps. I can imagine things grinding to a halt at period end times though as everyone tries to calc their spreadsheets monsters at the same time on the same server. But maybe if duplications were removed a lot less work would need doing?

What do you think, are spreadsheets locked to the desktop, or can you imagine that they will be served up remotely eventually (/soon?)?

I am not suggesting any particular technology is going to solve this, it could be Excel Services, could be Google spreadsheets, it could be a server based xll host. All have pros and cons, although I suspect those that are easiest based on what people already have will have significant advantage. What do you think will be the key factors that drive adoption? or why don’t you think it will work?