LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1884
Members Online:
2
Guests Online:
94

Total Post History
Posts:
81461
Topics:
18719

7-Day Post History
New Posts:
4
New Topics:
1
Active Topics:
2

Our newest member
manails232x

Other

FAQ
All times are UTC [ DST ]

UDF in another workbook

Please wait...

UDF in another workbook

Postby ca8msm on Thu Feb 19, 2009 10:00 am

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,
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Remou on Thu Feb 19, 2009 11:42 am

I guess you mean something more complicated than:

  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.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: UDF in another workbook

Postby ca8msm on Thu Feb 19, 2009 4:19 pm

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,
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Remou on Thu Feb 19, 2009 4:56 pm

I have tried this, and it seems to work:

In a cell: = AddHere()

In the current workbook:

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


In the other workbook:

  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.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: UDF in another workbook

Postby ca8msm on Thu Feb 19, 2009 5:36 pm

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?
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Remou on Thu Feb 19, 2009 6:52 pm

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.

  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.

  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
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am

Re: UDF in another workbook

Postby ca8msm on Thu Feb 19, 2009 9:46 pm

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.
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Remou on Thu Feb 19, 2009 9:57 pm

Well, now that you mention it,

=Book1.xls!AddMe(10)

Works for me. :blush:
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: UDF in another workbook

Postby ca8msm on Thu Feb 19, 2009 11:58 pm

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

I'll check in the morning and see what went wrong!
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Emtucifor on Fri Feb 20, 2009 3:36 am

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.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA

Re: UDF in another workbook

Postby ca8msm on Fri Feb 20, 2009 11:33 am

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.
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby ca8msm on Fri Feb 20, 2009 11:38 am

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,
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England
Unrated

Re: UDF in another workbook

Postby Remou on Fri Feb 20, 2009 1:58 pm

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:

  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>
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: UDF in another workbook

Postby Emtucifor on Fri Feb 20, 2009 7:56 pm

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)

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.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: UDF in another workbook

Postby Remou on Fri Feb 20, 2009 8:45 pm

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.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: UDF in another workbook

Postby Emtucifor on Fri Feb 20, 2009 9:49 pm

At least I detailed the syntax for every situation. :)
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated