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
|