Codematic is moving

We are currently in the process of moving codematic hosting.

Whilst we wait for the powers that be to redirect the domain names correctly all the content should be found via spreadsheethell. If you need to get in touch then either use the normal email address you were going to but with spreadsheethell.com instead of codematic.net. Or just leave a message on this blog somewhere easy to find.

I’m fully expecting to lose a few days mail at some point in the next week or two. However I think that was happening anyway – hence the change of hoster!

All the codematic.net pages should divert directly to the equivalent spreadsheethell one. (Courtesy of that well known HTML editor and site manager – MS Excel (and a sprinkling of VBA)).

Normal service via codematic.net should resume within a week or so, so there should be no need to update bookmarks etc as they should start working directly again in a few days (if everything goes right first time ;-))

If you do find something missing then please let me know here.

Cheers

Simon

Xlls in Excel 14

I was very pleased to see a renewed commitment to xlls in 2007 with the release of an updated SDK.

For Excel 14 I hope that work continues and expands. I would like to see better access to some of the newer features via the C API.

One particular area where xlls are weak I think is in event trapping. The classic missing one is selection change. So instead of doing this through the ultra fast C API we have to dawdle through the COM interface. As this is probably the most common event thats a pain. Access to CalculateFull would be handy too.

Being able to trap workbook open and close would be useful too. I know I’m dreaming when I imagine being able to set the title of an ALERT (a bog basic msgBox), but a bit more control would be nice.

Also I hope that xlls can work natively with whatever clusterfuck UI is foisted upon us in O14. The whole point, or a big part of it (for me anyway) is single file solutions. Thats not possible in E2007, unless you are happy for your add-ins UI to be dumped in the badd ladds corner. Of course I guess this comes into the new mantra: ‘the user shalt have total control of their UI (unless they try to change it by opening a code resource, or otherwise changing it from standard)’.

I know the encouragement is to use xlls for fast worksheet functions only, but I have been doing a lot of command based stuff recently and love the speed gain here too.

What would you like to see if the next revision of the xll SDK?

cheers

Simon

Debug formulas

I’ve been doing a load of conditional compilation in VC++ recently. I have a commercial product out with debug, release and trial versions. I always disliked cc in VBA but in C I find it much more useful. (FWIW I added code signing as a post build step so alt br gets me a signed xll to test.)

Anyway I’d like the same kind functionality in my spreadsheet formulas please. I’d like the ability to set a workbook to be in release or debug mode (or other) and have it behave accordingly.

In debug, lookup functions could return the address of the match rather than the value for example. Formulas that return an error could perhaps provide a bit more info like which argument caused the error. What things would you like in a debug version of a formulas?

I’d also really like a map of the calc tree and the calc order, especially any that bounce as uncalculated, as there could be real scope here to improve performance. I’d particularly like to see which cells calculate the most often, and perhaps how long they take – but perhaps this is more of a tuning/optimisation feature than debugging?

I’d also quite like a way to watch for cell data types changing over a few calc cycles.

I don’t really want to get into the audit tool feature sets here, like unique formula lists, data type/formula maps etc. I’m thinking more on watching it calc it a way we can’t currently do with Excel (but can with Resolver).

What things would you find useful in a debug version of a workbook?

cheers

Simon

Algebra

Something I’ve been meaning to do since Eusprig was code up this function.

It struck me that one of the things that can make worksheet formulas difficult to follow is that a cell reference can be repeated several times. Also IV2367 is a bit of a pain as far as identifiers goes. (A bit like all that Greek the maths department made us do at school.)

My preferred way to work with algebra is naming stuff a, b, c etc, so I thought I would code that up as a worksheet function:

Private Const T1 As String = "_a_"
Private Const T2 As String = "_b_"
Private Const T3 As String = "_c_"
Private Const T4 As String = "_d_"
Private Const T5 As String = "_e_"
Private Const T6 As String = "_f_"

