UDF in another workbook

[ca8msm]

I need to be able to use a UDF from another workbook. I know I can manually do this if I'm just sat at the machine (http://support.microsoft.com/kb/141288), however the way I'm doing this is via an automated process that opens up two workbooks and executes some macros. I've created a UDF in one of the workbooks and I need to use this as part of a formula in the other one.

Does anyone know if it's possible to do this?

P.S Another option I've thought of (but don't know whether it is possible) is to see if there is a way to actually create a UDF in VBA code and register it. I may be being overly optimistic here though...

Thanks,

[Remou]

I guess you mean something more complicated than:

textLine number Off | Hide | Select all

1. Application.Run "'Some.xls'!SomeProc"

It is certainly possible to save a module and import it, or even to add VBA on the fly.

[ca8msm]

Yeah, it's slightly different to that. I'm in the worksheet and I want to reference the UDF in the other workbook from a cell formula e.g.

=MyFunction("A1")

This works from the same workbook, but not from the other one.

I'm not adverse to somehow recreating the function at run time if needed or if it's my only option.

Thanks,

[Remou]

I have tried this, and it seems to work:

In a cell: = AddHere()

In the current workbook:

textLine number Off | Hide | Select all

1. Function AddHere()
2. AddHere = Application.Run("'TheOther.xls'!AddThis", 2, 3)
3. End Function

In the other workbook:

textLine number Off | Hide | Select all

1. Function AddThis(v1, v2)
2.     AddThis = v1 + v2
3. End Function

I will post back import and export once I have checked it in Excel.

[ca8msm]

It's just that I don't have the ability to manually create the function in the first workbook as this was created at run time. Maybe it will help if I explain in a bit more detail:

1. My .NET app opens template.xls which contains a bunch of functions in a module.
2. My .NET app creates a new file (file1.xls) and opens it
3. In file1.xls, I want to have a cell formula use a function that is located in template.xls

So, file1.xls doesn't have any functions in it, and unless I can use .NET to register any within that file then I can't add any as there is no human intervention in this process.

The link in my first post shows how to do what I need, but that involves manually doing something and in this case it isn't an option.

Does that make sense?

[Remou]

So I clearly cannot help you with the .Net, however, I can throw stuff at you in case it gives you an idea

I guess you know that code in the personal.xls file can be referenced by all workbooks? It is also possible to add a workbook as a reference to a VBProject, unfortunately I cannot find how to do this through code in my version of Excel.

Here is some VBA code for saving all modules and loading from text, for what its worth.

textLine number Off | Hide | Select all

1. Sub ExportModules()
2.     Dim fs As Object
3.     Dim f As Object
4.     Dim strMod As String
5.     Dim mdl As Object
6.     Dim i As Integer
7.      
8.     Set fs = CreateObject("Scripting.FileSystemObject")
9.    
10.     'Set up the file.
11.     Set f = fs.CreateTextFile("C:\Docs\Test.txt")
12.    
13.     'For each component in the project ...
14.     For Each mdl In Application.VBE.ActiveVBProject.VBComponents
15.         'using the count of lines ...
16.         i = Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.CountOfLines
17.         'put the code in a string ...
18.         If Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.CountOfLines > 0 Then
19.            strMod = Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.Lines(1, i)
20.         End If
21.         'and then write it to a file, first marking the start with
22.         'some equal signs and the component name.
23.         f.writeline "'" & String(15, "=") & vbCrLf & "'" & mdl.Name _
24.             & vbCrLf & "'" & String(15, "=") & vbCrLf & strMod
25.     Next
26.    
27.     'Close eveything
28.     f.Close
29.     Set fs = Nothing
30. End Sub
31.  
32. Sub ImportModule()
33.     Application.VBE.ActiveVBProject.VBComponents.Import ""C:\Docs\Test.txt""
34. End Sub

EDIT: This may be more useful, it is from code I have squirrelled away, but not tested.

textLine number Off | Hide | Select all

1. Snippet:
2. ' Started Excel above
3. xl.Visible = True
4. ' Add a new workbook
5. Set xlBk = xl.Workbooks.Add
6. ' Add a module
7. Set xlMod = xlBk.VBProject.VBComponents.Add(1)
8. ' Add a macro to the module...
9. strCode = Getresource("macro")
10. xlMod.CodeModule.AddFromString strCode
11. ' Run the new macro!
12. iData = xl.Run("ActW")
13. Set xlMod = Nothing
14. xlBk.Saved = True
15. xl.Quit

