Archive for September, 2008

Do your career a favour – get training

Wednesday, 10th September, 2008

A couple of rare, non-novice Excel related training events happening soon:

The Excel user conference is in a couple of weeks, in Atlantic City in the US of A

Chart king Jon Peltier and data king Mike Alexander have a 2 day Excel dashboard and visualisation course in Dallas in the US of A in October. Last time I met Mike he made me laugh so much my face hurt.

Both well worth a look if you happen to be that side of the pond. For us over here of course our plummeting pound goes that bit less far. But thats good as this is the east coast, hence that bit nearer.

There is a hint of a sniff of a possible UK Excel conf next year, more news on that in due course.

cheers

Simon

Windows gurus, VS gurus, but Office gurus?

Tuesday, 9th September, 2008

Chatting with one of my buddies the other day something dawned on me.

To my knowledge quite a few Windows gurus have taken up the blue card and become Microsoft employees. Eg the SysInternals team

Also quite a few Visual Studio devs I know from the conference circuit have made the same move. I’ve noticed quite a few MVPs having to give up their award on taking up employment with MS. In fact that seemed a ‘normal’ career path – get good, present a couple of MSDN roadshows, get a job.

It seems to go Win experts get put on the product team, VS experts get put on the evangelising team, Office experts get…?

I’m struggling to think of anyone I know or have heard of who has gone from well known Office guru/MVP to MS (Office) employee. I guess I can accept some of the VSTO team have come from this background, but core Office products?

Are there any?

Why?

Do you think community experts should be recruited? or should they be left to develop and contribute in the field (are the 2 exclusive?)

Now if you are a MS marketing wonk (‘margadeer’) you might be tempted to suggest why bother? and that Office already owns the office productivity space as it has 90+ % marketshare. That would be a FAIL. Your predecessors may own the market but the current version does not, and is in desperate need of some marketing (and UI) investment.

cheers

Simon

[personally I think the Office product teams are desperate for external expertise, but I don’t know if they know it. But I also think the Office user/Office dev community would really welcome this as a sign of commitment to Office as a developer platform/tool/framework.]

FileSearch in Office 2007

Monday, 8th September, 2008

Does anyone know why MS removed the FileSearch object from Office 2007?

I’ve seen a few newsgroup postings from folks struggling to work out how they are going to migrate to 2007 because all their file code now breaks.

Fear ye not – Codematic to the rescue, we have a replacement VBA code module that is a drop in replacement for the FileSearch object. It is a commercial product so it comes with proper support and a full redistributable licence.

Its currently only 15GBP + vat which is dirt cheap (too cheap I’ve been told) considering the effort required to recode all your filesearch.execute stuff as Dir() etc.

You can buy it direct and download it immediately from here.

If you can think of any other features that have been mislaid on the journey from 2003 to 2007 then leave a comment and let us know. Don’t bother suggesting they forgot a proper UI, I’ve looked into fixing that robustly (to a level I would be prepared to support commercially) and its a ton of work.

The fileSearch replacement is imaginatively called AltFileSearch. Something tells me thats going to be a bigger brand than Google in 2009.

Or maybe not.

Cheers

Simon

Excel row height annoyance

Saturday, 6th September, 2008

Like plenty of other people I use Excel to build all sorts of text based things.

I used Excel to build the 20 or 30 page html help file that comes with XLAnalyst (new web site and version due out possibly before Christmas, with lots of juicy xll based speedups).

I’ve also used it for all manner of dodgy data cleaning projects that should theoretically have been done in a database.


One thing that has regularly annoyed me is the bug some patronising dev introduced, possibly in XP. Where if you enter more than 1 line of text (eg with Alt+enter) when you leave the cell they helpfully extend the row height to ruin your spreadsheet. Doing cntrl z should put it right, that often tells Word to do as its told, not what it thinks you want, but no, in Excel you can’t turn it off. Or teach it to do what its been asked.

It dawned on me the other day that if I grab the row height on selection move and check it after data has been entered I can fix this usability blunder (perhaps the person responsible went on the found the ribbon team?).

So here it is:

Private dRowHeight As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   On Error Resume Next
   dRowHeight = Target.RowHeight
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If dRowHeight > 0 Then Target.RowHeight = dRowHeight
   Debug.Print dRowHeight
End Sub

