Solved

Excel Cell Precedents

Posted on 2010-08-15
11
901 Views
Last Modified: 2013-11-26
I am looking for code that shows how to get the precedent cells for a dependent cell containing a formula.  The catch is that the returned precedent cells MUST be in the order in which they appear in the dependent cell's formula.

I have seen and fiddled with plenty of code examples from all over the Internet that suggest using the NavigateArrow method.  However, in all such cases the precedent cells are returned in whatever seemingly random order Excel decides--NOT the order in which they appear in the formula.

My current workaround involves regular expression matching on the formula to parse out precedent cell references.  This gives me the precedent cells in the order I require.  However, the regex matching string is a bit unwieldy, and still misses some precedents.

I'm looking for a solution, or at least something that gets me close to it, in VBA or VB.NET.  Thanks.
0
Comment
Question by:macabacus
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:Emenizer
ID: 33442315
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33442400
Having played a fair bit with these techniques I would go the Regex route. What Regex are you using and what precedents is it missing?

@Emenizer,

That method raises the same issue that macabus wants to avoid

Cheers

Dave
0
 

Author Comment

by:macabacus
ID: 33442505
@brettdj,
Thanks for replying.  Not exactly sure which precedents it's missing.  I suppose if I knew that, I would just fix the Regex pattern.  I get feedback from end users every so often that report what is essentially a problem with the Regex pattern, but they're not in positions to elaborate.

I actually use about 5 Regex patterns in what I'm trying to achieve (one to replace defined names with their RefersTo properties, one to pluck out the cell references, another to do some other stuff, etc.), but I consider these patterns proprietary and cannot, unfortunately, post them publicly here.

I assume that someone must have figured out a foolproof pattern for extracting cell references from formulas (after replacing defined names), which is the pattern I am primarily interested in, but I haven't come across it yet.  So, any help you can provide along these lines is appreciated.
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 33442987
Its an interesting - read very difficult - task.  I have spent several  hours on this, reworking the NavigateArrows method from http://www.mrexcel.com/forum/showthread.php?t=297888  significantly in conjunction with a replace formula method to produce a  sequence list on a new sheet

The code
- searches offsheet references first
- longer references first

Would be interested in your comments

Cheers

Dave

