Feasibility of using VBA to make PowerPoint Quiz from Excel?
June 7, 2017 8:30 AM Subscribe
I am a very comfortable Excel user (feel fluent managing references between sheets, using index combined with match, using nested if statements, etc.) but have zero experience with macros, VBA, or any programming whatsoever. I would like to be able to generate PowerPoint "quizzes" based on "question banks" I have in Excel worksheets, incorporating randomization, feedback to quiz-takers, and results exported for later analysis - this appears possible based on a passing comment I heard from a conference speaker and subsequent Googling, but I need help estimating scope. I'd like the quizzes to be operational by August 15. Is this something I can learn in +/- 10 hours a week? Resources welcome! - right now I have a collection of bookmarks but no centralized go-to.
Suppose that I have a bank of quiz items in an Excel worksheet, in which each row is a separate multiple-choice quiz item with "Questions" in column A, "Correct Answers" in column B, and "Incorrect Answers" in columns C-E.
I would like to create code that does the following:
(1) Creates a PowerPoint file with each quiz item on a separate slide, ideally in randomized order.
(2) Present the "Question" as a clickable text box (button)
(3) On "Question" button click, display all answer options as clickable text boxes (buttons). It would be ideal for answer option positions to be randomized (e.g., column B/"correct" answer option isn't always the left-most button on the slide).
(4) On clicking an answer button, (a) record which answer was selected, (b) display whether it was correct or incorrect, and (c) immediately proceed to the next question.
(5) It would be great if I could receive data on when the question was clicked and when the answer was clicked, but this isn't essential.
Why am I not doing this by hand in PowerPoint? It's for a research project in which some of the "quizzes" are 352 items long and I don't want to record people's responses by hand as I watch them. Google Forms would be great, but doesn't have the capability of providing instant question-by-question feedback. So automation this way would be lovely. Thanks for any help you can provide!
Suppose that I have a bank of quiz items in an Excel worksheet, in which each row is a separate multiple-choice quiz item with "Questions" in column A, "Correct Answers" in column B, and "Incorrect Answers" in columns C-E.
I would like to create code that does the following:
(1) Creates a PowerPoint file with each quiz item on a separate slide, ideally in randomized order.
(2) Present the "Question" as a clickable text box (button)
(3) On "Question" button click, display all answer options as clickable text boxes (buttons). It would be ideal for answer option positions to be randomized (e.g., column B/"correct" answer option isn't always the left-most button on the slide).
(4) On clicking an answer button, (a) record which answer was selected, (b) display whether it was correct or incorrect, and (c) immediately proceed to the next question.
(5) It would be great if I could receive data on when the question was clicked and when the answer was clicked, but this isn't essential.
Why am I not doing this by hand in PowerPoint? It's for a research project in which some of the "quizzes" are 352 items long and I don't want to record people's responses by hand as I watch them. Google Forms would be great, but doesn't have the capability of providing instant question-by-question feedback. So automation this way would be lovely. Thanks for any help you can provide!
I agree with Ausamor that PollEverywhere would be a great solution for this, and that doing it in Excel would be quite difficult.
If you'd prefer a custom solution that you can own and won't incur recurring subscription fees to use, send me a MeMail--I built a similar project last fall that adapted some quiz software that Vox Media built for a custom application, and it might be easily adaptable for your needs.
posted by Kwine at 9:35 AM on June 7, 2017
If you'd prefer a custom solution that you can own and won't incur recurring subscription fees to use, send me a MeMail--I built a similar project last fall that adapted some quiz software that Vox Media built for a custom application, and it might be easily adaptable for your needs.
posted by Kwine at 9:35 AM on June 7, 2017
I'm not sure why you want to enlist PowerPoint in this; if I were attacking your requirements I'd just use a VBA form. The Form would have locations where the question and answers go and be populated by a sub that drew that information from some sheet A and wrote the results into a sheet B where those two sheets were in a file(s) separate from the Form and "macro" logic that did all the work (if that separation is necessary). I'll warn you that approaching this with no VB/VBA experience would make the task daunting. You don't mention whether you have experience in programming any other language, so I'm guessing that's a No? As others have suggested your time might be better spent finding a "package" that already does what you want. OTOH, if your fired-up to learn VBA and the Excel DOM, go for it! If you stay in Excel and VBA it's all pretty straightforward, even (5).
posted by achrise at 10:13 AM on June 7, 2017
posted by achrise at 10:13 AM on June 7, 2017
2nding the recommendation to keep this in Excel. I've written a couple VBA programs that talk between Excel and Word, and it's a whole extra layer of complexity that I would not recommend if you have no VBA experience. If you are open to working only in Excel, I think you could definitely do this within your timeframe. Excel is much better suited for macros - there is a built-in macro recorder so you don't have to constantly look up the syntax for range selection, sheet creating, formatting, etc. You could probably create this whole project by copy/pasting existing code samples from the internet. There simply isn't the depth and breadth of VBA code examples available for other programs in the MS Office suite, especially PowerPoint.
posted by smokysunday at 11:08 AM on June 7, 2017
posted by smokysunday at 11:08 AM on June 7, 2017
Response by poster: Hi all, thank you for your responses so far! Several clarifications:
I really appreciate the Polleverywhere suggestion. However, it appears to not provide the individual feedback I'm looking for - in the study, individuals will experience the quizzes by themselves at a computer, not as a class, and it's an important part of the design for this particular investigation. Also, it doesn't appear that you can designate a correct answer? Some quizzes are more like tests and some are more like learning modules where people learn by answering questions and learning whether their answers were correct or incorrect. Also, some of my quizzes are needing to have hundreds of trials, in which response options may remain the same across different questions. It looks like polleverywhere requires you to type in questions and answer options for each quiz item, and you can't duplicate questions to save some work like in Google Forms. If I'm missing some critical features that I couldn't see from my free membership please advise!
I like the VBA form suggestion - that sounds more like what I'm looking for, and I'll do some followup research into that. I think my mind went to PowerPoint because visually, that's what our current (non-optimal) software resembles in terms of user experience.
The PowerPoint and Excel combo comes from a colleague following up with the conference speaker who said they had prior VBA/programming experience and figured it out by "just Googling it". Why not hire someone to write custom software instead of tinkering on my own? Been there, done that, insurmountable roadblocks (favor-based motivation combined with a token amount of pay, nonresponsiveness, and advisor not feeling confident that the relationship is sustainable with the current programmer), now trying to get it done on my own and this is looking like the fastest and cheapest alternative. Because it's a research project, some parameters (like the randomization, feedback, and response recording) are non-negotiable. Please keep suggestions coming!
posted by shortskirtlongjacket at 5:14 PM on June 7, 2017
I really appreciate the Polleverywhere suggestion. However, it appears to not provide the individual feedback I'm looking for - in the study, individuals will experience the quizzes by themselves at a computer, not as a class, and it's an important part of the design for this particular investigation. Also, it doesn't appear that you can designate a correct answer? Some quizzes are more like tests and some are more like learning modules where people learn by answering questions and learning whether their answers were correct or incorrect. Also, some of my quizzes are needing to have hundreds of trials, in which response options may remain the same across different questions. It looks like polleverywhere requires you to type in questions and answer options for each quiz item, and you can't duplicate questions to save some work like in Google Forms. If I'm missing some critical features that I couldn't see from my free membership please advise!
I like the VBA form suggestion - that sounds more like what I'm looking for, and I'll do some followup research into that. I think my mind went to PowerPoint because visually, that's what our current (non-optimal) software resembles in terms of user experience.
The PowerPoint and Excel combo comes from a colleague following up with the conference speaker who said they had prior VBA/programming experience and figured it out by "just Googling it". Why not hire someone to write custom software instead of tinkering on my own? Been there, done that, insurmountable roadblocks (favor-based motivation combined with a token amount of pay, nonresponsiveness, and advisor not feeling confident that the relationship is sustainable with the current programmer), now trying to get it done on my own and this is looking like the fastest and cheapest alternative. Because it's a research project, some parameters (like the randomization, feedback, and response recording) are non-negotiable. Please keep suggestions coming!
posted by shortskirtlongjacket at 5:14 PM on June 7, 2017
Using MS Office, I'd set up an Infopath (included in all versions of Office which have PowerPoint) form linked to your Excel workbook which could record all the answers to the questions back into Excel, and can look as colourful as to want.
You could (and should) do this without VBA. Set up tables in the spreadsheet with the questions, the users, random numbers (because the randomize() function recalculates every time the spreadsheet's edited), then create the table with the lookups to generate the question set for each user, then another table to record the answers.
And set up an Infopath form with an input dataset of the questions table and an output dataset of the answers table, then see how it goes. You should be able to set it up so that the submit button reloads the same form with new questions, and the multiple choice options can be done as a list so that you can have as many options per question.
Hopefully that's enough to think about. The learning curve is steepish, but once you're there, this will be much more maintainable than any option using VBA.
posted by ambrosen at 11:37 PM on June 7, 2017 [1 favorite]
You could (and should) do this without VBA. Set up tables in the spreadsheet with the questions, the users, random numbers (because the randomize() function recalculates every time the spreadsheet's edited), then create the table with the lookups to generate the question set for each user, then another table to record the answers.
And set up an Infopath form with an input dataset of the questions table and an output dataset of the answers table, then see how it goes. You should be able to set it up so that the submit button reloads the same form with new questions, and the multiple choice options can be done as a list so that you can have as many options per question.
Hopefully that's enough to think about. The learning curve is steepish, but once you're there, this will be much more maintainable than any option using VBA.
posted by ambrosen at 11:37 PM on June 7, 2017 [1 favorite]
You can turn off "Gridlines" and "Headings" in Excel (View>Show...) to give it a better appearance, if UI is the primary drawback to using Excel. You are left with a big blank area of screen that still works like a spreadsheet.
posted by smokysunday at 3:34 PM on June 9, 2017
posted by smokysunday at 3:34 PM on June 9, 2017
« Older Poetry about relationships ending | Fish meal that can be prepared (not cooked) ahead? Newer »
This thread is closed to new comments.
posted by Ausamor at 8:39 AM on June 7, 2017 [1 favorite]