How to rename a user defined function

0
votes

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

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. leaving the function names as they are and cope with it
  2. 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
excelvba

2 Answers

2
votes

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

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.

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.

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

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.

1
votes

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