Modifying code via code

[alkatran]

Is it possible to modify code (obviously code that's not being run at the time) in Excel? There's a small bug in an application we recently sent out, and we want to release a workbook which, when run, will go in and modify the code.

The reasons we can't just re-release the actual workbook are:
-You can't email a 9 MB file to very many places
-Settings are stored in the workbook that shouldn't be lost
-There are technicly more than one workbooks which will be modified


To be specific, what I want is:
How do I input current code? (Locating the subroutine)
How do I modify code? (Replacing the subroutine)
Is it a different method whether I remove or add code?

[RobDog888]

Search in this vba forum for one of my posts that showed how to
programmatically gain access to the VBA Editor for such purposes.
There are some settings that need to be made manually, like
under Tools > Macros > Security > Trusted Sources tab...

I know it had to be with-in a year or so.

Although I never got it to work, not enough free time.

HTH

[alkatran]

I searched "programmatically gain access to the VBA Editor" but only found one post... this one.

[RobDog888]

]Found it!

Here

[alkatran]

There is some subtle irony here...
Considering that post was made by the guy I work for.

[alkatran]

Seeing as how I can't modify protected modules, I can up with another solution. Instead of modifying the modules, I just included the fixed modules with the "patch" workbook, had it copy-paste all the sheets from the target workbook into itself, and then save itself over the old one. It takes a minute to run, but I don't need to send out a 9 MB file

[RobDog888]

Cool, good idea.

[alkatran]

It works great. Final patch sizes (one for each sheet in need): 381 kb and 467 kb (there's a picture in both of them)

There's just one bug that I can't explain.

Workbooks("tbm_sheds.xls").Close False

will crash excel (oh I do love when excel dies instantly), but

Workbooks("tbm_sheds.xls").Close True

doesn't.

Now I don't need to save the workbook because I'm about to overwrite it. Any ideas?

There's nothing quite like debugging excel VBA... I've had lines that worked after they were commented.. and then uncommented.

[RobDog888]

What about turning off the prompt before the close?

Application.DisplayAlerts = False

[alkatran]

Alerts, events, calculation, and asking for links are all off. However, I did try it with alerts on, and with a calculate statement just before the save. Here, I'll post the code:

Private Sub Apply()
Dim Shp As Shape
Dim A As Long
Dim B As Long
On Error GoTo ErrorHandle:
 
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    ShtPatch1.Unprotect "******"
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
'Code to make the interface look good...
'(changing the color of three-four cells)
'Code to stop the patch from being applied twice...
'(an if statement that won't apply)
'Move the workbook to the target location before opening
'other workbooks to avoid link errors in 2003
    ThisWorkbook.SaveAs "C:/timbrplans/modules/" & ThisWorkbook.Name
'Open target workbook and its dependency (links, again)
    GotoWorkbook "tbm_intro.xls"
    GotoWorkbook "tbm_homes.xls" 'homes needs intro
    
'Move some buttons...
    With Workbooks("tbm_homes.xls").Sheets("Presentation")
        .Select
        .Unprotect Password:="open85e3"
        For Each Shp In .Shapes
            If Shp.Type = 8 Then
                Shp.Select
                Selection.ShapeRange.IncrementLeft -65
            End If
        Next Shp
        .Protect Password:="open85e3"
    End With
'Copy all sheets into the patch, which already contains modules and forms
    For A = 1 To Workbooks("tbm_homes.xls").Sheets.Count
        B = Workbooks("tbm_homes.xls").Sheets(A).Visible
        Application.StatusBar = "Please wait, replacing worksheet #" & ThisWorkbook.Worksheets.Count & " of " & Workbooks("tbm_homes.xls").Worksheets.Count
        Workbooks("tbm_homes.xls").Sheets(A).Visible = xlSheetVisible
        Workbooks("tbm_homes.xls").Sheets(A).Copy ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Workbooks("tbm_homes.xls").Sheets(A).Visible = B
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 1).Visible = B
    Next A
    ThisWorkbook.Activate
    
    Application.StatusBar = "Saving the homes module"
    Workbooks("tbm_homes.xls").Close True  'ERROR HERE IF FALSE
    
'More interface code...
    
'Save patch over workbook
    ThisWorkbook.SaveAs "C:/timbrplans/modules/tbm_homes.xls"
 
'And finish up
    Workbooks("tbm_intro.xls").Close False
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    ShtPatch1.Protect "******"
    MsgBox "The patch was applied succesfully.", vbInformation, "timbrplans"
Exit Sub
 
ErrorHandle:
    MsgBox "There was an error applying the patch.", vbExclamation, "timbrplans"
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    ShtPatch1.Protect "******"
End Sub
 
 
 
 
 
 
Private Sub GotoWorkbook(ByVal Filename As String)
Dim Filepath As String
On Error GoTo Errhandler:
 
    Workbooks(Filename).Activate
Exit Sub
 
Errhandler:
    'If the file wasn't open yet
    Application.ScreenUpdating = False
    Application.StatusBar = "Please wait, loading " & Filename
    Filepath = "C:\timbrplans\modules\" & Filename
    If (Dir(Filepath)) = Filename Then
        Workbooks.Open Filename:=Filepath, Password:="******"
    Else
        Filepath = "C:\timbrplans\" & Filename
        If (Dir(Filepath)) = Filename Then
            Workbooks.Open Filename:=Filepath, Password:="******"
        End If
    End If
End Sub