다른 워크북에서 셀에 대한 참조를 감지하고 있습니까?

다른 워크북에서 셀에 대한 참조를 감지하고 있습니까?

셀을 참조하는 다른 워크북/시트가 있는지 확인할 수 있는 VBA 및/또는 Excel 공식이 있습니까?이상적으로는 어떤 워크북/시트에서 작업하는 것이 바람직하지만, 이 작업이 불가능한 경우에도 괜찮습니다.

프록시 주소 목록이 있는 워크북이 있다고 가정해 보겠습니다. 셀을 참조하는 다른 워크북이 있는지 확인하여 프록시가 이미 사용되고 있는지 알고 싶습니다.이것은 무료 프록시인지 아니면 이미 사용 중인지 표시하기 위한 것입니다.

이와 유사한 대체 솔루션도 환영합니다.완전한 해결책을 찾고 있는 것은 아니지만 올바른 방향을 제시함으로써 멀리 갈 수 있습니다.

다음은 몇 가지 코드입니다. 사용자(또는 다른 공동작업자)가 두 개의 워크북의 예를 실행할 수 있는 몇 가지 설정 코드입니다. 하나는 다른 하나를 가리킵니다.설정의 일부로 두 권의 워크북이 Temp 디렉토리에 저장됩니다.

나의 경우 출력은

 Cell at Book2.xlsx!Sheet1!$A$2 has external workbook source of [Book1.xlsx]

워크북의 링크 소스를 검사한 다음 해당 링크 소스를 찾는 셀을 스위프합니다.

Option Explicit



'---------------------------------------------------------------------------------------
' Procedure : Investigate
' DateTime  : 06/02/2018 14:40
' Author    : Simon
' Purpose   : Start execution here.  There is some setup code
'---------------------------------------------------------------------------------------
' Arguments :
'    arg1      : arg1 description
'
Sub Investigate()

    '**************************************************
    ' START of Experiment setup code
    '**************************************************
    Dim wb1 As Excel.Workbook, wb2 As Excel.Workbook

    GetOrCreateMyTwoWorbooks "Book1", "SimonSub1", wb1, "Book2", "SimonSub2", wb2


    wb1.Worksheets(1).Range("a1").Formula = "=2^4"


    wb2.Worksheets(1).Range("a1").Formula = "=2^2"
    wb2.Worksheets(1).Range("b1").Formula = "=3^2"
    wb2.Worksheets(1).Range("a2").FormulaR1C1 = "=[" & wb1.Name & "]Sheet1!R1C1/r1c1*r1c2"

    '**************************************************
    ' END of Experiment setup code
    '**************************************************

    '**************************************************
    '* now the real logic begins
    '**************************************************

    Dim dicLinkSources As Scripting.Dictionary
    Set dicLinkSources = LinkSources(wb2)

    '* get all the cells containing formulae in the worksheet we're interested in
    Dim rngFormulaCells As Excel.Range
    Set rngFormulaCells = wb2.Worksheets(1).UsedRange.SpecialCells(xlCellTypeFormulas)

    '* set up results container (one could report as we find them but I like to collate)
    Dim dicExternalWorksheetPrecedents As Scripting.Dictionary
    Set dicExternalWorksheetPrecedents = New Scripting.Dictionary

    '* loop throught the subset of cells on the worksheet that have formulae
    Dim rngFormulaCellsLoop As Excel.Range
    For Each rngFormulaCellsLoop In rngFormulaCells

        Dim sFormula As String
        sFormula = rngFormulaCellsLoop.Formula  '* I like a copy in my locals window

        '* search for all the link sources (experiment has only one, chance are you'll have many)
        Dim vSearchLoop As Variant
        For Each vSearchLoop In dicLinkSources.Items
            If VBA.InStr(1, sFormula, vSearchLoop, vbTextCompare) > 0 Then

                '* we found one, add to collated results
                dicExternalWorksheetPrecedents.Add wb2.Name & "!" & wb2.Worksheets(1).Name & "!" & rngFormulaCellsLoop.Address, vSearchLoop

            End If
        Next vSearchLoop

    Next

    '*print collated results
    Dim lResultLoop As Long
    For lResultLoop = 0 To dicExternalWorksheetPrecedents.Count - 1
        Debug.Print "Cell at " & dicExternalWorksheetPrecedents.Keys()(lResultLoop) & " has external workbook source of " & dicExternalWorksheetPrecedents.Items()(lResultLoop)

    Next lResultLoop


    Stop
End Sub

