Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a complete list of Precedents and Dependents
back at the dawn of history, when computers were still steam powered, I was a
programmer ( then a designer, then an analyst, then an architect ). In those long-ago days of yore, we used programming languages to do the stuff I'm now doing with Excel. One of the tools we had to do that effectively was called a "Variable Cross Reference" - a list of every place in the program where each variable was used - it was INVALUABLE in debugging. In Excel, the closest thing we have is Trace Precedents, and Trace Dependants. Does anybody out there know of a "Cell Reference" tool ? It would list all cell Precedents and Dependents at once. ( or anything like this ) thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a complete list of Precedents and Dependents
I suspect the following code will be either "too much" or "not enough" ? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) '----- Sub FindFormulasRevised() ' Finds worksheet formulas on each sheet in workbook. ' Adds a new worksheet and lists all formulas found and their cell addresses, values and precedents. ' Formulas that have error values in the formula will also appear on the list. ' Formulas that contain references to other sheets are marked with a "!" in column A. ' Calls MaxShtNum function. ' August 29, 2004 - Created by James Cone - San Francisco, USA ' September 06, 2004 - Added dependents. ' October 08, 2004 - Added blnFound variable and MsgBox. ' December 09, 2004 - Added check for merged cells. On Error GoTo ErrFindingFormulas Dim objNewSht As Excel.Worksheet Dim objAllShts As Excel.Sheets Dim FormulaRange As Excel.Range Dim FormulaCell As Excel.Range Dim objGeneric As Excel.Range Dim objCell As Excel.Range Dim objSht As Object Dim lngD As Long Dim lngP As Long Dim lngR As Long Dim lngC As Long Dim blnFound As Boolean Const strMark As String = "!" Application.ScreenUpdating = False ' Sheet selection is an event so... Application.EnableEvents = False Application.Calculation = xlCalculationManual lngC = MaxShtNum Set objAllShts = ActiveWindow.SelectedSheets Set objNewSht = Worksheets.Add(Befo=Sheets(1), Count:=1) On Error Resume Next objNewSht.Name = "Formula List " & lngC On Error GoTo ErrFindingFormulas lngR = 4 ' Find all formulas on each worksheet. For Each objSht In objAllShts If objSht.ProtectContents Then Application.DisplayAlerts = False objNewSht.Delete Application.ScreenUpdating = True Application.Cursor = xlDefault MsgBox objSht.Name & " sheet is protected. " & vbCr & _ "Unprotect the sheet and try again. ", vbExclamation, " Formula Map" GoTo Exit_FindFormulas End If Application.StatusBar = "MAPPING SHEET " & objSht.Name If TypeName(objSht) = "Worksheet" Then objSht.Select 'Required On Error Resume Next Set FormulaRange = objSht.Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo ErrFindingFormulas If Not FormulaRange Is Nothing Then blnFound = True objNewSht.Cells(lngR, 2).Value = objSht.Name ' Add cell address, cell formula and formula value to new sheet. For Each FormulaCell In FormulaRange ' All but one of a merged group of cells are empty. ' If cell error, Value returns an error, Formula does not. If Len(FormulaCell.Formula) Then With objNewSht.Cells(lngR, 3) .Value = FormulaCell.Address(False, False) .Offset(0, 1).Value = "'" & FormulaCell.Formula .Offset(0, 2).Value = FormulaCell.Value If InStr(1, FormulaCell.Formula, strMark, vbTextCompare) 0 Then .Offset(0, -2).Interior.ColorIndex = 40 .Offset(0, -2).Value = strMark End If End With On Error Resume Next Set objGeneric = FormulaCell.Precedents On Error GoTo ErrFindingFormulas If Not objGeneric Is Nothing Then If IsNull(objGeneric.MergeCells) Or objGeneric.MergeCells Then lngP = 1 objNewSht.Cells(lngR, 6).Value = "Merged" Else lngP = objGeneric.Count lngC = 0 ' Add precedents to new sheet. For Each objCell In objGeneric With objNewSht.Cells(lngR + lngC, 6) .Value = objCell.Address(False, False) .Offset(0, 1).Value = objCell.Value End With lngC = lngC + 1 Next 'objCell End If Set objGeneric = Nothing End If On Error Resume Next Set objGeneric = FormulaCell.Dependents On Error GoTo ErrFindingFormulas If Not objGeneric Is Nothing Then If IsNull(objGeneric.MergeCells) Or objGeneric.MergeCells Then lngD = 1 objNewSht.Cells(lngR, 6).Value = "Merged" Else lngD = objGeneric.Count lngC = 0 ' Add dependents to new sheet. For Each objCell In objGeneric With objNewSht.Cells(lngR + lngC, 8) .Value = objCell.Address(False, False) .Offset(0, 1).Value = objCell.Value End With lngC = lngC + 1 Next 'objCell End If Set objGeneric = Nothing End If ' Make sure next row starts after last precedent/dependent. lngR = lngR + WorksheetFunction.Max(lngP, lngD, 1) lngP = 0 lngD = 0 End If 'Len(FormulaCell) Next 'FormulaCell objNewSht.Cells(lngR - 1, 2).Value = objSht.Name Set FormulaRange = Nothing End If 'Not FormulaRange is Nothing End If 'TypeName Worksheet Next 'objSht If blnFound = False Then Application.DisplayAlerts = False objNewSht.Delete Application.ScreenUpdating = True Application.Cursor = xlDefault MsgBox "No formulas were found. ", vbInformation, " Formula Map" GoTo Exit_FindFormulas End If objNewSht.Activate ' Determine number of formulas found. lngC = WorksheetFunction.CountA(objNewSht.Range(Cells(4, 3), Cells(lngR - 1, 3))) ' Make it look good. With objNewSht.Range("B3:I3") .Value = Array("Sheet Name", "Cell", "Formula ", "Value", "Precedents ", _ "Value", "Dependents ", "Value") .Font.Bold = True .Interior.ColorIndex = 40 .BorderAround LineStyle:=xlContinuous, Weight:=xlThin End With With objNewSht.Range("F3:I3") .Interior.ColorIndex = 15 .BorderAround LineStyle:=xlContinuous, Weight:=xlThin End With objNewSht.Range("F3:G3").BorderAround LineStyle:=xlContinuous, Weight:=xlThin With objNewSht.Range(objNewSht.Cells(4, 1), objNewSht.Cells(lngR, 1)) .HorizontalAlignment = xlHAlignCenter With .Item(.Rows.Count + 2) .Value = " Formula Map Release 1.0 - " & Format$(Date, "mm/dd/yy") .Font.Size = 8 End With End With objNewSht.Range("E:E, G:G, I:I").HorizontalAlignment = xlLeft objNewSht.Columns("A").ColumnWidth = 3 objNewSht.Columns("B:I").AutoFit ' After AutoFit objNewSht.Range("B1").Value = lngC & " Formulas Found" For lngC = 2 To 9 With objNewSht.Columns(lngC) If .ColumnWidth 30 Then .ColumnWidth = 30 End With Next 'lngC objNewSht.Range("A4").Select ActiveWindow.FreezePanes = True Exit_FindFormulas: On Error Resume Next Set objSht = Nothing Set objCell = Nothing Set objNewSht = Nothing Set objAllShts = Nothing Set objGeneric = Nothing Set FormulaCell = Nothing Set FormulaRange = Nothing Application.StatusBar = False Application.EnableEvents = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Exit Sub ErrFindingFormulas: Beep Application.ScreenUpdating = True MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical, " List Formulas" Resume Exit_FindFormulas End Sub '=================================== ' MaxShtNum() Function ' May 05, 2001 - created by Jim Cone ' Returns a number between 0 and 100. '----------------------------------- Function MaxShtNum() As Long On Error GoTo BadSheet Dim Sht As Object Dim N As Double For Each Sht In ActiveWorkbook.Sheets N = WorksheetFunction.Max(N, Val(Right$(Sht.Name, 2))) Next 'Sht MaxShtNum = N + 1 Set Sht = Nothing Exit Function BadSheet: MaxShtNum = 0 Set Sht = Nothing End Function '----------------- "woody" wrote in message back at the dawn of history, when computers were still steam powered, I was a programmer ( then a designer, then an analyst, then an architect ). In those long-ago days of yore, we used programming languages to do the stuff I'm now doing with Excel. One of the tools we had to do that effectively was called a "Variable Cross Reference" - a list of every place in the program where each variable was used - it was INVALUABLE in debugging. In Excel, the closest thing we have is Trace Precedents, and Trace Dependants. Does anybody out there know of a "Cell Reference" tool ? It would list all cell Precedents and Dependents at once. ( or anything like this ) thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel needs a better interface to trace precedents and dependents | Excel Discussion (Misc queries) | |||
Trace Precedents and dependents for a range of cell ? | New Users to Excel | |||
trace all precedents or dependents | Excel Programming | |||
Trace Dependents and Precedents not working | Excel Worksheet Functions | |||
Trace Precedents / Dependents | Excel Programming |