Archive for the ‘VBA’ Category

2010 looking back

Wednesday, 29th December, 2010

I just re-read my predictions for 2010 and I note I hardly went out on a limb, with most of them being either blindingly obvious or un-falsifiable. Here they were.

The big (IT) 2010 story for me is how Oracle and Microsoft fell in love. I totally didn’t see that coming at all. By destroying first the OpenOffice dev community, and secondly the Java community Oracle has hamstrung two of the few viable competitors Microsoft had. I’m not clear what Oracle get in return, MS maybe promised to keep Steve Ballmer in charge.

I’ll just skim over the predictions I made:


All the new stuff got released as expected, Office 2010 seems ok, they fixed some of the most obvious badness of 2007. VS2010 is the slowest, most bloated piece of pap I have ever used. I have 2008 and 2010 on my works machine and use 2008 wherever possible because you need a Cray super computer just to load VS2010 in under 3 minutes, never mind compile something.

I don’t think VBA went into freefall, it is still the user automation tool of choice (its a choice limited to one after all). But most financial services jobs are now looking for some C# in addition. Many seem to be trying VSTO and rejecting it though, and a lot of people I have spoken to recently are going the ExcelDNA route instead.

Sharepoint does seem to be hotting up, spreadsheets are ever more the scourge, Microsoft is becoming less relevant. There were quite a few spreadsheet risk/quality projects going on in 2010, they will be sharepoint clean ups in 2015.


Apple had a brilliant 2010 and that looks set to continue. The ipad still has no viable competition and they are flying off the shelves. Apple are reliving Microsoft from the 90’s before MS forgot that apps sell platforms. I can’t imagine Apple being too scared by the arrival of the Windows phone. I won’t get an iPad, but I could be tempted by the 11″ Air as a decent netbook to run Linux. Although rumours of an Ubuntu tablet in 2011 piqued my interest the other day.


Consumers are certainly living their lives more and more away from a pc on their ipads and iphones, not so sure about the corps? Blackberry still doing well.


I really hadn’t expected that you would have to be sexually abused in order to get on a plane, but I did forsee an increase in regulation. Luckily (?) with the change (?) in government in the UK the rate and intrusiveness of regulation seems to have slowed down, a little. Glad I’m not a banker though.

OLAP/BI seemed to stand still in 2010 as a lot of the column inches went on security and cloud fluff. Whilst we can argue about whether user requests could get a lower priority, I don’t think anyone would say they got higher in 2010.

Android seems to have picked up some credibility, although the Motorola Droid I have is the worst electrical device I ever owned.

Santa brought one of the kids a netbook for xmas but he gave up trying to find a Linux one and just got a windas 7 one. WTF happened to Linux on Netbooks?? They are as rare as rocking horse poop suddenly. Its pretty cool here though as all the kids want Ubuntu because it has the best games, so I’ll be replacing Microsofts’ finest with NBR 10.10 (no silly name this time?), obviously without caring about Ubuntu One.

So overall, many things seem to have turned out roughly as expected, a few haven’t (yet), some might never.

I’ll do my predictions for 2011 within the next few days, although from a spreadsheet point of view its hardly exciting.

What were your standout moments/trends/events from 2010?



Test harnesses in production

Tuesday, 30th November, 2010

You wouldn’t believe how the cold dark winters evening simply fly by here (well at least I managed to get home  even though 4″ (100mm for the metricians) of snow fell this afty, the first November neige in Geneva since 1980 allegedly). And its still dumping it down – might have to throw a sickie tomorrow and go sledging with the kids.

Anyway the big argument today was test code.

Should you or should you not put your VBA test code into production?

Should you strip down your project to the absolute minimum clean prod only code?

Or should you leave in the code you used to test your production code? (assuming you are one of the 3 VBA devs worldwide who bothers to test of course)

My vote is to leave it in, and even though I was in the minority at school today that doesn’t mean I’m wrong, yet.

