UDF Question

[Rob Kings]

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 Phillips]

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

Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Rob Kings]

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" <[email protected]> wrote in message
news:[email protected]...
> Very simple way, put the UDF in a separate normal code module, and copy
> that
> module over
>
> Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
> news:[email protected]...
>> 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Very simple way, put the UDF in a separate normal code module, and copy
> > that
> > module over
> >
> > Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

[Rob Kings]

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Very simple way, put the UDF in a separate normal code module, and copy
>> > that
>> > module over
>> >
>> > Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Rob Kings]

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("basFunctions").CodeModule.CountOfLines
sVB =
Workbooks("myUtils.xla").VBProject.VBComponents("basFunctions").CodeModule.Lines(1,
nlines)

Workbooks("Book1").VBProject.VBComponents.Add vbext_ct_StdModule
Workbooks("Book1").VBProject.VBComponents.Item(Workbooks("Book1").VBProject.VBComponents.Count).Name
= "basFunctions"
Workbooks("Book1").VBProject.VBComponents("basFunctions").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" <[email protected]> wrote in message
news:[email protected]...
> 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.Count
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > Very simple way, put the UDF in a separate normal code module, and
>>> > copy
>>> > that
>>> > module over
>>> >
>>> > Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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("basFunctions").CodeModule.C
ountOfLines
> sVB =
>
Workbooks("myUtils.xla").VBProject.VBComponents("basFunctions").CodeModule.L
ines(1,
> nlines)
>
> Workbooks("Book1").VBProject.VBComponents.Add vbext_ct_StdModule
>
Workbooks("Book1").VBProject.VBComponents.Item(Workbooks("Book1").VBProject.
VBComponents.Count).Name
> = "basFunctions"
>
Workbooks("Book1").VBProject.VBComponents("basFunctions").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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.Count
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > Very simple way, put the UDF in a separate normal code module, and
> >>> > copy
> >>> > that
> >>> > module over
> >>> >
> >>> > Workbooks("myAddin.xla").VBProject.VBComponents("Userform1").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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>