We've again been asked to find a serious Excel RAD person for a large investment house, whi is in the words of the hiring manager "as good as me".As you will have seen from some of my earlier posts, I do expect someone who puts Excel on their CV not to regard DDE as black magic, or who shows fear when he sees VBA.Am I being too tough ?

Quoteregard DDE as black magicI used to do DDE to interface to AutoCAD (under OS/2). Any good?

What are they supposed to do with Excel? RTD is commonly used in finance; so they should be able to discuss what it is good at (streaming data into Excel) and what is shouldn't be used for (getting data out of Excel).

Dynamic data exchange (DDE) is an older technology that is not secure. If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE). There is always a varying degree for the level of a programmer, some can get vba to print hello world via debug.print "Hello world" and others can do clever stuff with VBA to the level where they use all the modules, classes, procedures, etc.. I think expecting someone to know what DDE if they an average Excel user will be tough, as you have to remember some people use Excel for part of their work and not close to 100% like in dev type role. You can usually learn VBA and the bits you don't by using google etc. The hard bit is getting things working correctly and speeding up the algorithms and re-engineering code as you know.You can ask someone in an interview what this piece of code does and how you would improve it, which is a more useful indicator of how well someone understands programming rather than say what does x do etc. I see a lot of question on what does this and that do for programming type roles, but I don’t feel this actually tests how good a programmer you are, just shows you can learn the syntax in a few months and regurgitate it to the interviewer. Stats Guy

Quote If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE). ah go way! OLE,as word went out of fashion in the 90's Nowadays it COM, DCOM, ATL and even these are -----------> .NET To be fair, Automation is a new name for OLE...

"Where is <insert function name> located in Excel 2007"

Ask them about that '65536' Excel bug that surfaced a few months back. If they're worth their salt they'll know about it.I know about it and never use Excel.Ask about calling the Windows API from VB.If he's shit-hot he'll probably know how to get a pointer in VB too, although that's not entirely Excel related.

I don't think your questions are too tough. I ask them to write a simple routine in VBA such as removing leading and trailing spaces from A1:Z10000There are many ways to do this and I can usually guage a person's level by the method they use.If they write something similar to Sub A, they are "trainable"If they write something like Sub b, I rate them as solidIf they write sub C, they have been doing Excel vba for a long long time.Sub a() Dim i For i = 1 To Range("A1:Z10000").Cells.Count Range("A1:Z10000").Cells(i).Select Selection.Value = Trim(Selection.Value) NextEnd SubSub b() Dim rng As Range Application.ScreenUpdating = False For Each rng In Range("A1:Z10000").Cells rng.Value = Trim(rng.Value) NextEnd SubSub c() Dim v As Variant, i As Long, j As Long v = Range("A1:Z10000").Value For i = LBound(v, 1) To UBound(v, 1) For j = LBound(v, 2) To UBound(v, 2) v(i, j) = Trim(v(i, j)) Next Next Range("A1:Z10000").Value = vEnd Sub

QuoteOriginally posted by: jpsnjI don't think your questions are too tough. I ask them to write a simple routine in VBA such as removing leading and trailing spaces from A1:Z10000There are many ways to do this and I can usually guage a person's level by the method they use.If they write something similar to Sub A, they are "trainable"If they write something like Sub b, I rate them as solidIf they write sub C, they have been doing Excel vba for a long long time.Sub a() Dim i For i = 1 To Range("A1:Z10000").Cells.Count Range("A1:Z10000").Cells(i).Select Selection.Value = Trim(Selection.Value) NextEnd SubSub b() Dim rng As Range Application.ScreenUpdating = False For Each rng In Range("A1:Z10000").Cells rng.Value = Trim(rng.Value) NextEnd SubSub c() Dim v As Variant, i As Long, j As Long v = Range("A1:Z10000").Value For i = LBound(v, 1) To UBound(v, 1) For j = LBound(v, 2) To UBound(v, 2) v(i, j) = Trim(v(i, j)) Next Next Range("A1:Z10000").Value = vEnd SubI don't agree with you. I would rate c) as a very inefficient programmer. He wastes lots of time and memory resources to do what you've asked him. So, for me He's the worst.I have to apologize. I've just tried code c) and it's more efficient than I thought. You're right!

QuoteOriginally posted by: DominicConnorWe've again been asked to find a serious Excel RAD person for a large investment house, whi is in the words of the hiring manager "as good as me".As you will have seen from some of my earlier posts, I do expect someone who puts Excel on their CV not to regard DDE as black magic, or who shows fear when he sees VBA.Am I being too tough ?If someone puts Excel on their CV without adding VB and VBA to it, you shouldn't expect them to know about it. For that job you're looking I wouldn't pick a CV that doesn't mention VB and VBA.

If I was verifying that someone had advanced excel skills (as opposed to VBA), I'd be getting them to use offsets, match, index, and array formulas.

Using index with match is an alternative to vlookup. I would also ask them about data tables. It is often hard to know how much Excel one really knows since everyone in this field is supposedly an expert in Excel. With VBA you could ask what the difference between Empty, Null, Nothing as that is sometimes a confusion with some people. My view is that you should never think you an expert in anything since it restricts learning. Always you your something about x, and there is always a lot more to learn, this way you always keep learning. Stats Guy

Maybe you can ask if they know how to:- re-use code with classes and .dll- instance objects and use them- build an excel add-in to re-use functions- input/output data to .txt .csv files- query/append data from VB/VBA to a database

Quote re-use code with classes and .dll- instance objects and use them- build an excel add-in to re-use functions- input/output data to .txt .csv files- query/append data from VB/VBA to a databaseWell all these things you can look up on google and find the answers. So I don't think these are really the questions one should be asking. Most logical people can learn coding, but the difficulty comes when they have to use some crap code someone else has written and re-engineer it to do something useful. Learning all the perfect examples they teach you in a training course doesn’t prepare you for this. There are a lot of mediocre programmers out there for the good. Also given the huge variety of quant roles where not every role is 100% coding, you have to account for the fact some quants will be ok programmers but exceptional in developing the models etc.Stats Guy

I was once asked if I dream about Excel when I sleep by a "trying to be funny manager", as I have to work with dozens of XXMb excels spreadsheets at any time. Unfortunately his assumption then was that I knew nothing about software design and software architectures. There's a misconception that if you're good at using a tool like Excel, you can't program properly. You can and should mix the tools which does a better and faster job than all the volatile functions like vlookup, index...etc, Excel should be left as it is for quick changes and simple calculation, VBA for trial algorithms/models, other calculation can be put into addins. The addins can handle all the database requirements, web services, whatever you can think of.If an excel grows to be so complicated that you'll need to leave calculation in manual mode and you'll have to press "F9" to see where your P & L and risk is, you're using excel the wrong way, get rid of volatile functions, precalc your valuations, cache your results... excel doesn't need to recalc the same valuations again and again and again... so much can be saved by your MO, PC, RM by doing the same valuation again and again and again...