Archive for May, 2008

Real inflation rate

Saturday, 31st May, 2008

I was chatting to a mate today who is a farmer. He reckons his inflation rate is at least 40%, regardless of the fantasy figures from the government. He was saying feed is up 100% on last year, fertiliser the same, red diesel up 30-40% or so.

That got me thinking what my inflation rate is, I reckon its well over 10%, probably more like 20%.

Based on how you spend your money (business or personal) what do you reckon your real inflation rate is running at?

I reckon I could lower mine a fair bit by buying a few new gadgets (if I could get it past Mrs S of course).



Soz for lack of posts

Thursday, 29th May, 2008

This week should have been post-tastic.

Last weekend was the day I finally dealt with my demons РI dealt with the pile of tax misery with which our government attempts to strangle  businesses large and small, and micro.

Oh yes, the chances are very strong that unless you had a family bereavement, or a life destroying accident then you probably had a more fun bank holiday weekend than me (and the rest of the smurfs – always happy to share the misery me!).

In fairness, I could take some of the blame for letting things stack up, but kicking the govt is much more fun.

Anyway I no longer fear the postman, I’m sure he will be bringing some fines soon, but hopefully the ‘warnings’ will stop.

Of course getting down and doing the work was not as hard as I expected, and the relief of finishing is wonderful. I’m sure I’ll end up late again next year though!

How are you with your company/personal admin, tax returns and general pointless government bureaucracy? late early, happy, angst ridden?

Spreadsheet angle? all my finances are driven from Excel pivot tables, tax calcs are spreadsheets too, published accounts are OLE linked word docs containing ranges from the accounts.

All the accounting stuff I have looked at has been way too clunky for a little company like Codematic, what do you use?



Macro recorder 3

Sunday, 25th May, 2008

One product idea I had a while ago was for a ‘smart macro recorder’.

Basically a way for beginning developers to leverage decent code that others had written in solutions that they deploy. In a way this is what the Baarns Developer jump start kit was, so maybe I’m thinking of an update to that.

I guess I had sort of started it with the menu and toolbar thingy I mentioned a while back. The idea being that a dev would say this client spreadsheet needs a toolbar and click a button to add all that code to the workbook. Then maybe add an fully coded up index sheet etc etc,

In the background a decent VBA proc would get written, with loops and ifs/selects as required.¬† Hmm maybe its not so much a recorder as a code generator…

I was also thinking of maybe adding a few common UDFs.

Do you think there would be a market for that?

Would people (developers) pay for it?

If it was open source would you have code to contribute?

What sort of things might it include?

Any other thoughts?



Tech investments

Friday, 23rd May, 2008

Another MM question:

“Will ExcelDNA in C# replace xlls in C/C++? C/C++ much faster? Wondering if I should invest time in C++.”

I don’t know about ExcelDNA performance, anyone else?

Here is my view on investments:

IMO C/C++ still has some legs.
If O2007 had had a better .net udf story then I would have said don’t bother with C/C++.
But thats not happened is it?
They updated the C/C++ xll SDK instead.
I read that as the Excel team are still committed to C/C++ for udfs.
Office 14 or 15 or 16?? might change things, but those are going to be mainstream 5+ years away. This is way too early to be investing in what may or may not be in those products I reckon. .net’s replacement might be out by then!

If you learn basic C and the xll interface you can use those skills today (in Excel dev mainly) and onwards for at least 10 years (Excel 97 still in use in 2008). If .net becomes a better option picking that up will be trivial (after the pain of the C API ;-) )

If you invest now in .net skills you need a third party tool to convert that knowledge into performant udfs. If you can deploy the all prerequisites in your environment, and you have other uses for .net skills then fair enough. .net and VSTO are clearly a major part of the future of Office development, as well as much other Windows dev work.

The reality of software dev is that you are always going to be learning, always investing, and payback can be fast, slow, direct or indirect, and occasionally never.

.net has more general applicability, C has a key role in advanced Excel development in all current versions.

What does everyone else think?



Tech choice

Thursday, 22nd May, 2008

MM asked why would you use xlls?

2 big reasons

  • if you have significant C/C++ resources you want to expose to Excel
  • if performance is important

Here is my tech choice thinking (from codematic) (very roughly):

[c= interactive, command style stuff, f = worksheet functions]

  • For quick and dirty: VBA (c and f)
  • for workbook specific: VBA (c and f) or XLM (way faster than VBA in many cases) (f mainly)
  • for application level: VBA (c) or xll (f)
  • For best performance: xll in C (f mainly)
  • for protecting intellectual property: VB6 or xll (c and f)
  • For future proofing (??*): .net (c and f if performance is not important)

The thing with custom worksheet functions (I struggle to call them UDFs (Used Defined Functions) when considering xlls as that is so clearly *NOT* a ‘user’ technology) is that you often end up with 100’s of thousands of them. So a tech that can save a thousandth of a second per function could save many minutes of calculation time.

It seems to me almost counter intuitive, people think of C as executing fast, which it does, but VBA is pretty fast too. So for a long complex function that only gets called a handful of times VBA may be a better bet than C considering dev time and test time etc. But where you have a simple 5 or 10 line function that is used thousands of times then the xll route may offer compelling advantages.

Another point worth mentioning is that most serious BI tools use xlls as their Excel interface. Essbase, Oracle, Cartesis, Analysis Services are the main ones I’ve worked with. Some use xll UDFs directly to the DB, some use xll commands, but they all benefit from the performance of the C API over the COM interface. One BI vendor MD even told me they originally wrote their Excel client in VB6 (COM add-in), but had to recruit a bunch of C/C++ devs to convert the whole thing to use the xll interface because their performance was so far behind the competition.