Now with this needing to be a global setting really, then it needs to go in a class. Then you have a VBA project floating around messing up the project explorer. So then it makes sense to use something else. Xlls don’t see the selection change event (sadly – they do see the data entered one). So that leaves a COM Add-in.

I already have a VB6 COM add-in to stop Excel launching its pointless reviewing toolbar every time I open a workbook I did not write. So maybe what I (/we?) need is a MyExcel add-in. What other Excel annoyances would you like to go away?

(All credit to Excel for making it possible to fix these issues, some products (and UI’s ;-)) aren’t so powerful.)

cheers

Simon

[ps does it still do this in 2007?]

Google Apps hits 1 million businesses

Thursday, 4th September, 2008

Heres the details.

Anyone here using it?

Of course I don’t use it as I don’t have a burning need to share documents that email doesn’t already address. I guess I should show more willing for these things as everyone is telling us collaboration is key and this is collaboration. What about you?

I suspect as I’m signed up for Gmail and youtube I may be one of them. I notice that OOo has pretty good (sounding) google docs integration. I havent really used it of course,

cheers

Simon

[ps I find the numbers coincidental – not sure which was the first report Google or MS.]

Office Live Workspace hits 1 million users

Wednesday, 3rd September, 2008

Heres the details.

Anyone here using it?

It sounds to me like a sensible mix, full rich client for the main creation stuff and then some lightweight viewer that can also do minor mods via a browser.

Of course I don’t use it as I don’t have a burning need to share documents that email doesn’t already address. I guess I should show more willing for these things as everyone is telling us collaboration is key and this is collaboration. What about you?

cheers

Simon

Codematic status

Wednesday, 3rd September, 2008

Codematic.co.uk and codematic.net both seem to be back up and working sort of ok. Links within the site are ok. Links from external sites (like here!) probably don’t work.

I need to get onto the web team to fix that. Shes cooking tea at the moment though!

(I hope its chips…)

cheers

Simon

Codematic.net MIA

Wednesday, 3rd September, 2008

Rather unsurprisingly codematic.net is missing in action during the web host move. Codematic.co.uk is however kind of working. Yay!

No codematic.net email is getting through, so please use a spreadsheethell address or a codematic.co.uk one.

Of course the codematic.net one is the main one I have been giving out for the last 10 years so this is a rather sub-optimal. Hopefully if I ask nicely tomorrow we could be back in business. Business at the speed of light right?

Presumably its lost in the cloud/fog…

I hope you have been doing something more productive than me these last few days.

cheers

Simon

xlls v VB6 automation addins

Tuesday, 2nd September, 2008

Ok so I admit it, Mike got me spooked.

I tried VB6 automation addins a (good) few years ago in a vain attempt to rescue a big project that was going to get canned because we couldn’t meet the performance bar. Automation add-ins failed to help at all, the project got binned and Automation add-ins got binned in my eyes too.

Then I tried them again in C#, loved the theory did not love the slooow ‘performance’.

So I bit the bullet and went the xll route, and have never regretted that investment.

Anyway I thought I had better test my view formed all those years ago still held true.

I used a simple function that takes 2 double and adds them and returns the answer as a double. (20k calculated 10 times, averages of 10 runs)(measured with GetTickCount)

xll VB6 VBA
Average 580 3164 3556
Std Dev 89 222 476
Std Dev % 15 7 13
% of xll 100 546 613
% of VBA 16 89 100

I am both relieved, and disappointed, relieved that things havent changed much so I wasn’t far out. And disappointed that the boost from VB6 isn’t better.

Moving from VBA to VB6 will save just over 10%, moving to an xll will save almost 85% calculations time.

(I compiled the VBA and closed the IDE)

Bear in mind this test makes no use of any part of the object model. Using range object (and C API equivalent) for example would likely improve the relative performance of xlls and VBA. Intensive maths would likely improve xlls and VB6 relative to VBA.

For me the effort of needing to distribute a dll that the user needs to register would rarely be worth a 10% speed up. What about you?

There are other benefits of using VB6 like improved code security, nicer forms, more powerful IDE. Weighed against its supposed imminent demise of course. The official alternative .net is nice, I havent tested that performance for a while, or in a recent version, but last time I did it was way worse than the worst of VB6/VBA.

Does anyone here regularly write UDFs in stuff to distribute?

What tools/technologies do you use?

Do you turn calc to manual?

cheers

Simon

(my answers:

Yes I distribute UDFs

xlls and xlm, very very rarely I might do something in VBA if its just for a handful of cells.

No I much prefer automatic calc, and a ‘process’ button if needed to trigger a VBA routine)

