Hi

I'm using some User Defined Functions that currently reside in an add-in.
The addin is used to import data into a spreadsheet. However, the resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but even
declared Public is doesn't appear that I can use a function in a sheet
module as a UDF. It seems to need to be either in "This Workbook" or in a
code module.

Any ideas?

Cheers

Rob

Very simple way, put the UDF in a separate normal code module, and copy that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but even
declared Public is doesn't appear that I can use a function in a sheet
module as a UDF. It seems to need to be either in "This Workbook" or in a
code module.

Any ideas?

Cheers

Rob

Bob

Thanks for the answer. Thinking about it more. Is there no way to code the
equivalent of me dragging and dropping the module in the IDE?

Cheers

Rob

"Bob Phillips" wrote in message
...
Very simple way, put the UDF in a separate normal code module, and copy
that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but even
declared Public is doesn't appear that I can use a function in a sheet
module as a UDF. It seems to need to be either in "This Workbook" or in a
code module.

Any ideas?

Cheers

Rob

No, but you could rebuild it from bottom-up in an existing code module.

But why, the way presented is very simple?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Bob

Thanks for the answer. Thinking about it more. Is there no way to code the
equivalent of me dragging and dropping the module in the IDE?

Cheers

Rob

"Bob Phillips" wrote in message
...
Very simple way, put the UDF in a separate normal code module, and copy
that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an
add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but even
declared Public is doesn't appear that I can use a function in a sheet
module as a UDF. It seems to need to be either in "This Workbook" or in
a
code module.

Any ideas?

Cheers

Rob

Bob

I was just trying to avoid the external file I/O as thats the likely failure
point (permissions etc.) Also, since the modules can be dragged and dropped
I thought there might be some properties that would equate to that. I see
there is a .CodeModule.AddFromString but I can't find a way to represent the
code module as a string.

The other problem I have is that when I tried to test the code I got some
errors. I simplfied things (or tried to)

Debug.Print Workbooks("myUtils.xla").VBProject.VBComponents.Co unt

Gives me an error 1004 Method 'VBProject' of object '_Workbook' failed

any ideas? I wondered if I needed to add any references for this to work,
but the intellisense is all there.

Cheers

Rob

"Bob Phillips" wrote in message
...
No, but you could rebuild it from bottom-up in an existing code module.

But why, the way presented is very simple?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Bob

Thanks for the answer. Thinking about it more. Is there no way to code
the
equivalent of me dragging and dropping the module in the IDE?

Cheers

Rob

"Bob Phillips" wrote in message
...
Very simple way, put the UDF in a separate normal code module, and copy
that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an
add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but even
declared Public is doesn't appear that I can use a function in a sheet
module as a UDF. It seems to need to be either in "This Workbook" or
in
a
code module.

Any ideas?

Cheers

Rob

to answer my own question it is possible. I mucked about a bit more and got
to

Dim nlines As Integer
Dim sVB As String

nlines =
Workbooks("myUtils.xla").VBProject.VBComponents("b asFunctions").CodeModule.CountOfLines
sVB =
Workbooks("myUtils.xla").VBProject.VBComponents("b asFunctions").CodeModule.Lines(1,
nlines)

Workbooks("Book1").VBProject.VBComponents.Add vbext_ct_StdModule
Workbooks("Book1").VBProject.VBComponents.Item(Wor kbooks("Book1").VBProject.VBComponents.Count).Name
= "basFunctions"
Workbooks("Book1").VBProject.VBComponents("basFunc tions").CodeModule.AddFromString
sVB

This is pretty close to the code in
http://support.microsoft.com/?kbid=245801 which I found when looking for
details of the constant vbext_ct_StdModule

The problem with the code not working before is that it is necessary to goto
Tools | Macro | Security and select "Trust Access to Visual Basic Project"

Since this is going to be installed on multple machines (for multiple users)
I don't yet know whether this requirement will be a "Show-stopper"

Rob
"Rob Kings" wrote in message
...
Bob

