Scribd Logo
Upload

Welcome to Scribd!

  • 68 views

    Add or Remove A Macro From Code

    Uploaded by

    partho143
    This document describes VBA code that allows adding or removing macros from code modules programmatically. The AddCode subroutine inserts a new macro into a code module, while the delCode subroutine deletes an existing macro. Sample code is provided to demonstrate calling these subs to add and remove a macro called "CreatedMacro". The macros can dynamically generate and insert code based on string inputs, making it useful for creating macros with arguments from cell values at runtime.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Add or Remove A Macro From Code

    Uploaded by

    partho143
    68 views2 pages
    This document describes VBA code that allows adding or removing macros from code modules programmatically. The AddCode subroutine inserts a new macro into a code module, while the delCode subroutine deletes an existing macro. Sample code is provided to demonstrate calling these subs to add and remove a macro called "CreatedMacro". The macros can dynamically generate and insert code based on string inputs, making it useful for creating macros with arguments from cell values at runtime.

    Original Title

    Add or Remove a Macro From Code

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document describes VBA code that allows adding or removing macros from code modules programmatically. The AddCode subroutine inserts a new macro into a code module, while the delCode subroutine deletes an existing macro. Sample code is provided to demonstrate calling these subs to add and remove a macro called "CreatedMacro". The macros can dynamically generate and insert code based on string inputs, making it useful for creating macros with arguments from cell values at runtime.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    68 views2 pages

    Add or Remove A Macro From Code

    Uploaded by

    partho143
    This document describes VBA code that allows adding or removing macros from code modules programmatically. The AddCode subroutine inserts a new macro into a code module, while the delCode subroutine deletes an existing macro. Sample code is provided to demonstrate calling these subs to add and remove a macro called "CreatedMacro". The macros can dynamically generate and insert code based on string inputs, making it useful for creating macros with arguments from cell values at runtime.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    Login Forum

    Excel Add or Remove a macro


    from code module
    programmatically
    Ease of Use Intermediate
    Version tested with 2000, 2003
    Submitted by: mvidas
    Description: The macros can add or remove a specific macro from a module programmatically. Useful to create
    code with arguments from a string's contents.
    Discussion: The AddCode subroutine adds a macro to a specific (usually current) vba code module in an optionally
    specified workbook. It was originally designed to create code at runtime based on user input or cell
    contents. The random string is in there to identify the module to add to, as it was originally designed
    to add code to the current module and there is no real way to reference the current module without
    doing this.
    Code: instructions for use
    Public Sub AddCode(newMacro As String, RandUniqStr As String, _
    Optional WB As Workbook)
    Dim VBC, modCode As String
    If WB Is Nothing Then Set WB = ThisWorkbook
    For Each VBC In WB.VBProject.VBComponents
    If VBC.CodeModule.CountOfLines > 0 Then
    modCode = VBC.CodeModule.Lines(1, VBC.CodeModule.CountOfLines)
    If modCode Like "*" & RandUniqStr & "*" And Not modCode Like "*" & newMacro & "*" Then
    VBC.CodeModule.InsertLines VBC.CodeModule.CountOfLines + 1, newMacro
    Exit Sub
    End If
    End If
    Next VBC
    End Sub

    Public Sub delCode(MacroNm As String, RandUniqStr As String, _
    Optional WB As Workbook)
    Dim VBC, i As Integer, procName As String, VBCM, j As Integer
    If WB Is Nothing Then Set WB = ThisWorkbook
    For Each VBC In WB.VBProject.VBComponents
    Set VBCM = VBC.CodeModule
    If VBCM.CountOfLines > 0 Then
    If VBCM.Lines(1, VBCM.CountOfLines) Like "*" & RandUniqStr & "*" Then
    i = VBCM.CountOfDeclarationLines + 1
    Do Until i >= VBCM.CountOfLines
    procName = VBCM.ProcOfLine(i, 0)
    If UCase(procName) = UCase(MacroNm) Then
    j = VBCM.ProcCountLines(procName, 0)
    VBCM.DeleteLines i, j
    Exit Sub
    End If
    i = i + VBCM.ProcCountLines(procName, 0)
    Loop
    End If
    End If
    Next VBC
    End Sub
    VBA Express : Excel - Add or Remove a macro from code module prog... http://www.vbaexpress.com/kb/getarticle.php?kb_id=250
    1 of 2 3/13/2014 9:47 PM

    Sub TestingIt()
    Dim prmtrs As String, toAdd As String
    prmtrs = "Key1:=Range(""C1""), Order1:=xlAscending, Header:=xlNo"
    toAdd = "Sub CreatedMacro()" & vbCrLf & " Cells.Sort " & prmtrs & vbCrLf & "End Sub"
    delCode "CreatedMacro", "a1b2c3d4e5f6g7h8i9", ThisWorkbook
    AddCode toAdd, "a1b2c3d4e5f6g7h8i9", ThisWorkbook
    End Sub
    How to use:
    Copy above code. 1.
    In Excel press Alt + F11 to enter the VBE. 2.
    Press Ctrl + R to show the Project Explorer. 3.
    Right-click desired file on left (in bold). 4.
    Choose Insert -> Module. 5.
    Paste code into the right pane. 6.
    Call the delCode sub using the name of the macro you want deleted as the first argument, a (random)
    text string that exists in that module as the second argument, and an optional workbook reference for
    the third reference if the module that contains the code is in a different workbook than the workbook
    to be modified.
    7.
    Call the AddCode sub using the macro in string form as the first argument, a (random) text string that
    exists in that module as the second argument, and an optional workbook reference for the third
    reference if the module that contains the code is in a different workbook than the workbook to be
    modified.
    8.

    Test the code:
    Paste the above code into a module 1.
    Run the TestingIt subroutine. 2.
    It will check to see if the macro "CreatedMacro" exists, and if it does it will be deleted 3.
    It will then add the "CreatedMacro" to the module, which is simply a sorting macro. It demonstrates
    how you can add arguments from a string to code
    4.

    Sample File: Add-Remove Code.zip 6.9KB
    Approved by mdmackillop
    Bookmarking
    This entry has been viewed 246 times.
    Login Forum
    Please read our Legal Information and Privacy Policy
    Copyright @2004 - 2012 VBA Express
    VBA Express : Excel - Add or Remove a macro from code module prog... http://www.vbaexpress.com/kb/getarticle.php?kb_id=250
    2 of 2 3/13/2014 9:47 PM

    You might also like