Xlls and the ribbon UI

I thought I would do a quick post on the new features in the Excel 2007 xll SDK relating to the ribbon UI. So here is the summary:

_
_
_

And here is the detail:
_
_
_

Thats right, the 2007 xll dev kit and the Excel 2007 C API contain zero features that interact with the ribbon (unless I missed something??). And lets not forget xlls are the recommended add-in technology of choice for Excel 2007. Of course if you do any toolbar/menu interaction the ribbon will hijack it in the normal way. But there is no way to add tabs or anything, everything is going to get buried in the (second class) add-in tab/bin.

A couple of new constants I was hoping for:

xlcKillRibbon (optional - forever?)

xlcRevertToCommandBars

Maybe in the Office 14 SDK??

I thought the kit actually included an .xlam with the UI code in. I wouldn’t blame them for that, VBA is great for UI

• its quick and easy to write
• UI stuff doesn’t need to super fast
• No real intellectual property issues in creating a few buttons (is there a new inconsistent User Experience Astronaut term for button? ’switch’?’ or ‘кнопки’?)

But having ratched through it, I can’t just see the Ribbon manipulation stuff at all. And because the UEA’s have moffed up all the useful commands I have no idea how to find whats going on. It looks like the xll fires stuff into the ‘generic’ menu (as previously) which then ends up hidden in the add-ins ribbon tab. The .xlsm stuff just seems to contain a bunch of XLM - yay! Its great to see MS have not lost faith in this oldie but goldie technology. (reports of its death are premature as they say)

So if your xll based solution is to integrate natively in 2007 and its silly UI, it will need to be a multiple file thing. And possibly requiring registry access, and/or .net framework. Thats a bit crap really, a single file distribution is a much easier deployment story. Being a Ribbon refusnik I have no idea how to structure such a solution, and no immediate plans to find out.

I’m sure its down to a resource issue rather than a lack of love for the ribbon in the Excel team.

You can get to the ribbon using ATL as per this post. But that probably needs registration unlike xlls.

And its not quite as straightforward as xlfAddToolBar!

cheers

Simon

If…

Rob noticed something in the VBA code I posted the other day to look for that Excel calculation error (or presentation error depending on your view). I always put an Else clause even if its empty. Style? Quality? Habit?

I’ll stick my neck out a little here and say I think which ever works best for you is probably the ‘best’ for you. I personally prefer my way and can justify it (to myself anyway!), but I wouldn’t impose it on another dev. I’ve worked with lots of other peoples code and generally its not that hard to switch, as long as the main things are ok (option explicit, reasonable procedure length, sensible names etc).

Anyway heres my ‘If’ style and why:

If TheNormalCase then
‘ ‘do the normal processing
Else
‘ ‘the non-normal or error proc, or just a comment if no valid else process
End If


I always put the normal/usual/most common case first, all the exception stuff then drops to the lower part of the proc. I only do 3 or 4 levels of nesting max, beyond that I break out into a new procedure. I always put an else (I type the 3 lines together then arrow back up to fill out the logic) and I always consider what to do in the non normal case. If there is nothing to do I just put a comment of why there is no else code. Why bother? Well in Code Complete Steve McConnell discusses some research where missing else clauses was a major cause of bugs. Its his recommendation to put the normal case first too. (I try to do that in IF formulas too).

I rarely use goto but I do if I think it makes the code clearer, or faster if thats critical (and of course error handling).
I also rarely use the one line if statement like

If bOk then doSummat
If not blah than exit sub/function
Simply because there is so often an else to catch, or some tidying up to do.

I’ll post more about other elements of coding style, but I think a lot of it is just that -Style.

Whats your If style? and what are your thought processes around it?

Cheers

Simon

Excel 2007 calculation bug test code

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 3 - DG 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 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]

Twat nav

A new expression I picked up recently.

As far as I can tell it can refer to either the technology, or the user.

I generally read/hear at least one SatNav blunder story a week.

Anyone got one (a satnav)? good? bad? funny stories?

I have the option to get it on my Blackberry but havent bothered, am I missing much?

cheers

Simon

Great software day

The other day I was multitasking. I had some grubby spreadsheeting to do, and I was rebuilding my old laptop. That involved clearing 10 Gb of space for some new stuff to play with.

