Using find/replace to replace equal sign with slash to "turn off" UDFs for debugging -- Excel adds ' character when replacing slash with equal sign

[OaklandJim]

Sometimes it is necessary to turn off UDFs if changing them, otherwise the UDF gets called by each cell containing the UDF. In the past I replaced the equal sign -- in a cell formula containing the UDF call -- with a slash to "turn off the UDF". Then, when done I'd replace the slash with the equal sign to "turn on the UDFs." But now Excel adds a single quote in front of the equal sign. Example =MyFunc() => /MyFunc() => '=MyFunc().

Is there a way to tell Excel to not add that single quote when replacing a slash with the equal sign?

Is there another approach for this need?

 

[offthelip]

I just set calculation to manual while I modify the UDF

 

[OaklandJim]

That won't work for me. Currently my UDF is called from about 20 cells. Imagine putting some Debug.Print statements in for the UDF for debugging. In my case the Debug.Print statement fires 20 times. If debugging a loop that might mean hundreds of Debug.Print output lines.

The way Excel's Find/Replace adds single quotes in front of the UDF call when replacing say slash with equal sign is very frustrating.

As it is now I have to do all this one UDF call at a time. That is, replace the single quote/equal sign combo with just equal sign. Takes a while with so many UDF calls.

 

[OaklandJim]

Not ideal but I wrote this little sub that reinstates formulas as desired. First, in the worksheet I replace the formulas' equal sign with a slash using Find/Replace. Later when reinstating the formulas I select the cells containing the formulas then run the sub.

Sub ResetFunctionCall()

    Dim rCell As Range
    
    Dim rRangeToReset As Range
    
    Dim sFormula As String
    
    Dim iFormulaLen As Long

    If TypeName(Selection) <> "Range" _
     Then
        MsgBox "Select a range of cells", vbOKOnly + vbExclamation, "Replacing / with ="
        Exit Sub
    End If
    
    Set rRangeToReset = Selection
    
    For Each rCell In rRangeToReset
        
        If Left(rCell.Formula, 1) = "/" _
        Or Left(rCell.Formula, 1) = "\" _
         Then
            sFormula = rCell.Formula
            
            iFormulaLen = Len(sFormula)
            
            sFormula = "=" & Right(sFormula, iFormulaLen - 1)
            
            rCell.Formula = sFormula
        
        End If
        
    Next rCell
    
End Sub