Delete modules programmatically and save

[Brijesh Shah]

HI,

I have hit a wall here. I delete some modules using VBComponents.Remove.
After that, even if I save the workbook using Save method, the changes are
not saved. Does anyone has a clue? After I run the macro below it still asks
me if I want to save the changes when i close the workbook. Note the macro
to delete the modules is in the same workbook. So the macro is trying to
delete itself too.

Code

Dim obj As Object
Dim VBComps As Variant

Set VBComps = ThisWorkbook.VBProject.VBComponents

For Each obj In VBComps
If obj.Type = 1 Or obj.Type = 2 Then
VBComps.Remove obj
End If
Next

ThisWorkbook.Save

Thanks in advance,

Brij

 

[Tom Ogilvy]

You can't save in the same macro that did the deletion. The deletion
doesn't actually take place until the macro ends.

In the macro, use application.Ontime to run a sum that saves the workbook

application.Ontime now,"MacroToSaveWorkbook"

This allows the code to end and then the save is performed.

 

[Brijesh Shah]

Tom,

Thanks for the reply

This may sound naive, but where do i but the macro to save the workbook. I
am new to formula stuff in Excel.

My goal is to delete all macros from the workbook so that the user is not
prompted to disable or enable macros (based on security settings) when
he/she opens the workbook. Is there a way to put a formula in a cell that
would save the workbook later using Application.Ontime?

Thanks again.

Brij

 

[Tom Ogilvy]

I would put the code in a separate workbook to start with - that avoids the
problem of having to delete it.

I don't know what you are doing or what is feasible/consistent with what you
are doing, so I really can't suggest what the best way to proceed it.