I've had a glance around on the site, but advice on making Excel formulae (formulas?) more readable tends to be directed towards 'use named ranges' or 'use helper columns'. 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. 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
Problem background
I have a lot of nasty nest-within-nest complicated formulae, here's one I wrote today (see Excel sample document)
or as 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],">"&Latest[Weekday Num],Table4[Weekday Num],"<5")+Latest[Clocked In]-S13,"Refresh csv")
As ugly as it may be, IMO it's actually quite well constructed; IFS and SUMIFS 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 not pleasant to read through. (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)
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
Current approach
I have so far attempted the following approach: Columns A&B are used to split the formula into smaller parts. I define these parts as being the functions and arguments of Excel formulae, so the formula IF(A1=1,A2,B1) is split into to IF(, A1=1,, A2, and B1). I do this with the following formula in column B:
=LEFT(A2,IFERROR(MAX(1,MIN(IFERROR(FIND("(",A2),LEN(A2)+1),IFERROR(FIND(")",A2),LEN(A2)+1),FIND(",",A2))),LEN(A2)))
Meanwhile, column A looks at the last component found by column B, and chops it off the long formula (using =SUBSTITUTE(A2,B3,"",1)).
So, for an original formula in A2 (as text), B2 is the first component of it (such asIF( in my example) and A3 is the formula in A2 minus the first component in B2. I drag down to iterate.
The macro
That gives me a list of the components of the formula in each of the cells of column B. My macro then decides what level each component is, and indents the component by that many cells. The level is defined as the number of open brackets preceding a component of the formula * or 'segment' in my code that haven't been closed. The macro comments explain this.
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" & 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,` & `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, "(") <> 0 Then
OBrac = OBrac + 1
ElseIf InStr(Segment, ")") <> 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
How it could be improved
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 =IF(MAX(arg1,arg2)=1,arg3,MIN(arg1,arg2)) I would like to split into segments: IF(, MAX(, ARG1,, ARG2), =1,, ARG3,, MIN(, ARG1, & ARG2)) htn display them. Not like this: (as I currently am)
IF(
MAX(
ARG1,
ARG2)
=1,
ARG3,
MIN(
ARG1,
ARG2))
But like this: (or similar)
IF(◀
Which when you click on ◀ becomes this:
IF(▼
MAX(◀
=1,
ARG3,
MIN(◀
And then expanding Min to
IF(▼
MAX(◀
=1,
ARG3,
MIN(▼
ARG1,
ARG2))
Shortened problem description
To summarise:
- I have a formula in Excel; formulae in Excel have the general form
FUNCTION(argument1, arg2, arg3...) - Each argument may be simple (a constant, a text string, a cell reference) or complex (another formula with its own function and arguments)
- 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
- 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
- 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.