If your test code is crappy and distracting then yep take it out, no probs (in fact take out all the crappy distracting code), but why I think decent test harness code should be left in:

  1. It helps show what the code is meant to do
  2. It helps when you are trying to fix something later in production at a users’ desk.
  3. It shows that someone did some testing sometime
  4. If you make significant edits to your code you should retest, if that edit was removing what you thought was the test code how will you check you haven’t broken something? add a test harness???

What do you think? what am I missing that these clean code freaks can see? Remember I’m not for a minute suggesting leaving in a load of random junk scattered throughout a project. I’m thinking of separate modules or at least sections with a bunch of meaningful tests that exercise the main functionality of the system in a controlled way.

What do you do?



Excel VBA .net developer available

Monday, 22nd November, 2010

My current contract is drawing to a close leaving me available for new challenges. A casual scan of this blog and the codematic site should give an idea of the sort of work I do.

In summary: Advanced Excel development with VBA, C# and C++, Relational and OLAP databases

I normally work in and around financial services organisations, most recently commodities trading.

Happy to consider any location and remote work, currently I’m in Geneva. Contract or consulting. Available end Dec/start Jan 2011.

Drop me a line via here.





Sunday, 21st November, 2010

In my ADX review I mentioned that the XLL UDF performance was not as good as some other technologies. But I also mentioned that some of the other features probably more than made up for that for many people.

The reality is for Excel/Office development we have a wide range of choices, some Microsoft, some not. Some open source, some commercial.

There is not one single technology that is best at everything. Sorry if you want an easy ‘Excel is the answer, now what’s the question’ (Or VBA etc) type scenario.

I currently have live add-in projects using VB6, VBA, XLM, VSTO, ExcelDNA and XLL+, and raw C, and I guess I have VS2008 and VS2010 C# stuff going on too. And I hope to add ADX to that list soon. Within all of those I would say each one uses the right technology given the requirements. No – sorry, the improved interop in C# 2010 would put another nail in its coffin – if we could be bothered to dig it up.

The add-in tech choice is not easy, and is possible through something that a few manager types don’t really seem to understand – real world experience. Books and forums can help but its only when you try and deliver real solutions to real users with real needs that you see how the techs really work. For example I moved one of my projects off VSTO because I didn’t need the click-once goodness or the start up delay.

I’ll write more over time on the criteria I have applied when selecting the most appropriate technology, and how to spot when you are going down a dead end and how to swap. I’ll also write more detail on my xll performance tests because I have done a fairy extensive shootout.

Do I have a favourite add-in tech? No, not one, its not that easy. But if performance is critical then you have to use the C API not the automation interface. And if an attractive interface is important then you are going to need something a bit richer than XLM 4.0 dialogs. Then there is the whole pre-requisites excitement…

Do you have an overall favourite add-in tech? What other techs are you working with? Anyone doing any Java/Excel stuff?



Spreadsheet Control Projects

Thursday, 9th September, 2010

Any experienced developers out there looking for a role on a spreadsheet control project?

I have sniffs of a couple of roles here in sunny Geneva and also one in Luxembourg.

All for large financial institutions, all trading/banking, all looking at similar stuff.

  • Assess the current spreadsheet mess
  • propose solutions on a workbook by workbook basis, (either leave as is, tidy up, or migrate basically.)
  • Potentially assist/do or lead that work

Rates are market rate, between 3-600 quid depending on skill and experience. All are full time, on site, you’ll have to sort out your own travel and accom if you don’t live local to the roles. All are 3 months to start, with a good chance of an extension if you know what you are doing.

You’ll need:

  • good Excel skills and experience (min 5 yrs prob)
  • good VBA (min 3 yrs approx)
  • some none spreadsheet development experience (either relational db or .net or java or other lang)
  • full SDLC experience
  • Good financial services experience
  • Good analysis experience

If you are interested and think you meet the requirements then drop me a line with your CV and I will pass it on to the powers that be.



(send your cv to send spam or viagra or ED offers – thnx)

which add-ins?

Tuesday, 13th July, 2010

I got a little fed up poking around trying to work out which version of which addins are loaded where.

I found some great starter code here (thanks guys). And added some stuff.

Its here so anyone else can use it (and I can find it wherever I happen to be), and if you have any suggestions then please leave a comment.

