如何重命名用户定义的函数 [英] How to rename a user defined function

问题描述

在(非常复杂的)Excel工作簿中，我使用了许多用户定义的函数.这没有问题.

In my (quite complex) Excel workbook I'm using a lot of user defined functions. This works without problems.

现在，我想重命名其中一些功能，因为最初选择的某些功能名称不是很好.如果我在VBA编辑器中天真地将其重命名，则该工作簿将不再起作用，因为该工作簿中 formulas 中用户定义函数的名称不会自动重命名.

Now I'd like to rename several of those functions, because the some of the function names chosen initially are not very good. If I rename them naively in the VBA editor, the workbook doesn't work anymore, because the names of the user defined functions in the formulas in the workbook are not renamed automatically.

我现在只有两种可能性:

I now have only two possibilities:

1. 使函数名称保持不变并对其进行处理
2. 在工作簿的所有工作表中的每个公式中手动重命名功能.

是否有更有效的方法来重命名所有公式中的所有用户定义函数?

Is there a more effective way to to rename all user defined functions in all formulas ?

推荐答案

选择2-具有命名功能是非常好的做法(您可能现在都知道它们的含义，但是在6个月的时间或何时该怎么办?有人要照顾吗?)

Go with 2 - having well-named functions is very good practice (you might know what they all mean now, but what about in 6 months time, or when someone else has to look after it?)

第一项工作-重命名功能.很简单.其次，在VBA中到处都可以找到该函数.按下 Ctrl + F ，选择 Current Project ，然后搜索函数名称.

First job - rename the function. Easy enough. Second, find everywhere in the VBA that the function is called. Hit Ctrl+F, select Current Project and search for the function name.

最后，您可以在工作簿上执行基本相同的操作.点击 Ctrl-F ，转到 Replace 标签.检查内部是否设置为工作簿"，以及查找范围" 是否设置为公式".

Finally, you can do essentially the same on the workbook. Hit Ctrl-F, go to the Replace tab. Check that Within is set to Workbook and Look in is set to Formulas.

由于这是StackOverflow，我不为发布Find& amp;的VBA解决方案感到不解.在工作簿中替换:

Since this is StackOverflow, I'd feel remiss not posting a VBA solution for Find & Replace in the workbook:

Cells.Replace What:="OldFunctionName", Replacement:="NewFunctionName", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

但是，请注意要替换的值-如果您具有 Function a()之类的函数名称，请执行查找&在 a 上进行替换很可能会引起各种问题.

However, be careful what values you're replacing - if you have function names like Function a(), then doing a find & replace on a is likely to cause all kinds of problems.

这篇关于如何重命名用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！