打开或保存Excel时要触发的公式 [英] Formulas to be triggered when Excel is opened or saved

问题描述

我的Excel中有几个定制(VBA)公式.例子是

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

    = isfileExists(c3)
    = isReadOnly(c3)

对这些函数有多个调用(200个/列)

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

这些正在减慢文件的速度，因为它们涉及繁重的VBA.我希望以上公式仅在用户打开文件或仅保存文件时运行！

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?

非常感谢！

推荐答案

您可以将公式结果缓存在全局(或函数内部的静态)字典对象中:一旦检查了文件，就可以使用缓存的结果而不是重复文件检查)

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.

这篇关于打开或保存Excel时要触发的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！