Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
I'm working with a team reverse engineering a rather complex workbook.
The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
show formulas in the workbook
tool Menu -Options - Vew. Check formulas "davegb" wrote: I'm working with a team reverse engineering a rather complex workbook. The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
On May 7, 11:38 am, Joel wrote:
show formulas in the workbook tool Menu -Options - Vew. Check formulas Thanks, Joel. I should have mentioned that I'm using that too. Am looking for something more sophisticated that will help me map out the spreadsheet's logic/path. "davegb" wrote: I'm working with a team reverse engineering a rather complex workbook. The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks!- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
Maybe the "Formula Map" code in this post will help... http://snipurl.com/1joun -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "davegb" wrote in message I'm working with a team reverse engineering a rather complex workbook. The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
On May 7, 11:50 am, Tom Hutchins
wrote: Here is a macro I wrote years ago, which will find all dependents of a selected cell and list them on a new sheet which is added to the workbook. Sub FindDependents() 'Declare local variables Dim xx As Long, xxErr As Boolean, SelCell As Range Dim StartWS As Worksheet, c As Range, HitCount As Long, NuSht As Worksheet On Error GoTo FDerr1 'Turn off screen updating and change cursor to hourglass Application.ScreenUpdating = False Application.Cursor = xlWait 'Store the starting activesheet and activecell. Set StartWS = ActiveSheet Set SelCell = ActiveCell 'Add a new worksheet to the current workbook at the end. Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Set NuSht = ActiveSheet 'Use HitCount& to set the output row. Will add 1 each time, so set to 3 initially 'to begin output on row 4. HitCount& = 3 'Set xxErr to FALSE, and return to the starting sheet & cell. xxErr = False StartWS.Activate SelCell.Activate 'DirectDependents only gives dependents on same sheet as SelCell. For Each c In SelCell.DirectDependents If xxErr = False Then HitCount& = HitCount& + 1 NuSht.Cells(HitCount&, 1).Value = "'" & c.Parent.Name NuSht.Cells(HitCount&, 2).Value = "'" & c.Address NuSht.Cells(HitCount&, 3).Value = "'" & c.Formula End If Next c 'Activate SelCell again, then use ShowDependents method to draw arrows. SelCell.Activate ActiveCell.ShowDependents xx& = 1 'Set xxErr to FALSE again. If there were no DirectDependents, error handling set xxErr to TRUE. xxErr = False 'Built-in arbitrary limit of 100,000 (references in this one workbook) to this one 'cell! Did it this way because Excel doesn't provide a programmatic way to determine 'the total number of references. :( Do While (xx& < 100000) 'Always start by returning to StartWS and SelCell. StartWS.Activate SelCell.Activate 'Go to each external dependent reference in turn ActiveCell.NavigateArrow False, 1, xx& 'If ActiveCell.NavigateArrow fails (xx& higher than number of links), xxErr will get set 'to True and will break out of Do While loop. If xxErr = True Then Exit Do 'If activecell is the starting cell, there are no dependents, so stop. If (ActiveSheet.Name = StartWS.Name) And (ActiveCell.Address = SelCell.Address) Then Exit Do HitCount& = HitCount& + 1 NuSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name NuSht.Cells(HitCount&, 2).Value = "'" & ActiveCell.Address NuSht.Cells(HitCount&, 3).Value = "'" & ActiveCell.Formula xx& = xx& + 1 If xx& = 100000 Then MsgBox "Hit limit of 100,000 dependent references", vbInformation, "FindDependents macro" End If Loop 'Done. Clean up. Add headings for the output rows and resize all columns on NuSht. NuSht.Cells(3, 1).Value = "Sheet" NuSht.Cells(3, 2).Value = "Cell" NuSht.Cells(3, 3).Value = "Formula" NuSht.Cells.Select NuSht.Cells.EntireColumn.AutoFit Calculate 'Add a heading on row 1 NuSht.Cells(1, 1).Value = "Dependent cells for: Sheet [" & StartWS.Name & "], Cell [" & SelCell.AddressLocal & "]" 'Go back to the starting sheet and remove the arrows, then go to the new sheet. StartWS.Activate ActiveSheet.ClearArrows NuSht.Activate 'Turn on screen updating and restore the cursor to default Application.ScreenUpdating = True Application.Cursor = xlDefault 'Free object variables. Set NuSht = Nothing Set StartWS = Nothing Set SelCell = Nothing Set c = Nothing FDerr1: xxErr = True Resume Next End Sub When you copy & paste the code above into a VBA module, you will get errors because some of the lines wrapped in the forum because of their length. Afger you "unwrap" those lines the macro should run. Hope it helps, Hutch "davegb" wrote: I'm working with a team reverse engineering a rather complex workbook. The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks!- Hide quoted text - - Show quoted text - Thanks, Tom, worked fine! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse Engineer complex spreadsheet
On May 7, 12:11 pm, "Jim Cone" wrote:
Maybe the "Formula Map" code in this post will help...http://snipurl.com/1joun -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware "davegb" wrote in message I'm working with a team reverse engineering a rather complex workbook. The author is helping, but is available on a very limited basis. I've mostly been using the Audit features to trace precedents and dependents and manually drawing diagrams showing these relationships, which is cumbersome and slow. Does anyone have any good suggestions beyond what I'm already doing on how to expedite this process? Any tracing macros that will ID all downstream dependents cells of a given cell? Any clever techniques that would make this easier? I feel like I'm sawing down a redwood with a Swiss Army knife! :) Thanks! Thanks, Jim, this was a big help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reverse Engineer Pivot Table | Excel Programming | |||
How can i reverse saving an excel spreadsheet. | Excel Discussion (Misc queries) | |||
How Do I reverse axis in a spreadsheet | Excel Worksheet Functions | |||
How do I reverse a spreadsheet that is in the format of a subtota. | Excel Discussion (Misc queries) | |||
Engineer calculations | Excel Programming |