Archive for September, 2007

Excel 2007 calculation bug test code

Tuesday, 25th September, 2007

Here is a rough couple of VBA procs to test for any Excel 2007 calculation bug problems where your spreadsheet may be showing 100,000 when it really means 65,535. (eg 850*77.1)

The code looks at formula cells to see if the displayed value is the same as the underlying val (truncated to longs) if not it warns.

Its as rough as, only tested very basically, don’t sue me if its rubbish style. Feel free to post back with any improvements. This just punts possible probs to the immediate window.

(note alt T M M works in 2k7 to list and run macros)

Cheers

Simon

EDIT – link to .txt added at bottom

Edit2 – this isn’t going to work if you have special number formatting that presents the numbers different from the underlying value – like rounded thousands for example (Thanks Ed). Try saving a temp ‘test only version’ and remove the formatting then don’t save. Ugly I know, Anyone got better code?

EDIT 3DG has an explanation of the issue and what they are doing about it on the Excel Blog.

[have I got enough disclaimers??]

Option Explicit

Sub findCalcBlunder()
‘// courtesy of http://www.codematic.net – no warranties blah blah…
‘// feel free to modify adapt and improve – post better code at smurfonspreadsheets.co.uk
‘// checks displayed no == cell value.
‘// rushed and flaky – likely to fail and or be slow on big sheets
Dim rFormulas As Range
Dim clTest As Range
Dim ws As Worksheet
Dim lBlundercount As Long
Dim bHash As Boolean
Dim sText As String

On Error GoTo err_h
Application.Calculation = xlCalculationManual

