# Off Topic > The Water Cooler >  >  Questions to ask a candidate for an interview

## JieJenn

One of my clients asked me to conduct a quick interview (10 - 15 minutes) for them on a position that involves using Excel VBA and Excel function. They just want someone with general understanding of how to write sample code and how to use advanced functions, etc.

Any suggested questions that I can ask?

----------


## Whizbang

1:Which is more efficient, array formulas or SUMPRODUCT? - This will weed out the people who have never heard of either.  A: Usually SUMPRODUCT.


2:Which method do you prefer for dynamic named ranges, OFFSET() or INDEX() and why?  The answer is, IMO, INDEX because it is less volatile.


3:What are the things to consider when using the functions SUMIFS, COUNTIFS, etc. that can be found in Excel 2007 and newer? A: They will not work if the user has a previous version of Excel.  They only accept ranges as arguments and so will not work on closed workbooks or with more complex conditions that result in array values.  No possibility of OR conditions.


4:Using VBA, what is the best way to copy the values from one sheet to another?  A:


```
Please Login or Register  to view this content.
```



5:Using VBA, how would you delete all rows in a table that have an "X" in column B? A:


```
Please Login or Register  to view this content.
```

----------


## split_atom18

Explain how to use nested if statements.
Difference between HLOOKUP & VLOOKUP
List a few cell vba properties. - example font, color etc
Explain what a "Case Statement" is
Tell me something about error handling.

Just a few, honestly had to think pretty hard about that. I figure those are spread out but not to complicated, and would show a good well rounded knowledge. I look forward to seeing more responses on this as to what is "General/Common Knowledge".

----------


## JieJenn

Man. You must do this a lot. It is a phone interview, so I am probably not going to be harsh on the candidate. Do you have any recommendations on general VBA question rather than the specifics.

----------


## Whizbang

What was the most challenging Excel document you've developed/worked on and why?  How did you overcome the challenge?

What is an area of Excel you feel you are strongest in?  What is your weakest area?

When working on a document someone else developed/designed, what are your pet peeves?

What kinds of tasks have you automated using VBA?

Have you ever developed UserForms in VBA?  

What is your experience with debugging and error handling?

----------


## daddylonglegs

> 3:What are the things to consider when using the functions SUMIFS, COUNTIFS, etc. that can be found in Excel 2007 and newer? A: They will not work if the user has a previous version of Excel.  They only accept ranges as arguments and so will not work on closed workbooks or with more complex conditions that result in array values.  No possibility of OR conditions.



I might disagree with that last statement. Sum column C when column A = "x" and column B = "y" *OR* "z"

=SUM(SUMIFS(C:C,A:A,"x",B:B,{"y","z"}))

----------


## Whizbang

Interesting.

Thanks, DLL!

----------


## Firefly2012

> When working on a document someone else developed/designed, what are your pet peeves?



I think this is an excellent question to ask.  Especially good on the end of a telephone as it doesn't rely on specific code examples.  I think this would give you a reasonable idea of how good they are with VBA and also how frequently they've had to de-construct others' code.

----------


## JieJenn

> I think this is an excellent question to ask.  Especially good on the end of a telephone as it doesn't rely on specific code examples.  I think this would give you a reasonable idea of how good they are with VBA and also how frequently they've had to de-construct others' code.



It's hard to ask that question when we don't know how competent the candidate's Excel knowledge is.

----------


## split_atom18

> It's hard to ask that question when we don't know how competent the candidate's Excel knowledge is.



Isn't that the purpose for the questions? To determine their level of knowledge on the subject.

----------


## JieJenn

You'll be surprised how many people who claim they are power Excel/VBA user when my company conduct an interview, but among those, maybe 20% - 30 % know how to use vlook, and less than 10% can tell me what option explicit means.

----------


## split_atom18

Tell me what you know about Option Explicit, was one of the lines I deleted from my original list. I wasn't sure if that was required as you can code without it.(foolishly imo, but doable)

----------


## JieJenn

Oh. I meant can you tell me what does Option Explicit do. Well, the interview is in 45 minutes.

----------


## split_atom18

Ask if they are a member of excelforum.com.
If the answer is yes, ask them how many infractions they have.
If the answer is 0 => Hire cause they will get the job done even if DLL is doing it for them :P

P.S. Let us know how painful it is.

----------


## JieJenn

Just finished. It was okay. I asked the syntax of vlookup, array formula, and CASE Statement, and his experience in using MS Access. He messed up on the Case statement question, and for vlookup, he almost got the syntax right. His answer was, lookup value, table range, row #, and true/false).....I think I will move him to the next round just because he was able to give me the an example of how to use array and how to apply that to his past projects.

----------


## Mordred

I think it's important to remember that when answering unknown questions on the fly can be difficult for anyone who isn't an actual guru, and that goes for anything.  When I was hired at a couple years ago at my present employer, I was asked about 10 or so questions for which I got about 80% right.  It wasn't perfect or close to it but it was enough to hire me and I don't think I've let them down yet (other than a couple late appearances at meetings  :Wink: ).  I don't know what your success percentage would be but I would think that if the person go anywhere from 70 to 75 % or higher than it would be worth sending them to the next round.  My 2 cents.  I would think that your interview was a success and moving him to the next round is a good idea based off of what you have stated.

----------


## abousetta

IMO, it is more important to know how to find an answer to solution than to have all the right answers on the top of your head. The whole process of doing a PhD is centered around learning how to solve problems rather than memorizing a dictionary of terms, syntaxes, etc. There are usually many ways to tackle the same problem, but only the bright ones will think outside the box and search for innovation. Here on this forum, a lot of the solutions I have provided people come from searching the issue, piecing together other people's thought and ideas for solving similar problems, and creating a custom solution for the OP.

----------


## split_atom18

I have to agree. I was only partially joking when I said ask him if he is a member of this forum. More likely I think I would ask what online resources they use when they get stuck. If they say they don't get stuck, get your wading boots out so your shoes don't get crap on them, cause the room is filling fast.

----------


## JieJenn

Haha not everyone is a hardcore Excel addict. For me personally. As long as the candidate can give me an answer(s) that is(are) close to what I am looking for, it's a green light.

----------

