The magazine of the Melbourne PC User Group Spreadsheets - for the bookshelf Major Keary |
Spreadsheet applications operate in a remarkably similar way, regardless of vendor. The first spreadsheet program was Visicalc, published in 1979 and written by Bob Frankston and Dan Bricklin (who had been a programmer at Digital Equipment Corporation) for the Apple II. SuperCalc, written to run on CP/M machines, was released in 1981, Lotus 1-2-3 (written for 16-bit DOS) appeared in 1983, and Excel turned up later. It was the introduction of a completely rewritten Lotus that marked a watershed in spreadsheet program design; while more bells-and-whistles have been added to modern spreadsheets, and they are integrated in so-called office suites, they follow the Lotus model.
As a result of Microsoft's dominance of the desktop market Excel is the most widely used spreadsheet application, which is why most spread- sheet literature is about using Excel. That does not mean it is any better than others; in my opinion the OpenOffice.org (OOo) spreadsheet module (Calc) is superior. Calc comes with a library of filters that enables it to read and save-to formats used by the various versions of Excel (and many other brands of spreadsheet). It is likely that version 2 of OOo will include a VBA filter to enable the conversion of VBA macros. Calc also writes much, much smaller files than Excel; in one instance an identical set of data required a 2,025 kb file in Excel, as against 536 kb in Calc.
In Office 2003 Microsoft has changed the default format for files from RTF to XML, which makes files created under Office 2003 inaccessible to earlier versions. Users can install a reader, but that's not much help if they want to work on a file. However, OOo can be used to convert Excel 2003 files to the format of an earlier version, convert files created under earlier versions to Excel 2003, or simply convert them for use as OOo Calc files.
Excel 2003 may appear to be the same as previous versions with the addition of "a handful of new objects", but "underneath that are fundamental shifts implied by the new features" [Webb: Excel 2003 Programming]. Another, Microsoft-friendly, 'feature' is that users are encouraged to lock themselves into proprietary applications: .NET and SharePoint, programming Excel with Visual Studio Tools, and the collection of information with Infopath. It also has much improved security features, which are necessary if users are to take advantage of SharePoint and Web services.
Even though the 'native' format of Office 2003 is XML, the files are still stored in binary code. There are save-as options that include 'save as XML' and 'save as XML spreadsheet', but the default is binary. OOo has a more efficient arrangement. Its files are saved in tagged XML (plain text with XML tags) and compressed. Change the file extension of an OOo file to .zip, open it with one of the ZIP utilities and you will see a group of .xml files that can be read in a text editor; they don't need proprietary software to provide access to XML applications.
When it comes to spreadsheet methodology, any book about Excel is very likely to apply to other spreadsheet products. The main difference is that vendors use proprietary scripting languages for writing macros (VBA in the case of Excel); the problem of conversion to another programming language is being addressed by OpenOffice. One of the attractions of spreadsheets is that the wide range of available functions lends itself to extraordinary innovation, and powerful programming features enable unlimited customisation and automation.
Writing Excel Macros
Programming a spreadsheet application has many advantages, but you need to know how to program in the particular vendor's language. Microsoft uses a modified version of Visual Basic for each of its core Office modules: Excel, Word, and PowerPoint. Each module has its own Visual Basic for Applications (VBA) that has been tuned, so to speak, to the particular application's object model. That may sound complicated, but in fact the task of programming is made easier. When a macro is recorded the process is a simple capture of a sequence of keystrokes and the conversion of that information to VBA code. A macro runs a routine; programming with VBA extends the user's control over the application.
If you want to learn VBA for Excel the best resource is Steven Roman's Writing Excel Macros. It requires familiarity with Excel, but does not assume any programming experience and begins with a sound introduction to the Excel Visual Basic Editor. It then moves on to the VBA programming language and "Excel Applications and the Excel Object Model". The book does not pretend to be a definitive account of Excel programming, but sets out "to acquaint you with the main points of Excel programming -enough so that you can continue your education on your own". For many users this text will be all they need; for those with greater aspirations it is an ideal launching pad for learning to program Excel.
|
Steven Roman: Writing Excel Macros ISBN 1-56592-587-4 Published by O'Reilly, 529 pp., RRP $55.00 incl. GST |
 |