Sub ListAddins()
Dim oAddin As AddIn
Dim oCOMAddin As COMAddIn
Dim wb As Workbook
Dim sAddins As String
Dim RegisteredFunctions As Variant
Dim i As Integer
Dim j As Integer
Dim sd As String: sd = “|”
Dim p As Object
sAddins = “Standard Add-ins” & vbCrLf & “================” & vbCrLf
For Each oAddin In Application.AddIns
sAddins = sAddins & oAddin.Name & sd & oAddin.FullName & sd & oAddin.Installed & vbCrLf
Next oAddin

sAddins = sAddins & vbCrLf & “Standard wb Add-ins” & vbCrLf & “================” & vbCrLf
For Each wb In Application.Workbooks
‘If wb.IsAddin Then
sAddins = sAddins & wb.Name & sd & wb.FullName & sd & vbCrLf
‘End If
Next wb

sAddins = sAddins & vbCrLf & “COM Add-ins” & vbCrLf & “============” & vbCrLf
For Each oCOMAddin In Application.COMAddIns
sAddins = sAddins & oCOMAddin.progID & sd & oCOMAddin.Description & sd & oCOMAddin.Connect & vbCrLf
Next oCOMAddin

sAddins = sAddins & vbCrLf & “Xll Add-ins” & vbCrLf & “============” & vbCrLf
RegisteredFunctions = Application.RegisteredFunctions

If Not IsNull(RegisteredFunctions) Then
For i = LBound(RegisteredFunctions) To UBound(RegisteredFunctions)
For j = 1 To 3
sAddins = sAddins & RegisteredFunctions(i, j) & sd
Next j
sAddins = sAddins & vbCrLf
Next i
‘do nowt
End If

sAddins = sAddins & vbCrLf & “VBA Projects Add-ins/wbs” & vbCrLf & “============” & vbCrLf

For Each p In Application.VBE.VBProjects
sAddins = sAddins & p.FileName & vbCrLf

‘MsgBox sAddins
Debug.Print sAddins

End Sub

I can’t imagine for a minute that will copy out right, but I’ll load a proper version on Codematic when I have incorporated all your suggestions.

I know for example if you have a few market data apps loaded the xll reg functions list will exceed the immediate window capacity – so I just comment that out.

Improvement suggestions welcome, especially with code…


Excel Dev conf details

Saturday, 12th June, 2010

The draft (detailed) agenda is here.

The event location is here.

You can get yersen booked on here. Its currently 150 quid for the day (with early bird discount), that will go up to 200 at the start of July, if there are any places left (all plus vat). I didn’t work out how to do a declining stock thingy with the codematic shopping cart so I’ll just have to warn people as we get near the room limit and then disable the shop item.

Please tell everyone you know who might be interested. I think this will be an excellent event, and the first one I’m aware of that actually addresses the business of selling Excel stuff. Add in a bunch of technical content on the latest trends in corporate development, and plenty of time for questions and chat, an informal atmosphere, and I reckon we have an excellent well rounded event coming up.

If there is something you specifically want to be covered let us know. If you have questions for the panel let us know that too.

Accommodation-wise I suspect many of us will be booking into the nearest/cheapest travelodge.



UK Excel Dev Conf Venue

Wednesday, 9th June, 2010


so I forgot to mention where this magnificent gathering of the great and the good on Jul 12 is occuring!

Or the timings…

The game plan is for a meet and greet between 8:30 and 9:00, at 9:00 the first session will start. The sessions will be about half an hour long with additional time for questions and overruns.

The last scheduled session will finish somewhere between 17:30 and 18:00. The pubs starts at 18:01 prompt, last one in buys a round.

The venue is here. You will see they have wifi etc so if you have to answer emails to pretend you are ‘working from home’ that should be ok. (No guarantees mind, if its anything like the wifi nightmare I’ve been having recently you might be better bringing homing pigeons.)

We will be stopping for tea and coffee, and for dinner (or lunch) of course. If you want to bring cakes and/or biscuits feel free. Ross likes Wagon Wheels, I like all biscuits, even bourbons, no idea which is Charles’ fave, Charles?

