Archive for the ‘Excel’ Category

Not an MVP

Tuesday, 6th December, 2011

Just a quick clarification – as people are starting to congratulate me on my MVP-ness.

I am not an MVP.

It seems some of the info about our upcoming Excel developer conference (you are coming right?) mistakely assumed I was an MVP. which I am not.

Many of the other speakers at our Excel Developer conference in January in London UK are though, in Excel and VSTO.

Get ready with your credit card for when I finally organise the booking and payment processing….



UK Excel dev conf date sorted

Wednesday, 30th November, 2011

I have booked a venue for our event so now have time and place.

All info here.

I will sort out a booking method over the weekend and put up a link.



Excel Developer Conference London Jan 2012

Thursday, 3rd November, 2011

Well folks there has been a really positive response so we are game on for an event towards the end of Jan in Londonium.

I will sort out a more detailed draft agenda as things settle down with the speakers, but to give you some idea…

The following topic areas are likely to make it into the agenda

  • Excel Business Intelligence
  • PowerPivot and DAX
  • Migrating from VBA to ExcelDNA with
  • Aerobics
  • Effective Excel Add-ins with VSTO
  • Technology independent software development
  • Beer and chips (and gravy)
  • Practical advice on commercialising your add-ins/workbooks.
  • Various approaches to XLL development
  • Hairdressing
  • Excel and XML
  • Chess boxing
  • Quick tips for effective VBA use.
  • Programming paradigms and how they can help or hinder, a history lesson
  • My snowboarding holiday
  • A smart way to build spreadsheets
  • Beer
  • Curry

I am hoping the broad agenda will attract a diverse set of delegates…

Please start spreading the word: UK Excel conference Jan 2012 London…

I still need to sort a venue, but I am aiming for one day the week ending Fri 27th, probably the Tue or Wed.

Our friends at Microsoft have generously donated some gifts which we will give away on the day in return for cleverest question, dumbest question, nicest biscuits, most beer drunk, that sort of thing.

This event is specifically targeted at the more technical audience. The plan is to have a follow up event in July in Madchester. That event will be more user oriented and less developer oriented.

If you have any other suggestions for topics please let us know below.



why why?

Tuesday, 6th September, 2011

For Each yy In Worksheets(ws).Range(Cells(8, 1), Cells(8, 160))
If yy.Value = “Stop” Then
jj = yy.Column
End If
Next yy

Why would you use yy ? (nervous twitch??) (or jj)

Also note how I have generously indented it for your delight, unlike the original.



Code execution has been interrupted

Thursday, 23rd June, 2011

by whom?

not me!

I had this issue a couple of weeks ago, but for some reason (possibly an update) it seems to have stopped.

As my code was running, for no apparent reason this dialog kept popping up. It seemed especially prelavent when writing data to cells. In the end I had to wrap all my writes with

   ‘Application.EnableCancelKey = xlDisabled
   wsLogging.Cells(m_outrow, col).Value = wsLogging.Cells(m_outrow, col).Value & ” | ” & message
   ‘Application.EnableCancelKey = xlInterrupt

Anyone else bothered by this? I was going to try cleaning the code, but now it seems to have gone away. Or possibly it was someone elses code, and/or some interference from another addin? Whatever, I can’t seem to repro the behavior anymore.

If it makes any difference I am using a multilingual Office (2007 –  12.0.6550.5004).

so I am curious, did anyone else see this problem? did you work out what caused it? did you find a better workaround? has it fixed itself?



Eusprig 2011

Friday, 17th June, 2011

This years spreadsheet risk and quality extravaganza is almost upon us. 

It is exactly just less than a month away in mid July.

You can book here.

I am not presenting this year, as I thought I would let someone else have a turn speaking (and of course I missed the submission deadline).

In fact I probably wont be attending as I’m not sure where I will be working/holidaying then.

I would be expecting a good talk from Patrick as we worked together this year on a few spreadsheet related projects. Indeed he came face to face with the source of several of my formula horrors from previous years!

oh looks like he is not presenting this year, but on the bright side there is some more original research on the power (or not) of range names, amongst other interesting papers.

Here is the (current) draft outline schedule.

Are you going?



Linguistic gymnastics

Saturday, 11th June, 2011

As I’m sure many of you know I am a fully fledged European, living it up in a multi-country, multi-culture, multi-language, (limited understanding ;-)) kind of a way.

