Spreadsheets have been the workhorse of computational engines for over 30 years. They allow users to perform demanding calculations without requiring learning or using a higher-order programming language like C++ or Fortran. Excel is found on a majority of machines as a fundamental component of one of the Microsoft Office packages. Most users do not get beyond the use of formulas in cells, even in very sophisticated financial, engineering, or scientific applications. Others may use portions of the Visual Basic for Applications (VBA) macro language to add capabilities to their spreadsheets, but VBA is a programming language and must be learned as such. There are many books that have been published over the years describing the capabilities of Excel spreadsheets and VBA. This book builds upon the lessons taught in them. Excel 7 and later are supported.
The target audience consists of capable Excel and VBA developers who want to bring their skills to an advanced level. The author presumes this level of knowledge and skill. The tone of the book is conversational. He uses the first person as if he were giving a lecture or seminar presentation. He teaches the controversies of his recommendations for developing spreadsheets and VBA code.
The book consists of nine chapters divided into two parts: five chapters in the first part and four in the second. The first part is on advanced topics on the facilities of Excel: VBA, formulas, and form controls. The second part is an extended example of the development of an application for the capture of user input, reporting, and analysis of health statistics.
The first chapter in the first part would normally be an introduction or preface to the book. It is essential reading. The URL for the publisher contains a zip file with the example files used in the book. The first chapter also has two very sophisticated examples emphasizing the use of Excel functions: one regards a periodic table in which mouse movement brings up more information on each element, and another a maze game. These examples are not available from the publisher, but the URLs are correct and the discussion and spreadsheets can be downloaded. There are additional references to websites created by other Excel masters.
The actual content begins with chapter 2 on VBA. The author calls this chapter a refresher. It is not. It is a critique of development styles and principles, coding practice, naming conventions, and referencing. There are many code fragments illustrating his points. There is one major recommendation: use VBA when VBA code is actually needed. If a task can be done using formulas in the spreadsheet, use the formulas. The third and fourth chapters discuss advanced use of formulas. The issues presented in chapter 3 include dynamically sized ranges, union and intersection operators, the CHOOSE function (instead of nest IFs), and the use of Boolean functions. Chapter 4 uses functions for filtering, selecting, aggregating, and highlighting data. Among the functions employed are COUNTIF, LARGE, INDEX, OFFSET, and SUMPRODUCT (that is, the dot product). Chapter 5 completes the first part with a presentation on form controls, for example, checkboxes, radio buttons, and sliders. The author urges developers to use form controls instead of ActiveX controls whenever possible. He argues that form controls are leaner, more lightweight, and much easier to employ. ActiveX controls are larger and can sometimes behave unpredictably. When possible, go with the simpler choice. He discusses his favorite form controls: ComboBox, ListBox, ScrollBar, Spinner, and CheckBox. He discourages the use of the Button, Label, OptionButton, and GroupBox controls. For both the controls he recommends and those he discourages, he provides demonstrations in code with his example files to support his arguments. He concludes the chapter with an extended example of using controls to dynamically highlight points on a graph.
The second part of the book is an extended case study based on a World Health Organization report in which national health performance indicators are evaluated. Using this spreadsheet package, the author demonstrates how he applies his recommendations for developers. He instructs readers by leading them through a reverse-engineering process in which he analyzes a complete Excel spreadsheet instead of creating one from scratch. There are many reusable components in this package. Chapter 6 is the first chapter in this part, and its focus is on capturing user input by creating a spreadsheet wizard that does not employ any ActiveX controls. Some VBA is required to create the infrastructure of the wizard. The listings are given in the text and in the example files. Chapter 7 discusses the storage of data using typical database functions in Excel. The database is the recipient of the data captured using the wizard in the previous chapter. Typical database functions of adding, editing, and deleting records are implemented. Chapter 8 describes the creation of a dashboard to support a decision support system incorporating a sensitivity analysis of the data in the problem. The display is graphical and uses Excel’s functions for conditional formatting. Database functions for sorting and matching are among the featured tools. The ninth and final chapter is on presenting the results. This topic was introduced in the previous chapter, but receives its final polishing in chapter 9. Intermediate tables are used to develop the final user view of the results, and the capabilities of Excel to conditionally format cells are employed.
This is a slim book that is deceptive in the depth of material presented. Learning the lessons the author teaches will require attentiveness. The reader is in the presence of a master.
More reviews about this item: Amazon