'---------------------------------------------------------------------------------------
' Procedure : LinkSources
' DateTime  : 06/02/2018 14:38
' Author    : Simon
' Purpose   : To acquire list of link sources and more importantly the search term
'             we're going to see to look for external workbooks
'---------------------------------------------------------------------------------------
' Arguments :
'   [in] wb         : The workbook we want report on
'   [out,retval]    : returns a dictionary with the lik sources in the keys and search term in item
'
Function LinkSources(ByVal wb As Excel.Workbook) As Scripting.Dictionary

    Static fso As Object
    If fso Is Nothing Then Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    Dim dicLinkSources As Scripting.Dictionary
    Set dicLinkSources = New Scripting.Dictionary

    Dim vLinks As Variant
    vLinks = wb.LinkSources(XlLink.xlExcelLinks)

    If Not IsEmpty(vLinks) Then
        Dim lIndex As Long
        For lIndex = LBound(vLinks) To UBound(vLinks)

            Dim sSearchTerm As String
            sSearchTerm = ""

            If fso.FileExists(vLinks(lIndex)) Then
                Dim fil As Scripting.file
                Set fil = fso.GetFile(vLinks(lIndex))

                '* this is what we'll search for in the cell formulae
                sSearchTerm = "[" & fil.Name & "]"

            End If

            dicLinkSources.Add vLinks(lIndex), sSearchTerm

        Next lIndex
    End If
    Set LinkSources = dicLinkSources
End Function


'*****************************************************************************************************************
'                                         __                                __
'_____  ______ ___________ ____________ _/  |_ __ __  ______   ______ _____/  |_ __ ________
'\__  \ \____ \\____ \__  \\_  __ \__  \\   __\  |  \/  ___/  /  ___// __ \   __\  |  \____ \
' / __ \|  |_> >  |_> > __ \|  | \// __ \|  | |  |  /\___ \   \___ \\  ___/|  | |  |  /  |_> >
'(____  /   __/|   __(____  /__|  (____  /__| |____//____  > /____  >\___  >__| |____/|   __/
'     \/|__|   |__|       \/           \/                \/       \/     \/           |__|
'
'*****************************************************************************************************************
'* this is just something to setup the experiment, you won't need this hence the big banner  :)
'*
Public Sub GetOrCreateMyTwoWorbooks(ByVal sWbName1 As String, ByVal sSubDirectory1 As String, ByRef pwb1 As Excel.Workbook, _
                                    ByVal sWbName2 As String, ByVal sSubDirectory2 As String, ByRef pwb2 As Excel.Workbook)

    Static fso As Object
    If fso Is Nothing Then Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    On Error Resume Next
    Set pwb1 = Application.Workbooks.Item(sWbName1)
    Set pwb2 = Application.Workbooks.Item(sWbName2)
    On Error GoTo 0

    If pwb1 Is Nothing Then
        Set pwb1 = Application.Workbooks.Add

        Dim sSubDir1 As String
        sSubDir1 = fso.BuildPath(Environ$("tmp"), sSubDirectory1)

        If Not fso.FolderExists(sSubDir1) Then fso.CreateFolder (sSubDir1)

        Dim sSavePath1 As String
        sSavePath1 = fso.BuildPath(sSubDir1, sWbName1)

        pwb1.SaveAs sSavePath1
    End If

    If pwb2 Is Nothing Then
        Set pwb2 = Application.Workbooks.Add

        Dim sSubDir2 As String
        sSubDir2 = fso.BuildPath(Environ$("tmp"), sSubDirectory2)

        If Not fso.FolderExists(sSubDir2) Then fso.CreateFolder (sSubDir2)


        Dim sSavePath2 As String
        sSavePath2 = fso.BuildPath(sSubDir2, sWbName2)

        pwb2.SaveAs sSavePath2
    End If


End Sub

리본 > 수식 > 전례 추적 > 클릭:

다음 선행 셀을 알려줍니다.

• 현재 워크시트의 셀
• 다른 워크시트의 셀
• 열린 문제집의 세포들

하나 이상의 셀 백분율을 보려면 다음을 고려하십시오.

Sub TestMe()
    Dim myCell  As Range
    For Each myCell In ActiveSheet.UsedRange
        If myCell.HasFormula Then myCell.ShowPrecedents
    Next myCell
End Sub

그러면 다음과 같은 것을 얻을 수 있습니다.

언급URL : https://stackoverflow.com/questions/48596908/detecting-reference-to-cell-from-other-workbooks