Removing a module

[Candyman]

Is there a difference in code going to Excel 2003?

I have the code to remove a module:

Private Sub RemoveModule(mName As String)
Dim objVBComp As VBComponent
If modExists(mName) = True Then
Set objVBComp = ThisWorkbook.VBProject.VBComponents(mName)
ThisWorkbook.VBProject.VBComponents.Remove objVBComp
End If
End Sub
'Before using these procedures, you'll need to set a reference in VBA to the
'VBA Extensibility library. In the VBA editor, go to the Tools menu, choose
'the References item, and put a check next to "Microsoft Visual Basic For
'Applications Extensibility" library.


Function modExists(modName As String) As Boolean
On Error Resume Next
modExists = Len(ThisWorkbook.VBProject.VBComponents(modName).Name) <> 0
End Function

The code does not remove it and has a cannot recognize vbProject in workbook
error. What is wrong?

Thanks

 

[Dave Peterson]

You sure it didn't say VBComponent (not VBProject)???

I got a compile error if I didn't add that reference (tools|references within
the VBE).

Another problem that could occur is that the module isn't removed because:

Tools|Macro|Security|Trusted Publishers tab
Trust access to Visual Basic Project

is unchecked.

I didn't get an error in my test. The "on error resume next" line suppressed
it.

And this security setting is a user by user setting--not something a developer
can change via code.

 

[Bill Pfister]

I've been using the following code for several years (from XL 97 to 2003)
without issue. I dim the variables as objects so that I can avoid using the
VB6 extensibility reference (the versioning of this library caused some
issues around the time of 97).

Dim wkb As Workbook
Dim vbcsT As Object
Dim vbcT As Object

set wkb = ActiveWorkbook

' If module exists, remove it
If ModuleExists(strModule) Then
Set vbcT = wkb.VBProject.VBComponents(strModule)
Call wkb.VBProject.VBComponents.REMOVE(vbcT)
End If

 

[Candyman]

I did not have the security - trust access to Visual Basic Project checked.
good point. My code was cut and paste, my error message was from memory (not
Memorex). this sounds like the ticket. Thanks!

 

[Candyman]

I did not know the part about diming the variables and the VB6 extensibility.
Thanks! Have a great day.