Any luck using CopyModule?

[Janie]

I'm using Chip Pearson's CopyModule function found at
http://cpearson.com/excel/vbe.htm. I must say, I am thrilled to find this
bit. However, I am having a problem.

I have installed the VBA Extensibility library. Then I copied the sample
code for CopyModule and pasted it into my Excel module. But when I try to
use the function, I get the error "ByRef argument type mismatch". Any hints?

here is the command line of the function:
CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean

Here's what I used in my Immediate Window

?CopyModule("basMyRoutine", ProjectA, ProjectB, True)

If I place the ProjectA and ProjectB inside quotes, I get a Type Mismatch
error, so I don't think that is the cause of my problem.

I welcome any recommendations. And thanks in advance.

 

[Joel]

I'm using Chip Pearson's CopyModule function found at
http://cpearson.com/excel/vbe.htm. I must say, I am thrilled to find this
bit. However, I am having a problem.

I have installed the VBA Extensibility library. Then I copied the sample
code for CopyModule and pasted it into my Excel module. But when I try to
use the function, I get the error "ByRef argument type mismatch". Any hints?

here is the command line of the function:
CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean

Here's what I used in my Immediate Window

?CopyModule("basMyRoutine", ProjectA, ProjectB, True)

If I place the ProjectA and ProjectB inside quotes, I get a Type Mismatch
error, so I don't think that is the cause of my problem.

I welcome any recommendations. And thanks in advance.

 

[Janie]

NOPE! Same error "ByRef argument type mismatch"

any more ideas? This is the l-a-s-t requirement of my current project and I
am soooo ready to put this puppy to bed!!!

 

[Dave Peterson]

You didn't share how you set the FromVBProject or the ToVBProject variables.

Did you use something like this (modified from Chip's page):

Set FromVBProject = Application.Workbooks("Book1.xls").VBProject

 

[Janie]

well, that step wasn't in Chip's complete code which is what I copied and
pasted into a module. But what the heck --- gave it a try as you suggested,
nope, still get the same error message when I try to run from my Immediate
Window.

Dave, Joel or anybody else who has an idea -- Go to Chip's site
(http://cpearson.com/excel/vbe.htm) and copy the code from the section "Copy
A Module From One Project To Another" ... it begins after the the line:

The complete code is shown below:

and then tell me if it works for you.

Either something is missing (doubtful) or I am overlooking something
(probable)

 

[Dave Peterson]

You're going to need to set those ProjectA and ProjectB variables somehow.

Look at Chip's site again and you'll see where I copied that line from.

 

[Dave Peterson]

Try not running it from the immediate window.

Create a small subroutine:

Option Explicit
Sub testme()

dim ProjectA as VBIDE.vbproject
dim Projectb as VBIDE.vbproject
dim ret as boolean

Set ProjectA = Application.Workbooks("OldBook.xls").VBProject
Set ProjectB = Application.Workbooks("NewBook.xls").VBProject

ret = CopyModule("basMyRoutine",ProjectA,ProjectB,True)
end sub

I am STILL getting the same ByRef argument type mismatch error. Using your
recommendation, here's how the function starts:

Function CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean

Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim CompName As String
Dim S As String
Dim SlashPos As Long
Dim ExtPos As Long
Dim TempVBComp As VBIDE.VBComponent

Set ProjectA = Application.Workbooks("OldBook.xls").VBProject
Set ProjectB = Application.Workbooks("NewBook.xls").VBProject

in the Immediate Window I have:
?CopyModule("basMyRoutine",ProjectA,ProjectB,True)

the sound you hear is my head banging against the wall

 

[Janie]

Did you you suggested. Nothing happens. On the plus side I don't get the
error message. But ... I don't get the module in the other file, either.

I am overdosing on chocolate ... a sure sign I am getting v-e-r-y
frustrated. What the heck am I doing wrong???

 

[Dave Peterson]

Add a line to Chip's code and give it another try:

''''''''''''''''''''''''''''''''''''''''''''''
' Document modules (SheetX and ThisWorkbook)
' cannot be removed. So, if we are working with
' a document object, delete all code in that
' component and add the lines of FName
' back in to the module.
''''''''''''''''''''''''''''''''''''''''''''''
Set VBComp = Nothing '<--- added
Set VBComp = ToVBProject.VBComponents(CompName)

There was a slight bug in Chip's routine.

Did you you suggested. Nothing happens. On the plus side I don't get the
error message. But ... I don't get the module in the other file, either.

I am overdosing on chocolate ... a sure sign I am getting v-e-r-y
frustrated. What the heck am I doing wrong???

 

[Janie]

YES! YES! YES! YES!

Hosannas and lauds.

Add a line to Chip's code and give it another try:

''''''''''''''''''''''''''''''''''''''''''''''
' Document modules (SheetX and ThisWorkbook)
' cannot be removed. So, if we are working with
' a document object, delete all code in that
' component and add the lines of FName
' back in to the module.
''''''''''''''''''''''''''''''''''''''''''''''
Set VBComp = Nothing '<--- added
Set VBComp = ToVBProject.VBComponents(CompName)

There was a slight bug in Chip's routine.