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]

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]

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]

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

 

[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

 

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