如何重命名用户定义的函数

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

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

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.

现在我想重命名其中的几个函数,因为最初选择的一些函数名称不是很好。如果我在VBA编辑器中天真地重命名它们,则工作簿不再起作用,因为工作簿中的公式中的用户定义函数的名称不会自动重命名。

I now have only two possibilities:

我现在只有两种可能性:

leaving the function names as they are and cope with it

保留函数名称并处理它

renaming the functions manually in every single formula in all worksheets of my workbook.

在我的工作簿的所有工作表中的每个公式中手动重命名函数。

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

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

2 个解决方案

#1

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?)

选择2 - 具有良好命名的功能是非常好的练习(你可能知道它们现在意味着什么,但是在6个月的时间里,或者其他人必须照顾它的时候呢?)

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.

第一项工作 - 重命名该功能。很容易。其次,在VBA中找到函数被调用的所有地方。按Ctrl + F,选择“当前项目”并搜索功能名称。

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.

最后,您可以在工作簿上执行基本相同的操作。按Ctrl-F,转到“替换”选项卡。检查“内部”是否设置为“工作簿”,“查找”设置为“公式”。

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

由于这是*,我觉得没有在工作簿中发布查找和替换的VBA解决方案:

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

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.

但是,要小心你要替换的是什么值 - 如果你有像函数a()那样的函数名,那么在a上执行查找和替换可能会导致各种问题。

#2

I guess you have to parse all cells in all worksheets with this kind of function, being careful with the values you replace :

我想你必须用这种函数解析所有工作表中的所有单元格,小心你替换的值:

Sub replaceFormulaNameInWS(ByRef ws As Worksheet, ByVal toReplace As String, ByVal rePlaceBy As String)
Dim r As Range
Set r = ws.UsedRange
For Each cell In r
If InStr(cell.Formula, toReplace) > 0 Then
    cell.Formula = Replace(cell.Formula, toReplace, rePlaceBy)
End If
Debug.Print cell.Formula
Next
End Sub

#1