Formulas to be triggered when Excel is opened or s

Question

I have several custom-made (VBA) formulas in my Excel. Examples are

    = isfileExists(c3)
    = isReadOnly(c3)

There are multiple calls to these functions (200/column)

These are slowing down the file as they involve heavy VBA. I want the above formulas to be run only when user opens the file or saves the file only!

How can I go on to do this without touching 'enabling/disabling automatic calculations'? (Please note I do know how to write the event functions but I am looking for an idea to put inside them/anywhere)

Sample idea I have is, adding a ' infront the formulas and whenever user opens/saves, that ' (apostrophe) would be removed using macro; hence the formulas would calculate. Any other easier suggestions?

Thanks much!

Answer 1

You can cache the formula results in a Global (or Static inside the function) dictionary object: once you've checked a file once you can use the cached result instead of repeating the file check)

Public Function ExpensiveCheck(fpath As String) As Boolean

    Static dict As Object, ans As Boolean
    'create the dictionary if not already created
    If dict Is Nothing Then
        Set dict = CreateObject("scripting.dictionary")
    End If

    If Not dict.exists(fpath) Then
        ans = (Dir(fpath, vbNormal) <> "") 'do your checking here
        dict.Add fpath, ans
    End If

    ExpensiveCheck = dict(fpath)
End Function

The dict will be lost when the session ends, so the formula should always refresh the cache when you open the file.

Downside is you may need some mechanism to clear/refresh the cache if you want to update the output after some change in the files being checked.

Answer 2

If you don't want your UDFs to calculate along with the rest of the worksheet cells, then you don't want UDFs; make them Private, or stick Option Private Module at the top of the standard module where they're declared, to make them unavailable to use as custom worksheet functions.

Then handle Workbook.Open and Workbook.BeforeSave events, and have these two handlers call some Private Sub that's responsible for writing to the cells you currently have these UDFs in.

That way the values will only be computed when the workbook opens and before it's saved.

UDFs that incur disk I/O aren't ideal: keep that processing for macros.