I mainly wanted to install my Resolver beta, and that needed .net framework 2.0. I’ve steered clear of installing 2.0 for fear of breaking some delicate 1.1 stuff. But with the Resolver download burning a hole in my hard drive I had to make some tough choices - hence clearing 10 Gb of junk. Go to their website to find out more. If anyone has a 1 or 2 sentence decent description please post it - I’ve tried a few unsuccessfully. (Its a cross between a spreadsheet and a full power IDE, with real time access to the calculation process?)

I installed VS2005 - looks good, I havent used it since RC1, I still use 6.0 and 2003 usually. I’m really holding out for 2008 as they have added some new native win32 stuff (C++ MFC updated for Vista, and some other stuff I think).

Then I saw the 2007 XLL SDK was released so I installed Office 2007 and the SDK. Everything seemed to work, the Resolver doco looks good, I intend to play with that whilst I am in the US next week.

I had to repartition the machine a couple of times (trying to not delete too much stuff) so this little lot took most of the day whilst I trundled along on my other machine.

The final piece, I got SLED 10 on the cover of a magazine and I wanted to see if I could get VBA working in OpenOffice on Linux. So I installed that (needed yet more HD repartitioning - I really had to insist it left my Windows partitions alone and kept to the 6Gb I unformatted for it). The install went well, normally I half expect Linux installs to fail with an error I do not understand at all. This time though all was sweet.

The Novell OpenOffice 2.1 that was included does not appear to be as VBA rich as the (Windows) OxygenOffice version I looked at before. Still, seeing a

Private Sub Hello
MsgBox “Hello from some sort of basic on Linux”
End Sub

working on linux is pretty neat.

With OxygenOffice you could use ActiveCell etc, but I havent got that working in Novell OpenOffice 2.1. I do know that these early 2.x’s are where the VBA support has been rapidly developing. 2.3 is currently in RC so maybe that will add more. Maybe us VB die-hards have a Linux flavoured future? (How ironic that would be!). Although I’m more of a C#/C++ fanboy these days.

Anyway back to the XLL SDK - I had a bit of a poke around and the documentation seems loads better than the 97 kit. There aren’t as many sample projects, but the updated framewrk and generic seem clear enough. You’ll still need macrofun.hlp to get the skinny on many of the function/command parameters.

I plan to do a series of posts ‘from VBA to xll’ over the coming weeks, probably using VS2005 targeting Excel 97-2007 rather than 2007 specific, what do you reckon?

One big issue with the XLL story in 2007 is backwards compatibility. As usual its very good in terms of Excel 2007 supporting stuff you wrote for previous versions (excluding UI issues (never an xll strong point)). However because Excel 2007 is so different internally (bigger grid, multi-threaded, etc) stuff you write for 2007 that takes advantage of these new features, isn’t going to work well in previous versions. (think crash).

Proper (ish) report on Resolver when I get back from Seattle, unless that Biggus Dickus makes me go out and drink my own bodyweight in Bud every night (again).

Cheers

Simon

Excel 2007 XLL SDK released

Yahoo! Its here at last.

Whilst the Excel 2007 software development kit (SDK) has been sort of available for a while, the real live version has just been released officially. Well, Danny Khen announced it on microsoft.public.excel.sdk anyway.

Get it here:

http://www.microsoft.com/downloads/details.aspx?familyid=5272e1d1-93ab-4bd4-af18-cb6bb487e1c4&displaylang=en

Read about it here:

http://msdn2.microsoft.com/en-us/library/bb687883.aspx

Read about it here? (soon?):

http://blogs.msdn.com/excel/

It contains updated versions of all the usual stuff including VS2005 projects - neat. I think the last one was VS6, or possibly VS5?

I havent looked at it yet, I literally downloaded it 5 mins ago, but rushed here to tell you first. Hows that for selflessness?

As with most SDKs it will give you a sore head if you are not happy with C. But this would be a good time to pick up some C. (May be a few blog posts in that?). A decent handle on XLM helps too, so there may be the odd post in that too.

The biggest thing for me is long string support, finally we can get past the 255 character limit. Second biggest thing is multithreaded calculation.

I’ll report more when I get chance to look at it properly (think weeks not hours!).

