Archive for April, 2009

What is Sharepoint anyway?

Monday, 27th April, 2009

Sharepoint seems to be getting plenty of attention, and I expect that to increase as O14 info appears, but still people ask me ‘What is Sharepoint?’

In classic MS fashion they seem unable to describe their product in terms familiar to their target audience.

So here is my attempt.

Sharepoint is an intranet site.

It has a browser front end (even though it needs a proper UI), It runs off SQL Server and it allows you to create intranet sites for a variety of purposes.

Its a bit like the app wizards in Access that let you create a range of application types, they set up the DB structure and the basic switchboard/UI, and then you can go in and tidy things up.

Yes its got more than that, and in some ways I can see it being the Excel of the collaboration age. As in you can do a wide range of things with it, and it will eventually bite you if you do it in a haphazard way.

And my new hobby (Excel services) sits on top of Sharepoint. I feel an inextricable draw to the bark side!

I’m not going to bother to go into the minutiae of Sharepoint, a, because I don’t know it, and b, because I have other things I’d rather focus on. But if you want to take a shot a summarizing what Sharepoint means to you, please do)



Prediction victory claimed

Sunday, 26th April, 2009

I think Oracle buying Sun confirms my prediction that they would get bought or go bust this year. I did half think Microsoft would buy them, but at least there will be some movement. I thought nothing would happen when IBM gave up.

Anyone know what will happen to OpenOffice? will Sun/Oracle still fund it, will they add top class data connectivity?



Another bad internet week

Sunday, 26th April, 2009

Busy at work, and crap internet connection at night, they are my excuses and I’m sticking to them. This week will be better for posts.

Fave Excel 2007 feature

Thursday, 16th April, 2009

What is your favourite new feature in Excel 2007?

Just one if possible, and a why would be good.

I’ve had a good think about it and there are several candidates. And especially if considering different classes of user. I have swung back and forth between several features but in the end I think I’m going to go for:

Function intellisense. Its great to help beginner users learn new functions, and great as a prompt for more experienced ones, and its faster than typing.

What do you think is the best new feature?

(lets try and keep it on target, we can do ‘most pointless’ in another post)



Excel 2007 screen / video issues

Tuesday, 14th April, 2009

I have mentioned 2 visual issues with Excel 2007 recently, and I think they may be both resolved so I thought I would summarise here.

Here I mentioned about a full screen lock-up. We have applied many of the hotfixes released since SP1, which will be rolled up into SP2 due out any minute. This issue (caused by a leakage of GDI objects – possibly caused by graphs or drawing objects) seems now resolved and we have no recent reports of this screen freeze.

Secondarily I discussed disappearing workbooks on open here. I have only seen reports of this on multi screen setups. If you maximise the Excel window it will only maximise to a single screen, but you can de-maximise and resize across both screens – you can then have 2 workbooks open with nearly a full screen view – one on each screen. If you save and close the wb on the secondary window it will remember its placement within the Excel desktop.

If you then maximise Excel on the primary screen (or at lest drag it off the second screen) and reopen that file, it opens ok, but ‘displays’ on the secondary monitor. Which is not visible – so you cant see it. Resize the Excel application window back across both screens and there it is, over on the side.

Bug or operator error? you decide, but maybe Excel could check the proposed window is at least partially within the current XLDESK window?

Finally I heard about another issue last week that seemed to be related to freezing panes in certain (not particularly massive) spreadsheets in 2007. Seemingly scrolling and screenupdating don’t quite work right, I havent seen that myself – have you?

please let us know if you have seen these issues, and if these fixes work



Data type prefixing in Excel VBA

Monday, 13th April, 2009

I hinted at this during the Excel user conf the other week when I asked who used a naming convention in their Exccel VBA.

I am being very specific about what I am talking about – VBA code that sits in a spreadsheet or add-in in some business type application. Not C systems code.

Some authors suggest using a 3 letter prefix to signify the data type of a variable


Dim x as integer becomes

Dim intx as integer

I used to do this because I thought it made my code look ‘more professional’, harder to read, but ‘more professional looking’. Then I read this, which totally cleared up why I didn’t like data type prefixing.

And then I read Code Complete by Steve McConnell.

Around the turn of the century Microsoft released their naming convention advice for .net (ie – business applications), which built on advice from MFC era in the ’90’s. (note the advice ‘Do not use Hungarian notation’ here)

My advice is simple, and here (near the bottom) – basically just choose a good meaningful name. That presupposes other things like general design, procedure length etc are sensible.

I think, like in many things, if something works for you, then great, use it, if it doesn’t then don’t.

Just in case you didn’t know the VBAIDE has had the locals window feature since the last millennium:

Locals Window - clear full data type info

Locals Window - clear full data type info

(You will notice I havent discussed scope prefixes – I find them quite useful if I have to use non proc level variables)

As it happens I do use frm, qry etc in Access, but hey maybe thats just another habit I need to kick. Well, and hands up, I still often use a single letter data prefix, (s, l, d, r, etc).

So, do you use data type prefixes? If so why?

Do you use ‘Original Hungarian’ – where the prefix indicates the usage, rather than the somewhat less valuable underlying datatype?

