Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scoring Rank with Tie Breaker | Excel Worksheet Functions | |||
Password Breaker | Excel Programming | |||
Worksheet Security Breaker | Excel Discussion (Misc queries) | |||
Excel Sort Tie Breaker | Excel Worksheet Functions | |||
tool function | Excel Discussion (Misc queries) |