I have complicated worksheet which does some long calculations
I want to call it as function from another worksheet
Any thoughts would be appreciated.
Thanks
I have complicated worksheet which does some long calculations
I want to call it as function from another worksheet
Any thoughts would be appreciated.
Thanks
I've thought about this several times myself. I'll be interested to see if others have ideas for accomplishing this sort of thing.
Without specifics, it's hard to make specific recommendations. To date, my solution for this kind scenario usually involves converting the spreadsheet that I want to call as a "function" into a VBA User-Defined Function. UDF's can be called from any worksheet cell just like native functions.
The main drawback to using UDF's is that they run significantly slower than native functions (on the order of milliseconds per function call rather than microseconds needed for most native functions). I find this is only noticeable, though, when I have hundreds to thousands of function calls per calculation event.
You can do this by creating an "Add In" for the function. An add in is something added to a spreadsheet to give it additional functionality. For example, Excel ships with several default add in such as the "Data Analysis" pack. Add ins can be used to store functions and other code for use by other spreadsheets. This type of add in will generate a .xla file.
You can also develop COM addins using Visual Basic 5.0 (or greater), VB.NET or Office Developer Edition. Building an add in in this manner will essentially generate a DLL file which can then be distributed.
Read up on building add-ins. Also, buy one of the following two books (both are excellent) and provide in depth instruction on using and building Add-ins
- MS-Excel 2003 Power Programming with VBA By John Walkenbach
- Professional Excel Development - The Definitive Guide to Developing Applications Using MS-Excel and VBA By Bullen, Bovey, and Green
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks