Solved

finding DirectPrecedents

Posted on 2001-09-01
18
512 Views
Last Modified: 2008-09-26
how can i find all DirectPrecedents of a cell and all of the DirectPrecedents of that cell's DirectPrecedents

what i need is to collect all DirectPrecedents of a formula in excel workbook
and all of the DirectPrecedents of that cell's DirectPrecedents ( no matter how big and complex workbook is and how many sheets ) in vb runtime and add all of it to a collection or array

i am writing a VB app. that uses formulas from existing excel WB. for that if i use an excel formula i need to get ALL of its DirectPrecedents and  all of the DirectPrecedents of that formula's DirectPrecedents
i am using excel XP

thanks
0
Comment
Question by:kloppa
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6448581
Manually, use Tools|Auditing

For VBA/Excel, please ask.
0
 
LVL 1

Expert Comment

by:garethd
ID: 6448931
Kloppa,

Here are 3 VBA procedures to:
a) show all dependents
b) show all precedents
c) remove the arrows

You just need to add three buttons called "Precedents", "Dependents" and "RemoveArrows" and paste the following procedures. You may want to make the error handling more elaborate, but I am sure you get the idea.

Good Luck

garethd


Private Sub Precedents_Click()
On Error Resume Next  ' prevent error when no precedents

Dim r As Range

ActiveCell.ShowPrecedents     ' draw arrrow for first precedent

For Each r In ActiveCell.precedents      ' draw arrrow for secondary precedents
    r.ShowPrecedents
Next r

End Sub

Private Sub Dependents_Click()
On Error Resume Next  ' prevent error when no dependents

Dim r As Range

ActiveCell.ShowDependents  ' draw arrrow for first dependent

For Each r In ActiveCell.dependents   ' draw arrrow for secondary dependents
    r.ShowDependents
Next r

End Sub

Private Sub RemoveArrows_Click()
ActiveSheet.ClearArrows

End Sub
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6448953
what about a really complex sheet with precedents into 3rd or 4th degree? i'm wondering about that since this q popped up....

:O)Bruintje
0
 
LVL 13

Expert Comment

by:cri
ID: 6449046
gareth, why programming buttons if you can fetch a whole toolbar in View|Toolbars|Customize|Toolbars: Auditing ? And these buttons do increment...

Additionally, ShowDependents does not work for Excel 97, for compatibility use this workaround:
Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"

kloppa, if you state what you are trying to accomplish, perhaps a better solution can be found. I happen to have a macro for cell _ranges_ (plus one to switch the beeping off...)
0
 
LVL 13

Expert Comment

by:cri
ID: 6449835
kloppa, no need to increase the points, feedback would be sufficient.
0
 

Author Comment

by:kloppa
ID: 6449882
cri, i edited my question as soon as i got your response
may be it's a better idea to post comments?
0
 
LVL 13

Expert Comment

by:cri
ID: 6450088
Yep, I did not notice it. Editing the question is for typos/grammar only, otherwise it makes the thread very difficult to follow and the experts look like they can not read.
0
 

Author Comment

by:kloppa
ID: 6450857
sorry, didn,t know
0
 

Author Comment

by:kloppa
ID: 6450936
sorry, didn,t know
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:garethd
ID: 6451631
Kloppa,

The precedents and dependents are already part of a collection as you can see from the the code I posted earlier:

Dim r As Range

For Each r In ActiveCell.precedents      
  r.ShowPrecedents
Next r

This returns a collection of range values and will pick up all levels (even on other workbooks)

to get the cell locations you can use:

r.address , r.column, r.row

Hope this helps

Garethd

 

0
 

Author Comment

by:kloppa
ID: 6452168
Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa
0
 

Author Comment

by:kloppa
ID: 6452240
Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa
0
 

Author Comment

by:kloppa
ID: 6452281
Garethd,

i can make your code to get all of the precedents (on every level) of a cell but only on the same (active) worksheet.
i dont know how to get precedents from other worksheets
any suggestions?

thanks for your help

kloppa
0
 

Author Comment

by:kloppa
ID: 6452378
dont know why some of my comments appear more then once

kloppa
0
 
LVL 1

Expert Comment

by:garethd
ID: 6453638
Sorry Kloppa,

I was inaccurate when I stated that this technique would show precedents on other sheets. It shows a graohical link as I am sure you observed but the address is not returned as part of the collection.

Here is my suggestion (though I don't have time to code it all or test it , I am sure you get the idea):

1. You need an extra loop to control change in worksheet
2. At end of the inside loop (the one I already gave) you check to see if the last formula contained a reference to another sheet. If it does then you activate that sheet and perform the inner loop again.

I can spend more time on the coding later if you need it
Let me know !

Good Luck

GarethD


e.g.



Do       ' Outer Loop

Activecell.ShowPrecedents

For Each r In ActiveCell.Precedents

nextprecedent = r.address    
CellFormula = r.FormulaLocal

next r


If InStr(1, CellFormula, "!") Then
    ' code here to parse out the new sheet name AND new cell address (range) using
    ' CellFormula string and the functions Instr() and mid()  AND THEN
    ' Activate the new sheet and cell like this
 
 Worksheets(NewSheetName).Activate
 ActiveSheet.Range(NewCellAddress).Activate
FoundNewSheet = True
 
End If

loop while FoundNewSheet = true    ' outer Loop


0
 

Author Comment

by:kloppa
ID: 6454421
GarethD,

i think i need your help ( if you have some time )

thanks so much

kloppa
0
 
LVL 1

Accepted Solution

by:
garethd earned 250 total points
ID: 6454871
OK Kloppa,

Here is a complete routine (no arrow setting as I Now do not believe you need this)

This will build an array of all precedents and includes the sheetname as well as the cell location.

Hope this is what you need

Good luck

GarethD





Dim r As Range

Dim Arr_Precedent() As String           ' Dynamic Array to hold the Cell Addresses
Dim Arr_Count As Integer                ' Count Precedents (includes original cell)
Dim EndSheetName As Integer             ' MArk end of sheet in formula

Dim SaveSheet As String
Dim SaveCell As String


SaveSheet = ActiveSheet.Name
SaveCell = ActiveCell.Address


Arr_Count = 0
ReDim Arr_Precedent(1 To 1)


Do ' Outer Loop

        Arr_Count = Arr_Count + 1
        Arr_Precedent(Arr_Count) = ActiveSheet.Name & "!" & ActiveCell.Address
             
        For Each r In ActiveCell.precedents
                ' Check size of array
                Arr_Count = Arr_Count + 1
                If UBound(Arr_Precedent) < Arr_Count + 1 Then
                    ReDim Preserve Arr_Precedent(1 To Arr_Count + 3)
                End If
               
                Arr_Precedent(Arr_Count) = ActiveSheet.Name & "!" & r.Address
               
                CellFormula = r.FormulaLocal
       
        Next r
       
       
        EndSheetName = InStr(1, CellFormula, "!") 'where is the exclamation point ?
       
        If (EndSheetName) = 0 Then     '   no exclamation point so end loop
       
            FoundNewSheet = False
       
        Else
           
            ' For simplicity sake, I assume that any formula that refers to another sheet
            ' will have the reference to that sheet as the first part of the formula
           
            ' The Instr() above will have returned the position of the '!' in EndSheetName
           
           
            NewSheetName = Mid(CellFormula, 2, EndSheetName - 2)
           
            'The Cell could be either of the following forms bot must be followed
            ' ny a non numeric operator such as  *  / +  etc.
            ' An  or Ann  (eg B21 or B1)
           
            If IsNumeric(Mid(CellFormula, EndSheetName + 3, 1)) Then
                NewCellAddress = Mid(CellFormula, EndSheetName + 1, 3)
            Else
                NewCellAddress = Mid(CellFormula, EndSheetName + 1, 2)
            End If
            Worksheets(NewSheetName).Activate
            ActiveSheet.Range(NewCellAddress).Activate
           
            FoundNewSheet = True
       
        End If
Loop While FoundNewSheet = True

' Reset the Active Sheet and Cell

Worksheets(SaveSheet).Activate
ActiveSheet.Range(SaveCell).Activate
0
 

Author Comment

by:kloppa
ID: 6458895
thanks everyone.

GarethD,

i appreciate your help

kloppa
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now