جایگزینی برخی از فرمول ها با نتایج فرمول

Question

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

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

=Sheet2!A1

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

Sub ConvertFormulas1()
    Dim c As Variant
    Dim frm As String

    On Error Resume Next

    For Each c In Selection
        If c.HasFormula Then
            frm = c.Formula
            If InStr(1, frm, "!") = 0 Then
                c.Value = c.Value
            End If
        End If
    Next c
End Sub

یک اشکال در این رویکرد وجود دارد: علامت تعجب در تمام فرمول های خارج از کاربرگ فعلی ظاهر می شود، از جمله آنهایی که در کتاب های کار دیگر هستند. اگر واقعاً می خواهید فقط فرمول ها را با کاربرگ های دیگر در ورک بوک فعلی جایگزین کنید، اما فرمول هایی را که به برگه های دیگر کتاب های کار اشاره می کنند نادیده می گیرید، باید یک منطق اضافی اضافه کنید. وقتی به نحوه ارجاع اکسل به آن کتاب های کار دیگر نگاه می کنید، این منطق خود را آشکار می کند:

=[OtherWorksheet.xls]Sheet1!$C$9

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

Sub ConvertFormulas2()
    Dim c As Variant
    Dim OtherSheet As Boolean
    Dim frm As String

    On Error Resume Next

    For Each c In Selection
        If c.HasFormula Then
            frm = c.Formula
            OtherSheet = False
            If InStr(1, frm, "!") Then
                OtherSheet = True
                If InStr(1, frm, "[") Then
                    OtherSheet = False
                End If
            End If
            If Not OtherSheet Then
                c.Value = c.Value
            End If
        End If
    Next c
End Sub

لازم به ذکر است که اصلاح فرمول استفاده شده در این ماکرو نسبتاً آسان خواهد بود به طوری که از شر تمام مراجع خارجی خلاص شود و در عین حال مراجع به کاربرگ فعلی دست نخورده باقی بماند. در واقع، تنها کاری که باید انجام دهید این است که از بررسی براکت خلاص شوید و سپس از شر کلمه کلیدی "نه" در ساختاری که متغیر OtherSheet را بررسی می کند خلاص شوید.