VBA testing

[dk]

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.

Yes, you're right! I guess they would have been valid answers.

On question 4 could you not use this:

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

All a matter of taste/preference I suppose.

'

You certainly could. The question was in no way meant to be a complete list. You could also use:

    strName = InputBox("Please enter your name")
    On Error Resume Next
    Dim a
    a = 1 / Len(strName)
    If Err.Number <> 0 Then MsgBox "No name entered"

or many other variations.

Anyway, goodnight!
Dan

 

[litrelord]

Don't get me wrong, I'm not arguing with your logic. There's no doubt you're right. I just meant that for a test at a job interview it would be a little like turning up in ripped jeans and dirty trainers when the position will be in an office where everyone wears a suit.

 

[Tom Urtis]

Hi Dan - -

Your post is coincidental for me. As you probably know, I own an Excel & VBA development company here in silicon valley. The CFO of one of my client companies called me recently, really frustrated because he just hired someone who told him during the interview (as most people do) that they "know Excel", and it turned out on this person's first day on the job that they had never entered a formula before or written any VBA code (such as loops or sheet events or anything outside the realm of the macro recorder).

Business moves at lightning speed and these days employers do not have time to train anyone in anything technical, as you point out. In fact, most employers themselves do not know some of what they need to know.

Anyway, this CFO told me he commiserates frequently with other financial professionals in the SF Bay Area and elsewhere, and they all have the same need for a tool that evaluates a person's Excel skill level.

I am developing a skills test for my client companies in the financial sector to evaluate their applicants, so here are some observations of your test, which is an excellent idea in principle:

(1)
Your test is a bit difficult because it relies on people to know what your company needs them to know, the way they you imply they need to know it. Example, I never come across that run time error (#6 in question 1) because I always declare rows as Long. In my opinion, a better way to ask that question is:
"You want to identify the last used row. Which variable type is best?"
a) Object
b) Double
c) Long
d) String

As Norie and others pointed out, tests work best when their multiple choice possibilities contain only one viable answer.

I scored 8 out of 8 on your test but I guessed wildly on #2 and got lucky, because I rarely utilize SQL. People who truly know about Excel's memory issues as you noted earlier, would ask themselves reading Question #1 why looping through 40,000 cells is being asked in the first place, when a filter would be more efficient for "Flag", and then bolding for visible cells. I tend to cut people a little slack who are not like us (good for them) that do this stuff 14 hours a day.

(2)
Not that it came up here, but different Excel versions determine how people approach some solutions. The steps to create a Pivot Table are slightly different in 97 than 2003. Therefore, I find it's important to let people come up with a correct solution, even if it's not the most correct one, especially if the chair is only being filled for one month.

(3)
It might help to structure sets of questions that require beginning, intermediate, and advanced expertise. The potential pool of job applicants is never static or perfect. At any given time there may be no available person looking for a job who is beyond the beginner or intermediate level. At least if you know that about them, and they exhibit a conscientious nature, you can plan your month around their skillset without being surprised on Day 1 about having relied solely on their skillset.

(4)
I totally agree with your testing approach in principle. Personally, I never listen to people who say they know Excel, or they took this certification test or read that book, or that they are widely recognized as their office's Excel guru. Maybe I'm too skeptical or too hard, but I am not impressed with most Excel "certification" evaluations or the people who composed them. There is too much money and risk in business to rely on anything other than what you are sure is a proper evaluation.

The testing you have in mind is a great idea and something I am developing also for a wider audience. I'll be happy to share ideas with you or anyone about how I am doing it. I will be marketing my test in 2005, starting with the financial and placement hiring communities here in California.

 

[just_jon]

Dan,

I got 6 ( 7 if you consider a slight gaff in the SQL UNION syntax ), so I don't think the test was that extreme a challenge. Hope you get a keeper.

And, Tom -- be interesting to see your test when it's done. There are so many facets to XL/VBA, that I literally learn something new just about every time I browse the threads here.

 

[Cbrine]

I got 6 out of 8, but with a few minutes and a copy of Excel, I would have gotten 7. The R1C1 notation got me, I never use it. The addin event calling would have slowed me down for a while, since I've never needed it yet. The other thing to remember is that we don't have every piece of information in our heads. You should perhaps have something that determines thier searching for answers skills? Ie. Using the Online help, web sites, etc... If I don't know how to solve a problem now, I can most likely find a solution within a couple of hours using my other resources.

HTH
Cal

 

[NateO]

Hello Dan,