Do you still use the ‘my’ prefix so beloved in MS VBA documentation (myString, myVariant, etc)? (‘my’ as opposed to whose??)

Do you use carefully thought out, clear unambigous names?

Do you just type any old stuff in to get the thing to run?



(I do all of the above!)

Silverlight loss

Wednesday, 8th April, 2009

Last year there was a big hoo ha about Silverlight being used to stream some big game action in the US.

This spurred on more Visual Studio hype, and probably diverted those 3 devs that were going to try VSTO to try Silverlight instead. Cue more ‘Wow its nearly as good as a desktop app was 10 years ago’ type fluff.

Anyway its game over for Silverlight streaming of Major league Baseball.

Hey all you spurned VS devs, why not try a reliable, futureproof* technology with a stunning compatibility story? One with clear and present business value?

(* futureproof within reason, as much as anything in IT can be futureproof)

So while everyone goes back to Flash for their pointless blinking text web presence the business world still lives and breathes Excel/VBA and a bit of Access.

I wonder if Office 14 will have a Silverlight client?

Anyone here use Silverlight? (I’m on Linux, so it would have to be Moonlight for me, which just seems monumentally pointless)(so no – I haven’t tried it)



UK Excel user conf 2009 – report

Friday, 3rd April, 2009

We had an excellent event over the last couple of days at Microsoft London.

All the program info is over here so I won’t repeat it.

We had around 100 people for 2 days geeking out on Excel, which was great. Even better there are another 1-200 hundred signed up as reserves for the next event in October. This will be a complete repeat of the one we just did, specifically for those who couldn’t get in this time. I’m thinking I might stick to my script next time and not miss half of it out by accident!

I have had a quick glance at the feedback forms (in the pub – well outside it in the ffrrreeezing cold) and they are extremely positive for all the sessions.

I did sessions on working with the grid (clever cut n paste, and select special stuff) and an intro to VBA (setting up the IDE and a bit of macro recorder). I think the sessions could have been aimed a bit higher without leaving many of the audience behind, but it was advertised as a beginner level event.

Next time we can maybe have a proper fight about the relevance of convoluted naming conventions in VBA in Excel, and the value of classes and Object oriented designs in an application extension scripting technology, especially when that application does not support data hiding in any shape or form.

One interesting point – about half the delegates said they were mainly using 2007, yet 90% of the presentations were done in 2003. (I did one in Classique, and one in 2007 – fair’s fair).

If you came along I hope you enjoyed it, if not maybe see you in October.

Plans are already afoot for the 2010 series…



The road to Excel Services

Wednesday, 1st April, 2009

I am sure there are very few of us here naive enough to assume tinkering with Excel services would be a 10 minute job. But just in case, I’m here to tell you that was not the case for me!!

This post will document my battles to try out this technology.

Hopefully we are all aware that Excel services is a newish feature of Sharepoint 2007. Bear in mind its only in the Enterprise version of Microsoft Office Sharepoint Server 2007 (catchy eh?). That would be your first check then – what version of MOSS2007 can you get access to?

Before that though you may want to consider how/where this MOSS2007 will be.

Here is my plan:

On a virtual machine on a USB drive. Yep I know the performance will be poor. Maybe I can delete a few Gb of junk off my laptop and squeeze it on there. But experience tells me messing with virtual images when disc space is tight is bad news.

So the first thing is that Virtual pc is not up to the job. You need virtual server. Thanks to rather aggressive competition in that area, those nice Microsoftians give this product away. I won’t link to where it was because it won’t be there by the time this get published, probably.

Anyway I bought myself a new USB drive (WD Passport – bus powered, handy). It came formatted as FAT32 – great for interoperability, crap for files larger than 2Gb. I tried to repartition to leave some FAT32 and make most of it NTFS. Partition Manager was not prepared to do that for me, Ghost crashed (as usual). So in the end I just reformatted the whole thing to NTFS – this took over an hour!

I should have checked what Ubuntu could have done – next time…

MS do a trial version of MOSS as a virtual image on Windows Server 2003. This was my target, this is six 700 Mb downloads. But it saves any install hassles. It’s time limited for 30 days and includes Office 2007. You can just recycle the virtual image after 30 days. This seems fair enough to me, although its easy enough to find 60/90/180 day trial versions of many of Microsofts products.

The first version I found had a hard expiry date of October 2008 not a right lot of use – did they really think they would have 100% uptake by then, with no more need for demos??

It’s bloody hard to work through Microsofts maze of shitty names – there are only a couple of differentiating characters in each 90 character name.

Anway I found this one. Which works.

March update – of course a few images have expired, and been reinstalled.

Anyway on getting VS up (needed me to reset my default web site in IIS) you need to create a new site – choose the document library one.

Setting up Sharepoint is pretty obvious, once you get used to the limitations of a brwser interface for an app that patently need a proper UI.

Once you have a document library you can open Excel 2007 and publish docs to it. You can then browse to them.

In Excel you can set up range names as usual, but when saving to SP you set them as parameters. You can then modify these values in the browser version later and see the impact.

I’ve only just dipped my toe in the water, I hope to do more before this current image expires. In particular I want to find a compelling use case, I’m sure the technology has value, I just havent worked out where I can apply it in the stuff that I do yet.

Anyone else working with ES? In what circumstances?