Phineas's Excel Page

This is another website dedicated to Excel.  It primarily focuses on Excel 12 development, this is the most recent version of Excel I have, there may be compelling reason to upgrade to Excel 14 but I have not found what it is. The code is compiled and linked with Visual Studio 2010 using the Excel 2007 SDK. There is not, yet, anything on VBA but this may change in the future.

Projects

This is a list of projects I am working on.  Typically the projects include the source and xll in a zip file.  They should be considered works in progress you are welcome to use the compiled code however you do so at your own risk.  They are not intended to be functioning software but guides on how implement the functions and should used in conjunction with the source.

Multithreading in Excel

This is a set of functions used to test the various forms multithreading in Excel.  It includes the source and a compiled xll.

XLL Utilities

This a set of Excel utility functions, there are functions that measure the time taken to execute a function and the memory Excel is using.

Timing Excel Functions

This looks at some of the myths surrounding calculation time in Excel and gives some optimizations tips.

A COM Interface for Excel

This is a couple of projects that build an xll add-in, a COM server and COM client that can call COM servers from spreadsheets without having to go via VBA

Calling .NET Functions From Excel

This outlines a method for calling functions written in any .NET language from Excel

Why Are My Excel Files So Large?

This is a quick look at the size and structure of Excel files, what can be done to stop then increasing ad infinitum and how to reduce their size once they have grown.

Improving Excel's Calculation Performance by Inspecting calcTree.xml

This looks at methods of improving Excel's calculation perfromance, by looking at calcTree.xml which contains information on the calculation train and the dependency tree.

Solution to the Countdown Maths Problem

This is a quick bit of fun, it is a spreadsheet that solves the maths problem on Channel Four's Countdown program.




Tips for Learning Excel

You are never going to know Excel, you only need to know the things you use.  Take a real problem try various solutions, if they don't work then there are enough resources on the web to help.  Learning VBA is different as it is a programming language thus you need to acquire the basic skills of a procedural programming language, looping and branching.  These are difficult to learn by trial and error, my entry point was a standard teach yourself VBA in 24 hours book, if you can avoid any Access VBA as this will obscure the programming language.

I have only ever found two books useful:
Microsoft Excel 97 Developers Kit This book documents the Excel 97 SDK which has been superceded Excel 2007 SDK which in turn has been replaced by subsequent SDKs.   Most of the book covers the now redundant BIFF file format, but the stuff on general Excel and Add-in development is useful. It is from this book I picked up the unusual triple quote (''') commenting style.  The book is now out of print but most of the content is on MSDN.
VB Controls In A Nutshell.  An good GUI can hide a multitude of sins.


Useful Links

How to Ask Questions the Smart Way This is a guide on how to ask questions on news groups etc.

Don't Call Yourself A Programmer, And Other Career Advice This tells it like it is in the world of professional software development, the reactions of  computer science students is as interesting as the blog itself.  I have been programming for around 20 years and count the numbers of times I've used a dictionary on fingers of one hand, even then I've used the one that is packaged with IDE.  If I thought I could write better data structures than the Microsoft FCL I doubt the programmer charged with maintaining the code I had written would agree.

Excel Experts A good site with Excel and VBA tips and training with an active community

Microsoft's Excel 12 Documentation A surprisingly good Excel 12 reference, including a link to the Excel 2007 SDK


Phineas Campbell
My public LinkedIn profile
Last updated 25 July 2013