VBA Delete Module

[jrdnoland]

I'm trying to delete all the controls, modules and code from specific workbooks and then add back new controls, modules and code.

I'm using the following to delete the modules and code and am not sure why two of the four modules in a specific workbook will not delete. Stepping through the code verifies that the commands are being executed and if I stop the code the two modules are deleted.

I could stop the code and then restart it, but I thought I'd see if anyone had any ideas first.

Please Login or Register  to view this content.

I have also tried the commented out code, to remove the specific modules; but still no luck.

Any help will be appreciated!
Thanks

[romperstomper]

Bit of a long shot, but have you tried adding a DoEvents after each delete?

[jrdnoland]

Thanks for the help!
Yes, I tried the DoEvents and also:

Please Login or Register  to view this content.

Neither worked. I can't see anything different in the two modules that are deleted vs the two that aren't either.

[romperstomper]

Is it always the same two modules that won't delete? If so, what do they contain? Subs, UDFs, other stuff? If you can post the workbook it might help.

[jrdnoland]

Yes, it's always the same two. One module just contains constants and global variables. The other has subs and functions in it.

I've attached an example that has one of the modules in it that won't delete.

Thanks!

[romperstomper]

Does it fail for you with that sample workbook? (it worked without issue for me)

[jrdnoland]

Yes, it fails with the sample workbook also. Must be some sort of wierd conflict?

Try to delete the module and then add a module back, this is when it fails. The newly added module (with the same name) will have a 1 after it (modTTWLossConstants1) indicating that the original was still there when the new module was added.


Workbooks(frmOptions.lblWorkbookName.Caption).VBProject.VBComponents.Import "PATH\modTTWLossConstants.bas"

[romperstomper]

Do you get any errors or does it just not do anything?

[jrdnoland]

No errors, see the previous response. I edited it after the initial post to explain what was happening a little more clearly.

[romperstomper]

Nope, that worked fine for me too.
Have you tried moving the import code to a separate routine and calling that using OnTime from the end of the code that deletes the modules?

[jrdnoland]

That would work because it has the same effect as stopping the code and then restarting it. That's similar to what I was going to do for a work around if I couldn't come up with a reason for not being able to delete the modules.

[romperstomper]

Well, it's nothing inherent in the module or it wouldn't work for me.
Does it work for you if you open the workbook that is being modified in a separate instance of Excel?

[jrdnoland]

I'm not sure I haven't tried that. At this point it would be faster to stop and restart the code than to change the code to reference two different instances of Excel.

Thanks for your input!

[jrdnoland]

I tried a few more things and I think the problem resides in the 2nd module (the one I didn't include the first time).

When I make an example workbook with this module, it causes a problem.

[romperstomper]

That one works fine for me too.

[jrdnoland]

Interesting!, I'm going to start pulling out subs from that module one by one (already checked that it works if the module is empty) and see if one of the subs is causing a conflict.

[jrdnoland]

For whatever reason, it appears that this sub is causing the conflict. If I remove all the code in the sub there is still a conflict, if I remove the whole sub from the module then it works.

Please Login or Register  to view this content.

In the workbook that I'm removing and adding the code to this is the code in the Workbook_Open event.

Please Login or Register  to view this content.

[broro183]

hi all,

Romper's more likely to give an informed definitive answer than my guess, but I'd say the problem may be around the line

Please Login or Register  to view this content.

because the rest of it looks reasonably normal to me
In other words, I haven't seen the use of "OLEObjects" before.
- is there any other way of referencing the object?
- could you use another approach to hold the value (eg Defined Name/constant, a Named cell)?
Potentially, if the module code is being deleted but not the Object, Excel may be getting confused or there could be a "junk buildup"*.


As a matter of "good" (some may disagree?) programming practice I recommend adding "Option Explicit" at the top of your modules which will then force you to declare all your variables eg

Please Login or Register  to view this content.

This a longshot too...
*I've read that over time "junk" can build up in modules & potentially cause unexplainable errors. What happens if you run Rob Bovey's free Code Cleaner addin (see http://www.appspro.com/Utilities/CodeCleaner.htm) over the file before deleting the code modules?

hth
Rob

[jrdnoland]

Thanks for looking. The problem seems to be in the name of the sub, I'm going to try other names and see if that works. I removed the contents of the sub line by line, with only the Sub "Name" ... End Sub remaining and it still didn't work correctly.

I add the Option Explicit in my constants module, which is one of the four modules I'm removing and re-adding.

There are other ways to hold the name of the worksheet, but this is just the way I chose to do it. I often have code working on 3 or four open workbooks at a time; all of which may be named differently with differently named worksheets also. I typically design a "template" and add some way of getting the workbook name from it and then I can get the worksheet names and then apply my code as needed.

the reason I need to remove and then re-add all the code in the workbooks is because I've made changes in the code and may have thousands of workbooks containing old code.

[broro183]

hi,

Have you tried the Code Cleaner?

I recommend adding "Option Explicit" to all of your modules - not just to the constants module.

No solution, but some thoughts...
Yes, there are many ways to "skin a cat". I'm assuming that you're working on a network since you may have "thousands of workbooks" that need the code replaced. If so, have you considered using a single "macro file" or addin that is located on a shared drive?
This approach means that you could minimise the number of lots of code you need to change, for example in the individual workbooks you could create a reference to the "macro file" OR get it to open as a hidden read only file via a workbookopen macro OR on the flip side, have the new template & rather than copying the code across to the old files, copy the data into the new template.

hth
Rob

[jrdnoland]

Thanks, I'll look at the code cleaner tomorrow. I'm at home now and the code is at work. Yes it's running over a network.

I've thought of some of the things you mentioned but have not tried them all.

[jrdnoland]

Still no luck with this.

If I remove the Open Event:

Please Login or Register  to view this content.

Or if I remove this sub from the modWLossOther module, it works fine.

Please Login or Register  to view this content.

And it's nothing in the sub itself including it's name.

[romperstomper]

Have you disabled events when you open the workbook in order to replace the code?

[jrdnoland]

No, I wasn't doing that and never even though to try it, but guess what when I did it fixed the issue!

Not sure why, be something was preventing the one module from being removed after it was deleted and setting Application.EnableEvents = False
fixed the issue.

Thanks romperstomper for your persistence!

[phendark]

it seems that i got the similar problem...
I just want to delete the existing module which is config and add it with new module which has the same name. It successfully run at the first time. when i click the button for second time, it generate error. when i see the code, it seem the old module being remove, but the new module name end with number "1". how can solve this problem??

Private Sub addremove_Click()

ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("config")
ActiveWorkbook.VBProject.VBComponents.Import ("c:\Module2.11.bas")

End Sub

[shg]

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.