Creating Individualized Data Sets for Student Exercises Using Microsoft Excel and Visual Basic
Weiyu Tsai - mgtwt@business.utah.edu
Don G. Wardell - mgtdgw@business.utah.edu
Management Department David Eccles School of Business,
University of Utah, 1645 E. Campus Center Dr. #106, Salt Lake City, Utah 84112-9304
Abstract
In this paper we describe an approach that utilizes Excel macros to help OR/MS instructors to enhance and assess their students' learning. We begin by explaining a specific macro, Data Generator, which mainly helps statistics instructors to create assignment questions where numbers or data sets used in the questions are randomly generated according to the instructions specified by instructors. Because of the potentially narrow application range of our particular macro, we also describe an approach that can be used for more general questions. For the most part, this more general approach requires simply recording a macro and then pasting a few other instructions into the resulting code to make the recorded macro more user-friendly. We also provide a suggested procedure to help instructors to grade the students' answers automatically.
Download the PDF
10.1287/ited.7.1.136
Supplementary Material
We have included 4 supplemental files with our submission. The files are Final_Part_2.doc(25), final06.xls(26), sample_student_final.xls(27) , and grading_file.xls(28) . Each is described briefly below.
This is the Word file that contains the questions that we asked the students. It also includes instructions to the students. Note that it is Part 2 of a two-part exam. The first part of the exam contained more conceptual questions and did not require Excel.
This is the file that generates the data sets as explained in section 4. When opened the macro will begin automatically, prompt the student for his or her id, and then create the data file using the supplied id to name the file. Typically we would hide and protect the code, but for the purposes of the paper we have not done so. In order to see the VBA code associated with the file, it is necessary to disable the macro when opening it (because the macro starts automatically and closes the final06.xls file as soon as it is done running). Also, we have protected the workbook so that students cannot change its structure. The file can be unprotected using Tools/Protection/ Unprotect Workbook and entering password when prompted for a password. Once unprotected, it is possible to unhide the hidden worksheets and see the functions that we used to simulate the data.
This is a file that a student submitted for the exam. It can be used to experiment with the grading macro. Both this file and "grading_file.xls" should be open, but this file should be the active file before running the grading macro.
This file contains the grading macro that we created using the methods described in section 3.2. It is used to check student answers automatically. With this file and the "sample_student_final.xls" file open, and the latter active, use CTRL-SHIFT-g to invoke the macro.
Citation Information
Tsai, W., Wardell, D. 2006. Creating Individualized Data Sets for Student Exercises Using Microsoft Excel and Visual Basic. INFORMS Trans. Ed. 7(1) 136-148. Available online at http://ite.pubs.informs.org/.
DOI: 10.1287/ited.7.1.136