The test looks good. Re: #1, if someone wanted to solve the integer data type quandary on large numbers by removing the data type declaration all together, I'd be tempted to give them -8 bonus points towards their score (because I can).

Perhaps tell them that Option Explicit is enforced... Also, I'd be tempted to use an Autofilter (as Tom mentioned)...

Re: question 2, semantics, what about Union (removes duplicates)? E.g.,

Select * From Table1 Union select * from table2

I can see Records 1 & 3 would require that Distinct Row not be enforced...

Good luck and happy holidays.

 

[zilpher]

I'm with Nate, force these guys to declare correctly and -8 is generous.

I'd be uber picky though and penalize anyone who didn't say:

"Change all instances of intRow to lngRow and redefine lngRow as a Long"

And I'd expect them to pick me up on that for not making it clear in the question.

I'd also dock 50 points for anyone who didn't state they'd comment the hell out of it and keep the ReadMe.txt up to date.

But then I have just been audited.. :D

 

[zilpher]

>0¬ Forgot to check it back in to SourceSafe, doh!

 

[dk]

Hello Dan,

The test looks good. Re: #1, if someone wanted to solve the integer data type quandary on large numbers by removing the data type declaration all together, I'd be tempted to give them -8 bonus points towards their score (because I can).

Perhaps tell them that Option Explicit is enforced... Also, I'd be tempted to use an Autofilter (as Tom mentioned)...

Re: question 2, semantics, what about Union (removes duplicates)? E.g.,

Select * From Table1 Union select * from table2

I can see Records 1 & 3 would require that Distinct Row not be enforced...

Good luck and happy holidays.

When Norie posted his original alternative answer of ommitting the declaration my initial reply was "OK, I guess that would pass but then I'd punch the interviewee in the head for **** poor programming technique". However, that isn't really in the spirit of Christmas is it, so I changed it

Re the SQL syntax; the question asks for "ALL records from both tables" and you can see duplicate rows in the sample output. Therefore the UNION ALL is necessary. Maybe I should add to the question something like: "do not remove duplicates" to remove any ambiguity.

Anyway, thanks for taking a look Nate, and Happy Christmas to you too

Dan

 

[dk]

Hi Dan - -

Your post is coincidental for me. As you probably know, I own an Excel & VBA development company here in silicon valley. The CFO of one of my client companies called me recently, really frustrated because he just hired someone who told him during the interview (as most people do) that they "know Excel", and it turned out on this person's first day on the job that they had never entered a formula before or written any VBA code (such as loops or sheet events or anything outside the realm of the macro recorder).

Thanks for that Tom. To reply to each of your points in turn:

1. When I wrote the first question I had it tagged as "Easy". However, on thinking about it, and hearing the comments here, I guess it should receive a harder grading. Anyway, these questions were all supposed to be based on real world problems. Although I always use Longs, and never Integers, the same cannot be said of others. I recently had to fix a simple macro that had been working for years, but that had fallen over exactly because of this problem i.e. the report it was formatting had grown and grown and eventually reached 33000 odd lines, at which point it failed. The organisation I work in has a strong reliance on macros (some that have been in place since 94/95). This is good, in that it keeps me in a job , but bad because there is all sorts of dodgy code floating around which may or may not be working as intended. I'm often only made aware of the code when it goes wrong, as in this case. Therefore I think to ask this sort of question is valid. However, I am going to try and re-word is so that the questionee is pointed to the fact that there is a problem with the declaration.

3. For more general testing, this would be a nice "luxury". However, this position has come about because my bosses boss decided at the last minute that they wanted someone to cover the test period of the add-in (rather than me fix any problems on my return to work). I deemed that there would be no point in hiring someone unless they would actually understand the code, be able to fix it, and enhance it. Therefore, I tried to set a level of questions that would indicate an intermediate to advanced level of VBA expertise. Interviewing is proven to be a poor management technique so I chose this method to allow a more objective conclusion to be reached. If we were hiring for a permanent replacement for me then I would consider the VBA skills less important, as long as they had the desire/ability to learn.

4. This is something that I've also been thinking about. I know that a lot of recruitment agencies have software that can test a candidate's level of Excel/Word/Access knowledge but I've never seen one purely for VBA. Here in Australia (and I'm sure in the US even more) "advanced Excel, macros, VBA" are becoming a more and more common phrase within job adverts, especially for accountants. I'm sure there is a market for this kind of testing. I'd be interested to hear how your ideas develop and will happily share my thoughts with you.

All the best,

Dan