Writing a function to another workbook

Topic

New Reply

Hi

When the software I’m working with generates a report to Excel, it uses a standard workbook(1) to collect and format the data into a report in a new workbook(2). It is usual to add custom code to worksheet(1) to further enhance the output of workbook(2).

I am writing some code in workbook(1), but in order for it to work in the macro free workbook(2) a UDF function needs to reside in workbook(2).

My question is, how can I write the function to workbook(2) from workbook(1)?

The function is a Hyperlink function with workbook(2) sheet range links.

Thanks for any advice

Peter

Reply | Quote

Replies

Peterinth,

To create a module in another workbook, you must first reference the Microsoft Visual Basic for Applications Extensibility 5.3 library. This is simply done within the VB editor. On the VB Editor menu bar click TOOLS > References… > scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 > tick the checkbox to its left > OK. That is the hardest part to do this project.

Note: To create a module and inject code into it from another workbook, the target book must be open. The following codes will create a standard module in the target Workbook and insert the UDF AreaOfCircle into it. Change out my sample UDF with yours listing each line of code in quotes followed by a carriage return except for the last line of the code. If you need to use the second code to open the target workbook, then change the path.

In the source workbook, which you describe as workbook1, place the one of the following 2 snippets in a standard module.

If the target workbook (Workbook2.xlsx) will be open, use this code:

Code:

Public Sub SendUDF()
    Dim module As VBComponent
    Set module = Workbooks(“Target.xlsx”).VBProject.VBComponents.Add(vbext_ct_StdModule)
    module.name = “UDFmodule”
    module.CodeModule.AddFromString “Public Function AreaOfCircle(radius As Double) As Double” & vbNewLine & _
                                    “‘CALCULATE THE AREA OF A CIRCLE USING REFERENCED RADIUS” & vbNewLine & _
                                    ”    AreaOfCircle = 22 / 7 * radius ^ 2″ & vbNewLine & _
                                    “End Function”
End Sub

If the target workbook (Workbook2.xlsx) will be closed, then used use this code:

Code:

Public Sub SendUDF()
    Dim module As VBComponent
    Application.Workbooks.Open (“C:UsersMaudibeDesktopTarget.xlsx”) [COLOR=”#008000″]’CHANGE PATH[/COLOR]
    Set module = Workbooks(“Target.xlsx”).VBProject.VBComponents.Add(vbext_ct_StdModule)
    module.name = “UDFmodule”
    module.CodeModule.AddFromString “Public Function AreaOfCircle(radius As Double) As Double” & vbNewLine & _
                                    “‘CALCULATE THE AREA OF A CIRCLE USING REFERENCED RADIUS” & vbNewLine & _
                                    ”    AreaOfCircle = 22 / 7 * radius ^ 2″ & vbNewLine & _
                                    “End Function”
End Sub

Prior to running code:
35746-Code1

after running code:
35747-code2

HTH,
Maud

Reply | Quote

Perhaps I am missing something, but one can call a function from a different open workbook with a command like:
=Workbook(1).xls!MyFunction()

Put the appropriate workbook name, the function name and add any parameters required.

Steve

Reply | Quote

Thanks for the replies.
This macro will be run by about 100 different machines in extended independent locations many times and it looks as though the code by Maudide will do the trick.

Many thanks.

Peter

Reply | Quote

You’re welcome Peter.

Good luck

Reply | Quote

Hi Maud

..so you can add UDF’s into a .xlsx file?????

I thought you could only have vba components in a ‘macro enabled’ file, like .xlsm or .xlsb

Am I missing something here????

zeddy

Reply | Quote

..so you can add UDF’s into a .xlsx file?????

AFAIK, you can add the UDF to the XLSX, but when you go to save the file, it can no longer be saved as the xlsX, but must be saved as the xlsM or xlsB, the xlsX will remain unchanged and thus will not contain any code.

It would be no different than opening an existing XLSX file and manually adding code to it. The code will work, but you can not save it in that form.

Steve

Reply | Quote