Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot)

Re: Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful?

#11

Post by damian_r » Wed, Jul 29, 2015, 2:35 PM UTC

jasode wrote: post #8

>What are the advantages? You could save your formulas to a separate file and import them through the add-in. This lets you easily share your formulas with others and add version control and code review to your project. Is this plugin meant for programmers or non-programmers? The distinction is critical because... >Inspired by two discussions [...] You have to be careful with criticisms about Excel made by programmer…

Thanks for this perspective.

As a programmer I haven't actually thought much about how important this link between a cell in a grid and its formula may be.

I have seen huge (i mean HUGE) Excel files full of complex formulas being built by non-programmers. Some even took whole night to recalculate. There was a programmers mindset emerging from those files - extracting common formulas to separate cells (DRY), writing 'unit' tests by using formatting (if result for sample input is less than 0 then make the cell red - which means someone broke the formula).

The question arises - is there a point where a non-programmer starts thinking like a programmer, on a different level of abstraction? Is it possible to teach non-programmers some basic programming practices to make their work easier?

Re: Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful?

#12

Post by damian_r » Wed, Jul 29, 2015, 2:46 PM UTC

LarryMade2 wrote: post #6

I like the concept, when debugging some complex sheets that could come in handy - though having all the formulas in a windows might hinder understanding the layout of the sheet, especially if you are lazy and don't label all your cells (or just know enough about excel to be dangerous). I thought it would be cool if formulas would pop into little syntax highlighted text edit areas when double clicked (or some other me…

I like the concept of simple editor for the formula.

Maybe an editor that would extract all formulas from the sheet and give you different view after double click would be a good solution. On one hand you would still have your normal spreadsheet but you can go to a different level if you want to do refactoring etc.

Re: Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful?

#13

Post by philbroberts » Wed, Jul 29, 2015, 2:53 PM UTC

Have you checked out PowerPivot? I've been developing in Excel for 15 years and created many a convoluted formula along the way. Two years ago I moved all Excel development to Powerpivot and I hardly write any formulas in "regular" Excel anymore. PowerPivot provides more of a formula IDE (along with DAX Studio), along with a ton of other functionality. I think it's clear that's the direction sophisticated Excel development is going.

Re: Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful?

#14

Post by damian_r » Wed, Jul 29, 2015, 7:54 PM UTC

philbroberts wrote: post #13

Have you checked out PowerPivot? I've been developing in Excel for 15 years and created many a convoluted formula along the way. Two years ago I moved all Excel development to Powerpivot and I hardly write any formulas in "regular" Excel anymore. PowerPivot provides more of a formula IDE (along with DAX Studio), along with a ton of other functionality. I think it's clear that's the direction sophisticated Excel devel…

I haven't seen that. I tried installing it on my Excel but it looks like kind of pro version is needed for PowerPivot. I'll try to find some more information on it. Thanks!

Re: Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful?

#15

Post by brudgers » Wed, Jul 29, 2015, 8:31 PM UTC

Excel is already programmable via everything from Powershell to VB to F# via its .NET bindings. The problem isn't that anything is missing from Excel, it's that it's used by so many people who are not 10x software engineers that no higher-level approach will ever work with 99,5% of the Excel out there.

The people who are organized enough and use Excel enough to find value in saving formulas in a library are already extreme edge cases...and then you are selling into that tiny market against the idea of using full blown tools like Visual Studio and C#.

And it seems to me that really sound previously snakebit engineers aren't going to go around changing working software that isn't backed up by a suite of regression tests. The problem isn't Excel, it's bad code. The cause of the bad code is bad programming practice, and the reason for that is that the people programming are not programmers and even if they were they're under just give me a kludge time pressure.

Spreadsheets are successful because they're really easy to use. Excel dominates because making one that's userful for important work is really really hard.

Good luck.