Excel vba delete sub code

Question

Regarding VBE. If a worksheet does not exist it will create it and add a commandbutton at Sheet1. It will also add the appr. code for the private sub commandbutton1_click. This works all fine. This is part of the existing code that will add a new sub:

Dim Code as String
Code = "Private sub CommandButton1_Click()" & vbCrLf
[..]
Code = Code & "End Sub"

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
    .insertlines .CountOfLines + 1, Code

However before adding that 'sub code', it should check if in the already existing (object) the sub code (for commandbutton1) already exists and if so, then delete it before adding new code.

score 0 · Answer 1 · answered Mar 31 '16 at 06:35

have a look below.

If ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule.CountOfLines <> 0 Then
    ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule.DeleteLines 1, ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule.CountOfLines
End If

score 0 · Answer 2 · answered Sep 07 '16 at 06:53

The following works for me, change the sub name as needed:

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Set VBCodeMod = Workbooks(1).VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
    StartLine = .ProcStartLine("Worksheet_BeforeRightClick", vbext_pk_Proc)
    HowManyLines = .ProcCountLines("Worksheet_BeforeRightClick", vbext_pk_Proc)
    .DeleteLines StartLine, HowManyLines
End With

Excel vba delete sub code

2 Answers2