Code to change code in a sheet and workbook module

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

Excel XP and Win XP
I have used code furnished by Chip Pearson to copy a module from one
file to another. I believe, maybe I'm wrong, that this code deals with
regular modules only. My question now is: Can this be done with a sheet or
workbook module? And how? Thanks for your time. Otto
 
Try something like the following. You'll need a reference to the
Extensibility library.

Sub CopySheetModule()
Dim ThisVBP As VBIDE.VBProject
Dim ThatVBP As VBIDE.VBProject

Dim ThisVBComp As VBIDE.VBComponent
Dim ThatVBComp As VBIDE.VBComponent

Dim S As String

Set ThisVBP = ThisWorkbook.VBProject
Set ThatVBP = Workbooks("Book2").VBProject '<<< CHANGE

Set ThisVBComp = ThisVBP.VBComponents("Sheet1") '<<< CHANGE
Set ThatVBComp = ThatVBP.VBComponents("Sheet1") '<<< CHANGE

With ThisVBComp.CodeModule
S = .Lines(1, .CountOfLines)
End With

With ThatVBComp.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString S
End With

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Hello Chip, is there a way to place into the code the activation of the
reference that you speak about to the Extensibility library.
Thanks
BOB R
 
Chip
I'm learning things here that I've never been into before. And I thank
you for that.
The code you gave me is for a sheet module and I can put it to good use.
How would I modify that code to work with the Workbook module? Thanks for
your time. Otto
 
Chip
I noticed that setting the Extensibility reference does not stay put
when I shut down Excel and reopen it. Is that normal behavior? Thanks for
your time. Otto
 
I noticed that setting the Extensibility reference does not stay put
when I shut down Excel and reopen it. Is that normal behavior?
Click to expand...

No, that is not normal behavior. The reference will come and go as that
workbook is opened or closed, but that workbook should always have that
reference set. References are part of the workbook, so every workbook that
needs to use a reference must have it checked in the VBProject.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip, would it be possible to have the code (on startup) to go to C:\Program
Files\Common Files\microsoft shared\VBA
VBA6\VBE6EXT.OLB and activate the Extensibility reference automatically or
will that work. I too have the problem Otto has in losing the connection in
that workbook each time. I had read about a number but couldn't find one,
only the file and location.

Or is there another way for that to happen????
Thanks
BOB
 
I wouldn't use the file name since you cannot be sure that the DLL will
always be in the same place on one machine as it is on another machine.
Instead, use the AddFromGUID method.

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=0, Minor:=0

The problem here is that if there is no reference to VBIDE and VBA decides
to compile the code before the code to add the reference is run, you'll get
compiler errors (that cannot be trapped with an On Error statement).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Thanks Chip, that explains it. I thought the setting was global, not
workbook peculiar. Otto
 
Chip
I had another question in this thread regarding altering the code you
gave me for manipulating code in a sheet module, to make it work with a
Workbook module. You may have overlooked that query and I would appreciate
your help with this. Here is the query:

Chip
I'm learning things here that I've never been into before. And I thank
you for that.
The code you gave me is for a sheet module and I can put it to good use.
How would I modify that code to work with the Workbook module? Thanks for
your time. Otto
 
Just to add, but if anyone has both XL97 and later versions installed (won't
apply to many) using -
Major:=0, Minor:=0
may add the Office97 Existensiblity library 5.0 instead of 5.3 for XL2000
and later.

The proc below is probably overkill but I found it useful to keep in my
respective Personal's. Helped me ensure I had a reference to the correct
library for whichever version I was working in. Also helped as annoyingly it
always seemed that the wrong version would be listed in the dropdown and
would need to browse and find the right one.

Note this was only for my development use and would not normally add the
reference in a distributed workbook. As Chip mentioned, other code may break
due to a temporary missing reference (though there are ways round that).

I only add a reference to the library to get the intellisense. Otherwise I
declare all object variables 'As Object', ie late binding to avoid the need
to add any reference at all.

Sub AddExtRef(wb As Workbook, Optional bRemoveRef As Boolean)
Dim bXL9plus As Boolean
Dim bWrongRef As Boolean
Dim objRef As Object
Dim objRefs As Object

#If VBA6 Then
bXL9plus = True
#End If

Set objRefs = wb.VBProject.References

On Error Resume Next
Set objRef = objRefs("VBIDE")
On Error GoTo 0

If Not objRef Is Nothing Then
If (bXL9plus <> (objRef.Minor = 3)) Or bRemoveRef Then
' incompatible ext library for current xl version
objRefs.Remove objRef
Set objRef = Nothing
End If
End If

If objRef Is Nothing And Not bRemoveRef Then
Set objRef = objRefs.AddFromGuid("{0002E157-0000-0000-C000-000000000046}", _
5, IIf(bXL9plus, 3, 0))
'If no need to cater for xl97 change the continuation line to 0,0)

End If

'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB 5.3
xl9+
'C:\Program Files\Common Files\Microsoft Shared\VBA\Vbeext1.olb 5.0 xl97
End Sub


What I tend to do is select the project in the VBE, then paste following in
the Immediate, with the cursor at the end of the line hit enter at -

Call Application.Run("Personal.xls!AddExtRef", ThisWorkbook)

Regards,
Peter T
 
Try


Sub CopyThisWorkbook()

Dim ThisVBComp As VBIDE.VBComponent
Dim ThatVBComp As VBIDE.VBComponent
Dim S As String

Set ThisVBComp = ThisWorkbook.VBProject.VBComponents("ThisWorkbook")
Set ThatVBComp = Workbooks("Book3").VBProject.VBComponents("ThisWorkbook")

With ThisVBComp.CodeModule
S = .Lines(1, .CountOfLines)
End With

With ThatVBComp.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString S
End With

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top