Function breaker tool

[Leung]

Hi

Anyone has done similar things before?

My situation is that, there are many unkonwn function and parameters
involved, e.g. =FUNC1(Par1,Par2,Par3,Par4, FUNC2(ParA,ParB),Par5)

Par1 could be a range name / another function, like 2,
I want to create a tool that you point to this active cell. then a list of
paramter will be listed on cells on right one by one, also another list can
be list with a "=" before so i can visualize which parameter is good and
which cannot compute.

Is anyone has a similar tools? it would be good if it could drill down to
function within function.

if no, i will build it from scratch.

Leung
from HK

 

[Bernie Deitrick]

Leung,

The macro below will give you something to go on.

HTH,
Bernie
MS Excel MVP


Sub BreakDown()
Dim myList As Variant
Dim myC As Range
Dim i As Integer
Dim myCount As Integer
Dim myAdd As String

Set myC = ActiveCell
myCount = 1
myList = Split(Replace(Replace(Replace(myC.Formula, _
"(", ","), "=", ","), ")", ","), ",")
For i = LBound(myList) To UBound(myList)
On Error GoTo NotRange
myAdd = Range(myList(i)).Address
myC.Cells(1, myCount + 1).Value = myList(i)
If Range(myList(i)).Cells.Count = 1 Then
myC.Cells(1, myCount + 2).Formula = "=" & myList(i)
Else
myC.Cells(1, myCount + 2).Value = "Multi-cell!"
End If
myCount = myCount + 2
GoTo OK
NotRange:
Resume OK
OK:
Next i
End Sub

 

[Leung]

Thanks Bernie

It works very well.

Can I add also a parameter, e.g. 1= show formula, 2= show value?

usually i will put first row as formula
2nd row as value so i can compare.