Excel Timesaving Techniques
The Dummies series has begun to introduce titles that cater for
intermediate-level users who would like to improve their skills. On the front
cover of Excel Timesaving Techniques for Dummies it says the book provides
"expert insights that help you work like a pro". It contains sixty-one
stand-alone items (techniques) that are grouped under: Make Excel Work Your Way
(customisation); Quick Worksheet Creation Tricks (includes navigation, data
entry, data validation, verifying entries with text-to-speech); Handy Ways to
Format and Present Worksheet Data (includes range and style formatting,
controlling when certain formats are used, charts and graphical presentation of
data, customise number formats); Worksheet Formula Timesaver (includes copying
formulas, speed up table creation with array formulas, smarter formula
construction);
Worksheet Editing Timesavers; Tips for Printing, Sharing, and Reviewing
Workbooks; Streamlining Data Listing and Data Analysis; and a miscellaneous
group that includes entering data and commands by voice, automated table
lookups, creating queries to import data from external databases, and creating
custom functions.
The content is presented in tutorial style with clear 'step 1,2,3, ."
instructions. Screen shots are used to good effect and there are ample tips,
warnings, and other asides that add to the book's usefulness. Readers are
assumed to be using Excel, either at work or for study, at a basic level. The
covers features of Excel 97-2003 running under Windows 95-XP. If you want to
step up from novice or intermediate-level user, this is worth having.
Readers are assumed to be using Excel, either at work or for study, at a basic
level. The book covers features of 97-2003 running under Windows 95-XP. If you
want to step up from novice or intermediate-level user, this is worth looking
at.
|
Greg Harvey: Excel Timesaving Techniques for Dummies ISBN 0-7645-7427-2 Published by Wiley, 395 pp., RRP $44.95 incl. GST |
 |
Excel Hacks
This is a title in O'Reilly's hacks series. Readers should be aware that the
term, hack (hacker, hacking), has acquired a pejorative usage in some quarters
that reflects ignorance of its origins. O'Reilly gives recognition to the work
of hackers, who have contributed greatly to finding solutions for shortcomings
in vendor products and creating useful tools. Excel Hacks is a compilation of
hacks "created by Excel users looking for simple solutions to complex problems".
This is not a tutorial for novices; it assumes the reader is an experienced
Excel user. As already noted, books such as this one are equally relevant to the OpenOffice.org (OOo) Calc program.
A problem with spreadsheets is the way in which other users muck about with
them. One person in an organisation creates a spreadsheet; copies are
distributed or made available on the server; other people may add data, change
parameters, and otherwise leave behind debris or even corrupt a worksheet. The
first chapter of this most useful text, Reducing Workbook and Worksheet
Frustration, contains a group of hacks that shows how "to manage how users
interact with worksheets". The other chapter headings are: Hacking Excel's
Built-in Features (overcoming the limitations); Naming Hacks (how to reference
information by name and create names that adapt to data); Hacking Pivot Tables
(getting the most out of pivot tables); Charting Hacks (customised charting);
Hacking Formulas and Functions; Macro Hacks (using macros to extend features);
and Connecting Excel to the World (take advantage of Web sites and services).
A great source of information about how to make Excel work the way you want it
to work: things like getting subtotals to print in boldface, creating ranges
that expand and contract, and how to load an XML document into Excel. There are
great examples of getting charts to do things that Excel never intended. The
book also shows how to structure data so that Excel will process it more easily.
It is not just about getting more out of Excel - it shows how to get more out of
your data. Both Excel 2003 and OOo's Calc use XML as the default storage format;
the difference is that OOo/StarOffice does it so much better. The XML connection
opens up a new field of opportunities, which Excel Hacks explores. An essential
resource for every serious Excel and Calc user. The authors have done a
remarkable job of finding and bringing together this compendium of solutions.
Exceptional value.
|
David & Raina Hawley: Excel Hacks ISBN 0-596-00625-X Published by O'Reilly, 283 pp., RRP $44.95 incl. GST |
 |