At my current role I am running the multi language version of Office in English – which is pretty good (apart from Outlook obviously, that does not respect language settings properly). I have Windows set to English interface too, and that works well too.

Google and its spooky spammy ad network seem completely unable to respect my language choice. I would never notice this as I usually run an ad-blocker but here at work we get the full pointless hit. I had no idea the internet was so infested. Even on my blog here I have seen ads, I didn’t think it was popular enough to warrant it.

But anyway I just wasted a couple of hours of my life trying to understand why some VBA wouldn’t work.

Its something I have done 13 1/3 times before with no problem, but here, today, no go. writing a formula from VBA to a cell – no bigee.

ActiveCell.Formula = “=CONCATENATE(….”

I built up my string carefully using an in cell version as a model. I copy pasted it into a cell no bother. And yet when I tried to run it, it errored. With the ‘oh so helpful’ ‘Application or object defined error’. It would have been easier and more honest for that to read ‘Error, tough shit’

So anyway, I wondered if it was an R1C1 thing, I have been burnt by that a few times, so I changed all my code to be R1C1 stylee, and tried to set ac.formulaR1C1, still no go.

I noticed it was a long formula, so I cut out a big chunk of stuff to get it in under 256 chars, still no joy.

Then I thought maybe I had too many arguments, so I changed my code to just do 3, still no joy. In the immediate pane (pain) I tried activecell.formula = “=A1”, worked fine no problem.

So then I asked another dev to help, then, as is often the case as soon as you ask someone else, it finally it hit me: with nl settings the argument separator is a semi colon ; whereas in en it is a comma ,. Edit replace ; with , and job done, my formula worked. VBA always talks to Excel with en culture. So my simple immediate window test formula worked fine, but anything with multi-arguments failed. there is a point there about making tests realistic I guess.

So there it is that’s what I did the other morning morning, I relearnt that I need to build formulas in VBA using commas not semi colons. Of course I could ‘just’ change the windows regional settings, but anyone who has done that will know what a can of worms that opens. for example VSTO add-ins wont install, CSVs wont import, and a million other things I have not been burnt by recently, no, regional settings is best left alone.

As a matter of interest I find it very difficult to get used to entering formulas via the UI with ;’s instead of ,’s too. nearly every time I get a slap for using a comma.

Have you had any multi lingual challenges recently?



New Excel Performance blog

Sunday, 5th June, 2011

Charles Williams from Decision Models, makers of FastExcel, has started blogging on Excel and UDF performance issues.

Charles has spoken at many of our Excel user and developer conferences, writes for MSDN, runs the de facto standard Excel performance site and sells the de facto standard Excel performance testing toolset, so he doesnt’t need any introduction from me…

The blog is here.



pure joy

Friday, 3rd June, 2011

I loooove this!

I might even apply for the job (it might be the only chance to get tickets…)



XLL plus 7 is out

Monday, 16th May, 2011

My favourite XLL tool just got updated.

I decided ages ago that xll+ was the best way forward for C++ xlls. The main reason was its ability to target both 2007+ and classic Excels from the same file. Hand coding that in C++ is a PITA.

I am still awaiting my first commercial 2010 job, and I am looking forward to a couple of new 2010 features, the main one being the compute cluster integration (native async). XLL plus 7 helps you target that exact functionality, neat.

This version also has Ribbon integration which is great because the native xll interface does not support any ribbon interaction meaning normal xlls become a 2 file solution in 2007+ as you need a workbook/xlam to hold the ribbon customisations. Which was a bit crap.

And this new version of XLL plus also supports 64 bit, which I imagine was a pretty big job.

I’m a bit unconvinced about 64 bit Excel personally, if I needed a spreadsheet bigger than 2gb I would take up yoga so I could give myself a kick up the arse. I suppose if it improves RAM use too I could see some value (and avoid all that stretching).

Perhaps someone could enlighten me on the benefits of 64 bit? 2gb+ jet dbs I could totally use, I have been hit with that barrier loads of times. A 3gb powerpoint would be particularly unwelcome…

Anyway, as a key performance tool provider I guess XLL+ didn’t have much choice, as these big monsters are exactly where you would use XLL+.

Anyway I haven’t even downloaded a copy of 7 yet so I haven’t had chance to try it, I’ll report back when I have.