Function breaker tool
 

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

Function breaker tool
 

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" wrote in message
...
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

Function breaker tool
 

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.



"Bernie Deitrick" wrote:

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" wrote in message
...
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