Public Function Algebra(theFunction As String, _
      Optional Arg1 As Variant, Optional Arg2 As Variant, Optional Arg3 As Variant, _
      Optional Arg4 As Variant, Optional Arg5 As Variant, Optional Arg6 As Variant) As Variant

      Dim sTempFunc As String
      sTempFunc = theFunction
      Debug.Print sTempFunc
      If Not IsMissing(Arg1) Then sTempFunc = Replace(sTempFunc, T1, Arg1)
      If Not IsMissing(Arg2) Then sTempFunc = Replace(sTempFunc, T2, Arg2)
      If Not IsMissing(Arg3) Then sTempFunc = Replace(sTempFunc, T3, Arg3)
      If Not IsMissing(Arg4) Then sTempFunc = Replace(sTempFunc, T4, Arg4)
      If Not IsMissing(Arg5) Then sTempFunc = Replace(sTempFunc, T5, Arg5)
      If Not IsMissing(Arg6) Then sTempFunc = Replace(sTempFunc, T6, Arg6)
      Debug.Print sTempFunc
      Algebra = Application.Evaluate(sTempFunc)
End Function

I can then call it from a cell using:

=algebra("_a_+_b_*_c_",A2,B2,C2)


Obviously it would get more useful if the formula was more complex and/or had repeating arguments. I also havent bothered to try and optimise it, or add error handling, or debugging…)

I think the _a_ stuff is a bit ugly, but if it used something too simple it might replace the wrong thing. Any better suggestions?

What do you think?

As a concept is it useful to pass in to a function a string formula, and a bunch of values to use as replacements?

cheers

Simon

funny vid

Spotted this on Andrew W’s blog. Its a rather poignant and amusing video about developing for Office 2007.

There are a few things here worth mentioning. First, to get the bleedin obvious out of the way, I’m always going to laugh at anything that pokes fun at the ribbon, and/or the numpties who fumbled it out into a live product.

Second when he quotes the ‘mantra’, user in control of UI. I think we all agree on this, but I suspect we disagree on the how. Me I think control is simple customisation and simple reset (eg Excel 95-2003), the effluent team think user control is the user doing exactly what the ribbon ui blunderons tell them, or learn a whole new language to make a trivial change.

Anyway thats old ground, all sides think they are right and have the evidence to prove it.

I’d have to disagree with casting the ribbon team as some shadowy gangster type. Whilst they certainly have that level of empathy with their victims, I think something like this would be a more accurate portrayal.

Also did you get the dig about working on something easy, like deployment, with VSTO? tee hee!

Its great to see the product teams acknowledging the issues their users face. Although in fairness I think the VSTO team are pretty well connected to their users, The VSTO team is bunch of VS devs with a focus on Office, working on improving the interconnections between those 2 technologies. And their customers? A bunch of VS devs with and interest in Office….

Contrast with the Ribbon team who have probably 10 minutes real world Office experience between them trying to create a marketable User interface for a product they don’t understand for a target audience they dont know at all. Its no wonder they made such a pigs ear of it, and the VSTO deployment story gets better by the day.

Now if HM had made that video in the UK, the competition (is there any?) would have been up in arms saying why aren’t they working on fixing deployment instead of squandering tax payers money on on a completely frivolous project.

have a good (bank holiday where appropriate) weekend

cheers

Simon

Code signing certificate rip OFF!

Its coming up to renewal time for the Codematic code signing certificate. I have been with Thawte the past few years – but that is going to change!

When I bought the last one 2 years ago it was 320 USD

Exactly the same thing today costs 500 USD

Thats over 50% inflation over 2 years!!

Now I know the price of cattle feed has shot up, fuel has shot up and real inflation for most people is in double figures, but what source material has caused this price hike?

Oh, I know, is it because we are being forced more and more into using them so more people need them? Basic economics right? increased demand = increased price. For scarce resources maybe, this just looks like blatant profiteering to me and abuse of market dominance (Thawte is now owned by Verisign). Have you got any better suggestions for what is going on?

I can live with paying 250 quid, but I hate being shafted and thats just what this feels like. So I’m on the hunt for a decent code signing certificate supplier, any ideas/recommendations?