*question marks because surely at some point the Excel/.net story will evolve to consign this C stuff to the dustbin of time (but I have been saying that since 2002, or so, and its in no nearer in 2007 than it was in 2002.). VSTO is improving all the time, but thats more command stuff than UDFs. How far it evolves will impact how ‘future-proof’ anything written with current tech might prove to be.

For me, in 2008 its VBA if possible as thats quick easy and robust (comparatively!), or XLM or xll if performance demands it.

Thats how I see it anyway – what do you think?



C or C++

Wednesday, 21st May, 2008

I get quite a lot of correspondence around xlls. These are mainly coded in C or C++, and many people are not totally clear on the difference. Language purist are going to hate this, but here goes:

C is like modules in VBA, C++ is like classes.

If all your VBA is in classes (why would you do that in Excel??) then C++ will probably make more sense to you.

If most of your VBA is in modules then plain (or should that be pure?) C will suit you better.

If you have some monster horrible stuff to code, then the OO side of C++ will probably give it the edge, or if you have existing C++ resources you want to expose to Excel, C++ may be better.

But for many VBA devs just wanting moderately simple functions to perform sensibly C is probably the best bet.

Thats a bit of an issue as almost all the frameworks, and tools for writing xlls, are aimed at exposing Excel to C++ devs. None are aimed at helping VBA devs understand this fast xll interface, That what I intend to correct over the next few months.

Excel has an API that is accessible from C (cunningly called the C API), and just about accessible from C++ (If you get it to pretend it is C code).

My advice is the think C when thinking about xlls, that is after all what most of the windows API dlls we use are coded in.




Tuesday, 20th May, 2008

I have a few posts coming up about various coding stuff so I thought I would lay some of the groundwork.

An interface is the bit of something that gets exposed so you can interact with the bit that actually does the work. The classic example is a video recorder, the interface being the play/rewind buttons that we use to control whatever goes on inside, which most of us neither know or care about.

Same with a car, the interface is the driver controls, we don’t care about the engine, just that it wheelspins in 3rd when you hoof it. (must have been an ice patch officer (in July))

From a code point of view the interface is the set of externally accessible functions, variables, properties, methods, subs, or whatever. Some might only be accessible within the same project, some might be accessible from totally separate bits of code.

Same project is easy enough we often do that with public subs in modules in VBA. Making stuff visible from a different component though is a bit more challenging, especially in VBA, but the principle is the same.

Those nice people at Microsoft equipped Excel with several sets of externally accessible functions. One is the C API, this Application Programming Interface follows the C calling convention, and cannot be accessed by VB as VB doesn’t do the C calling convention. In fact this set of functions can only be accessed by real native code components so is out of (direct) reach of .net etc. I am going to cover this stuff in more detail as time goes on.

The other interface Excel exposes is a COM one, COM is much less picky than C so a great many languages can talk to Excel via this interface. VBA is probably the most common, but most languages can do it using something similar to CreateObject(‘Excel.Application’) and then navigating the object model just as we do in VBA. I’m going to cover this later too, which could be fun.

The most useful point about interfaces is that they can stay stable whilst all the stuff in the background gets changed. (This is what that binary compatibility malarky was in VB6 – get that wrong and you eventually crashed your registry – anyone else do that?).

So in rough summary:

interface = bunch of procedures and variables that can be seen from outside.

stable interface = good, frequent interface changes = bad (all clients affected)

Feel free to add or remove or correct in the comments



Top quote

Monday, 19th May, 2008

Vista functions like a bloated asthmatic slug towing a caravan full of elephants to a carnival.

I don’t use Vista so couldn’t comment on the accuracy, but I think its a very eloquent summary.



OpenOffice downloads

Saturday, 17th May, 2008

I found this chart whilst wondering how the office suite marketshare had changed since the arrival of MSO2007.

Sadly the chart looks like a great candidate for Ross’ PPP (Piss Poor Presentation) collection, but what I think its saying is 2.7 million downloads in the last 30 days. It could be saying 600k per day (or 2 days?), but the narrative suggests the 2.7 million.

Of course downloads is only part of the picture as OOo is free on the cover of many computer magazines. And Google gives away its sibling Star Office. And then there are the various forks to target different areas, and the Novell version.

Really I was wondering was OpenOffice in part responsible to the extraordinary MAC Office VBA U-turn. I say extraordinary mainly because of the timing, its unusual to torpedo a product so soon after its release.

Imagine if 6 months ago MS had held their hands up and said O14 will have a classic UI option, what would have been the impact on O2007 sales? (IMO, it would have shafted 2007, of course if you are a ribbon sympathiser you’d see it differently).

Do you reckon OOo is impacting MS decisions? Do you think OOo is becoming (or already is) a force to be reckoned with?

Experience suggests that MS respond well to competition so I hope they do feel some Office suite heat.

Do you think other apps (/companies) are affecting their Office thinking too? I know they wished they were born Google, so I’m thinking more of the desktop (remember that?).



Macro recorder 2

Friday, 16th May, 2008

So lots of people find the MR useful, me too. Especially when the documentation is completely nonsensical.

So who misses ‘record at mark’?

Who can remember when that was quietly retired? (97?)

Stephen Bullen has an add-in to put it back here.

For those of you too young to remember Excel 95, record at mark allowed you to record directly within a routine you were part way through. Instead of the more modern more clumsy always creating a new sub that you have to cut and paste from. In the olden days, if you got everything set up right (right selection etc) you could record the vba for a single operation within a loop, stop, select another line record another operation and so on. No copying no pasting (in theory). Me I always ended up cutting pasting or deleting, or correcting references.

I must admit I’m happy to keep the junk I record away from hand written code. what about you?