CODEWITHSUNDEEP
vbaexcel
Preston
Preston

Reputation: 8187

Remove specific code from a module VBA using .DeleteLines

I'd like to use the .DeleteLinesfunction in VBA. As I'm not deleting all the lines in the module i need a targeted approach. I assume there is a function like Find("FooBar").LineNumber, however I can't find it here/with google:

https://msdn.microsoft.com/en-us/library/office/gg264546.aspx

Sub Deletings()
    With Workbooks("ClassExperiment.xlsm").VBProject.VBComponents("Module2").CodeModule
        .DeleteLines(HowDoIGetThisValue, 0)
    End With
End Sub

Help appreciated.

Upvotes: 4

Views: 6046

Answers (2)

Comintern
Comintern

Reputation: 22185

If you're removing the entire procedure, you can find its location with the ProcStartLine property and the line count with ProcCountLines.

Dim module As CodeModule
Set module = Workbooks("ClassExperiment.xlsm").VBProject.VBComponents("Module2").CodeModule

Dim start As Long
Dim lines As Long
With module
    start = .ProcStartLine("button_Click", vbext_pk_Proc)
    lines = .ProcCountLines("button_Click", vbext_pk_Proc)
    .DeleteLines start, lines
End With

Warning:

This should be obvious, but I'll throw it out there anyway. Do not use this (or any other method) to alter the module that the code is running from in Debug mode. This is a good way to break your workbook.

Upvotes: 6

Nathan_Sav
Nathan_Sav

Reputation: 8531

Sub test()

Dim vb As VBComponent
Dim i As Integer

Set vb = ThisWorkbook.VBProject.VBComponents("Module2")

For i =vb.CodeModule.CountOfLines to 1 step -1

    If InStr(1, vb.CodeModule.Lines(i, 1), "'   remove") <> 0 Then
        vb.CodeModule.DeleteLines i, 1
    End If

Next i

End Sub

I would of also suggested using a condition statement to allow execution of the code line, rather than deleting it, when is it put back? this could cause issues if you wish to automate that bit, as you'll need to know where it came from.

Upvotes: 2

Related Questions