- Application.Run "'Some.xls'!SomeProc"
Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.
Forum Search
Forum Statistics
UsersTotal Post History
- Posts:
- 81456
- Topics:
- 18718
7-Day Post History
- New Posts:
- 0
- New Topics:
- 0
- Active Topics:
- 0
Our newest member
Other
-
FAQ
All times are UTC [ DST ]
UDF in another workbook
16 posts • Page 1 of 1
Please wait...
UDF in another workbook
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,
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,
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
I guess you mean something more complicated than:
It is certainly possible to save a module and import it, or even to add VBA on the fly.
It is certainly possible to save a module and import it, or even to add VBA on the fly.
Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
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,
=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,
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
I have tried this, and it seems to work:
In a cell: = AddHere()
In the current workbook:
In the other workbook:
I will post back import and export once I have checked it in Excel.
In a cell: = AddHere()
In the current workbook:
- Function AddHere()
- AddHere = Application.Run("'TheOther.xls'!AddThis", 2, 3)
- End Function
In the other workbook:
- Function AddThis(v1, v2)
- AddThis = v1 + v2
- End Function
I will post back import and export once I have checked it in Excel.
Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
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?
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?
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
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.
EDIT: This may be more useful, it is from code I have squirrelled away, but not tested.

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.
- Sub ExportModules()
- Dim fs As Object
- Dim f As Object
- Dim strMod As String
- Dim mdl As Object
- Dim i As Integer
- Set fs = CreateObject("Scripting.FileSystemObject")
- 'Set up the file.
- Set f = fs.CreateTextFile("C:\Docs\Test.txt")
- 'For each component in the project ...
- For Each mdl In Application.VBE.ActiveVBProject.VBComponents
- 'using the count of lines ...
- i = Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.CountOfLines
- 'put the code in a string ...
- If Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.CountOfLines > 0 Then
- strMod = Application.VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.Lines(1, i)
- End If
- 'and then write it to a file, first marking the start with
- 'some equal signs and the component name.
- f.writeline "'" & String(15, "=") & vbCrLf & "'" & mdl.Name _
- & vbCrLf & "'" & String(15, "=") & vbCrLf & strMod
- Next
- 'Close eveything
- f.Close
- Set fs = Nothing
- End Sub
- Sub ImportModule()
- Application.VBE.ActiveVBProject.VBComponents.Import ""C:\Docs\Test.txt""
- End Sub
EDIT: This may be more useful, it is from code I have squirrelled away, but not tested.
- Snippet:
- ' Started Excel above
- xl.Visible = True
- ' Add a new workbook
- Set xlBk = xl.Workbooks.Add
- ' Add a module
- Set xlMod = xlBk.VBProject.VBComponents.Add(1)
- ' Add a macro to the module...
- strCode = Getresource("macro")
- xlMod.CodeModule.AddFromString strCode
- ' Run the new macro!
- iData = xl.Run("ActW")
- Set xlMod = Nothing
- xlBk.Saved = True
- xl.Quit
Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
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.
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.
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
Well, now that you mention it,
=Book1.xls!AddMe(10)
Works for me.
=Book1.xls!AddMe(10)
Works for me.

Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
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!

I'll check in the morning and see what went wrong!
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
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.
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.
God cries a little bit every time someone builds a database.
-
Emtucifor - Guru
-
- Posts: 2835
- Joined: Fri May 30, 2008 9:30 pm
- Location: Bellingham, WA
Re: UDF in another workbook
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.
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 - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
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,
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,
-
ca8msm - LTD Admin
-
- Posts: 2492
- Joined: Wed Oct 10, 2007 6:49 pm
- Location: North East England
Re: UDF in another workbook
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:
- <%
- Response.ContentType = "application/vnd.ms-excel"
- %>
- <TABLE>
- <TR>
- <TD>
- <!-- Cell : A1 -->
- 2
- </TD>
- </TR>
- <TR>
- <TD>
- <!-- Cell : A2 -->
- 3
- </TD>
- </TR>
- <TR>
- <TD>
- <!-- Cell : A3 -->
- =Template.xls!AddThis(A1,A2)
- </TD>
- </TR>
- </TABLE>
Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
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)
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.
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)
Excel wrote: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.
God cries a little bit every time someone builds a database.
-
Emtucifor - Guru
-
- Posts: 2835
- Joined: Fri May 30, 2008 9:30 pm
- Location: Bellingham, WA
Re: UDF in another workbook
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.
Stop quoting laws to us. We carry swords.
-
Remou - LTD Admin
-
- Posts: 5414
- Joined: Sun Oct 14, 2007 11:26 am
Re: UDF in another workbook
At least I detailed the syntax for every situation. 

God cries a little bit every time someone builds a database.
-
Emtucifor - Guru
-
- Posts: 2835
- Joined: Fri May 30, 2008 9:30 pm
- Location: Bellingham, WA
16 posts • Page 1 of 1
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.