Sheet names in formulae

[James_Latimer]

Win 10 / Excel 2016


Hi all, I was wondering if someone could give me some pointers please...


Long story short, i have a 'tool' that i created that will search a workbook for external links and list them in a text file. I've recently found an external link that my 'tool' wasn't taking into account... I found formulae that were pointing to sheets that didn't exist.


Can someone please provide some information as to how i can check a formula in a cell for any references to sheets? I was hoping to go through each formula and check the sheet references against the sheet names within the workbook.


For example if i had a formula =IF(Sheet1!A1 = "YOURE HAPPY TO HELP","THIS IS HOW","WORK IT OUR FOR YOURSELF") I would need to be able to determine that this formula is pointing to sheet "Sheet1". Equally, if there are multiple references to sheets within the same formula i would like to look at ALL of them.


I hope this makes sense!!!


Thank you in advance kind Excelers!

 

[James006]

Hi,

Would suggest to post you macro ...:wink:

HTH

 

[wideboydixon]

Untested to any great extent but this might get you started for looking for sheets:

Public Sub CheckFormulas()

Dim thisSheet As Worksheet
Dim thisCell As Range
Dim thisFormula As String
Dim splitFormula As Variant
Dim thisRef As String
Dim singleQuote As Boolean
Dim thisSplit As Long
Dim thisChar As Long
Dim c As String
Dim testSheet As Worksheet

On Error Resume Next

For Each thisSheet In ThisWorkbook.Worksheets
    For Each thisCell In thisSheet.Cells.SpecialCells(xlCellTypeFormulas)
        thisFormula = thisCell.Formula
        splitFormula = Split(thisFormula, "!")
        If UBound(splitFormula) > 0 Then
            For thisSplit = 0 To UBound(splitFormula) - 1
                singleQuote = Right(splitFormula(thisSplit), 1) = "'"
                thisRef = ""
                For thisChar = Len(splitFormula(thisSplit)) - IIf(singleQuote, 1, 0) To 1 Step -1
                    c = Mid(splitFormula(thisSplit), thisChar, 1)
                    Select Case c
                        Case "'"
                            singleQuote = False
                        Case "a" To "z", "A" To "Z", "0" To "9", " "
                            thisRef = c & thisRef
                        Case "]"
                            thisRef = ""
                            Exit For
                        Case Else
                            If singleQuote Then
                                thisRef = c & thisRef
                            Else
                                Exit For
                            End If
                    End Select
                Next thisChar
                
                If thisRef <> "" Then
                    Set testSheet = Nothing
                    Set testSheet = Sheets(thisRef)
                    If testSheet Is Nothing Then Debug.Print "'" & thisRef & "' is referenced in sheet '" & thisSheet.Name & "' in cell " & thisCell.Address
                End If
            Next thisSplit
        End If
    Next thisCell
Next thisSheet

End Sub

WBD

 

[James_Latimer]

Hi James,

Thank you for taking the time to reply, it's much appreciated. I don't currently have a macro for this... ideally i need someone to show me example code that goes through a formula within a cell and lists the sheet names used in the formula (regardless of how many sheet names are used).

I cant for the life of me figure it out

Thanks in advance.

 

[James_Latimer]

Thank you Sir Dixon of the WideBoy variety.

I'll have a play with that and see where i get... thank you kindly. Sincerely appreciated.

 

[mikerickson]

Perhaps this will do what you want.

Sub test()
    Dim aCell As Range
    Dim i As Long, j As Long
    Dim nextAddress As String, allAddress As String
    
    Set aCell = Sheet1.Range("B1"): Rem adjust
    i = 1: j = 1
    
    With aCell
        .Parent.ClearArrows
        .ShowPrecedents
        
        On Error Resume Next
        Do Until Err
            nextAddress = vbNullString
            nextAddress = .NavigateArrow(True, i, j).Address(, , , True)
            If nextAddress <> vbNullString Then
                allAddress = allAddress & vbCr & nextAddress
            End If
            j = j + 1
        Loop
        On Error GoTo 0
        i = 2: j = 1
        Do
            nextAddress = .NavigateArrow(True, i, j).Address(, , , True)
            If nextAddress = .Address(, , , True) Then Exit Do
            allAddress = allAddress & vbCr & nextAddress
            i = i + 1
        Loop
        'allAddress = Mid(allAddress, 2)
        .Parent.ClearArrows
        
        MsgBox "The precedents of " & .Formula & " (in " & .Address & ") are" & allAddress
    End With
   
End Sub