User Defined Spreadsheet Functions in Excel

1. User Defined Spreadsheet Functions in Excel Dermot Balson Perth, Australia Jerzy Tyszkiewicz University of Warsaw, Poland

2. Why User Defined Functions? • Method to control complexity divide task into inpependent simpler pieces • Write once, use often makes sense to invest in quality • Method to deliver high quality components shape-independent integration Euspirg 2012, Manchester

3. Which one to choose? The same function in VBA Peyton Jones, Blackwell, Burnett “A user-centered approach to functions in Excel. Proceedings ICFP 2003. Euspirg 2012, Manchester

4. vs. • Less powerful  • Safe • Same paradigm  • Easy start  • More powerful  • Security concerns  • New paradigm  • Difficult start  Euspirg 2012, Manchester

5. User Defined Spreadsheet Functions Euspirg 2012, Manchester

6. Yes we can! * Excel does have the capability to declare and call User Defined Spreadsheet Functions • Immediately • Out of the box ________________________________________ *From "Bob the Builder" children’s animated TV series Euspirg 2012, Manchester

7. Plan of the talk • how we do it • limitations and workarounds • auditing • side effects • speed of computation • construction of function libraries • how to popularize it Euspirg 2012, Manchester

8. Computation speed Adding 200 calls Recomputing all calls Euspirg 2012, Manchester

9. How to popularize UDSFs? • Difficult to create ... • ... but not so difficult to use • So let’s create "open source" libraries ... • ... for everyone to use (and get attracted) • And let EuSPRIG keep a repository  Euspirg 2012, Manchester

10. Summary • UDSFs are possible in Excel • Serious limitations • Some workarounds are possible • Function libraries are possible • Can you help? Euspirg 2012, Manchester

11. Thank you!