Changing workbook code via vba

Hello all,

I have code for my sheets (e.g Sheet1(Main)) that runs all the activation and change events etc. I also have various modules.

I have code that will modify the modules: for example, this follow code deletes a module:

Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Mod_MyModule")

However, I don't know how to manipulate subroutines within the sheet module.

In principle, I would like to locate a string within a Sub, and change it. Or, if I have to change the entire sheet module without affecting the sheet itself.

How can this be done - can anyone point me in the right direction. I look at CPearson stuff but it didnt seem to provide this particular answer.

Cheers

Hi Journeyman3000,

See the attached file and/or code in post #3 of the following thread. If it doesn't solve your problem, the references may point you in the right direction. http://www.excelforum.com/excel-prog...de-in-vba.html

Lewis

Thank you for your responses. I was kinda hoping for a shorter solution in which I could provide the sheetname, locate the string in the VBE editor and change it. I'll search through the solution and see if I can find it there. cheers

Norrie: I want to do it becuase I've distributed the workbook to mulitple people, and I need to update the code in each. I can do this by looping through each book, looking for the correct worksheet, updating the VBE ect. In this case, it's just a single string. Cheers

I don't know what add-in to use, and I beleive it should be acheviable with code.

Thank you all for for advice.

Using addins is a good idea, but for this particular excercise I needed to change the code in the VBE for Sheet1. It turns out there are several locations that needed updates so I can pretty much use the same code, although lesson learned for the future. I found the following code, and modified it slightly and it works as required.

Function CodeRewrite(SheetName As Workbook)
Dim VBP As VBIDE.VBProject
Dim VBC As VBIDE.VBComponent
Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim S As String
Dim Found As Boolean
 
    On Error Resume Next
    Set VBP = SheetName.VBProject
    On Error GoTo 0
     
    If VBP Is Nothing Then
        MsgBox "Your security settings do not allow this macro to run.", vbInformation
        Exit Function
    End If
     
    For Each VBC In VBP.VBComponents
        If VBC.Type = vbext_ct_Document Then
            If InStr(1, VBC.Name, "ThisWorkbook", vbTextCompare) = 0 Then
                If VBC.Name = "Sheet1" Then
                    With VBC.CodeModule
                        SL = 1 ' Start Line
                        SC = 1 ' Start Column
                        EL = .CountOfLines ' End Line
                        EC = 999 ' End Column
                        Found = .Find("Find This String", SL, SC, EL, EC, True, False, False)
                        If Found = True Then
                            S = .Lines(SL, 1)
                            S = Replace(S, _
                                "Find This String", _
                                "Replace With This String", 1, -1, vbTextCompare)
                            Debug.Print S
                            .ReplaceLine SL, S
                        End If
                    End With
                    Exit Function
                End If
            End If
        End If
    Next VBC
     

End Function

Thanks to all who provided advice.

Cheers