We will make the resources available at the event somehow, if you want to bring a laptop to follow along that’s fine, in fact if you want to bring a desktop that’s fine too, as long as it fits on your knees…

Anymore questions, or blindingly obvious oversights let me know.

I will publish a proper timetable in a couple of days when the presenters and schedule have firmed up.



Excel Developer conference London Monday July 12 2010

Wednesday, 9th June, 2010

Here are some more details for the forthcoming Excel Developer Conference on Monday July 12 2010.

Outline initial Agenda
A one day event focussing on real world development with Excel. The event targets Corporate Excel developers and individuals considering selling Excel add-ins, tools, and templates on a commercial basis. There is an excellent mix of the business of Excel tools and the technical aspects of getting the best from Excel. And probably most usefully it is an excellent (and rare) opportunity to network with fellow professionals operating in the same technologies.
The presenters are all commercial developers, most of whom have been selling Excel related products for years.
The format is relaxed and informal, the numbers are strictly limted to maintain a friendly atmosphere. (the expectation is for an intimate event of 20-30 people)

The business of Excel:
Back Office systems and activities to sell on-line
security, piracy, sales and marketing tools
(both of these are based on the real world experience of Charles Williams and his highly acclaimed Fast Excel optimisation toolset).

Technology of Excel:
How to choose the most appropriate implementation technology, both in a corporate environment and as a independent software vendor.
Creating fast User defined functions with .net and ExcelDNA and XLL+, and by hand.
Using Excel as a window to your corporate data
Designing Excel based systems for easy maintenance, support and modification
Extending Excel with VSTO and Add-in Express based add-ins
Review of Office 2010 and Visual Studio 2010 and the possible implications
Discussion of the future direction of Excel development, which technologies to back which to drop.
Panel based questions and answers – we guarantee to answer all pre-submitted questions at the event, and will follow up any on the days ones we can’t answer there and then.

Currently confirmed:
Charles Williams – Excel calculation master
Ross Mclean – international lute playing spreadsheet developer
Me – general spreadsheet botherer and Visual Studio tinkerer

A key part of these event is the opportunity to meet other people going through the same issues. We will be in the pub the night before and the night after to discuss all things Excel, corporate, commercial and technological, and possibly a little footy too.

The event will include unlimited tea and coffee but the midday meal is left open to delegates to arrange. Some of us will no doubt be going to a local pub.

In order to ensure that cost is not a barrier to attendence, the event is priced at only 150 GBP (+vat) for the day.

I will put up a booking and payment page on the codematic site in the next day or 2.

Any questions, comments or topic suggestions – please leave a comment below.



Analyst v developer

Monday, 24th May, 2010

An analyst is just a developer who can’t code in the chosen implementation technology.


the reason I bring this up is:

We are always told we have to use tortuous programming languages because English (or your choice of normal language) is too imprecise. Our ability to code in higher and higher language is developing slowly as people write layer upon layer to translate our higher language to computer meaningful stuff.

But if an analyst is writing specs that someone is going to code in this precise programming language, how come they can get away with using an imprecise language? How does the coder move from vague to precise? (hint: personally I go and ask the customer, but that’s just me). Don’t say CASE tool, unless you genuinely, regularly use one, in which case, which? why? strengths? weaknesses?

I’m only asking because this analyst thing came up in conversation recently, and for business systems development in higher level languages* I think this role separation is ridiculous. Sure plenty of people don’t have the full set of business and technical skills to cover both roles. IMO those people should be developed where possible to enable them to cover the full lifecycle. Not compartmentalised and forced to communicate via the 2 cans and a string that is specs written in Word.

[* by higher level languages, I’m thinking of the stuff business systems are generally built with, thats most of the stuff we discuss here, C++ I’m willing to concede may be out of reach for some business analysts, but I don’t think SQL or C# should be].

I think the analyst being a separate person from the dev is pretty unusual in Excel/VBA. Have you seen it? I’ve worked from specs before rather than users, its crap, and you always end up going to sit with the users anyway. WOT. (hint)

Do you think it would work? Does it make sense to split the roles?