VB6 and xlls

Tuesday, 2nd September, 2008

Mike asked a couple of interesting questions:

———————————————————-

Hey Simon,

What did you mean exactly with respect to:

(1) “VB6 and .net can produce something they call dlls, but aren’t the usual native code ones. They are activex ones that don’t export symbols in a way Excel can understand.”

(2) “An xll is just a dll with some registration info excel can use to call it directly from a cell.”

Other than a little bit of COM doing the marshalling, shouldn’t a VB6 automation add-in be pretty darn close functionally to a XLL? I’m not a C++/XLL guy at all though, so I’d love to understand what you mean here better…

———————————————————-

Quick summary – (IMHO) the only way to get performant UDFs in Excel is the use the C API, C/C++ and Delphi are about the only tools that can use that API. VB6 and .net languages can’t use the API so they can write fast worksheet functions. (But see ExceDNA for .net)

Detail – My view is there is not that much of an execution speed difference between VBA, VB6 and C/C++, and probably C#/.net. Note I said execution speed. Now I’m happy to accept that in certain corner cases any one of those will be materially faster than the others. But to save a religious war lets say all devs favour their preferred language.

Where there is a monumental difference is in how you trigger the execution from Excel.

Simply put: if you use COM you pay a significant cost per call.

All the previous work I did trying to quantify that can be found here (took ages to find!):

If you don’t want to use COM the only other realistic choice for cell based worksheet functions is the C API. (note automation add-ins (of all flavours) are using COM underneath, as is VBA, XLM isn’t and can be fast as a consequence)

To use the C API your chosen programming language needs to export each function you want to call in a way Excel can understand. And it needs to tell Excel what functions it contains. To do that your chosen language must export using the C calling convention. VB dlls only export those functions required by the COM runtimes, as do C/C++ COM dlls (see following depends screenshots).

Here is depends on 2 COM dlls one in VB6 the other in C++ (ATL). If you look in the bottom right hand corner box you can see they only export a bunch of register unregister type stuff, no recognisable functions:

Depends on VB6 COM addin

Depends on VB6 COM addin

Depends on C++ ATL COM dll

Depends on C++ ATL COM dll

.net dlls dont seem to export anything and connect up to mscoree to sort everything out for them. That makes sense, although I could be wrong as my .net skillz are a bit rusty these days as I find it so irrelevant for the Excel based stuff I do.

Here are the exports for a win32 dll (note how those 5 callable function are exported and visible (these could be called from VBA with a Declare Function…)):

Depends on win32 dll

Depends on win32 dll

Here are the exports for the same dll converted to an xll (note the 3 additional exports xlAddInManagerInfo, xlAutoClose and xlAutoOpen):

Depends for an xll

Depends for an xll, I temporarily changed the extension to dll

In the xlAutoOpen the xll tells Excel about speedtest 1-5, what arguments they take, what data types they return by using the REGISTER() function. You could write that same xlAutoOpen routine in VBA or XLM to register speedtest1-2 from the win32 dll above. To do that in XLM –

=REGISTER(“path to dll”, “SpeedTest1″,”JJN”, “spdTstDemo”, , 1)

You could then call the SpeedTest1 dll function directly from a worksheet cell by using =spdTstDemo(2,3).

Hence my comment that an xll is just just a dll + some registration stuff (a set of =REGISTER()s to be precise)

Thats the direct answer to number 2.

My answer for 1. is that as neither VB or .net can talk to Excel through this fast interface you can’t write fast functions with them. Its nothing to do with how fast they run, just being unable to utilise this fast interface. You could argue, and I wouldn’t disagree, that the real issue is the poor performance of the COM interface, and stacked on that the even poorer performance of the .net interop layer.

ExcelDNA passes .net functions through the C interface, if a future version of Excel had something like this functionality then xlls and C/C++ could finally be laid to rest.

Now its just possible you could contort VB6 in a even more unsupported way than normal to export its functions and an xlAutoOpen in a C stylee, And .net could probably do it at a push. Personally I prefer to use C, the language that the C API was designed for right?

The final point, isn’t a VB6 automation addin pretty similar to an xll? Answer: maybe but its different in that critical way of not using the fast interface.

Hope that makes sense, and helps, leave a comment either way, with further questions or comments especially if I’ve got anything wrong.

cheers

Simon