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!