Patrick O’Beirne sent me a link to Alex Palfi’s VBA Assessment. I assumed I would ace it, but I got 7 wrong. That’s 80%. I knew I was in trouble when I read the first question.
The following figure illustrates a help system in Excel.
The user can click on a tab topic and see the help for that topic. How would this functionality be achieved?
- A:In Excel – by using conditional formatting
- B:In Visual Basic – by detecting a SheetSelection Event
- C:In Visual Basic – by adding an in-sheet menu.
I’m not sure what “in-sheet menu” means. There was some others that I simply had to guess on. For instance, there were two questions testing what keyboard combinations you could assign to recorded macros. I assumed that meant through the Options button on the Tools – Macros – Macro dialog box. And I honestly don’t remember what keys it limits you to. Of course I could use Application.OnKey to set it to whatever I want. Maybe I was over-thinking the question. I did not refer to Excel or anything else during the quiz, relying solely on my memory.
There are some questions that are worded such that I don’t understand them. And there are some questions that are worded such that, to me, it gives the answer away. That might just be me, though. Also, I can’t find where it tells me which questions I got wrong. I’m probably not going to look them all up, but there are few I’m curious to see if I guessed right.
I think writing an assessment like this is very difficult. This one seems to be pretty well done. While I don’t consider myself to be at the 80th percentile of VBA users, I think a passing grade on this assessment would give you an indication of someone’s familiarity with VBA.
If you take it, post your score in the comments. If you know of other online assessments for VBA, post a link.
14 wrong = 60% ok
;-)
I got 80% also — 7 wrong.
…my result shows they could make some money on me…
;-)
14 wrong
=60% correct
I got 82%.
I disagree with your opinion that it’s pretty well done. I’d say it’s poorly done. Many of the questions are ambiguous, and I could make a decent argument for multiple correct answers.
I should point out that you need to provide an email address to get your score (you discover this AFTER you answer all the items). And the email you get doesn’t even tell you which items you got wrong.
I’d say Tykoh Training needs some training in test construction.
I agree with John on the ambiguous nature of some of the questions. And not just because I got only 77%
Letting you know which questions were wrong would be good.
Providing the correct answers would be better.
88%
Or 31/35, which means they’re truncating rather than rounding. Hmph.
Two that I got wrong were the Columns(“H:K”) and Rows(“4:9?), which are constructs I’ve never used (and don’t like much now I know you can use them).
I agree with John that many of the questions had some degree of ambiguity – in a number of cases I had to think more about the question’s intent than content.
And there’s a typo (“if” for “it”) in Q2…
I got 30/35. 85% is much better than I think I deserve since I have written about 20 lines of VBA in the last 3 years. The questions were pretty ambiguous and if you avoid certain constructs to begin with, it is hard to know if they are going to work or not.
It is also interesting to observe that they did not ask what version of Excel/VBA we should assume.
I’m the dunce so far, to my eternal shame I only got 65%.
I’m with John though; these quizzes often make the assumption that everyone uses the system in exactly the same way: the way the question writer uses the system. Having ambiguous questions/answers doesn’t exactly help.
For instance, I rarely use keyboard shortcuts so wouldn’t even know what Ctrl+Shift+R does (I’ve since checked!), but does only knowing how to achieve the same using the UI mean I am any worse at VBA than someone who knows both methods? What about someone who only knows the shortcut?
I remember getting assessed on Word in some course a few years ago and almost all the questions were like “In which menu would you find Web Discussions?” – not ever having used this, or considering it in any way relevant to my work, I didn’t have a clue. I could easily find it in about 20 seconds though if I ever needed to though. I would be marked down…for this?
What’s more important is knowing that X is at least capable of something, and being able to apply yourself to getting the required result. For instance, today I was asked about creating some XML metadata for some images that will be scanned to a network share, then shifting the images & XML over SFTP. I’ve not done this before, but know of a few ways in which this *could* be achieved.
If you can get an online quiz/CBT to identify this kind of knowledge then you’ve probably got the basis of a very profitable training corporation!
Also 88% which implies 31/35.
Unfortunately we don’t know the quiz’s definition of correct answers, so maybe we all disagreed with Alex on the same questions?
I’ve been involved in setting tests for the Spreadsheet Safe exam, and the peer review process was pretty painful in shaking out all the ambiguities you start with when you think of things one way and discover the amazing number of ways your question can be interpreted!
Patrick
I got 88%, and I know that my level of VBA skill is not higher than John Walkenbach and Andy Pope (for Pete’s sake) so I too rate it as not a very good assessment. I think I must have been a pretty good guesser on more of the ambiguous ones.
Mike, I guess I got Rows and Columns incorrect tooI never would have tried that. Other than that I have no idea what I got wrong and it would take way too long to figure out.
I took the test twice.
I got 2 different results (60% and 74%) and 2 different comments from Tykoh: “If you were to attend our Visual Basic for Financial Applications course we would expect your post-test score to rise to be above 85%/90%” (respectively).
so they are quite flexible…
I got 88% as well. I agree on the ambiguity of the questions, and some of them have multiple correct answers. (I can’t remember prior versions, but in Excel 2010 selecting any area of the chart puts the name in the name box, so question 4 is a waste of a question.)
Multiple choice tests are like that though. I’ve rarely (if ever) seen knowledge properly graded in a multiple choice fashion.
I think there has to be some ambiguity in the questions. If the questions are written too precisely, the answer becomes obvious and someone of questionable skill can get a good grade. However, too much ambiguity likely leads to experts getting low grades – and that seems to be what’s happening here. If I ask a question with the right amount of ambiguity, I should be able to tell how much you know about VBA.
Take this question: What is the worst way to monitor a range variable’s address while running VBA code?
A. Create a watch on the Watch List
B. View it via the Locals Window
C. Insert break points and hover over the variable
“Worst” is a qualitative word. A casual observer might think there’s no wrong answer and that the test is unfair. But if you’ve worked with VBA for any length of time, the answer is obvious. If it’s not obvious, then you haven’t worked with VBA long enough or you just don’t happen to use certain features. In the latter case, you may be a great VBA programmer and still miss the question. But if I have 34 other similar questions, I can start to get a feel for your abilities. If you miss half because you don’t happen to work with those features, then I think the case can be made that you’re not as great as you think.
That leads to the unfortunate situation where people who think they should get 100% (like me) don’t. Rather than admit I’m not great, I’ll say the test sucks. Maybe the test doesn’t tell you your score, but tells you your range: Correct answers 31-35 “Your great”, 27-31 “Your good”, 22-27 “Probably dangerous”, and so on. But then you wouldn’t be getting the “correct” answers, which is something I didn’t like about the above test.
I think we need to crowdsource a VBA assessment test. Sounds like a blog post.
Look at this from a curriculum development perspective. If I design a course that you take and give you an exit test there are some things that you should expect. (1) I can not ask a question on material that I did not teach as part of the class. (2) Not everything that I teach in class is on the test. (3) On a “per test” basis, if everyone answers all the questions correctly I don’t have an adequate test. 75% to 85% range for a class average is a reasonable goal. (4) On a “per question” basis, there can be some questions that everyone answers correctly, but these need to be evaluated to ensure that they are necessary. Again, 75% to 85% range is a reasonable goal. If I have a question that at least 75% of the students don’t answer correctly I have either a problem with my question, or a problem with my course.
Now look at this from the perspective of you taking an “assessment” for material that you did not not attend a specific class on. (1) They can ask questions on anything since they taught you nothing. (2) If their agenda is to sell you on taking a course they are better served by making the test too hard / ambiguous. (3) They don’t care if everyone gets a question right (i.e. the question is beginner level but everyone taking the assessment is advanced).
Now imagine that the “assessment” was actually the exit test for a course. Pass the assessment and you know you don’t need the course. Would that all “assessments” were this way!
I’m not goin t to take it, in case I get less than Ken.
LOL!
I got 82% which, given the results from some of distinguished prior commenters, I think is more than acceptable since I don’t even have an Excel/VBA blog.
On the other hand, I agree that a lot of the questions were ambiguous so maybe I’m just running good in multiple choice guessing. Who knew that the answer was always ‘B’.
I also got 88% woowho
More than most of the MVP’s here !!
A new line on my resume I think, Not.
I do agree with most of the comments about the test, especially since functionality is different between versions of Excel.
You don’t need to know the answers to these questions to be able to do something useful with Excel or VBA.
82% – 6 wrong.
This seems to be the majority result….Which tells me all of us are making the same mistake….or We are getting it right but the author has the answers wrong…
But I agree with John. The e-mail does not tell you which ones you got wrong. So this is debatable.
74%. I agree with Doug, the questions need to have some ambiguity, or you’re just testing recall of ‘facts’ about VBA, whereas a question which requires some level of subjective judgement requires the test-taker to call on their experience.
“the questions need to have some ambiguity”
No, the correct answer needs to be demonstrably correct and exactly fitting the language of the question.
A good design would include a possible answer that’s close but no cigar.
For example, in the question about whether Powerpoint includes VBA.
MS has withdrawn the support for the macro recorder in PP 2007 on.
But the question is about whether VBA is included, not the recorder, so the answer is Yes.
I agree that good tests are about recognition, not recall. The latter explains why so many pass exams but can’t hack it.
Dan is right that it needs to be tough enough that some will be a severe test. After all the results of a test can be seen as a report on the performance of the test in communicating what is required! But then what is its required purpose? In this case, to qualify whether people need his VBA course, which is a perfectly valid objective.
I’ve sent Alex some feedback. By not including his official answers in the quiz response email, he’s missing some great review opportunities from this group.
P.
Sadly, I can not view my results as the email address form has a 30 character length limit (I don’t think I’ve ever seen that before!) Guess we should add that to the list of improvements that need to be made.
82%. I feel like some questions were irrelevant, particularly those about recording macros. Recording macros helped me learn VBA, but I can’t remember the last time I did it. Same goes for referring to a range like Columns(“H:K”). Does that work? I have no idea, but I don’t think that’s what holds me back.
I got 39/50 (78%) – but reckon I should get higher cos I disagree with a couple of them! :-/
Luke M: you are WRONG!
Email addresses should NEVER be longer than 30 characters … didn’t you know that. Go to the back of the class.
Seriously, I had several problems with this test – I never did find out my score ’cause I wasn’t going to give them my email that easily. But there are not always right and wrong answers. Often it just depends …
I got 100%, you guys are all loser! ;-)
I got 84%, 42 out of 50, there is now a check box at the end to enable it to send the questions with your answers and the correct answers. And I definitely agree that there are some ambiguous questions. One question that I had was “Can a Visual Basic function change the background colour of a cell?
[Allowed responses – 1: Yes 2: No ] [Your response – 1, correct answer – 2 ]” Obviously, a function used as a UDF cannot change the cell colour, but when calling it from a macro, then it can indeed change the colour. I’d say of the 8 I missed, 4 could be argued effectively. The writer of this test definitely has his ideas on how code should be written but does not have a good grasp on writing questions that accurately assess the users skill. I’d love to see him post the reasons for his answers.
That bloody Ross
He copied my answers!
;-)
I got 86% (7 wrong, 43 right, including a load of lucky guesses). And there’s an option to have them send you the test questions and answers with your score. For a poorly paid Excel monkey in the depths of Englandshire I don’t think that’s too bad.
Agree that there are questions on there which are poorly worded and which (if someone was to ask me f2f) I’d prefer to answer with ‘it depends’.
For example
“You write a macro in a particular workbook. Can you specify that the macro runs automatically whenever the workbook is opened? “
[Allowed responses – 1: Yes 2: No ] [Your response – 2, correct answer – 1 ]
That’s weird, because the last few spreadsheets I’ve written I had to include a nag screen to ensure the user enabled macros. If you create a workbook with the assumption that the Workbook_Open event will always trigger, you’re instantly stuffed when the user has Security set to high, or Disables macros on Medium.
Can’t say I was impressed by the keyboard shortcut questions either; I develop spreadsheets for other people’s use, so I go big friendly Active X button controls all the way. Haven’t used a keyboard shortcut in… well, ever. Given the minimal cost of getting a keyboard shortcut wrong (“Alt-R doesn’t work. Okay, I need to find another key combination…”) I’m sure they could have devoted a question of two to rounding, or datatype conversions, or other areas which will cause potentially more serious but less obvious errors.
In short, not a hugely bad test but could be a lot better with bit more effort and polish.
I scored 90% (5 wrong). I agree with J-Walk about the ambiguous nature of some of the questions…most of the ones I got wrong weren’t written very well.
I got 80%(10 wrong). I failed at most of question about shortcut key:(
Agree with J-Walk about the ambiguous questions.
For example, in question 12(find the PC name), the answer seperated Excel function, Visual Basic function and Windows function. But in question 19(change background color of a cell), this “visual basic function” become “excel function”.
I scored 88% (6 wrong). Some aspects of VBA I don’t know much about and there’s more I don’t know anything about. The test has a fairly broad range of questions, I’m not sure what they would manage to push through a course. I’m still learning from John Walkenbach’s books.
In addition to ambiguity, the problem I have with tests of this ilk is the subjective nature of the questions.
Take, for example Dick’s intentionally ambiguous question. He says, “A casual observer might think there’s no wrong answer and that the test is unfair.” The implication is that one of the three options is in fact no way at all to monitor a range variable’s address at all. If so, this option must be discounted as a candidate answer. Therefore, I must choose subjectively between two valid answers. Therefore, I think the question is unfair but due to admitted over-analysis rather than casual observation ;)
A fair question IMO would ask something more like, “Which of the following would not reveal…”
I took the test (hgaven’t yet received the results) and was asked about the approximate number of lines before a VBA sub procedure can generally be considered to too large. Obviously, there’s a lack of objectiveity in that one but I was asked two questions pertaining to the Personal workbook, a concept I vaguely recall from a decade ago, never but had anything to do with. Absolutely no questions about the Implements keyword in VBA, which tends to ‘sort the men from the boys’ in my experience. In other words, not only are individual questions subjective but the features chosen to question on are likely to reveal the test’s author personal biases.
I scored 3 wrongs, so a bit over 91 percent.
Didn’t agree with some of the questions, bit obviously managed to pick the expected answer :-)
This is written more than a year after the previous comment. I think they must have changed some questions since this blog post was published. I did not find some of the questions mentioned above.
Also, when you provide your email addy after the 50th Q, you’ll get a full report, listing *your* answer and the *correct* answer for each of the 50 Qs, so now you can see your bloopers.
Some of the questions are still ambiguous and open to discussion, I feel.
OTOH, I won’t complain. I consider myself a relative VBA rookie, writing the odd change event and otherwise using the macro recorder and clean up what I can. Definitely not a developer, and for me a “class” is something that you take when you want to learn tap dance or ikebana, and not something I’d even dream of writing in VBA.
Still, with the current set of questions, I scored 80% correct (40 out of 50 Qs)
Happy with that.
43 out of 50 here. I suspect they have changed some of the dodgier questions. Most of the ones I got wrong I’d put in the “OK, but who cares” category, but:
“Can you call a macro from an Excel formula?
[Allowed responses – 1: Yes 2: No ] [Your response – 1, correct answer – 2 ]”
What is a UDF, if not calling a macro from an Excel formula?