Han Oostdijk Quantitative Consultancy

Excel

Of all Microsoft Office products I like Excel with or without Visual Basic for Applications (VBA) most of all. Even when doing the analyses with a different tool, I find it often convenient to gather (partial) results in a workbook and quickly present them with a pivot table or Excel graph.

Because almost every company works with Excel, this analysis tool is readily available. And because of its well known interface it is easy to integrate the results of an analysis with existing reports. The more so because other applications often have Excel format as an output option.
For the analyst it is convenient that inputs, formulas and output can be merged in one Excel sheet. Of course while this is just great for an small application it makes it difficult to support larger applications because the sometimes complex business logic may be obscured by lengthy cell formulas.
For these applications it is worthwhile to apply VBA that enables (but not necessarily leads to) a more accessible description of the business logic. VBA offers possibilities for structured programs with constructions such as if-then-else and do-while. With VBA one has smooth access to Excel objects (such as ranges, workbooks, worksheets, charts, user forms, ...) so that complete applications are easy to build.
Because Excel is very much used, there is an enormous choice in Excel books. Also on the internet a lot of information can be found. Here we give a few links :

	'The Spreadsheet Page' by John Walkenbach, the author of a great number of Excel books. His site offers tips on the use of Excel and references. His search page uses Google to query some of his favourite sources. In his own words: "In my experience, at least 80% of all Excel questions can be answered by searching the Web". Of course this is true if you have a question about a detail. However an Excel book can very worthwhile if it offers a good overview of the possibilities of Excel and shows techniques to use or to avoid in your advantage.
	'Excel Tips from MrExcel'. In my opinion the nicest part of this site is the Excel Forum (Message Board). Easily searchable you will find here thousands of questions about Excel with their answers. And if your question is not among them, just post it on the Message Board. As well suited for a beginner (who wants to know how to handle a specific problem) as for an experienced person (that is not satisfied with a solution found: 'something more efficient/easier should be available'). Apart from this the site contains a large number of articles and references to articles by John Walkenbach (previous link) and Chip Pearson (next link) .
	This site by Chip Pearson is especially interesting because of the many examples of VBA code. If you have a new task concerned with Excel it is advantageous to look here for an example to start with. And for more references (for Microsoft in particular) look at his resources page .
	A very interesting collection of links can be found on this page. They link to websites of persons that are marked by Microsoft as Most Valuable Professional (MVP). Their websites contain examples of VBA techniques and ready to run auxiliary programs.
	'Getting Started with Macros and User Defined Functions' by David McRitchie. "If it is not broken, don't change it" : if something works as good as Excel it should not be allowed to be changed. However Microsoft introduced with Excel 2007 a large number of changes with respect to earlier versions. This page describes how to work with macros and functions in Excel 2007.