Test harnesses in production

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?

cheers

Simon

Excel VBA .net developer available

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.

cheers

Simon

 

Performance

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 VB.net – 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?

cheers

Simon

Spreadsheet Control Projects

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.

cheers

Simon

(send your cv to info@codematic.net)(dont send spam or viagra or ED offers – thnx)

which add-ins?

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
Else
‘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
Next

‘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…

cheers
simon

Excel Dev conf details

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.

cheers

Simon

UK Excel Dev Conf Venue

D’oh

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.

cheers

Simon

Excel Developer conference London Monday July 12 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.

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

Social:
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.

Cheers

Simon

Analyst v developer

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

Discuss….

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?

cheers

Simon

Excel Developer Interview Questions

We are looking for some Excel/VBA devs. They need to be good, or better.

I’m not a big fan of silly questions that have no bearing in the real world, but equally, I need in 20 minutes or less, to get a good sense of whether the candidate is going to cope in the harsh reality of financial services spreadsheeting. (And over the phone).

Here is what I have come up with so far:

Whats the keyboard short cut to open the VBAIDE?.
Name 3 new functions in Excel 2007
Explain scope
How would you translate one set of product codes into another (assume 1 to 1 matching)
What is the SQL to return a list of products where the maturity date is after June 2010.
In excel what is a volatile function? Name 2?
In VBA what does option explicit do for you, and how do you set it.
What size of spreadsheets have you worked with?
What is the main body interested in spreadsheet quality?
Name a couple of good on-line resources for Excel VBA
Describe decent code
Talk about combining Excel and C#
Describe the best and worst spreadsheets you have worked with

…

Have you got any favourite questions?

I’m not thinking of ‘pass or fail’ type questions, more ones that will allow us to get a good sense of people strengths and weaknesses. Just technical, someone else will be covering the other stuff.

cheers

simon