ایجاد فهرستی از تابع برای یک ورک بوک

Question

تیم یک ورک بوک نسبتاً بزرگ دارد و به این فکر می کند که آیا راهی برای "موجودی" وجود دارد که از کدام توابع در فرمول های مختلف ورک بوک، همراه با سلول هایی که آن توابع در آنها استفاده می شوند، استفاده می شود.

این کار کمی سخت تر است که ممکن است در ابتدا به نظر برسد. شما می توانید قوانینی را در مورد اینکه چه چیزی یک تابع اکسل را تشکیل می دهد ایجاد کنید، اما این قوانین ممکن است کمی مبهم باشند. به عنوان مثال، ممکن است فکر کنید که یک تابع با یک کلمه بزرگ و به دنبال آن یک پرانتز باز تعریف می شود. با این حال، این برای مدتی صادق نبود، زیرا نام توابع اکنون می توانند حاوی نقطه باشند.

ساده ترین روشی که Ive برای بدست آوردن موجودی تابع پیدا کرده است، ایجاد یک ماکرو است که از طریق هر فرمول در هر کاربرگ در ورک بوک قدم گذاشته و آن فرمول را در برابر هر تابع کاربرگ ممکن بررسی می کند. ماکرو زیر این کار را به خوبی انجام می دهد:

Sub FormulaInventory()
    Dim EFunc(500) As String
    Dim iEFCnt As Integer
    Dim sFile As String
    Dim sTemp As String
    Dim SourceBook As Workbook
    Dim TargetBook As Workbook
    Dim TargetSheet As Worksheet
    Dim w As Worksheet
    Dim c As Range
    Dim iRow As Integer
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer

     Read functions from text file
    sFile = ActiveWorkbook.Path & "ExcelFunctions.txt"
    iEFCnt = 0
    Open sFile For Input As #1
    While Not EOF(1)
        Line Input #1, sTemp
        sTemp = Trim(sTemp)
        If sTemp > "" Then
            iEFCnt = iEFCnt + 1
            EFunc(iEFCnt) = sTemp & "("
        End If
    Wend
    Close #1

     Sort functions; longest to shortest
    For J = 1 To iEFCnt - 1
        L = J
        For K = J + 1 To iEFCnt
            If Len(EFunc(L)) < Len(EFunc(K)) Then L = K
        Next K
        If L  J Then
            sTemp = EFunc(J)
            EFunc(J) = EFunc(L)
            EFunc(L) = sTemp
        End If
    Next J

     Create and setup new workbook
    Set SourceBook = ActiveWorkbook
    Set TargetBook = Workbooks.Add
    Set TargetSheet = TargetBook.Worksheets.Add
    TargetSheet.Name = "Inventory"
    TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name
    TargetSheet.Cells(3, 1) = "Function"
    TargetSheet.Cells(3, 2) = "Worksheet"
    TargetSheet.Cells(3, 3) = "Cell"
    TargetSheet.Range("A1").Font.Bold = True
    TargetSheet.Range("A3:C3").Font.Bold = True
    With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

     Perform actual inventory
    iRow = 4
    For Each w In SourceBook.Worksheets
        For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)
            sTemp = c.Formula
            For J = 1 To iEFCnt
                If InStr(sTemp, EFunc(J)) Then
                    TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)
                    TargetSheet.Cells(iRow, 2) = w.Name
                    TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "")
                    iRow = iRow + 1
                    sTemp = Replace(sTemp, EFunc(J), "")
                End If
            Next J
        Next c
    Next w
End Sub

بررسی دقیق ماکرو نشان می دهد که در یک فایل متنی (ExcelFunctions.txt) خوانده می شود که شامل فهرستی از تمام توابع کاربرگ Excels است. این فایل را می توان با استفاده از این لینک دانلود کرد و باید در همان پوشه ای که ورک بوک آنالیز می کنید ذخیره شود. (این بدان معناست که ورک بوکی که در حال تجزیه و تحلیل هستید باید قبلاً در یک پوشه ذخیره شده باشد.)

هنگامی که نام تابع کاربرگ از فایل متنی بارگیری می شود، ماکرو یک پرانتز باز به هر نام اضافه می کند. این نام ها در یک آرایه ذخیره می شوند و سپس این آرایه با توجه به طول نام تابع، با طولانی ترین نام توابع در ابتدای آرایه مرتب می شود. این به دلیل برخی ابهامات در نام توابع انجام می شود. به عنوان مثال، هنگامی که آرایه نام تابع در خوانده می شود، یک تابع به نام LEFT( و دیگری به نام T( خواهید داشت. وقتی به یک فرمول نگاه می کنید، اگر نام LEFT( پیدا شد، نام T نیز پیدا می شود. ابتدا در طولانی ترین نام توابع و سپس حذف آن نام ها از رشته فرمول، احتمال "مثبت نادرست" را از بین می برید.

لازم به ذکر است که این تابع فقط با توابع کاربرگ لیست شده در فایل ExcelFunctions.txt کار می کند. شما می توانید فایل را به دلخواه تغییر دهید تا با آنچه قرار دارد سازگار شود. در حال حاضر شامل تمام توابع استاندارد کاربرگ برای اکسل 2016 است، اما ممکن است بخواهید آن را طوری تغییر دهید که شامل توابعی باشد که توسط افزونه های شما در دسترس است، یا ممکن است بخواهید توابعی را که بیش از حد باطنی می دانید حذف کنید. (نکته: شما همچنین می توانید به سادگی یک آپستروف در جلوی نام تابع در فایل متنی اضافه کنید و هرگز در فهرست موجودی قرار نخواهد گرفت.) اکنون کمتر از 500 نام تابع در فایل وجود دارد. اگر تعداد بیشتری اضافه کنید، باید عناصر عددی اعلام شده در ماکرو آرایه EFunc را تغییر دهید.

هنگامی که ماکرو تکمیل شد، موجودی خود را در ورک بوکی جدیدی که ماکرو ایجاد می کند، پیدا خواهید کرد. باید توجه داشته باشید که مدت زمانی که طول می کشد تا ماکرو تکمیل شود می تواند از بسیار سریع تا بسیار آهسته متفاوت باشد. هرچه ورک بوک شما بزرگتر باشد - هرچه کاربرگ های بیشتری داشته باشد و فرمول های بیشتری در آن کاربرگ ها باشد - مدت زمان بیشتری طول می کشد تا کار آن به پایان برسد. به یاد داشته باشید که باید هر فرمول موجود در ورک بوک را در برابر تقریباً 500 نام تابع کاربرگ بررسی کند.