Skip to main content
Article
Technical Note: Detecting and Subcategorizing Hard-coding Errors in Bioenergy-relevant Spreadsheets Using Visual Basic for Applications (VBA)
Applied Engineering in Agriculture
  • Vertika Rawat, Iowa State University
  • D. Raj Raman, Iowa State University
  • Robert P. Anex, University of Wisconsin–Madison
Document Type
Article
Publication Version
Published Version
Publication Date
1-1-2011
DOI
10.13031/2013.37064
Abstract
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. The code was used to audit six engineering 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.
Comments

This article is from Applied Engineering in Agriculture, 27, no. 3 (2011): 469–474.

Access
Open
Copyright Owner
American Society of Agricultural and Biological Engineers
Language
en
File Format
application/pdf
Citation Information
Vertika Rawat, D. Raj Raman and Robert P. Anex. "Technical Note: Detecting and Subcategorizing Hard-coding Errors in Bioenergy-relevant Spreadsheets Using Visual Basic for Applications (VBA)" Applied Engineering in Agriculture Vol. 27 Iss. 3 (2011) p. 469 - 474
Available at: http://works.bepress.com/raj_raman/25/