For Each ws In ActiveWorkbook.Worksheets
Set rFormulas = safewrapFormulas2(ws) ‘limit of 8000 ranges?
If Not rFormulas Is Nothing Then
For Each clTest In rFormulas.Cells
sText = clTest.Text
If InStr(1, sText, “#”, vbTextCompare) = 0 Then
If IsNumeric(sText) Then
If CLng(sText) <> CLng(clTest.Value) Then
lBlundercount = lBlundercount + 1
Debug.Print “sht: ” & ws.Name, _
“cell: ” & clTest.Address, _
“formula: ” & clTest.Formula, _
“text val: ” & sText, _
“real val: ” & clTest.Value2
Else
‘leave it
End If
Else
‘nan leave it
End If
Else
bHash = True ‘ col width
End If
Next clTest
Else
‘blank sheet
End If
Next ws

If lBlundercount > 0 Then
MsgBox lBlundercount & ” potential errors were found” & vbCrLf & _
“Check the immediate window for more info”, vbExclamation
Else
MsgBox “no obvious errors were found this time”, vbExclamation
End If

If bHash Then
MsgBox “Some cells could not be checked as the values were not displayed” & vbCrLf & _
“This is probably due to some columns being so narrow they display ‘###'”, vbExclamation
Else
‘no hash probs spotted
End If

Exit_proc:
Application.Calculation = xlCalculationAutomatic
Exit Sub

err_h:
MsgBox “Error ” & Err.Number & vbCrLf & _
” (” & Err.Description & “) ” & vbCrLf & “in findCalcBlunder”
Resume Exit_proc

End Sub

Private Function safewrapFormulas2(s As Worksheet) As Range
On Error GoTo err_h
Set safewrapFormulas2 = s.Cells.SpecialCells(xlCellTypeFormulas)
exit_here:
Exit Function
err_h:
Set safewrapFormulas2 = Nothing
End Function

[Edit – text file here as WP seems to have mangled the code a bit]

Excel 2007 Calc bug

Tuesday, 25th September, 2007

Harlan pointed this out in a comment on the fluent UI strategy post, having spotted it in the newsgroups.

If you type

=850*77.1 in a cell it results is 100,000 rather than the more traditional 65,535.

It seems many combinations that result in 65,535 have been updated to 100,000. (could it be a covert metrification attempt?)

Slahdot has some plausible explanations and some potential uses (try paying 655.35 quid into your bank account for example).

This looks a bit like a new-for-2007 optimisation that has a border case error, or an off by one error. How wide the impact might be I suspect we will never find out. I suspect this only affects in cell cal…. Whoooaa scratch that, this is a bit more serious.

Put any 2 numbers that when multiplied give 65,535 or 65,536 in 2 different cellsĀ  multiply them in another, in about 1 out of 10 times it will give the wrong answer (100,000 or 100,001).

I did this in 2003:

col A = row()

col b = 65,535/A1 or A2 etc

copy and paste (values – it did that by default??) into 2k7 and in col C put A*B and let the fun begin.

In col D put =C1=100000 – it never says true. And in most calcs it seems to know its really 65,535 or 6.

You can also do combinations of multiply and add to get wrong answers.

How serious is it? I don’t know – do you?

What caused it? I don’t know – do you? it does look like some bit diddling rather than a simple overflow though.

Am I glad I’m still using 2003? oh yes. Are you?

cheers

Simon

Security dilemma

Tuesday, 25th September, 2007

Office 2003 SP3 is heavily focused on security patches. Thats great, no-one wants to get pwned opening an office doc, but heres a thing:

The bad guys now have a list of loads of attack targets in non patched versions of Office. That will probably represent the majority of the market for a long time. (think 97 – 2002 ~ 50-60%?).

Service pack analysis is a standard security vulnerability discovery technique, and a simple way to find vulns in non current versions.

This is a catch 22 for Microsoft, if they don’t tell us enough info they’ll get a kicking, if they tell us enough so we can make an informed decision the bad guys get a bunch of targets for free. (And MS still get a kicking!)

I think SP2 had a lot of security stuff in it too, so at this stage I think my advice is to use 2003 if at all possible. Especially for any connected machines. I don’t think I would ever recommend 2007 as its new (with bugs) and the UI is rubbish. I am assuming much of this patch is either already in 2k7, or in SP1 which is currently in beta.

Would you see this a potential lever to encourage enterprises to migrate off 97/2k/2k2? I assume those are all completely unsupported by MS now? Or are people really not that bothered about security?

cheers

Simon

Excel 2003 SP3 and COM Addins

Monday, 24th September, 2007

Rob just posted a useful summary of SP3 changes on the earlier post here. And Dennis points out WOW are advising not to install yet.

That prompted to me to look at it a bit more, and this one may prove to be a headache for developers of COM add-ins and their customers:

Enable or Disable specific COM add-ins based on category

The rationale is sound enough:

Say you download a COM add-in (dll) or COM control (ocx) to try out, currently you could try and use it in many products in many places. Neat, but it may not work in many of those cases.

I have a COM add-in to do worksheet metrics, it makes no sense to allow people to open that in Word. So a simple way to cut down the attack surface is to force it to only load in Excel. (no Word, no PowerPoint, no Office forms, no Outlook, no IE etc etc)

Not too important for my metrics stuff, but if the component was malware you just reduced by 80/90% or more the chance of that thing being invoked. Or put it another way, you maybe just forced the bad guys to write (and test (yeah right!) and maintain) seperate versions of their tiresome badware for each Activex container they want to target. Oh, unless its just a case of registering it into each category? Which it looks like it is. And multiple categories is OK?

So thats the good, it should genuinely cut down on peoples vulnerability, and make social engineered mischief just that bit harder. The other good is that this new lock down is not turned on by default – so its not like anything should stop working.

The bad? Should some semi smart sys admin decide they will enable this (and over time that will make more and more sense – on by default in a few years time?) COM add-ins (including VBAIDE ones) will stop working unless they get ‘categorised’.

The possibly bad – I only saw an ExcelRTD category – not sure if that will cover all Excel COM and Automation add-ins.

I only saw a C++ snippet to register your add-in in a particular category – so I don’t know if you can do it in VB6 at all, or if its just a WINAPI call, or an external step.

I’m not sure if you could add your own categories, probably not.

Dunno if my shimmed .net COM add-ins can be categorised.

From what I have read it looks like registering a COM component will be a 2 step process if this ComCatCheck is enabled in your target environment. Register as normal, then register that Class ID under the various categories it should run under. For fun register yours in the white list and your competitors in the black (do not load) list – tee hee.

Thats the theory as I see it anyway. I’ll need to actually try it all out to understand it properly. I don’t think there are any COM add-ins as part of the standard Excel install that might break?

Anyone know any more?

Cheers

Simon

Fluent UI strategy

Monday, 24th September, 2007

I saw a great quote about the Ribbon here the other day.

In summary an Office User Experience [edit] person shows the ribbon to Bill Gates.

Bill says you will have a classic mode right?

UX [edit] person says no, that doesn’t fit with our vision for the future of Office.

Exqueeze me?

Having agreed to stop whinging about the ribbon, I wont pass comment except..

Jensen Harris said on his blog that Office 2003 was the classic mode of 2007. I’d say that is rather dismissive of the hard work of his colleagues in the product teams.

Imagine you’ve spent several intense years unpicking the Excel calculation engine to rebuild it multithreaded, and then someone says if you don’t like our crappy new UI just use the old product. Thats hardly going to make you feel good about yourself is it?

cheers

Simon

Renewed interest in VB6

Monday, 24th September, 2007

I mentioned recently that I was back doing VB6 work, yesterday I was doing VBA and realised that my VB IDE is more productive than my VBA one, just because of the tabbed editing. Seems I’m not the only one still using VB6.
I just tripped over a couple of VB6 resources that have appeared recently.

Karl Peterson who was an VB6 MVP for ever has just (September 07) been asked to write a new column in Visual Studio Magazine focusing exclusively on VB6.

A new website has been setup with a ton of on-line VB6 learning resources.

The content on KPs articles initially will be on getting VB6 stuff working on Vista, which is totally supported, but with many control updates, is unsurprisingly not zero touch.

Its great to see a bit of life in the old dog yet. What is interesting is the one thing many of us wanted to see was the runtimes included as part of the operating system. MS got close to that with the VB6 stuff by including it with all versions of Office since 2k, and I think it is native as part of XP and Vista. Vista also natively includes the .net framework (version 3.0 which is roughly 2.0 plus some UI stuff).

In a few years when(/if?) most people have moved to Vista then in theory that whole framework deployment adoption blocker should be lifted. Well it would be if each version of Visual Studio did not mandate a new version of the framework, and a new VS is being released every 2.5 years. In reality for many people the framework deployment will continue to be an adoption blocker unless IT departments wake up and schedule it as almost a service pack.

Whats ironic is that because VB6 is no longer being actively developed by Microsoft, it, and its runtimes have been stable for years. This makes it a very attractive development option, much more attractive than chasing the tail of the .net runtime in many cases. Having abandonware so attractive compared to flagship products should really wake a few people up at Redmond.

My big concern for VB6 is 64 bit, I am not totally clear on how well that story will pan out. I guess its a few years out yet. Anyone heard any sniffs of a 64 bit VB6 compiler?

Why do I even care about VB6? Mainly as a migration route from VBA, and nothing in .net compares.

Has anyone tried the VB6-VB.net migration wizard on some meaty VBA? Or is the .net/interop cost so high its not worth bothering?

Cheers

Simon

Dell and Linux

Monday, 24th September, 2007

A while ago I said my next pc would probably be a Dell as they were allegedly offering ubuntu preinstalled. Well it turns out these mystical machines are pretty thin on the ground and pretty hard to find and hard to buy, and more expensive than the Windows versions.

Basically the reality was a considerable disappointment from the expectations raised by their initial announcement. So my next pc won’t be a Dell after all. And having installed SLED 10 on an old laptop with only minimal excitement, I think my next machine will be a Vista one.

Someone once said always under promise and over deliver because you’ll never recover a disappointed customer. Dell obviously don’t believe that, I do. And that is the mantra I try to follow with my customers. I think that has lost me a few customers who maybe either thought I was offering too little, or going to take too long, or cost too much. I’d rather that than the battles around not delivering what was agreed, or squabbling over money.

On the pc front I think I will get a quad core desktop next to really exercise the multithreaded stuff in E2007. I have had laptops the last few times and havent been overly impressed with performance.

Whats your preference laptop or desktop, and how do you keep them synced if you use both – I have always made a pigs ear of this.

cheers

Simon

Boring

Saturday, 22nd September, 2007

Is Office/productivity suite/spreadsheet discussion boring?

I accept there are more effective ways of impressing the laydees on a night out, but is the whole area less exciting than watching paint dry?

The reason I ask is that I’m sure I have read comments like that. And I don’t see that many headlines on the bbc about our little world.

And yet this application area has had some serious big changes in the last 12 months.

  • Google apps
  • new Apple suite
  • VBA in OpenOffice
  • IBM release ‘some’ Office suites
  • MS Works touted as zero cost, advertising supported (why would anyone choose that over OOo??)
  • Oh and the small matter of MS Office 2007 (with its User Experience Astronaut (UEA) inflicted UI lemon)

Seems to me its been a pretty lively year. And yet in a world where vacuous time-drains like facebook and myspace assault us from every angle, no one really seems to give a shit. Although in fairness I guess not much has changed, MS Office’s market share may have moved a couple of nano points, but its hardly a new world order.

Is it just that this space has become a bit utility and a bit mundane. Like car tyres, essential for sure, but really not that sexy. Did I ever mention the donkey who put my front car tyres on back to front? I had a couple of life threatening moments before I noticed and swapped them. That was exciting for sure, ABS really isn’t all its cracked up to be. (Although it never wheel span under acceleration!).

BTW I saw (and heard) a new Ducati 1098 today, he was having more fun than me (it was sunny today). I only wish Subaru could afford a days worth of design input from Tamburini (916-998) or Fabbro (1098). The new scooby is butt ugly, as usual, lets hope Prodrive can rescue them again. I am going to skip the UEA dig I was going to add here.

I’ve always done plenty of stuff outside of spreadsheets and I’m thinking of increasing that. I’m thinking of maybe doing a bit more security stuff for one, as far as IT goes this is probably the ‘exciting’ side. Of course I expect the majority of work to still be spreadsheeting, but a bit of variety now and again wouldn’t go amiss. (I still havent worked out where to target on the adoption curve).

What do you reckon, is ours a rich and fertile area of software? Any plans to incorporate (more) work from non spreadsheet areas?

Cheers

Simon

Office 2003 serivce pack 3 released

Friday, 21st September, 2007

Title says it all really. A new service pack has just been released in the last couple of days for 2003 (around 20th sept?)

I can’t remember the precise details but I think its about 80%(?) security fixes. Some of these may well be related to zero day exploits and/or unadvertised holes. So if you dont want to get pwned you should get this installed.

more details here

I dont actually run 2003 on any machine I put on the web, but I guess I still need to install this to check my stuff will work with client machines that have it installed. And for protection for all the stuff I install to evaluate. This may be one to test in a virtual machine first though looking at the details.

I’m guessing, but I assume much of this stuff will be in the 2007 SP1 that was out in early beta a few weeks ago.

Let us know if you install it and it breaks something you needed.

Cheers

Simon

Seattle Sept 07

Friday, 21st September, 2007

I’ve just about recovered from the journey home from Seattle. Which is not far off 24 hours door to door.

I think pretty much all content is covered by NDA, and even some of the pub chat is probably NDA type stuff. I’d certainly like to suggest some of Dicks jokes as suitable for NDA! Luckily I’ve already forgotten most of them, so I’m unlikely to get myself into trouble.

I guess the big question for me is do I stick with my current market of laggards using 2k to 2k3, or do I move to 2k7 and move to the early adopter segment. Having seen some of what may be coming up in Office 14 I have to say that moving to these early adopters is looking more and more attractive. I’ll give this some more thought over the next wee while.

I don’t spose there is a big rush to decide with 2007 just out the door.

cheers

Simon