I was just trying to avoid the external file I/O as thats the likely
failure point (permissions etc.) Also, since the modules can be dragged
and dropped I thought there might be some properties that would equate to
that. I see there is a .CodeModule.AddFromString but I can't find a way to
represent the code module as a string.

The other problem I have is that when I tried to test the code I got some
errors. I simplfied things (or tried to)

Debug.Print Workbooks("myUtils.xla").VBProject.VBComponents.Co unt

Gives me an error 1004 Method 'VBProject' of object '_Workbook' failed

any ideas? I wondered if I needed to add any references for this to work,
but the intellisense is all there.

Cheers

Rob

"Bob Phillips" wrote in message
...
No, but you could rebuild it from bottom-up in an existing code module.

But why, the way presented is very simple?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Bob

Thanks for the answer. Thinking about it more. Is there no way to code
the
equivalent of me dragging and dropping the module in the IDE?

Cheers

Rob

"Bob Phillips" wrote in message
...
Very simple way, put the UDF in a separate normal code module, and
copy
that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an
add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but
even
declared Public is doesn't appear that I can use a function in a
sheet
module as a UDF. It seems to need to be either in "This Workbook" or
in
a
code module.

Any ideas?

Cheers

Rob

That restriction will apply whatever method of creating code that you use,
as it applies to trying to add code to the other workbook.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
to answer my own question it is possible. I mucked about a bit more and
got
to

Dim nlines As Integer
Dim sVB As String

nlines =

Workbooks("myUtils.xla").VBProject.VBComponents("b asFunctions").CodeModule.C
ountOfLines
sVB =

Workbooks("myUtils.xla").VBProject.VBComponents("b asFunctions").CodeModule.L
ines(1,
nlines)

Workbooks("Book1").VBProject.VBComponents.Add vbext_ct_StdModule

Workbooks("Book1").VBProject.VBComponents.Item(Wor kbooks("Book1").VBProject.
VBComponents.Count).Name
= "basFunctions"

Workbooks("Book1").VBProject.VBComponents("basFunc tions").CodeModule.AddFrom
String
sVB

This is pretty close to the code in
http://support.microsoft.com/?kbid=245801 which I found when looking for
details of the constant vbext_ct_StdModule

The problem with the code not working before is that it is necessary to
goto
Tools | Macro | Security and select "Trust Access to Visual Basic Project"

Since this is going to be installed on multple machines (for multiple
users)
I don't yet know whether this requirement will be a "Show-stopper"

Rob
"Rob Kings" wrote in message
...
Bob

I was just trying to avoid the external file I/O as thats the likely
failure point (permissions etc.) Also, since the modules can be dragged
and dropped I thought there might be some properties that would equate
to
that. I see there is a .CodeModule.AddFromString but I can't find a way
to
represent the code module as a string.

The other problem I have is that when I tried to test the code I got
some
errors. I simplfied things (or tried to)

Debug.Print Workbooks("myUtils.xla").VBProject.VBComponents.Co unt

Gives me an error 1004 Method 'VBProject' of object '_Workbook' failed

any ideas? I wondered if I needed to add any references for this to
work,
but the intellisense is all there.

Cheers

Rob

"Bob Phillips" wrote in message
...
No, but you could rebuild it from bottom-up in an existing code module.

But why, the way presented is very simple?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Bob

Thanks for the answer. Thinking about it more. Is there no way to code
the
equivalent of me dragging and dropping the module in the IDE?

Cheers

Rob

"Bob Phillips" wrote in message
...
Very simple way, put the UDF in a separate normal code module, and
copy
that
module over

Workbooks("myAddin.xla").VBProject.VBComponents("U serform1").Export
_
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob Kings" wrote in message
...
Hi

I'm using some User Defined Functions that currently reside in an
add-in.
The addin is used to import data into a spreadsheet. However, the
resultant
XLS isn't "portable" since the other users won't necessarily have
the
add-in.

Is there a way to copy the UDF into the new workbook?

I do copy sheets (and charts) from the addin to the workbook, but
even
declared Public is doesn't appear that I can use a function in a
sheet
module as a UDF. It seems to need to be either in "This Workbook"
or
in
a
code module.

Any ideas?

Cheers

Rob