title: VBA to make Excel formulae more readable
body: <p>I've had a glance around on the site, but advice on making Excel formulae (formulas?) more readable tends to be directed towards <em>'use named ranges'</em> or <em>'use helper columns'</em>. I'm not after techniques to make formulae easier to read, I'd like to create a macro to display the formulae in a more readable manner. <strong><em>You can skip to the end for a shortened problem description. I say this because although usually I like to explain my approach, I feel this time that my approach may be a distraction from the essence of the problem. Also, I think that the problem can be distilled down quite nicely without all the preceding background</em></strong></p>
  
  <h1>Problem background</h1>
  
  <p>I have a lot of nasty nest-within-nest complicated formulae, here's one I wrote today (see <a href="https://drive.google.com/file/d/0BxIuVxIOOZc_dGxXcHNZVXZrMDg/view?usp=sharing" rel="nofollow noreferrer" title="Sample document">Excel sample document</a>)</p>
  
  <p><a href="https://i.stack.imgur.com/Nrt4b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Nrt4b.png" alt="![Excel Code sample"></a></p>
  
  <p>or as code:</p>
  
  <pre><code>=IFERROR(IF(Latest[Weekday Num]=5,0,M13+MAX(Table3[Lunchtime],Latest[Lunchtime])-INDEX(Table4[Out],Latest[Weekday Num]))+Table3[Time remaining]-SUMIFS(Table4[Work time],Table4[Weekday Num],"&gt;"&amp;Latest[Weekday Num],Table4[Weekday Num],"&lt;5")+Latest[Clocked In]-S13,"Refresh csv")
  </code></pre>
  
  <p>As ugly as it may be, IMO it's actually quite well constructed; <code>IFS</code> and <code>SUMIFS</code> where possible to avoid nesting of formulae, all named ranges stored in tables, helper columns used where applicable. Yet under certain circumstances it returns a buggy result and the formula is <strong><em>not</em></strong> pleasant to read through. <em>(It's not the longest either, I have compiled formulae with several a4 pages, of code, although that was when I couldn't use vba to construct helper columns)</em></p>
  
  <p>I would like a macro which takes this formula and splits it into a sort of tree of branching functions, where each nested function is another twig - perhaps split the components of the formula across cells in the workbook</p>
  
  <h3>Current approach</h3>
  
  <p>I have so far attempted the following approach: Columns A&amp;B are used to split the formula into smaller parts. <strong>I define these parts as being the functions and arguments of Excel formulae</strong>, so the formula <code>IF(A1=1,A2,B1)</code> is split into to <code>IF(</code>, <code>A1=1,</code>, <code>A2,</code> and <code>B1)</code>. I do this with the following formula in column B:</p>
  
  <pre><code>=LEFT(A2,IFERROR(MAX(1,MIN(IFERROR(FIND("(",A2),LEN(A2)+1),IFERROR(FIND(")",A2),LEN(A2)+1),FIND(",",A2))),LEN(A2)))
  </code></pre>
  
  <p>Meanwhile, column A looks at the last component found by column B, and chops it off the long formula <em>(using <code>=SUBSTITUTE(A2,B3,"",1)</code>)</em>.
  So, for an original formula in A2 (as text), B2 is the first component of it (such as<code>IF(</code> in my example) and A3 is the formula in A2 <em>minus</em> the first component in B2. I drag down to iterate.</p>
  
  <h3>The macro</h3>
  
  <p>That gives me a list of the components of the formula in each of the cells of column B. My macro then decides what <em>level</em> each component is, and indents the component by that many cells. The <em>level</em> is defined as the number of open brackets preceding a component of the formula * <em>or 'segment' in my code</em> that haven't been closed. The macro comments explain this.</p>
  
  <pre><code>Sub DispFormula()
  'takes a split-up formula and indents lines appropriately
  
  Dim CurrLev As Integer, OBrac As Integer, CBrac As Integer
  Dim Segment As Range 'each part of the split up formula
  LastRow = Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
  
  Set orange = Range("B2:B" &amp; LastRow) 'all the segments make an orange
  
  CurrLev = 0 'the "level" of the "segment" - it's level is a measure of how many layers deep the formula is nested
              'if(a=1,b,c) is split into 4 components: `if(`, `a=1,`, `b,` &amp; `c)` where `if(` is level 0 and all the other segments are level 1
  
  OBrac = 0 'how many open brackets have happened/ precede a segment of the formula
  CBrac = 0 'how many closed brackets have happened
  On Error Resume Next
  For Each Segment In orange
      If InStr(Segment, "(") &lt;&gt; 0 Then
          OBrac = OBrac + 1
      ElseIf InStr(Segment, ")") &lt;&gt; 0 Then
          CBrac = CBrac + 1
      End If
      Cells(Segment.Row, CurrLev + 3) = Segment 'copies the segment value into a column indented by a number of cells equal to the order of the segment
      CurrLev = OBrac - CBrac 'current level is how many brackets have been opened - how many have been closed,
                              'ie. the number of brackets preceding a segment which are currently open
  Next Segment
  
  
  End Sub
  </code></pre>
  
  <h3>How it could be improved</h3>
  
  <p>That's how far I've got so far. What I really want though is for the tree of indentations to be replaced with a tree of dropdown lists. For a formula <code>=IF(MAX(arg1,arg2)=1,arg3,MIN(arg1,arg2))</code> I would like to split into segments: <code>IF(</code>, <code>MAX(</code>, <code>ARG1,</code>, <code>ARG2)</code>, <code>=1,</code>, <code>ARG3,</code>, <code>MIN(</code>, <code>ARG1,</code> &amp; <code>ARG2))</code> htn display them. Not like this: (as I currently am)</p>
  
  <pre><code>IF(     
      MAX(    
          ARG1,
          ARG2)
      =1, 
      ARG3,   
      MIN(    
          ARG1,
          ARG2))
  </code></pre>
  
  <p>But like this: (or similar)</p>
  
  <pre><code>IF(◀
  </code></pre>
  
  <p>Which when you click on <code>◀</code> becomes this:</p>
  
  <pre><code>IF(▼
      MAX(◀   
      =1, 
      ARG3,   
      MIN(◀
  </code></pre>
  
  <p>And then expanding <code>Min</code> to     </p>
  
  <pre><code>IF(▼
      MAX(◀   
      =1, 
      ARG3,   
      MIN(▼
          ARG1,
          ARG2))
  </code></pre>
  
  <hr>
  
  <h1>Shortened problem description</h1>
  
  <hr>
  
  <p>To summarise:</p>
  
  <ul>
  <li>I have a formula in Excel; formulae in Excel have the  general form <code>FUNCTION(argument1, arg2, arg3...)</code></li>
  <li>Each argument may be simple (a constant, a text string, a cell reference) or complex (another formula with its own function and arguments)</li>
  <li>I would like a macro which takes an input formula, and creates some form of user interface (be it specially located cells as in my example, or some other approach) to display the function in a tree like fashion</li>
  <li>That means in the first layer of the UI, I display the function, layer 2 has the arguments of the function, if the arguments are complex they will have further sub layers</li>
  <li>To access the sub layers, each function can be expanded with a dropdown arrow or some other means to reveal its arguments (the 'layer' below it). Like a Reddit post where you can click on the [+] to get 1 layer deeper.</li>
  </ul>