Excel Menu builder

Here is my (free) menu builder. Its for classic Excel only, but maybe one of you ribbon fanbois would modify it to work with 2007 too? Ribbon XML is so easy right?

I haven’t made much effort to tidy it up for release, its just as I use it (ie tested and working on my machine, last time I needed it, nothing more).

There are plenty of other menu builders around, but often they are worksheet driven. Thats great for .xls and .xla stuff, but as I do a lot of COM stuff its not ideal. My one uses a worksheet initially, but actually outputs a .bas module. That can then be imported to an excel based project or a VB6 one.

Watch out though as the code appends, so if you run it a few times with the same output file you end up with tons of repeated code. I am sure there was a reason why its like that, I can’t remember it though.

Here are the base settings:

And here is the table where you fill in the menu text, tooltips etc.

It only does basic buttons, you can just go in and adjust the VBA if you need any fancy stuff. It does a matching toolbar and menu. Just delete the code for the bits you dont want.

The workbook also has an embedded toolbar with the first 1,000 toolbar buttons on it, with their IDs. When you shut the workbook that stays around. You can hide it or delete it from Tools>>Customise>>Toolbars. I have never found these embedded toolbars very manageable, so I always create and destroy my menus on open and close. Doing that with 1,000 buttons is painfuly slow though.

The code is open and unprotected, but all the work is done concatenating strings in the worksheets (the hidden one called workings). It also has basic instructions.

Let us know what you think

cheers

Simon

Office 2007 VSTA scarlet pimpernel

They seek him here, they seek him there, they seek him everywhere. (now available as a ringtone!)

I thought I’d have a tinker with VSTA in Infopath 2007. So I fired up a clean Win XP VPC and installed Office 2007. (great install btw - one click, leave for half an hour job done - not like VS which needs a prod every few minutes)

(Bear in mind the inclusion of VSTA in Office 2007 was a much trumpeted feature.)

I click around trying to remember which one of the apps I’ve never used has VSTA, Google a bit follow some instructions and come to a dead end.

‘Feature not available’ the installer says - hmm. More Googling gets me here. In summary to use VSTA you need .net framework 2.0 (or higher presumably) and the MSXML security hole.

So I then have to go and download these 2 components install them on the VPC (which meant a whole additions/networking dance (3 reboots I think)) then rerun the Office installer (glad I left the temp install files on there) and now I think I’m in business. I havent had chance to check because I’m trying to get all the VPCs built for next week - I could have done without this multi hour diversion. And I don’t want to use up my 20 odd uses and end up locked out for the conf ;-).

But the big question is, if Office 2007 has a dependency on .net framework 2.0 for some of its functionality shouldn’t it distribute the framework as part of the Office install? (at least on those versions that include VSTA)?

I know Office and VS are on different release schedules but really, what chance is there of convergence and adoption if its this painful to even try the thing out? (never mind deploy a solution to 20,000 desktops)

In a way I think this hidden/missing/unexpected dependency thing sums up the .net experience. I can’t help wondering if the Office team are trying to avoid getting sucked into it, if so I can’t blame them. But just fess up and tell us to use C++ native code then we’ll know. (Excel SDK in C/C++?). (Oh and please expose all the new stuff to the C API too - ta)

Cheers

Simon

[Stop press - We have VSTA - not a right lot of use as I know completely nothing about the Infopath object model, but still its there.]

Riding 2 horses

I think I have finally worked out my current frustration. Trying to do serious Excel development and serious .net development is not really possible and getting harder. Like trying to ride 2 horses that are going in different directions.

I think this is a classic case of what Joel calls the MSDN camp v Raymond Chen camp. (VS being the MSDN, Excel being RC)

Excel is still COM based and native code based for xlls. And even if the next version were totally .netted up (or if 2k7 SP2 added proper .net stuff) it would still be 5+ years before that would make a viable business. And those are big ifs.

Visual Studio 2008 looks pretty good, and adoption seems to be going incredibly well. As to market penetration of .net framework 3.5 (NOT part of Vista (Vista has framework 3.0)) that could be some time coming.

VSTO (Visual Studio Tools for Office) is clearly the bridge between VS and Office. And the 2008 version looks good, however I have serious doubts about the market for this technology. And if it doesn’t gain the traction, what is going to happen to those few projects that do get deployed? I think its too early to call yet, but by the end of 2008 things should be clearer.

A big part of the VSTO problem is that all versions before 2008 (still in beta mind you), were challenging to deploy. Although the scene has just been quiet, not rife with deployment hassles. This make me think few people have even assessed it, never mind deployed to production. VS2008 looks to solve the deployment pain, so it could set the market alight.

