Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    49
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Writing a function to another workbook

    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

  2. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    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:\Users\Maudibe\Desktop\Target.xlsx") 'CHANGE PATH
        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:
    Code1.png

    after running code:
    code2.png

    HTH,
    Maud

  3. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Alouso (2013-12-13),RetiredGeek (2013-12-13)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

  5. #4
    Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    49
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    You're welcome Peter.

    Good luck

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,492
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    ..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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •