Office 2007 Service Pack 1

Service Pack 1 for the 2007 Microsoft Office System is available.

I found the link

its here

The published date says 8th Dec but I had a ratch around the other day and couldn’t find it.

Its 200 Megs

The link to the MSDN doco is not working, I suspect its not been properly published yet.

I’ve read suggestions that its not that big of a change, a bit more performance here, a bit less crashing there. (No mention of a grown-ups UI)

Let us know what you find below, I’ll post back when I get it installed.

cheers

Simon

Consistency or quality?

I’m working on a remote spreadsheet patch that will be applied to a few thousand workbooks worldwide for one client. I didn’t write the base spreadsheet originally but have maintained it for years. Its a standard big gnarly finance model with a few hundred thousand formulas and 6 thousand lines of VBA.

The remote patch tool I use can pretty much make any changes, including swaping worksheets, replacing formulas and array formulas etc. It can also add/remove/edit the VBA, and it cleans the VBA on each application.

This particular patch is a pretty deep surgery one, normally its just salary rate, currency rate and some other financial reference data changes. This time though the business needs have changed and some reports need re-working.

One of the frustrations is the hardcoded range addresses in the VBA.

public const countycol = 64
Public const currency = "J33:L66"
etc

I have to change these each patch – if only they had used range names…

So now I need to add some more.

1. Should I stay consistent and use the same crappy hardcoded approach or
2. Should I add my new ones as defined names so they at least work right and won’t need further maintenance?
3. Should I rip out the working code because its ugly and redo it in what I currently think is a better way?
4. Other options?

I’m currently planning on going with 1 unless someone comes up with something pretty convincing in favour of one of the other approaches. Heres why:

• Any further maintenance will have to update the current ones anyway, a couple more will add seconds to the next patch.
• Starting changing the mechanics now could backfire in future patches
• I think I prefer a consistent (ly poor) approach to hit and miss quality

2. will probably not get updated right as the assumption until now has been there is no need to insert rows in the worksheet, as the ranges are defined in code.

3. might be nice, but the client won’t pay because they won’t see any benefit.

4.?

What do you think? Any other reasons to go with option 1? Or reasons to avoid it?

Does anyone else do this remote patching? what approach do you follow?

(we tried to export the data to a new model, with the new logic, rather than apply new logic to existing data, but the users data proved to be too unstructured for that to work reliably.)

cheers

Simon

Excel Conference reports

I finally got the conference reports done (using ScribeFire – neat (a Firefox blogging addon, cleaner than the WordPress UI))

They are here

Feel free to chip in.

I think that the conference blog will quieten down for a while now until we ramp up for the 2008 event. Currently that is pencilled in for June or September or both.

cheers

Simon

Some gory VBA details

I found a great link the other day describing the decision to drop VBA in the next version of Mac Office.

Here it is

Well worth a read (its long mind), although I’m not sure if it gives those of us in the Windoze world any guidance for the fate of our beloved VBA.

In the ‘Future of VBA’ session at the conf I estimated 2020 something. Anyone else prepared to stick their neck out?

If you have time to read that whole post and all the comments (and you probably shouldn’t!)(I still did it though – thats another hour of my life I’ll never get back). But if you do there are some really juicy gossipy tidbits. All pure blog sphere gossip, could be completely untrue, in fact almost certainly is untrue actually.

Jonathan said: ‘Steven Sinofsky (at the time the Group VP in charge of Office) has made several public statements that VBA would remain in for “at least the next two full product cycles” of Office. The first of those is Office 2007′

Richard said: ‘nd that office for x86-64 is dropping VBA support.’ [Anyone else heard this? I’m not convinved]

Nick Hodge? Nick Hodge? Oh, its the other one, not the ‘real’ one.

Various: a version of .net for MacOS might solve all the issues (and re-align the Mac and Win programmabilty story)

Various: Dropping VBA is the start of the end for MacBU.

One interesting factor from a business POV is that the single shining USP (unique selling point) of Mac Office is Win Office compatibility I’d have thought? surely?

I don’t recall ever reading advice that dumping your USP was going to improve business.

Also interesting was the mention of difficulty filling vacant roles in the MacBU. I’ve seen this mentioned elsewhere about MS in general, and the fashion to want to work at Google instead.

There is a good technical explanation of some of the guts of VBA that are almost universally derided by the commentors. Personally I suspect that code is hard to maintain because it was coded to eek out the maximum performance on a platform less powerful than a modern mobile phone. Not because the coders were ‘brain dead’. (That and the fact there are few C coders still coding now)

I had my win 3.11 lapper with Office 5.0 at the conf, circa 1993 vintage.

80486 proc, 640k RAM, 163 Mb Hard drive. [Edit – caught red-handed by Harlan, there is 3 Mb Extended Ram too]:

VBA was conceived, designed, coded, built and tested before that time, and worked acceptably. And I bet some of the guts of the Excel calc engine hail from that era too.

I really don’t agree with the often stated modern view that performance is not an issue anymore. Maybe not if you are waiting for a web or database connection or user input action , but for the number crunching I see and do, performance is extremely important. What about you?

Cheers

Simon

Anyone off to the MS Office Dev conf?

Heres the link.

Here is some info on the tracks

Its in San Jose, California in Feb. Which would make a nice change from the freezing drizzle we’ll be enjoying in Cumbria.

I toyed with the idea of presenting (well offering), probably trying for the real world track (whaddya mean I’d never get on the executive track??!)

But when I really thought about it I’m not sure the event is aimed at people like me. As the agenda is not finalised yet I couldn’t check. (I sometimes feel a bit like me and my clients are getting left behind by Microsoft).

I live in fear of Microsoft conferences overrun with Sharepoint content, and Contoso use cases. Its not that I don’t like Sharepoint, far from it, its just a bit far away from my day to day focus. I’ve never worked anywhere that resembled Contoso or Adventureworks or FoodMart. If they invented a new use case called BigBankWithObstructiveISDepartment then that would be something I could relate too.

I’m delighted that MS are putting on the event, and they recognise Office developers as a valid and important part of the ecosystem. I hope they get the attendance to prove it.

If it were in Europe I might be able to get it past the budget committee (even though its probably definitely cheaper travel and accom in the US). I actually have clients nearby in Mountain View, but the chances of getting the two to tie up are pretty slim.

Is anyone planning on going? Anyone proposed a session? Anyone know more on the content? Or speakers? Anyone from the Excel community presenting?

Cheers

Simon

Security – pah!

Most ‘security’ arguments are little more than thinly disguised attempts by IT departments to get back the power and control they had in the golden era of the mainframe.

That pretty much sums up the view of most of us at the dev conf last Saturday. And I think a fair bit of the web app and server based systems focus was put down to similar motivations.

I’m not saying security isn’t important and neither was anyone else, just that as business developers working in and around Excel it probably doesn’t need to be top of our agenda.

What do you think?

In most organisations it really is someone else’s job, or possibly something else’s job (Firewall, Anti virus etc etc). That’s not to say we abdicate all responsibility, just assign a level appropriate to our exposure.

I think for developers in other areas, especially web apps that are connected to the wild wild web, security does need serious focus. For most Excel/VBA stuff I’m not so sure.

Many of the delegates set macro security to low preferring instead to rely on other controls, like trust of where a workbook comes from and AV scanning etc. Personally I prefer medium, but having discussed it I can understand why others feel comfortable with low. (of course I can’t see how to set Excel 2007 to my preferred setting.)

Incidentally Charles also pointed out the most recent VBA malware report he could find was from 2002. I’m more concerned with crapware VBA (ostensibly innocent, but so badly written maliciousness may be closer).

Should our little part of the business/developer world worry more about security? what benefits would it bring? what real world losses could have been avoided? And what macro security setting do you use?

Cheers

Simon

Office 2007 SP1 due next week

Mary Jo Foley has the scoop here. Tue 11th Dec apparently

I’m not sure there is anything available officially about its contents. I think all the beta testers are still under NDA. And I’m not sure its been on that broad a beta either.

Performance, security and reliability were mentioned. (That doesnt sound like they fixed then UI then. SP2 maybe?)

I also read that MS sold 70 million Office licences (all versions) in YE 2007. Anyone know the version breakdown?

I plan to do some performance testing once SP1 has settled as I am keen to see the benefits of multithreaded calc. Although my crappy laptop (core duo) is reporting as one processor. I have my eye on a quad core box from Santa (let hope its not hankies and socks again then eh?)

Am I right in thinking VPC (and VMware presumably) use the physical processor?

Cheers

Simon

Excel Developer conf

Last Saturday a few of us Excel developers sat down and discussed many of the issues facing us and our projects.

Many of the topics have also been discussed here too.

One common point was the .net/office disconnect. Not necessarily the technology, but the cultural gap seemed to be a factor.

When I got back home I had the latest VSJ in my in-box with an ad for DevWeek. This is possibly the biggest .net developer training event in the UK. There is not one single session touching VSTO or COM add-ins, or anything to do with Office in fact. There is plenty of Silverlight fluff, of course.

I know Dennis and Johan, and others I’m sure, are making .net and Office work from a business POV.

But here are the factors I’m personally considering:

• Discussions at the event and in the pub suggesting .net has low appeal
• Stephanes comment about lack of control of .net framework version and
• Ross’ work to test that

Based on all of that I intend to completely ignore .net/VSTO for 2008. I’ll still tinker with C# for glue code etc, but I don’t even think I’ll bother looking much further at VS2008 VSTO. If we get VSTA in Excel 14 then I may invest some effort during the beta, but otherwise I’ll pass I think. (And even if VSTA is there, it will be a version 1.0 (or less!) technology which holds very little appeal.)

The load order issue makes code reliant on more recent frameworks more likely to fail than stuff that targets older frameworks. This just isn’t something I want to get tangled up in. [Edit: This is nowhere near as bad as I understood it to be – see Erics comment]. [note to self, trust but check before relying on stuff from the intarweb!]

I actually think that looking at Delphi will be a better investment of time for most Office oriented devs, but I’m personally going to focus more on C++ and xlls.

Most of my xll demo on Sat was in VS6.0, and Stephane has also raised concerns about deployment with all versions since then so it looks very much like I’m going to be stuck firmly in the nineties with my VB6, VBA, and VC++6.0.

I can’t help wondering if the Visual Studio team have lost the plot a little. Or maybe I(/we) just havent kept up and have missed the benefits of the deployment pain we now face?

Cheers

Simon

UK Excel User Conference report (brief)

Last Thur, Fri and Sat was the conf. It seemed to go well, the venue looked after us, the delegates gave lots of very positive feedback about the session content etc. I haven’t worked through the feedback forms properly, but just skimming them most were positive or very positive.

I lost my voice, which meant one of my slots was a pure croak – sorry to everyone who had to put up with that.

The social side worked well too, with lots of good food and good conversation.

I’m not long home so I’ll write more later.

If you didn’t come, you missed out.

cheers

Simon