Hacker News Reader

Comment by cmsonger | Hacker News Reader

IMO this really asks the question: Why is there not a code view for an excel spreadsheet?

I get that some of the basic operations probably create expressions that are too wordy / very "specific data" intensive. That is, if you took the first step and just did your best to create that code view it would have a lot of stuff conditional on specific things.

But it's the next step that gets interesting. Now that you've got it, in what ways can the visual UI change to have the code view create tighter expressions. Now that you've got this view, how can it become super handy for doing things that today are clunky?

IMO there's an interesting "no-code" path in there somewhere and there's also an interesting "make spreadsheet re-use more powerful." Or maybe not, what do I look like, an Excel engineer? Ha!

View on HN

>Why is there not a code view for an excel spreadsheet?

The MS Excel grid with code underneath each cell is declarative (not iterative loop) formulas so what would the ideal "code view" be?

Because of the architecture based on formulas, Excel does already have "Show Formulas" option (keyboard shortcut Ctrl+`) and "Trace Precedents" and "Trace Dependents".

For Excel iterative code like VBA Macros, it does have a typical "code view" (keyboard shortcut Alt+F11).

An ideal "code view" of Excel could be a declarative programming language that states the relations that hold between cells, for example a logic programming language such as Prolog or Datalog, using constraints to express the relations.

> what would the ideal "code view" be?

SQL?

You might want to try out Power Query (get & transform).

Step-by-step repeatable transformations where the UI records steps and writes code in the background.

Almost exactly what you're talking about, and comes out-of-the-box in the last few versions.

Cool! Next time I'm opening up excel, I'll give it a look.

Also DAX and power pivot - if you think excel doesn’t have the features to reuse data sets I think you will be pleasantly surprised! This works alongside power query to create a relational data model from everything you import.

I wrote a VBA function that returns the formula of a cell as a string, or "const" if the cell is not a formula, or "empty" if the cell is empty. (The latter is useful for finding bugs). Then I use it to display the formula in the cell immediately adjacent to where the formula actually resides. It's not a panacea, but overcomes the problem of "the code is invisible when reading a spreadsheet."

I don't remember the macro, it was more than a decade ago. Something along the lines of:

  function foo(c)
    s = c.Formula
    ' do something with s
    foo = s

Then in a cell, I could write something like:

  =foo(A32)

> Why is there not a code view for an excel spreadsheet?

Isn't that VBA?

Well, I'm not a VBA / excel expert -- but my neophyte view is that VBA is used to add code to excel, which is great -- but it's not the same as every single spread sheet is automatically creating a "this VBA is the equivalent of your spread sheet."

Again, not an expert. I'd expect I've never written one line of VBA. (Yay for me!)

>Why is there not a code view for an excel spreadsheet?

There is, just press Ctrl+`. There's also dependency tracking, sort of debugger.

Reply on news.ycombinator.com