So which horse should I stick with?

For the next 12 months I’m going to stick with Excel/xlls and the Chen camp. I’ll just keep half an eye out for big VSTO adoption trends, and do a full review at the end of 2008 (might be an Excel 14 alpha/beta floating around by then??).

What about you?

cheers

simon

Where next?

[note I recently had a sniff of Excel/VSTO work - this was written before that]

A while ago I mentioned that I was reviewing my skill set and the services Codematic offers. There seem to be 3 realistic options:

1. Continue in Excel/Access/VB/VBA/finance stuff ( Office 97-2003) - this is the safe proven option
2. Revise my .net skills and go for VSTO, in the same business area, focus on Office 2007 and 14.
3. Concentrate on C++ and xll type stuff

Client wise I suspect I’ll carry on with large orgs with a hint of financial services. There isn’t much around where I live so it will continue to be a mix of remote and travel.

1 and 3 leave me the option to sell software I write (micro ISV - I dislike that term enormously). I don’t see that option with the current VSTO deployment burden - others may disagree. Also I can’t get away from:

Jobserve doesn’t even believe VSTO exists, and this is what, a 4 year old technology?

I think technology-wise VSTO is great, as a developer its a good experience. Commercially I would have to say its a lemon, no offence like.

I think it really needs some serious marketing push from MS if its to get any kind of meaningful adoption. Basically all the pre-requisites must be distributed with Office IMO.
If Office 14 comes with all the VSTO plumbing (or Office 2k7 SP2? (or SP1??)), then I will probably move in this direction in 12-18 months. If they continue out of sync releases and needing admin rights for the VSTO runtimes deployment, then I’ll probably wait until it reaches 10% of Jobserves ‘Excel and VBA’ results.

If they fix the real world deployment pain that current VBA devs would face, then I think VSTO will take off. But not until the deployment story is as good or better than VBA, and that is a challenge I’d say.

So that really leaves a choice between VB and C++ for now. There is no real stampede to either of these technologies, but both have plenty of life, and both blow .net out of the water for Excel work. In reality I suspect I will end up doing a combination of these 2.

I can’t get away from how productive VB/VBA is for Excel dev, so the only way to add in more C++ is to offer services that VB/VBA can’t deliver. That basically means xlls. As this is the recommended add-in technology for 2007 I can’t see it going away anytime soon. And of course it leaves the door open for working with other spreadsheets (or even non-spreadsheets!) should Excels market domination blip.

I would love to wade in with the early adopter crowd, but I think for me the prudent approach is go C++/xlls rather than VSTO. I’ll continue to dabble in VSTO I think, but my main focus will be elsewhere.

What about you, any technology thoughts for the next few years? Do you think I’m a scaredy cat for not going bleeding edge with VSTO?

Cheers

Simon

R1C1 v A1 notation

Like many people I usually use A1 notation. There was an interesting thread on the Eusprig list a while ago about the benefits of using R1C1 instead (eg easier to see if a block contains similar relative formulas).

The challenge was to use R1C1 for a few days and then go back to A1 and see which you preferred.

I find I prefer A1 in general but every now and then drop into R1C1 for the odd thing. Certainly if you are using .cells(r,c) notation in VBA using R1C1 makes it easy to get the column numbers. But as I pretty much exclusively use named ranges to coordinate VBA and worksheets, its not that big of a win.

If I’m assigning a formula to a range I usually use .FormulaR1C1.

Funnily enough Dennis also emailed me today suggesting this as a topic. I’d be interested to hear what your preference is and why (and when), and what you don’t like about the other system.

I find that since making a conscious effort to try it more, I have found R1C1 to be better than A1 for some things. eg: I find formulas easier to understand in A1, but easier to compare relative ones in R1C1.

Cheers

Simon

VBA Functions

A while ago I read an interesting post here:

http://discuss.joelonsoftware.com/default.asp?joel.3.507923.45

A guy questioning the point of breaking code up into functions. He seemingly uses as few as possible. I’m the exact opposite - I almost use as many as possible, and I make them as short as possible (1 screen ~ 40 lines). No more than 3 or 4 parameters.

His argument is that the extra code of the build up, pass control and tear down of all those functions makes things harder not easier. Its an interesting approach. Personally I don’t think it would scale to over a couple of hundred lines of code max - what about you?

I’m a bit of a fan of Millers theorum (7, + or -2), but a few people have pointed out there is no evidence that it applies to programming. Suits me though.

Whats your view on it? do you even care about function length?

