Detecting and categorizing hard-coding errors in Excel Spreadsheets using Visual Basic for Applications (VBA)

dc.contributor.author Rawat, Vertika
dc.contributor.author Raman, D. Raj
dc.contributor.author Raman, D. Raj
dc.contributor.author Anex, Robert
dc.contributor.department Agricultural and Biosystems Engineering
dc.date 2018-02-13T03:44:11.000
dc.date.accessioned 2020-06-29T22:35:17Z
dc.date.available 2020-06-29T22:35:17Z
dc.date.copyright Fri Jan 01 00:00:00 UTC 2010
dc.date.embargo 2012-12-07
dc.date.issued 2010-06-01
dc.description.abstract <p>Electronic spreadsheets play an indispensable role in the simulation, modeling, and analysis of bioenergy systems, and their results have the ability to affect decision-making significantly. Prior research has shown that spreadsheets are highly error-prone, and that a large percentage of these errors are difficult to detect. To that end, we developed computer code (implemented in Visual Basic for Applications, running under Microsoft Excel) to detect a particularly insidious form of spreadsheet error: the hard-coding error. These errors are defined as the presence of one or more unreferenced numerical values in a cell formula. Hard-coding errors are dangerous because they are a likely source of erroneous constants and/or non-updating assumptions. The code was used to audit six spreadsheets relevant to bioenergy systems, three developed in our lab (and reported on in other sessions at the AIM), and three in the public domain. The preliminary audit results were analyzed to understand the nature and distribution of hard-coding errors. The preponderance and diversity of hard-coding errors in these spreadsheets motivated us to subcategorize them. Together, the hard-coding error detection program and sub-categorization program provide a robust and rapid means of detecting and categorizing multiple types of hard-coding errors. Use of these programs could increase the reliability of spreadsheet software used in simulation, modeling, and analysis of bioenergy systems.</p>
dc.description.comments <p>This is an ASABE Meeting Presentation, Paper No. <a href="http://elibrary.asabe.org/abstract.asp?aid=29827&t=3&dabs=Y&redir=&redirType=" target="_blank">1009201</a>.</p>
dc.identifier archive/lib.dr.iastate.edu/abe_eng_conf/55/
dc.identifier.articleid 1050
dc.identifier.contextkey 3522286
dc.identifier.s3bucket isulib-bepress-aws-west
dc.identifier.submissionpath abe_eng_conf/55
dc.identifier.uri https://dr.lib.iastate.edu/handle/20.500.12876/593
dc.language.iso en
dc.source.bitstream archive/lib.dr.iastate.edu/abe_eng_conf/55/Raman_2010_DetectingCategorizingHardcoding.pdf|||Sat Jan 15 00:54:46 UTC 2022
dc.source.uri 10.13031/2013.29827
dc.subject.disciplines Bioresource and Agricultural Engineering
dc.subject.keywords Hard-coding errors
dc.subject.keywords non-updating assumptions
dc.subject.keywords bioenergy
dc.subject.keywords Visual Basic for Applications
dc.subject.keywords VBA
dc.subject.keywords Excel
dc.subject.keywords error detection
dc.title Detecting and categorizing hard-coding errors in Excel Spreadsheets using Visual Basic for Applications (VBA)
dc.type article
dc.type.genre conference
dspace.entity.type Publication
relation.isAuthorOfPublication 0d20027b-c384-4033-aafe-d7cf62e10240
relation.isOrgUnitOfPublication 8eb24241-0d92-4baf-ae75-08f716d30801
File
Original bundle
Now showing 1 - 1 of 1
No Thumbnail Available
Name:
Raman_2010_DetectingCategorizingHardcoding.pdf
Size:
282.39 KB
Format:
Adobe Portable Document Format
Description: