User authentication/protection

[Phillip]

I'm a professor and use Excel extensively in my classes. I need to be
able to assign Excel projects to my students and have some increased
assurance that the students have submitted their own work. I've tried
my hand at VBA programming and also simply used track changes. None of
these solutions (even the track changes) worked reliably across both
mac and pc platforms.

I would like some advice about whether what I need from Excel is even
possible.

I Here's what I need and what I think would work:

1. I'd like to prevent a student from getting someone else's solution,
opening it up and making some changes, and then submitting it as his
or her own. Here's what might work: something to generate individual
Excel file templates for them to do their work on that are encoded
with a unique key for each student that can't be removed and that can
be easily checked. This will make sure that the students don't simply
email their work to each other, make changes on the file, and submit
it as their own. I've programmed something like this in VBA, with a
VBA checker, but after a lot of time programming, it didn't port to
the mac environment.

2. I'd like to prevent a student from opening up his (presumably
uniquely keyed) template next to someone else's and cutting and
pasting the solution into his template. I don't know what would work
for this.

3. I don't want it easily defeated. Honestly, if it is hard to defeat,
then someone smart enough to defeat it will be plenty smart enough to
do the work instead, and would find the work easier!

4. This needs to work on mac and PC versions, and be backward
compatible with at least 2003.

I understand I won't ever be able to prevent students from opening up
another spreadsheet and manually copying the formulas. That's fine.
Note: I suppose I could have not only unique identifiers but also
unique assignments. That would make it pretty hard to create and
grade, though, unless the assignments only varied trivially from
student to student, so I'd rather not go with unique assignments.

Has anyone seen anything like this, or has an idea that it may or may
not be possible to do it?

Thanks!

 

[Jan Karel Pieterse]

Hi Phillip,

I understand I won't ever be able to prevent students from opening up
another spreadsheet and manually copying the formulas. That's fine.
Note: I suppose I could have not only unique identifiers but also
unique assignments. That would make it pretty hard to create and
grade, though, unless the assignments only varied trivially from
student to student, so I'd rather not go with unique assignments.

You might share the workbooks and turn on track changes.
That will make Excel record each edit and store it with the username that did
the edit. But if student A works on his assignment on Student B's system, that
won't be of much help, unless he also works on his own system.

Still, having two usernames in the change list is no proof of fraude.
The username recorded is the one in tools, options, general, which can simply
be changed.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Ed Ferrero]

Hi Phillip,

My comments in-line;

1. I'd like to prevent a student from getting someone else's solution,
opening it up and making some changes, and then submitting it as his
or her own. Here's what might work: something to generate individual
Excel file templates for them to do their work on that are encoded
with a unique key for each student that can't be removed and that can
be easily checked. This will make sure that the students don't simply
email their work to each other, make changes on the file, and submit
it as their own. I've programmed something like this in VBA, with a
VBA checker, but after a lot of time programming, it didn't port to
the mac environment.

You could try Dermot Balson's stenography code to embed unique codes in a
workbook. See
http://www.westnet.net.au/balson/ModellingExcel/PowerTools.shtml

If you use this approach to embed student names in a data column, you may be
able to catch out those people who copy all the workbook, including the
data. Not perfect, because you could not stop someone from just copying the
solution.

Since you just need to encode a data column, you do not need to store VBA
code in the workbook. You would need to run the code on each student's
workbook on your own PC.

2. I'd like to prevent a student from opening up his (presumably
uniquely keyed) template next to someone else's and cutting and
pasting the solution into his template. I don't know what would work
for this.

I don't know either. You can run code to disable paste on a workbook, but it
is trivial to circumvent, just open the workbook with macros disabled.

Ed Ferrero
www.edferrero.com