Excel 2003 Programming
This is a title in the Developer's Notebook series, recently introduced by
O'Reilly. Books in the series are modelled on the concept of laboratory work -
as distinct from lectures -where it is all about doing rather than
talking-about-doing: the focus is on application rather than explanation. The
typographic design of the books is in keeping with that theme and they are well
worth looking at as yet another example of publishing innovation.
Excel 2003 Programming: A Developer's Notebook has been written for developers.
It is not about learning to program (as is Writing Excel Macros), but is
designed for those who know how to program Excel. It also makes a strong
argument for learning XML: "There is still a great deal that can be done with
VBA and non-XML approaches to sharing data in Excel, but XML and .NET are the
next wave. Ignore it at your peril."
So, why would a programmer or developer need a book like this? It is to catch up
with the use of XML, which enables a workbook or worksheet to be part of "a
giant stream of XML data". Workspaces and lists can be used to share workbooks
and ranges of cells (including sort and filter ranges of cells). XML enables the
transformation of spreadsheets into HTML and other data formats, and transform
XML data into workbooks. Excel 2003 can be used to create Excel .NET
applications. InfoPath enables the creation of XML data entry forms, linking
forms to databases or web services, and validation of data entries. Developers
who want to keep everything within the Microsoft system, or who are required to
do so, should find the Developer's Notebook invaluable.
This text is a source of specific solutions that are illustrated by real-world
example code supported by screen shots, and accompanied by useful marginal
notes. Each chapter deals with a topic (for example, Share Workspaces and
Lists); it opens with a statement of the issues and then addresses specific
requirements (for example, "create a shared work-space") using a common format:
How to do it, How it works, What about ... (the last being a listing of resources,
including where to find the relevant software).
Some chapters include another 'category', Common Questions. For example, in the
chapter, Explore Security in Depth, there are questions such as, "How do you get
rid of the macro security warning?" (which is answered in step-by-step
instructions with a reference to the section in which 'add digital signatures'
is dealt with.
The format is not rigid; in some 'labs' there is additional explanatory
material.
The presentation of this title has been exceptionally well executed; information
is well laid out, is concise without being too terse, and comes straight to the
point. There is a minimum of discussion - the focus is on how things are done -
and plenty of code. The change to XML has taken Excel to a new, Web-oriented
level, and the author shows how developers can exploit that connection. I look
forward to a similar title covering OpenOffice.
|
Jeff Webb: Excel 2003 Programming: A Developer's Notebook ISBN 0-596-00767-1 Published by O'Reilly, 294 pp., RRP $55.00 incl. GST |
 |
Excel Personal Trainer
This is a most impressive self-teaching resource; it comes with a companion CD
that is the most practical multimedia training aid that I have come across. It
really works. If you want to learn Excel, or improve your skills in fundamental
operations, this is a great place to start.
The book-CD package is a well conceived and cleverly executed interactive
teaching program with visual and audio features. It is suitable for Excel
beginners, assuming no more than a knowledge of how to turn on a computer, and
use keyboard and mouse. It is also aimed at Excel users who want to improve
existing skills and develop new ones. The course is not intended to turn out
gold-medal champions, but - to use the series' gymnasium theme - delivers a
level of fitness that will equip the trainee to move on to more rigorous levels
of competition.
For most users the program will provide all the skill levels
they will need. There are 142 lessons grouped under topics: Fundamentals (begins
with understanding the Excel screen, using menus/toolbars, difference between
'labels' and 'values', opening and closing a workbook, and other basic skills);
Editing a Worksheet (the fundamental processes of cut/copy/paste cells,
edit/clear/replace cells, insert/delete rows/columns, comments, smart tags, file
management); Formatting a Worksheet (adjusting cell/row/column dimensions, cell
alignment, fonts, styles, and other formatting procedures); Creating and Working
with Charts; Managing Your Workbooks; Functions and Formulas; Working with
Lists; Automating Tasks with Macros; Working with Other Programs (insert
worksheet into a Word document, insert graphics, open/save files in different
formats); Using Excel on the Internet; Data Analysis and PivotTables; What-If
Analysis; and Advanced Topics.
The CD contains all the lesson data in .xls files, which provides hands-on
exercises using a simulated version of Excel 2003. The book is geared around
Excel 2003 running on Windows XP, but the simulation will run on Windows 98. The
step-by-step instructions are written in particularly clear language and are
supported by annotated screen shots that make the tutorials all the easier to
follow. At the end of each lesson there is a 'quick reference' box that
succinctly repeats how to do things required in the particular session, and at
the end of each group of lessons there is a review of what has been covered; it
is written in a terse style that is useful for making up one's own cheat sheets.
There are also questions (with answers) for testing one's knowledge and
comprehension.
An excellent training resource that enables users to be flexible in how they put
it to work. Don't be put off by the comic-book appearance of the cover; this is
a real tool and its tutorials can be applied to most modern spreadsheet
programs, such as OpenOffice Calc.
|
CustomGuide, Inc.: Excel 2003 Personal Trainer ISBN 0-596-00853-8 Published by O'Reilly, 464 pp. + CD, RRP $49.95 incl. GST |
 |
Reprinted from the June 2005 issue of PC Update, the magazine of Melbourne PC User Group, Australia
