How to replace the function in excel

Question

I have a huge excel file with bunch of formulas. With in cells there are bunch of reference to a old function funcA, which will take 1 parameter. The parameter are also could embedded other functions, or other cell value, or formula. like so

=C1+C2+funcA(C3)/2 
=funcA(C4+AnotherFun(B1))

Now I need to change all the funcA to be funcB. How ever, funcB take two parameters. I need the original para from funcA as the 1st parameter for funcB, but second one 0. So after replace, it will looks like this

=C1+C2+funcB(C3,0)/2 
=funcB(C4+AnotherFun(B1),0)

When I try to do a replace funcA --> funcB excel refused as funcB need two parameters. Also I still need to think of a way to add a ',0' in the function call. I think of RegEx match, but it seems Excel does not support this.

What can I do?

Answer 1

Option Explicit

Sub ReplaceFunction()

    Dim ufr As Range, ufrng As Range
    Dim b As Long, i As Long, x As Long, f As String
    Dim oldf As String, newf As String, p As String

    oldf = "funcA("
    newf = "funcB("
    p = ", 0)"

    On Error Resume Next
    Set ufrng = Worksheets("sheet3").Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not ufrng Is Nothing Then

        For Each ufr In ufrng

            f = ufr.Formula
            b = 0
            x = InStr(1, f, oldf, vbTextCompare)

            If x > 0 Then

                For i = x + Len(oldf) To Len(f)
                    'are there nested functions?
                    If Mid(f, i, 1) = ")" Then
                        b = b - 1
                    ElseIf Mid(f, i, 1) = "(" Then
                        b = b + 1
                    End If
                    'ending bracket for funcA
                    If b = -1 Then
                        'add parameter
                        f = Application.Replace(f, i, 1, p)
                        'change function
                        f = Replace(expression:=f, Find:=oldf, Replace:=newf, compare:=vbTextCompare)
                        'no reason to continue
                        Exit For
                    End If
                Next i

                'change formula
                ufr.Formula = f

            End If
        Next ufr

    End If

End Sub


Function funcA(i As Integer)

    funcA = i

End Function

Function funcB(i As Integer, j As Integer)

    funcB = i * j

End Function

Function AnotherFunc(i As Integer)

    AnotherFunc = i

End Function