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