Does eveyone else sign their code?

cheers

Simon

Hard times

I know we are in a recession but this still seems a bit tight:

—————————–

My West Yorkshire based client immediately requires an Access and Excel Developer. You will be required to produce reports using both Access and Excel along with some development work. This is an initial 3-month contract.

Essential Skills include:
– Access
– Excel
– Pivot Tables
– VBA

If you are interested in this role, please forward your CV for immediate attention.

Rate: 14- 15 per hour

—————————-

If you promise me 10% I’ll tell you where I saw it!

Of course you could argue we should be paying them for the honour of working in Gods own county! I suppose it doesn’t sound like too senior a role, but still 15 quid sounds on the low side, what do you think?

On the job front I’ve also heard that a few companies have lopped 10% off their contractor rates in a take it or leave it stylee – nice!  A client once tried that with me (but just 5% or 5 something) I said I’d leave and get a new contract the day the new rate came in, they backed down – well derr! thats the whole point of being a free agent right?

Anyone got any tales to tell?

cheers

Simon

Compiler FAIL

So I mentioned that you can use VS2008 Express to create Xlls.

What I didn’t clarify was the rather pathetic compiler story.

Sadly VS2008 relies on a different version of the C runtime libraries than previous VS’s (I think they all expect different versions). What this means is that if you compile your xll with VS2008 and try to deploy to a corporate WinXP environment, chances are the xll will fail to work.

I had an email from someone last week and that is exactly what he had done, and exactly the issue he came up against. I explained the situation and suggested he recompile with VS6. He later replied that he had compiled with DevCpp (A free open source C/C++ editor) and it now worked fine. (He was creating the Generic xll from the SDK)

Over time Microsoft have updated the C runtime libraries for performance, security and features. I think thats great, what I think is poor is the way they seem to expect application developers to distribute these updated runtimes for them.

This runtime issue used to annoy me in VB6, makes .net useless to me and now makes all recent Microsoft C/C++ IDE unusable (as compilers). Who decided that single file distribution was too easy and convenient and we should all be forced to use some convoluted installation system to get our apps to clients?

You can still use VS2008 as an editor, just use a real compiler to actually _compile_ your code, as MS no longer make compilers.

Anyone else caught by this?

cheers

Simon

Excel xlls for free

I just wanted the highlight the fact that our friends at Microsoft have made it pretty simple to get set up to write xlls for free. I’m watching my language here as writing xlls is not simple and the documentation is not all that it could be. But getting hold of the tools to do it has never been easier.

Anyway you need Visual C++ express from here.

And the Excel xll SDK from here.

The SDK documentation is here.

There are a few gotchas and you need to roll back some of the newer security features in VS2008. As I recall leaving DEP in default crashed Excel.

Also you need to get all the SDK files into the right places on your machine, I have a paper on this from a training course I delivered a while ago, I’ll dig it out and put it on the Codematic site (currently here) next time I’m on that machine.

Getting the files in the right place will be no problem to us Excel folks – we’re used to the misery created by externally linked workbooks, that only work when you put them in the right directory.

Cheers

Simon

Codematic email warning

My email is well messed up again.

My blunderfuck ISP seems to have upgraded their email ‘processing’ system. That would explain the 12 hour hole in my email last week. If anyone sent me anything last Tuesday I didn’t get it. If you sent me anything since, I probably didn’t get it. Of course they didn’t have the courtesy or business sense to pre-warn us so we could be on guard.

I am trying to change ISP but was hoping to do it without losing a load of email, anyway it now seems clear that I’ll end up losing some whether I stay or go.

My test messages are currently disappearing which is not a good sign – looks like its going to be a long night!

I saw a link to a ‘professional’ email service provider the other day and poo pooed the idea because of the cost. I’m just about to start looking into it a bit more seriously!

I reckon the true cost of spam is staggering. If you consider all the time money and effort spent in anti-spam efforts, and the missed opportunities of false positives it adds up to a big number I think.

My blog comment notifications have disappeared into the ether now too.

Anyone else have much email hassle?

cheers

Simon