I received a comment on one of my older posts about interviewing for an Excel VBA job, asking about possible interview questions. I’ve been on both sides of the interview table, and I enjoy interviewing, and I’ve thought about this quite a lot. So I thought I’d write a more thorough reply, and make it a full post instead of hiding it in a comment. Maybe others can find this useful, too.
If I was conducting an interviewing for an Excel VBA, I would probe you from the following directions.
One: your general attitude towards and approach to programming. I’d ask you to write code to solve some simple generic problem that shouldn’t take you much more than 15 minutes. It could be sorting an array, or printing a checkerboard pattern of 0s and 1s, etc. I’d check that you have meaningful variable names, that you declare your variables (which in VBA you don’t necessarily need to do), that your code is reasonably well-organised etc.
Two: your knowledge of Excel. I’d want to see that you can use array formulas and pivot tables, lookup functions, named ranges, etc. You can’t be a good Excel VBA developer if you’re not a good Excel developer. I would give you a laptop and a few actual problems to solve, and leave you to it.
Three: your knowledge of the Excel object model. I’d ask you to write code that manipulates a worksheet – for example, sort and filter a range, or clean out duplicate data from a list, or consolidate data from multiple files. I’d look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also I’d definitely want to see that you don’t write “macro recorder”-style code with Select and Activate everywhere.
Four: your knowledge of how Excel VBA works. I’d check what you know about events (“Can you write code that runs every time the worksheet changes?”), about user-defined functions (“What is and isn’t allowed in UDFs?”), volatile functions (“How can you ensure that your UDF is volatile?”, “Which built-in Excel functions are volatile?”).
Five: questions specific to whatever the job is about. If it’s at a bank, I’d want to see you do some basic financial calculations. If it’s a database reporting job, see if you can work with ADO and basic SQL, or interop with Word, or creating charts in VBA, or whatever else is relevant.
This is quite a lot and might not all get covered in the first interview, but I would ideally want to go through all of this before deciding whether to hire you.
Personally I’d prioritise the parts just as I’ve listed them here, most important first. But I’ve been to interviews where they do almost the opposite – I once got a 10-question multiple-choice test about some nitty-gritty stuff in Excel but they didn’t care about what my code looked like. So you never know.
The reason I’ve put “knowledge of Excel” above “knowledge of Excel VBA” is that in my opinion, the latter is more straightforward and easier to learn. It’s just code, and it is reasonably well documented. It’s harder to find people who are good at writing efficient Excel formulas. But often formulas can yield a far more efficient solution – faster by several orders of magnitude. If I have a choice between formula and code, I’d almost always choose the formula.
John, I hope you find this helpful, and good luck with your interview!
fantastic. thanks so much for your thoughtful response! your comments are more than helpful, they are really quality areas of question, and i greatly value your input. In fact i am following your exact priority, as i realize that i am a bit weak in the excel-side formula area… i will try to come up to speed as fast as possible. You must have brought great talent to your company!
thanks again -john
Helen,
As I said on another post, I found this really useful. It inspired me to do my own!
http://wp.me/pN8iB-1D
Doh,
Link to the wrong article! http://wp.me/pN8iB-1r
Hi there,
It is an interesting set of questions. As a developer with donkey’s years of Excel/VBA experience, I found that the above are enough to establish an average developer.
I think what is lacking from above is the serious OOP questions, even though that VBA is not a 100% OOP but it is as close as. For example, what are the interfaces for? Where and how do you encapsulate code? what are the benefits of using Classes as oppose to modules, how do you make a simple class object, how do you start and dispose of classes…
Also some questions on the how to make a singleton design patterns, what is good and bad with them? also, hash tables or arrays? when would you use dictionary or collection object and why?
This I believe will separate the boys from men!
I have been on both side of the table and when in interview, asking specific task to do is the worst test. General knowledge of something is one thing, but asking someone to write code is another! let’s say you have worked with one particular technology (InterOps for integration of excel in C sharp) then asking the poor interviewee about that specific test, is pointless. It is better to ask if he/she knows the subject and give examples of where and how to use it.
Anyhow, that is my opinion.
Hi Farshad and thanks for stopping by to leave your feedback!
Indeed as you say, to separate the boys from men you’d ask questions on a different level. This post was mostly written with “boys” in mind – developers who will need to write code to automate Excel to solve problems of low to medium complexity.
To be honest, I think that an Excel VBA application should never reach beyond a certain level of complexity at all. If it does, I’d wager that you’ve chosen the wrong tool. Excel VBA is a specialized tool for solving a specific kind of problem, not an all-purpose language.