CODEWITHSUNDEEP
excelvbareference
Jaime Oro
Jaime Oro

Reputation: 10135

Find on Excel Sheet which cells are referenced to other Sheets

I need to locate them and substitute its formula by its value.

Currently I'm looking for ! but I think its not very clean.

! could be used inside a formula without refering other sheets, i.e: =IF(A1<0; "Be careful A1 is negative!"; "OK");

Checking if ! appears before " doesn't seem definitive solution, i.e.: =IF(A1<0; "Be careful A1 is negative!"; Sheet2!A1+2);

UPDATE: Apart from whats explained above, named ranges should also be taken into account. They could refer other sheets without using !.

Upvotes: 0

Views: 634

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

You can use Regex to find a reference in a formula to another sheet:

Option Explicit

Function GetFormulaReference(sFormula As String) As String
    Dim sPattern As String, sRng As String
    Dim oRegex As RegExp, oMatches As Object, oMatch As Object
        
    Debug.Print sFormula
    sPattern = "\w+!\w{1,}\d{1,}"
    Set oRegex = New RegExp
    With oRegex
        .Pattern = sPattern
        Set oMatches = .Execute(sFormula)
        For Each oMatch In oMatches
            sRng = oMatch
        Next
    End With
    Set oMatch = Nothing
    Set oMatches = Nothing
    Set oRegex = Nothing
    
    GetFormulaReference = sRng
    
End Function

Usage:

Sub Test()
    Dim sRng As String
    
    sRng = GetFormulaReference("=IF(A1<0; ""Be careful A1 is negative!""; ""Sheet2!A1+2"");")
    Debug.Print "Address: = '" & sRng & "'"
    sRng = GetFormulaReference("=IF(A1<0; ""Be careful A1 is negative!""; ""OK"");")
    Debug.Print "Address: = '" & sRng & "'"

End Sub

Result:

=IF(A1<0; "Be careful A1 is negative!"; "Sheet2!A1+2");
Address: = 'Sheet2!A1'
=IF(A1<0; "Be careful A1 is negative!"; "OK");
Address: = ''

Note #1: you need to add a reference to Microsoft VBScript Regular Expressions 5.5 Note #2: above pattern will work for A1 style addressing, but not for R1C1!

reference

Final note: If GetFormulaReference function returns empty string, then there's no reference to another sheet.

[EDIT]

For named ranges, you can use something like this:

Function GetNamedRangeReference(sFormula) As String
    Dim nms As Object, sName As String, sRetVal As String
    Set nms = ActiveWorkbook.Names 
    For i = 1 To nms.Count 
        sName = nms(r).Name 
        If InStr(1, sFormula, sName, vbTextCompare)>0 Then sRetVal =  nms(r).RefersToRange.Address 'return address instead of name
    Next

    GetNamedRangeReference = sRetVal

End Function

Note: I haven't tested above function.

Good luck!

Upvotes: 2

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please:

Sub testOtherSheetsRef()
  Dim sh As Worksheet, rngF As Range, C As Range
  Set sh = ActiveSheet
  Set rngF = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
  For Each C In rngF
    If InStr(C.Formula, "!") > 0 Then
        If InStr(C.Formula, " !") = 0 And _
            InStr(C.Formula, "! ") = 0 And _
            InStr(C.Formula, "!""") = 0 Then
            Debug.Print C.Address & " is referencing another sheet..."
        End If
    End If
  Next
End Sub

Upvotes: 2

Related Questions