User Defined Spreadsheet Functions in Excel Dermot Balson

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

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 2

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

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

User Defined Spreadsheet Functions Euspirg 2012, Manchester 5

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 6

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 7

Computation speed seconds 150 100 50 0 0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 Number of calls Adding 200 calls Recomputing all calls Euspirg 2012, Manchester 8

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 Eu. SPRIG keep a repository Euspirg 2012, Manchester 9

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

Thank you!