I don’t know if the SDK will work with the Express editions of VS, but I suspect it will. You may well need to download the Windows SDK to enable you to create win32 dlls and exes though.

What is in the XLL SDK?

A bunch of C/C++ resources that you can/must include in your own XLL projects. A couple of example VS projects, and a load of documentation.

Cheers

Simon

Back on with VB6

I did a lot of development with VB5 and VB6 in the late nineties/early 00’s. When .net 1.1 (2003) came out I changed focus to C#. I specifically have never looked at VB.net because the way the VB6 retirement has been handled has always made me believe VB was no longer a good investment of effort. Others disagree I know, which is fine. Whats great is that we can choose and it doesn’t make that much difference.

Anyway I’m back working in VB6 again. I have always preferred the VB6 editor to the VBA one, maybe thats why I go for VB forms over VBA ones.

One of the features I missed from using VS2003 is tabbed code editing. I looked at implementing this as a VBAIDE add-in, but the extensibility model is seriously poor. The VB6 editor is much richer.

I had a little hunt around the internet and found this:

Pretty neat I reckon - proper tabs in VB6, shame it won’t work in VBA. You might recognise MZ tools in there too (free).

The other thing I miss is the class view from VC++ 6.0:

I like that this allows you to go straight to any procedure or member variable. I have found one of these for VB6 too but not started using it yet.

I was quite surprised there are still tools available for VB6, some have had updates in the last couple of months. Reports of its death have been greatly exaggerated as they say.

Here is a link to the (free) tools I found at Planet Source code, well worth a visit.

Anyone got any other top VB6 tool links?

cheers

Simon

Firefox blocked

So soon after our browser discussion I read this on slashdot today:

http://slashdot.org/articles/07/08/17/1359206.shtml

Its about a web site that deliberately blocks Firefox because some users have the ad blocker installed. Their logic being the ads pay for the content, so not viewing them is akin to stealing the content. Some great debate and insightful comments at /. as usual.

I tried to look at the site but it just got /.’d so is unreachable at the moment even from IE.

do you feel bad for not viewing other peoples ads?

Also I found this site with some usage stats:

http://www.w3schools.com/browsers/browsers_stats.asp

roughly 35% ff, 35% IE6, 20% IE7, with IE6 losing share to ff and IE7. They also discuss how their stats may be biased.

cheers

Simon

Excel CALL()

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?

cheers

Simon

Dead machine part 2

[just a FYI - don't let this post stop the debate on tech books]

Careless careless careless.

http://forums.zonelabs.com/zonelabs/board/message?board.id=MalwareDiscussion&message.id=2466

Why oh why didn’t I google it first?, too complacent I guess (and it was getting late).

Seems like Zone Alarm killed my machine. Thats Zone Alarm *ANTI* Virus.

They recommended I delete my video driver! thanks guys!

And I thought Symantec was bad from taking up all my system resources, I don’t remember them telling me to kill my machine.

I’m happy to accept a proportion of the blame for being so naive and dumbly doing what they advised, but it still seems a bit poor from their side.

On a lighter note one of the guys on that forum followed the ZA advice and killed his machine, realised what he did, restored it, re ran the scan, then in a ‘Homer Simpsonesque moment’ thought he was attacked by the same trojan again and obediently killed his box again!

Looks like I’m gonna be changing AV vendor sooner rather than later!

I ran Windows in Safe mode (F8 on start up), re-installed the video drivers and rebooted normally. Everything is now working fine. I’m in the process of updating my virus defs before I get caught out like the bloke above!

So from 2 broken machines, I now have 2 working ones. cewl. I havent done any fee paying work, but now I have the machines to do it on. I actually went out yesterday to buy a new machine. But luckily (as it turns out) I couldn’t bring myself to pay so much more than internet price for so much less power, just to bring it home the same day.

And yes I guess this one wasn’t really MS/Windows fault either. In fact I think the AV community has kicked up a stink because MS specifically tried to prevent this type of AV driven blunder from killing Vista.

Lessons learnt: be even more sceptical, check properly - dont delete .inf’s willy nilly or was it .oem?.

I normally refresh my defs and immediately run a scan, I’m thinking I might update, wait 24hrs, check google, then run the scan in future. Anyone think that makes sense or already doing that?

Cheers

Simon