Wednesday, July 9, 2014

Excel Needs a Custom Formula Sandbox

Today I found myself in need of an Excel function to search a string from right to left (the opposite of the built-in FIND function).  I wasn't sure if Excel had a function for that, so I Googled it to find that Excel does not have that function, but it can be done using a complicated formula.

Of course it can be also done with a macro, which is what I ended up doing.  Since I have a personal macro workbook with a lot of custom functions, so I'm not adverse to creating new ones whenever I need to.

A lot of people are adverse to this approach, as the StackOverflow thread I linked to above indicates.  And I can understand why, since macros come with a lot of overhead (compatibility, security, etc.).

That gave me an idea.  MS should create a "formula sandbox" area in Excel where users can create custom formulas.  For the language, I would suggest limited-functionality versions of C#.NET and VB.NET.  The limited functionality would be necessary to ensure that the formulas don't present any security risk.

The custom formula sandbox would be linked to the user, any any custom formulas that he/she uses in the spreadsheet would be saved with that spreadsheet, so that the spreadsheet works for any user.

I think a lot of Excel users would welcome something like that.

No comments:

Post a Comment