VBA in the spreadsheets from the Enron email corpus

The European Spreadsheet Risk 2015 Conference papers are now available at
http://www.eusprig.org/papers-presentations.htm

My presentation was on investigating the use of VBA in spreadsheets in the Enron email corpus.

http://www.eusprig.org/presentations/VBA%20in%20spreadsheets%20from%20Enron%2CPO%27Beirne%202015.pdf

Most of the slides deal with the mechanics of how I did it, and statistics on the 538 workbooks found with unique VBA content. The network graph I did for interest was #madewithgephi.

Workbook VBA code similarity

Workbook VBA code similarity

Some conclusions I came to are:

1) The workbooks are probably not typical of the routine mass of everyday spreadsheets.
If people email spreadsheets to others, I infer that they don’t have a shared folder on the network. Therefore these workbooks are for communication. So, they are probably not an insight into the real ‘dark matter’ of EUC that stays in shared folders and never emailed – eg routine accounting workbook.

2) Apart from simple static analysis which gives a general indicator of code quality, it is very difficult to say if the VBA contains errors.
The real test is in execution of the code. But we cannot reproduce the environment in which these workbooks were created. The files we have are probably circulated as reports for reading, so after the execution of the code. Therefore do not have the preconditions to run the code. Static analysis like TM-VBA inspector only goes so far.
Code inspection can raise questions of unsafe practices and assumptions, but is time consuming.

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, Research and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s