Hacker News Reader

Comment by tambourine_man | Hacker News Reader

> The compiled Excel file can be executed by triggering iterative calculation (pressing F9 repeatedly)

I know very little Excel, but can’t you create a loop with it?

Not without dropping into VBA, at least last I looked. The cell dependency graph should form a DAG. If there is a cycle, it will break (Excel will detect this). A common way to produce a "loop" is to create a row for each iteration depending on the one above (which can actually be handy to see convergence and intermediate values). This means there's no way to do something like:

  // initial
  A1 = 0
  A2 = 100 (the limit)
  A3 = =A3+A1 (self referential, 0 initial)
  // somehow
  A1 = 1
  A2 = 100
  A3 = =A3+A1 == 1

If you could refer to previous values it might be possible, but would also force some synchronization into the process that might be problematic. Like you'd need A1 to only update after A3 has had a chance to read it, then the next, then the next.

Excel supports iterative calculations, which allows you to use circular references. If you enable it, you need to specify a maximum number of iterations excel will run on each update. I believe you can also specify a minimum delta which will cause Excel to terminate early if subsequent iterations differ by less than the delta. I suspect the reason you need to press f9 repeatedly is this project uses circular references, but running your program takes longer than the maximum number of iterations, so pushing f9 allows it to run for another set of iterations.

That makes sense, I've never made use of that. Apparently that was available as early as Excel 2007, I've just never come across it and haven't used Excel for much more than a scratchpad in years now.

Goal seek also covers various optimization / iteration scenarios.

Or solver.

Options exist for iterations and maximum change directly with circular references.

https://bettersolutions.com/excel/formulas/circular-referenc...

Adding in indirect/offset/lookups/indexes etc - I've gotten surprisingly far.

I'm also OK doing VBA BUT the MAJOR downside of VBA - horrendous to share it and got worse over time for security reasons.

Excel without VBA - you can give the "program" to anyone, and almost anyone can use it (and in a pinch change it).

This sounds like we're going to learn about macro and VB viruses all over again.

Using newer Excel you also have the power of making custom named functions with LAMBDA() which can also recurse (I think the stack limit is 1024). If you combine this with the idea of columns or rows as iterations of the memory with some of the array functions like MAKEARRAY() and relative position functions like OFFSET() or INDIRECT() you can do pretty much any reasonably sized computation without needing iterative calculation enabled.

The reason being Excel is a tool designed for performing operations on tabular data not designed for general data programming. If you needed programming there was always VBA which was proper so they never wanted to muck it up. LAMBDA made its way in because people wanted reusable function compositions for their tabular data without having to enable the security PITA that is allowing full macros/VBA which can do a lot more than interact with just the data inside the spreadsheet.

Many people stop at VBA, but you can grow beyond it into full VB via .NET Office automation and AddIns, or if feeling miserable, COM in C++.

Additionally if only data transformations are required, there is the F# inspired Power Query.

vba is fine but it seems to exist halfway outside the calculation chain space that sheets and cells operate in. excel can work with it, but one has to be quite careful with function returns and parameters.

offsets and indirects also exist in that twilight zone being volatile functions.

these three combined can bring down a reasonably sized spreadsheet.

Sort of. It is possible to make a loop of mutually dependent formulas. By default this will politely assume you made a mistake and throw a warning message without calculating anything. However, if you know where to poke in the settings, you can enable recursive formulas and use that to rig loops. Ignore the sibling comments answer, VBA is not required.

Excel supports arbitrary recursive functions without macros as of this year. See the new LAMBDA feature with Excel.

This exactly what https://xllmonte.com does.

You would accomplish loops in Excel as you would in any array languages (apply operations on columns and tables of data). You can also do recursion in Excel. We used to solve boundary value problems in my computational physics class using Excel.

Reply on news.ycombinator.com