VBA testing

[dk]

Today I interviewed a couple of guys for a contract role maintaining and developing an Excel add-in I've written whilst I spent Christmas getting pissed in the mother country. My boss asked me to get in someone who "has a very good handle on VBA". I thought the best way to do this was to chat with them and then ask them to complete a test

I wrote the test expecting that an intermediate to advanced VBA programmer (i.e. someone who we'd want to do the role) would be able to answer at least 5 questions from this. One scored 2 and the other 3 out of 8. The test was carried out without a computer i.e. it should all be on paper.

Now, my question. The test can be found here (it's a Word doc). What do you reckon? Is the test a good test of reasonable level of VBA/SQL or is it too hard? Appreciate any feedback and let me know your scores if you want.

By the way, I'll post the answers later if anyone needs them.

Dan

 

[P Sitaram]

Well, I found it reasonably easy. My score will probably be more than 5.

Do I get the job?

 

[Zack Barresse]

Hey Dan,

Nice test.

Sorry to say, I only got 5. Had trouble with the Database/SQL, Class Module and Array question. I just don't work with them enough. The others were fairly descent (easy ? ) questions. Couple made me stop and think.

Okay, time to grade me:

1) Change to Long, not Integer.
2) ---
3) I'd guess c, but don't know.
4) c
5) =R[-4]C-R[-2]C
6) b
7) I'd guess b, but don't know.
8) b

.. so, are the other 5 right?

Overall .. short, sweet and to the point. A good test imho.

 

[adaytay]

Hi,

Well, I've actually also been doing a few interviews recently as I'm moving to a different part of the country and need to get a replacement in. I also tested my applicants, but backed the written up with a simple practical assessment (ie - here's a workbook - finish it off!)

Your test is set at a more advanced level than mine. I wasn't interested in HOW technical they were - more what kind of overall grasp did they have of VBA. So they were asked to give explanations of some simple (and some advanced) commands, what certain things did etc. At the end of the day, whoever comes in will be supporting my system so my main concern is that they can understand code - and why it does this and that.

I consider myself to be an intermediate VBA coder (hell, I've written about a dozen systems, some that carry out very complex operations) - but I had problems with some of the questions. I think some of them were too advanced - but then that's probably because I probably only got one or two of the answers

But then I dabble and can build systems but I'm not a code expert.

Just my $0.02...

Ad

 

[Richie(UK)]

Hi Dan,

Well, it seems a reasonable test.

Not everybody will have had cause to use Application level events and not everybody will have used Access (I passed on number 2 as I don't use it ). That said, scores of 2 or 3 correct out of the 8 questions posed doesn't sound very promising.

I suppose it depends how far you want to take the testing - you might avoid the results being skewed by having a larger number of questions, but where do you draw the line? Or you could pose a small problem and ask them to write the code (there and then, not go home and ask for a solution on one of the forums!) to solve it.

There may also be an expectation gap here. Remember most Excel users haven't even got a clue that VBA exists, let alone know how to write in it. Therefore, anybody having a basic understanding may genuinely think that they are appropriate for the role that you are seeking to fill. Perhaps you could have applicants complete the test before you decide whether to invest any time interiewing them.

Good luck.

 

[litrelord]

I think I did OK with that. As with everything though it just depends what you've done before. There's probably loads in your add-in which I haven’t looked at before.

So, do you still get the mark for a question if your answer is "I don't know but I could find out on MrExcel"?

There's nothing wrong with using avilable resources after all.

Nick

 

[dk]

Thanks for the replies lads (that was quick!).


To psitaram:
Mate, get yourself to Sydney this weekend and you've got yourself a job



To firefytr:
Thanks Zack. 5 would have got you the job :wink:

Answers:

1. Yes, changing integer to Long is correct. This stumped both candidates.
2. One possibility: SELECT * FROM Table1 UNION ALL SELECT * FROM Table2;.
3. c is correct. The WithEvents keyword is the vital part.
4. b, c and d are all valid answers. a is only relevant for object variables.
5. Correct - =R[-4]C-R[-2]C
6. Yep, b is correct
7. b it is. Statement a would fail if you had an array that was anything other than 0 based. The other are made up.
8. Again, b is correct.

To adaytay:
Well it sounds like we're trying to do similar things. The only difference is that this person will be here for just a month, will need to get up to speed very quickly and will be asked to do a lot of work i.e. there won't be much time for a relaxed handover. The questions were representative of some of the code that exists in the add-in and some of the problems they may face during the add-in's test period.

To Richie(UK):
Some good points Richie me old china. In this case, the add-in makes quite heavy use of application level events so it is quite relevant. Also, the add-in relies on an Access database for data e.g. historical ledger data, previous year budget and so on. That said, if they got the other questions right and not these then that wouldn't have been an immediate 'no'.

Therefore, anybody having a basic understanding may genuinely think that they are appropriate for the role that you are seeking to fill.

This is an excellent point. I interviewed another guy yesterday who could record and edit macros. He seemed genuinely surprised when I said that we needed someone who could write code. He said that he had always been considered a guru. Re your point about making them do the test before interview - just not possible at short notice given the need to ensure they didn't use help to complete it.

Anway, again, thanks to you all for your useful replies. I'm meeting another guy on Monday who looks very promising. I feel comfortable that the test is reasonable so will still use it.

Have a good weekend

Dan

 

[Norie]

Dan

Surely there is more than one answer to the first question?

1. Type the variable as Long

2. Don't type the variable

3. Type the variable as Variant.

On question 4 could you not use this:

If strName = "" Then MsgBox "No name entered"

All a matter of taste/preference I suppose.

 

[litrelord]

Surely there is more than one answer to the first question?

True, but any of those will be using more memory than declaring the variable as long.

Although, as was mentioned on this board and elsewhere before, you’re better of using long instead of integer anyway since if you use integer then VB has to covert the integer to long, then run the code.

Apparently anyway.

Nick

 

[Norie]

True, but any of those will be using more memory than declaring the variable as long.

I know but memory/performance wasn't mentioned in the question.

If are striving for efficiency obviously you need to take in to consideration such things.

But if your just writing a one of piece of code then I think it's OK to skirt such issues.