cheers

Simon

[My session at the Excel user conf will be looking at this and other VBA design issues in more detail]

Virtual Machine security

I read an interesting snippet from a security researcher the other day.

Apparently one of the recent bits of malware had specific code in it to detect if it was running on a virtual machine. And if so to shut down ASAP. Why? because the author knows that most researchers use VMs to investigate badware, reverse engineer it and come up with signatures that can be added to the AV detection list.

This then begs the question, would we be safer running on a virtual machine all the time? I keep meaning to do this, but never get around to investing the necessary time.

An interesting feature of VMware is the many freely available images you can download and try here. One thing someone pointed out to me that I had’t realised is any of these will run with the free version of VMware Player.

I don’t think Microsoft Virtual PC (also free now) can run them as they are mainly Linux based.

This strikes me as a great way to try out an operating system or product with no commitment, and no installation hassle. Stephane mentioned ages ago about distributing software as an ‘appliance’.

I love the idea, but I’m not clear how realistic it would be for a spreadsheet auditing tool for example. (compared to an xla and ignoring anyone who doesn’t use Excel). I have no idea of the licensing issues, but I assume using open source will be easier than proprietary.

Is anyone else evaluating their use of virtualisation s/w? What are your conclusions?

cheers

Simon

Free beta testers wanted

I’m slowly but surely releasing bits and bobs that I think people might find useful. They are targeted towards the pain points I have seen (and felt) whilst working with large and complex spreadsheets.

There are a bunch of them over at codematic. And I have a few others in various states of readiness. They are all free until or unless I can find a (pain free) way to make a few bob on them.

The latest one is here. No liability blah blah.

Its a print manager worksheet. Copy the sheet into a new workbook and it then lets you keep upto 10 lists of sheets that can be printed together with a simple double click.

Hopefully its self explanatory. All the code is in the class behind the sheet, you can do pretty much what you like with it. If you improve it (or fix it ;-)), it would be nice if you leave a comment with what you did and why. If you see any gaping holes in the functionality then leave a comment on that too.

Test it in a fresh Excel with non critical workbooks first. Tested on Excel 2003 and Excel XP, not 2007 or 2000, or 97.

Known issue 1: I had a few crashes, but I think it was a conflict with another add-in. My usually rock solid 2003 (SP1) has gone all soft recently - keels over a the first sign of trouble. The code is fully visible so feel free to tinker.

Known issue 2: The number of printed pages doesn’t work right if you have ‘fit to x high by y wide’ set.

If you do use it, then thanks for being a free beta tester please post back any feedback. Or email me.Other ideas of simple (ish) self-contained components welcome.

Cheers

Simon

UK Excel User Conference

The UK Excel User conference looks like it will be held in Cambridge on the 29/30 of November and the first of Dec (Thur/Fri/Sat). I went to a conf here last year - its a great spot.

More details to follow but get the date in your diary. Current plan is for the Saturday to be focused on developers, Thurs and Fri to be intermediate and advanced users.

Hope to see some of you there

cheers

Simon

Excel macro performance

The previous post was meant to discuss what sort of language we think might work best for programming Excel.

It got a little diverted onto performance, I’m happy with that, I’m extremely conscious of performance having had projects cancelled for being too slow in the past.

So here is a question about a VBA upgrade or replacement:

How important is performance?

I would split that into 2 areas, you may have others?

1. Excel object model manipulation performance
2. General code execution performance

I would say VBA is good enough at 2, but a bit slow at 1. And that seems to be the pattern, .net is even worse as it has to go through another layer of interop.

The only fast way for 1. is through the C API (AFAIK?) and that is incomplete and not being extended to include new features.

Ignoring for the time being whether you think the Excel COM interface is broken beyond repair, would poor performance (say compared to VBA) be an adoption blocker for you? Would an alternative need to be faster than VBA?

The reason I ask is that I don’t see that many signs that MS are too worried about performance. No one ever accused COM of being too fast, and .net doesn’t seem to be any faster. I’ll save my thoughts around that for another post.

For now: would you be willing to have a slower performing VBA replacement if it was easier to learn/work with?

Another point VBA is never going to participate in the new multithreading (even though it could), xlls can, should any new macro language?

I’m not sure one language/development environment can meet the wide variety of uses. So should high end devs move to .net/VSTO and users move to something simpler than VBA, and VBA just quietly disappear?

The first and last point I am pretty confident is exactly what MS want to happen, the middle point though I don’t know? That seems to be the target of the 2007 Access macros, but there is no equivalent for Excel.

What do you think?

cheers

Simon