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 
 
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:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. 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.
  8. 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.
 

Test the code:

  1. Paste the above code into a module
  2. Run the TestingIt subroutine.
  3. It will check to see if the macro "CreatedMacro" exists, and if it does it will be deleted
  4. 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
 

Sample File:

Add-Remove Code.zip 6.9KB 

Approved by mdmackillop


This entry has been viewed 276 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express