4 ms·

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 t

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!

jmkni 5y ago
> Why is there not a code view for an excel spreadsheet? Isn't that VBA?
- cmsonger 5y ago
  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!)
  - deleted 5y ago
    [deleted]
Hjfrf 5y ago
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.
- cmsonger 5y ago
  Cool! Next time I'm opening up excel, I'll give it a look.
  - Closi 5y ago
    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.
jasode 5y ago
>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).
- triska 5y ago
  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.
- throwawayboise 5y ago
  > what would the ideal "code view" be? SQL?
ComodoHacker 5y ago
>Why is there not a code view for an excel spreadsheet? There is, just press Ctrl+`. There's also dependency tracking, sort of debugger.
deleted 5y ago
[deleted]
analog31 5y ago
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)

jmkni 5y ago

> Why is there not a code view for an excel spreadsheet? Isn't that VBA?

cmsonger 5y ago
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!)
- deleted 5y ago
  [deleted]

Hjfrf 5y ago

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.

cmsonger 5y ago
Cool! Next time I'm opening up excel, I'll give it a look.
- Closi 5y ago
  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.

jasode 5y ago

>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).

triska 5y ago
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.
throwawayboise 5y ago
> what would the ideal "code view" be? SQL?

ComodoHacker 5y ago

>Why is there not a code view for an excel spreadsheet? There is, just press Ctrl+`. There's also dependency tracking, sort of debugger.

deleted 5y ago

[deleted]

analog31 5y ago

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)