Any way to use VBA to comment out / uncomment a standard module?

Rather than commenting out a UDF, its much easier just to not use it.

And, yes you could use code in the Open event to remove all the REMs from commented out code. The problem is that you couldn't use that module until the changes were Saved.
I should mention, that if you are working in a company environment, your Permissions might be set so that you can't edit vb code on the fly.

Hi again,

Take a look at the attached workbook and see if it does what you need. It uses the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Sub CommentOutRoutine()

    Call RoutineIsCommentedOut(bTrueOrFalse:=True)

End Sub


'=========================================================================================
'=========================================================================================


Sub RestoreRoutine()

    Call RoutineIsCommentedOut(bTrueOrFalse:=False)

End Sub


'=========================================================================================
'=========================================================================================


Sub RoutineIsCommentedOut(bTrueOrFalse As Boolean)

    Const sMODULE_NAME  As String = "Has_Formula"
    Const sCOMMENT      As String = "'   "
    Const sLINE_1       As String = "Function HasFormula(rCell As Range) As Boolean"
    Const sLINE_2       As String = "    Application.Volatile"
    Const sLINE_3       As String = "    HasFormula = rCell.HasFormula"
    Const sLINE_4       As String = "End Function"

    Dim codHas_Formula  As Object
    Dim sNewLine_1      As String
    Dim sNewLine_2      As String
    Dim sNewLine_3      As String
    Dim sNewLine_4      As String
    Dim iLineNo         As Long
    Dim wbk             As Workbook
    Dim vbp             As Object

    Set wbk = ActiveWorkbook
    Set vbp = wbk.VBProject

    Set codHas_Formula = vbp.VBComponents(sMODULE_NAME).CodeModule

    For iLineNo = 1 To codHas_Formula.CountOfLines

        If codHas_Formula.Find(Target:=sLINE_1, _
                               StartLine:=iLineNo, StartColumn:=1, _
                               EndLine:=iLineNo, EndColumn:=999, _
                               WholeWord:=True) = True Then

            If bTrueOrFalse = True Then

                  sNewLine_1 = sCOMMENT & sLINE_1
                  sNewLine_2 = sCOMMENT & sLINE_2
                  sNewLine_3 = sCOMMENT & sLINE_3
                  sNewLine_4 = sCOMMENT & sLINE_4

            Else: sNewLine_1 = sLINE_1
                  sNewLine_2 = sLINE_2
                  sNewLine_3 = sLINE_3
                  sNewLine_4 = sLINE_4

            End If

            codHas_Formula.ReplaceLine Line:=iLineNo + 0, String:=sNewLine_1
            codHas_Formula.ReplaceLine Line:=iLineNo + 1, String:=sNewLine_2
            codHas_Formula.ReplaceLine Line:=iLineNo + 2, String:=sNewLine_3
            codHas_Formula.ReplaceLine Line:=iLineNo + 3, String:=sNewLine_4

            Exit For

        End If

    Next iLineNo

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M



P. S. The above approach uses late binding, with the variables "codHas_Formula" and "vbp" being declared as type Object - you can use early binding instead if you create a reference to "Microsoft Visual Basic for Applications Extensibility 5.3"