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.

 

[Joel]

Try adding these declarations to the routine that calls CopyModule. this
always works for me.

Dim ModuleName As String
Dim FromVBProject As Variant
Dim ToVBProject As Variant
Dim OverwriteExisting As Boolean
CopyModule("basMyRoutine", ProjectA, ProjectB, True)

 

[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.