[ca8msm]

Hmm, that looks interesting I'll look into whether there is any way of manipluating my .NET code to include the ability to add macros to the new excel files that I create. Thanks.

I guess what I really hoping for though would be that I could just use a simple cell formula to reference the second workbook e.g.

=template.xls!MyFunction("A1")

but that was probably overly optimistic.

[Remou]

Well, now that you mention it,

=Book1.xls!AddMe(10)

Works for me.

[ca8msm]

Hmm, I just tried it too and it worked...I wonder what went wrong in my automated process!

I'll check in the morning and see what went wrong!

[Emtucifor]

Sorry I didn't check this earlier, I knew the answer. Yes, it is =workbookname.xls!Function(params)

I think you can even use full path names to an Excel file if you use square brackets as in =[c:\blah.xls]!Function(param).
There is also a notation for referring to cells in a particular workbook, which I forget right now, but you can find it simply by pressing = in a worksheet cell, then use the mouse to click to another worksheet and click a cell, then press Enter.

[ca8msm]

Hmm, something strange is going on here. If I open both files manually and enter the formula then it works, however if I do it through code it doesn't (so I guess it can't see the template file for some reason).

I'd like to try the method Erik suggested ([c:\blah.xls]!Function(param)) but that syntax isn't quite right so I'm just trying to find out exactly what it should be.

[ca8msm]

Ok, the correct syntax is just 'c:\template.xls'!MyFunction(params).

There's something making is still not work but I think that's my code not excel so I'm fairly happy that this bit is correct.

Thanks,

[Remou]

I think that it is the single quote that is causing the problem. I found, too, that even if you type in the full name and path, you cannot reference a procedure in a closed workbook, which makes the path part redundant as it will be converted by Excel to the short form (template.xls!MyProc). I put this in a textfile and opened it with Excel and it worked:

textLine number Off | Hide | Select all

1. <%
2. Response.ContentType = "application/vnd.ms-excel"
3. %>
4. <TABLE>
5. <TR>
6. <TD>
7. <!-- Cell : A1 -->
8. 2
9. </TD>
10. </TR>
11. <TR>
12. <TD>
13. <!-- Cell : A2 -->
14. 3
15. </TD>
16. </TR>
17. <TR>
18. <TD>
19. <!-- Cell : A3 -->
20. =Template.xls!AddThis(A1,A2)
21. </TD>
22. </TR>
23. </TABLE>

[Emtucifor]

Er, sorry if I gave you misinformation. Maybe I am getting cell reference notation confused with VB function reference notation.

I just tried a cell reference between workbooks and the result was:

=[Book8]Sheet2!$A$1

Then, I saved and closed the new unnamed workbook Book8 and the cell reference changed to:

='C:\temp\[eraseme.xls]Sheet2'!$A$1

Then I reopened the target workbook (eraseme.xls) and added a function, then referenced it in my main workbook (press Shift-F3 and select category User Defined to get this).

=eraseme.xls!Square(3)

I closed eraseme.xls and the function changed to

='C:\temp\eraseme.xls'!Square(2)

And the result value stayed. But when I changed the formula to Square(3) I got an error:

=eraseme.xls!Square(3)

Microsoft Office Excel cannot find 'Square' on 'eraseme.xls'. There are two possible reasons:

• The name you specified may not be defined.
• The name you specified is defined as something other than a rectangular cell reference. Check the name and try again.

Then I tried adding in the module name:

='C:\temp\eraseme.xls'!Module1.Square(2)

This still didn't work, but when I opened the eraseme.xls workbook, it immediately calculated correctly.

So it looks like you can reference functions in other workbooks, but you can only run those functions when that workbook is open, though the calculated value will be retained after you close the workbook.

[Remou]

though the calculated value will be retained after you close the workbook.

That is normal for Excel. When I opened the example I posted without first opening the template workbook, it asked me if I wished to recalculated the formula that referenced another workbook - this is the same as for workbooks that reference external ranges.

[Emtucifor]

At least I detailed the syntax for every situation.