Const StrDelim = "||-||"
Const wsWorking = "ArrayDump"
Const ZZ = "aaa|"
Sub test()
    Dim strFormula As String
    Dim strNew As String
    Dim strAddress As String
    Dim vArr
    Dim vExcelArr
    Dim vElem
    Dim X
    Dim lngCnt As Long
    Dim lngCnt2 As Long
    Dim LngCht3 As Long
    Dim ws As Worksheet

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    If ActiveCell.HasFormula Then
        strAddress = ActiveCell.Formula
        strFormula = Replace(Replace(ActiveCell.Formula, "$", vbNullString), "'", vbNullString)
        vArr = Split(OnecellsPrecedents(ActiveCell), StrDelim)

        vExcelArr = Range([a1], Cells(UBound(vArr) + 1, "B"))

        For Each vElem In vArr
            If InStr(vElem, "!") = 0 Then
                vExcelArr(lngCnt + 1, 1) = ZZ & vArr(lngCnt)
            Else
                vExcelArr(lngCnt + 1, 1) = vArr(lngCnt)
            End If
            lngCnt = lngCnt + 1
        Next

        On Error Resume Next
        Sheets(wsWorking).Delete
        On Error GoTo 0

        Set ws = Sheets.Add
        ws.Name = wsWorking

        lngCnt2 = 0

        Range([a1], Cells(UBound(vArr) + 1, "A")) = vExcelArr
        Range([a1], Cells(UBound(vArr) + 1, "A")).Sort Key1:=Range("A1"), Order1:=xlDescending, DataOption1:=xlSortNormal

        X = Range([a1], Cells(UBound(vArr) + 1, "B"))

        For lngcnt3 = 1 To UBound(X)
            X(lngcnt3, 2) = InStr(strFormula, Replace(X(lngcnt3, 1), ZZ, vbNullString))
            strFormula = Left$(strFormula, InStr(strFormula, Replace(X(lngcnt3, 1), ZZ, vbNullString)) - 1) & Application.WorksheetFunction.Rept("|", Len(Replace(X(lngcnt3, 1), ZZ, vbNullString))) & Right$(strFormula, Len(strFormula) - InStr(strFormula, Replace(X(lngcnt3, 1), ZZ, vbNullString)) - Len(Replace(X(lngcnt3, 1), ZZ, vbNullString)) + 1)
        Next
        
        Range([a1], Cells(UBound(vArr) + 1, "B")) = X
        Columns("A").Replace ZZ, vbNullString
         
        With Sheets(wsWorking)
            Sheets(wsWorking).[c1].Resize(UBound(vArr, 1) + 1) = "=RANK(RC[-1],R1C[-1]:R" & lngCnt & "C[-1])"
            .UsedRange.Sort Key1:=Range("C1"), Order1:=xlDescending, DataOption1:=xlSortNormal
            .Rows(1).Insert
            .[a1] = "'" & strAddress
            .UsedRange.Columns.AutoFit
        End With

    Else
        MsgBox "Not a formula", vbCritical
    End If

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub
Function OnecellsPrecedents(ByVal rng2) As String
'brettdj adapted the code from Bill Manville
'some versions of this are attributed to Mike Rickson
    Dim strAddress As String
    Dim strOut As String
    Dim rngReturn As Range
    Dim i As Long
    Dim lngArrow As Long
    Dim lngLink As Long
    Set rngReturn = Selection
    strAddress = rng2.Parent.Name & "!" & rng2.Address

    With rng2
        .ShowPrecedents
        Do
            lngArrow = lngArrow + 1
            .NavigateArrow True, lngArrow, 1
            If ActiveCell.Parent.Name & "!" & ActiveCell.Address = strAddress Then Exit Do
            If Err.Number <> 0 Then Exit Do
            On Error Resume Next
            lngLink = 1
            Do
                If Err.Number <> 0 Then Exit Do
                strOut = strOut & (IIf(rng2.Parent.Name <> ActiveSheet.Name, ActiveSheet.Name & "!", vbNullString) & Selection.Address(0, 0) & StrDelim)
                lngLink = lngLink + 1
                .NavigateArrow True, lngArrow, lngLink

            Loop
            On Error GoTo 0
        Loop
        ActiveCell.ShowPrecedents Remove:=True
    End With
    With rngReturn
        .Parent.Activate
        .Select
    End With
    OnecellsPrecedents = Left$(strOut, Len(strOut) - Len(StrDelim))
End Function

Open in new window

string-reference.xls
0
 

Author Comment

by:macabacus
ID: 33443075
Dave,
Thanks, Dave.  Seems to work pretty well, with two exceptions I found doing some quick checking:

1)  It doesn't not handle formulas that link to external workbooks, at least when those workbooks' names contain characters that would require single quotes in the formula.

2)  It doesn't handle links to other sheets in the same workbook when those other sheets' names contain characters that would require single quotes in the formula.  This causes the cell references shown in column A of the output to read something like "Sheet 2!A1" rather than "'Sheet 2'!A1".  The values in column B are then off by the amount of preceding single quotes that were omitted.

FYI, I use the following Regex pattern to check sheet names to see when Excel will require sheet names to be enclosed in single quotes in formulas:  "[\s~!@#%\^&\(\)\-\+\{\}'"";,\|<>]".  This pattern might also apply to workbook names.  Not sure if that would be helpful in refining the solution.

Ryan
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33444084
Unless I'm misreading it, your regex to catch single quotes is incomplete. If you enter something that looks like a date separated with full stops, it requires single quotes.
FWIW.
Rory
0
 

Author Comment

by:macabacus
ID: 33451376
@rorya,
I'm assuming "full stop" = "period".  Periods in worksheet names do not cause Excel to surround the names in single quotes, which is why periods are omitted from the pattern.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33452020
I said "something that looks like a date separated with full stops". For example 14.02.10
0
 

Author Comment

by:macabacus
ID: 33452070
You're correct.  Not really an issue for my purposes, though, and doesn't affect the outcome I'm trying to achieve.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33452093
Hence the "FWIW"...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Why, and when, to use Windows Workflow 1 20
Convert .PDF 6 42
How to compare data in excel 5 27
TT Auto DashBoard 4 29
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

27 Experts available now in Live!

Get 1:1 Help Now