Introduction

This article demonstrates how to overcome the limitations of user defined functions (UDF) in Excel:

When used conventionally (i.e., not as an array function), UDFs can return only a single value.

When more than one value should be returned, this is achieved by selecting the correctly sized range in advance and concurrently pressing <CTRL><SHIFT><ENTER>. This tells Excel that the formula should be used as an Array Formula.

The example shows how to return tables of values (e.g., results of a database query) as a result of a single function entry that was entered conventionally (i.e., without pressing <CTRL><SHIFT><ENTER>). The results stay linked with the data source, and therefore are updated automatically. This way, it is possible to create functions that always return the current result of a database query or a calculation that returns more than a single value!

Background

Most of the end users of database applications conduct further data processing, preferably in Excel. Standard ways of exporting data (file exports) always involve tedious and repetitive manual steps.

User defined functions are an ideal way to accomplish that task:

they are easy to use and well known to Excel users,

parameters (e.g., date) can easily be updated,

updates are done automatically.

System requirements and installation

Microsoft Excel 2002 or higher.

Microsoft JET 4.0 OLE DB Provider. This provider is automatically installed with Microsoft Office, otherwise it can be downloaded from here.

Demo project installation

Download the demo project, and copy the files excelmvf.dll, excelmvf.xla, excelmvf.mdb, adortl60.bpl, dbrtl60.bpl, and rtl60.bpl to C:\excelmvf. It is important to strictly stick to that path since the absolute path is used in the declaration of the DLL-functions within excelmvf.xla (the alternative would be to copy the files into your system directories, but that makes cleaning up more difficult).

To start the add-in, you can either open the excelmvf.xla file via the FileOpen-dialog or use the add-in manager.

Excel Security Settings have to be set to allow execution of macros. If the functions getCustomers and getOrderItems show up in the Excel's function input dialog within the section "User defined functions", then the functions are ready to be used.

Function syntax and parameters

getCustomers(fieldList as String, sortingCriteria as String, header as Boolean)

fieldList: String with comma or ; delimited list of columns to be displayed.

Building the sources

excelmvfVC2005.zip contains a version for Microsoft VC++ 2005 Express Edition (this edition is free and can be downloaded here).

excelmvf.zip contains a version for C++ Builder 6.0.

The main difference is the access to the database. Some proprietary VCL classes are used in the C++ builder version, whereas the VC version uses basic ADO.

For the Visual C++ version, follow these steps:

Download and install Microsoft VC++ 2005 Express Edition and the SDK (follow the instructions given in VC++ 2005 for that).

Unzip the contents of excelmvfVC2005.zip to a directory of your choice.

Open the VC solution excelmvf; you should now be ready to build the project successfully. To enable debugging of the DLL, you must set the path to your Excel installation in the project settings.

For the Borland C++ Builder Version:

Unzip the contents of excelmvf.zip to a directory of your choice.

In C++ Builder, open the project file excelmvf.bpr. You should now be ready to build the project successfully. To enable debugging of the DLL, you must set the path to your Excel installation (menu: Run, Parameters).

The example add-in is designed in a way to make adaptations for own purposes as easy as possible. For a detailed description of all the steps necessary to add a new function, see Adding function getArticles. For a description of the mechanism used to circumvent the user defined function restrictions going on here...

Full article: Excel functions unlimited

Developers of database-related applications regularly get confronted with user requests to make the data available in MS Excel for further processing. File based exports (CSV, XLS) often don't do the job because there is no way to select the data and they do not provide automatic updates. Excel queries on the contrary are difficult to set up and manage. The usage of user defined functions would provide a simple and flexible way to get the data into Excel sheets but there are some built-in limitations to Excel functions which prevent the usage of tables as return values. The main limitation is the fact that Excel does not allow functions to write values into cells outside the range that was selected upon the function call. The following example will demonstrate this:

When this function is called from inside the code editor everything will work fine and the value of 100 will be written beneath the cell which was active at call time. But if the function is called from within a cell (=myuserdefinedfunction()) an error will occur and the cell beneath will stay empty.

The second difficulty is to return more than a single value as the result of a function call. Although this is always possible with matrix formulas, the following example will show you the limitations:

This function fills a two-dimensional array with values depending on the value of the parameter base. If a range of 6x6 cells is selected within Excel and the expression "=myMatrixformula(2)" is entered by simultaneously pressing <CTL><SHIFT><ENTER> the function will be used as a matrix formula and show the following result:

Although the function only returns an array of 5x5 values, Excel fills the whole range that was selected at call time. If the function does not provide enough values, the redundant cells are filled with #NA (not available) values. If the range selected is too small, Excel would only show as many values as are necessary for filling this range and suppress the rest (without an error message or any other hint). The requirement of pressing <CTRL><SHIFT><ENTER> simultaneously is another drawback of matrix formulas that does not make them user friendly and therefore should be avoided. So, by now we have defined the prerequisites that our functions should fulfill:

no range should be selected in advance,

it should not be necessary to press <CTRL><SHIFT><ENTER> to enter the function,

if the size of the returned data-table changes due to insertions or deletions in the database or due to changes in the functions parameters, the size of the range in Excel should be adjusted automatically.

Besides these functional requirements, there are some other functionalities that these functions should contain:

Number and order of the columns to be displayed should be changeable.

Sorting should be possible.

Column headers should be displayed or not.

Filtering should be possible.

To demonstrate the functionality of these functions, I have developed a sample add-in that returns values from a simple MS-Access database.

The getCustomers function returns all the rows from the database table, customers. The parameter fieldlist expects a list of columns to be displayed, the parameter sortingCriterias expects a list of sorting parameters including sorting directions and the parameter Headers decides whether column headers should be displayed or not. All the parameters are optional, so if you enter =getCustomers(), the function returns the whole table of customers with no sorting including column headers. Function getOrderItems is similar to getCustomers, but it additionally provides a possibility to filter the values to be displayed by entering the parameters startDate and endDate. All the parameters are optional too, so =getOrderItems() displays a table of all single items in all orders in the database:

Although the functions were entered in a single cell (getcustomers() in cell A1 and getOrderItems() in cell A10), the results look like as if they were entered as a matrix formula with the correct range selected in advance. Parameter changes can be carried out by editing a random cell in the result table and simultaneously pressing<CTRL><SHIFT><ENTER>, even if the size of the result table changes due to the parameter changes. This behavior is achieved through the following trick:

Within the initial function call the database query is executed to get the size of the result table. The formula and the parameters are stored for later usage and the function returns an arbitrary value (#n/a).

Excel automatically triggers a Calculate event after each function call. This event is caught from the add-in.

During the processing of the Calculate event a range big enough to hold the result table is selected and the previously stored formula is inserted as an array formula into this range. Since the original function call is already processed there is no write protection for cells outside the function scope any more.

The insertion of the array formula within the Calculate event immediately triggers a further call of the function. The size of the selected array now matches the size of the required array and thus the result can be inserted.

Since the processing is usually very fast, repeated function calls are not visible to the user. The functions behave just like any other Excel function, but now they have the ability to return table data.

The collaboration between the DLL functions and the calculate event handling is somewhat tricky and it took some time especially to explore the behavior of Excel, but now, as the mechanism is up and running it is quite easy to use for any view or table in any database environment. I have tried to build the example add-in in a way that it can serve as a kind of framework for the development of own add-ins. C++ development skills are still required but with the help of this article customization can be done rather easily.

Excelmvf.xla is very simple, since it mostly contains function declarations only. There are functions getCustomers and getOrderItems declared as public so that they appear within Excel as user defined functions and the functions getCustomers_dll and getOrderItems_dll respectively, which are declared as private. The reason for not immediately declaring the DLL functions as public is because of the hidden parameter currentCell, which passes an IDispatch pointer to the calling cell to the DLL function. More on this later. To adapt this xla simply add the public and the associated private declarations of your functions.

An important role is played by the function setExcelApp, which is automatically called at startup and it passes a pointer to the Excel application object to the dynamic link library. This pointer is later on used in the DLL to initialize the event handling mechanism.

The dynamic link library excelmvf.dll is the main component of the add-In. It consists of a DLL main module and several classes, of which the most important are explained below.

In the DllEntryPoint function, which is also automatically called when the DLL is loaded, the connection string for the ADO database connection is assembled from the path of the DLL module and the file name of the database, in our case excelmvf.mdb. The usage of ADO (or OLE-DB, I never look through the Microsoft notations...) makes the use of different database environments very easy since only this connection string has to be changed to work with different database products. The following is an example for a connection string to an Oracle database via the Microsoft OLE-DB Bridge for ODBC:

Besides this adoption of the data source, there are no further changes required for database access.

The functions getCustomers_dll and getOrderItems_dll make up the interface to Excel, since they are called when the user enters the functions getCustomers and getOrderItems respectively in Excel. These functions will be referred to as excelmvf functions. Since they are essential to understand the mode of operation, the simplest function getCustomers_dll is shown in listing 1.

Listing 1: function getCustomers_dll

The main functions collaborate closely with the TExcelEventDispatcher::Calculate function, which is automatically called from the Excel event mechanism after each call of any Excel function. This function is implemented in the class TExcelEventDispatcher, which is derived from Borland's TEventDispatcher class and uses the pointer to the Excel-Application object passed from the .xla file at startup to establish a connection with Excel's event mechanism. Listing 2 shows this function:

At first glance, the frequent use of the function TComHelper::Autowrap in both listings stands out. The purpose of this function is to call Excel functions or read/set property values of Excel objects within the DLL. It needs a pointer to the IDispatch interface of the object whose methods need to be called or properties need to be read/written. In the case of excelmvf functions, we have to interfere with the range from which the function call originates. The pointer to this range is passed as the last argument pcell from the excelmvf functions.

It is helpful to distinguish two types of function calls to the excelmvf functions:

Type 1 calls originate from a range, which comprises of a single cell. Calls of that type arise from a new user input or, as we will see, as a result of the processing calls of type 2.

Type 2 calls originate from a range that is made up of at least two adjacent cells. They arise from recalculations of the already existing excelmvf functions due to Excel's recalculation mechanism or user changes in the function parameters.

The first few lines in listing 1 figure out whether Excel's function input dialog is open. Excel makes function calls during parameter validation and shows the intermediate results in this dialog. Because only single values can be displayed here excelmvf functions always return #n/a when the function call originates from this dialog.

In the next few lines properties of the calling Excel range are read out for later validation and storage. Then, a call to the requestController.getCustomers function returns a pointer to a DataSet object that represents the results of the database query.

Afterwards, the type of the calling range is analyzed. Only for Type 2 calls the calling range is of type SAFEARRAY-VARIANT and the original formula has to be read out from the first cell of the array. This is not necessary for Type 1 calls.

In the next step, the size of the calling range and the required size for the return values are compared. The size always differs for Type 1 calls, but is different for Type 2 calls when the size of the result table is changed (e.g., when new records are added or when the user changes the parameters of the function).

If size-comparison says that the current range is not properly dimensioned to hold the database query the function stores all the required values in the (static) TrequestController::funcParams struct and then returns #n/a value(s).

Excel now triggers a calculate event and therefore our TExcelEventDispatcher::Calculate function is called (Listing 2). The event has to be processed when the deleting flag is not set (this flag is explained later on) and the state of the funcParamsstruct is set in the previous call to the excelmvf function. If this is not the case the function deals with an event triggered by a conventional Excel function like e.g. SUM() and has nothing to do.

After deciding which of the excelmvf functions were originally called by querying the FunctionID, a pointer to the respective dataset is retrieved by calling the appropriate get function of the requestController. If a valid dataset is returned, the size of the result set is determined. At this point, it is important if the excelmvf function call was of Type 1 or Type 2.

Type 1 calls (originating from a single cell) are now processed by extending the range in Excel to the size needed to contain the results. Then the original function is copied into this extended range as a FormulaArray thus triggering an immediate recall of the original excelmvf function. In this second call the comparison between the selected range and the needed range does not return a difference any more and therefore the results can be inserted. Voilà.

A call of Type 2 needs some additional processing to turn it into a call of type 1. The range originally selected has to be deleted, since it is not correctly dimensioned to take the results any more. This is done by simply inserting an empty VARIANT value into the whole original array. Before doing so the deleting flag has to be set to true to avoid processing the triggered calculate event. Afterwards the deleting flag is reset to false and the original formula is inserted into the upper left cell of the original range. This results in a function call of Type 1, which is then processed as described above.

Before I describe the necessary adoptions to the above code to fit our own needs some important classes that deal with data handling should be introduced. As you could already see in the listings there are several calls to the methods of TDataset objects. These calls retrieve either the information about the size of the result table or the result table itself. TDataset derived classes thus represent the result of a database query which in turn are made up of records represented by classes derived from the class TRecord. To create a new function that retrieves data from a different table or view it is necessary to derive new classes from TDataset and TRecord respectively. Since TDataset expects its respective TRecord class as a template parameter upon derivation we first have to derive from TRecord. A look at the TCustomerRecord header file shows what has to be considered next.

The static array of TFieldDescriptor values contains descriptions of the fields contained in the database query. At least one TFieldDescriptor value has to be defined for each field but defining more of them with different values for the fieldNameAlias member opens the possibility to identify fields with different synonyms. In our example, the postal code field will show up in the result list when the user enters one of the following values in the fieldList parameter: Postal code, Postalcode, ZIP, Postleitzahl, PLZ. The defaultFieldList string contains a list of fields that should be displayed as default i.e. when the user does not enter a value for the fieldList parameter. In the private section of the class all the fields of the query are defined as VARIANT members. To make them accessible to the respective TDataset derivation, without having to define getter-functions for all the query fields, the TDataset class is declared as a friend class. And finally the [] operator has to be defined the same way as it is done in TCustomerRecord.cpp.

Now the new dataset class can be derived from TDataset. Besides the previously derived derivation of the TRecordset class as template parameter the following member variables and member functions are needed to be defined or overloaded: The SQL member contains the SQL query to retrieve the desired dataset from the database. For the TCustomerDataset it simply is:

Select * from customers

The retrieve function fetches the results of the query from the database and stores them in a std::set container. The implementation can strictly follow the example from TCustomerDataset, only the while (!query->Eof) loop has to be filled with the number and names of the fields retrieved from the query. Finally, it is also needed to overload the < operator. This is necessary to avoid duplicate and performance decreasing database queries within short intervals. This needs a small explanation: as mentioned above in the description of the code in listing 1 and 2 there are two calls of the getRangeSize function within a very short interval (one in the excelmvf function and one in the calculate event). To be able to tell the range size of the resulting table it is required to execute the database query when the first call is executed. To avoid a further query in the second call the results of the query are stored in a local cache made up of std::set container for a short period of time (in this implementation for 10 seconds, can be set in the static member variable TrequestController::cachingperiod). The < operator is required to find the dataset in the container within this cachingperiod.

The functions that are defined in the example add-in and all the functions that may be added are bound together by the class TRequestController. For each function to be implemented this class contains a respective getter function, in our example they are getCustomers and getOrderItems. Besides that it contains the containers to implement the local cache for avoiding dispensable database queries.

To avoid time consuming debugging sessions when trying to implement a excelmvf function I have described the whole process of adding a new function in detail. You can find this description here (Adding function getArticles)

An add-in based on this framework is in use in a 160+ employees company, where it is very well accepted. The implementation does not have any negative impacts on Excel's stability or performance. This encouraged me to publish this article and start an open source project (Excel Multivalue formula framework at sourceforge.net), which will hopefully collect all enhancements that are to be done based on the idea of Excel multivalue formulas.

History

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Comments and Discussions

This is a very clever project. I really appreciate all the footwork you've done here.

If you were packaging this for use on a client network, would you be able to bundle everything into an exe to install as an excel add-in? How might you accomplish that? I can follow your steps here and can modify the code, but, without experience in C or COM, I'm unsure how you would go about distributing this to all the users who would want to query your database. The "demo project" install is a bit cumbersome for the layman, I think.

It looks like you have some very useful code here! I'm writing an open source Excel add-in to interface to the R statistical language (see http://sourceforge.net/projects/excelsir/) and think it may help. I just had a few questions:

First, it's been a few years since you've written the code, do you notice any incompatibilities with any newer software, like Excel 2010 or Visual Studio 2010?

Second, it seems to be released under the GNU GPL. But Excel itself is not open-source, this code links to Excel, and I thought the GPL prohibits linking with proprietary software. I listed my project under the LGPL (GPL + linking exception) for this reason. But I'm not a lawyer and could be convinced otherwise. It just seems that the GPL is an awkward license for software by its very nature must link to proprietary software. Also, if I wanted to use your library I would have to license my code under the GPL (because an LGPL project add-in cannot link to GPL code). I wouldn't necessarily be against that, I'm just wondering if you made a decision about these nitty-gritty details.

Finally, apparently your code adds a hook into Excel's recalculation event, which presumably Excel can execute thousands of times every second. Is it possible that adding that hook would noticeably slow down a spreadsheet? Excel can already get sluggish; I wouldn't want my add-in to slow users down even if they weren't using my add-in's functionality.

Thanks for any responses, and thanks of course for sharing your code and your ideas on this page.

we use Office 2010 in our company in the meantime, we did not even have to create a new build, the same addin-version works with all Excel versions from 2003 to 2010, no problems so far. I did not update the Visual Studio project to the newest edition, but I don't expect problems here.

About the licence topic: I have to admit I did not do much research about that and appreciate your efforts to find the right licence for projects like this. I will have a closer look at the LGPL and change the licence for my project as well if appropriate. Thanks for the hint!

The performance issue: the hooked calculate event only checks if the event is triggered by one of the excelmvf-functions. If this is not the case control is immedeately passed back to the default calculate handler. This is extremely fast. I could never encounter a performance difference within Excel when the addin is activated or not and in our company there has never been a complaint about an Excel slowdown. But of course it is additional code to be executed, so one thing is for sure: it won't get faster... But I would say it is negligible.

Great, thanks very much, I will definitely delve deeper into your code. I'm not really familiar with windows programming though; I hope I don't end up diving into the COM rabbit-hole never to return

This is only partially related to your project, but I think you may know because you have taken steps to ensure your code runs on a free (at least as in €0) compiler. Currently my add-in is compiled with GCC and uses VBA Declare statements to exposure the C/C++ functionality. You seem to take a similar approach.

I was hoping to continue using GCC, and perhaps deal with the IDispatch stuff using http://sourceforge.net/projects/vole or some other way that works with GCC. But I've never done anything with COM before. It seems I need to decide upfront whether to continue with GCC or port to Visual Studio, so I'd appreciate any thoughts you had on the issue. If you're not familiar with GCC, it would also help if you could summarize your code's requirements in a description I could take to the MinGW forums to ask about.

I've been reading your code and I think I understand it now---except how you ever figured out how to write it!! For instance, how the heck did you figure out how to hook into the application events with C++ (e.g. that 0x0061b means a Calculate event)? I've read enough about COM to understand the purpose of IDispatch and the other runtime binding stuff, but I can't find any other reference about applying this specifically to Excel events.

I rebuild the vc2005 project and put excelmvf.dll, excelmvf.xla, excelmvf.mdb file to c:\excelmvf, then test it in excel, I input getcustomers(), no error , but the active cell only return "#N/A", why?

Always difficult to trace down such errors from remote. Can you please answer the following questions:-could you successfully build excelmvf.dll in VC2005?-do you have MS Access installed on your computer? Can you open and read excelmvf.mdb?-did you open excelmvf.xla or did you bind it in as Addin (Extras-Addins, Search, select excelmvf.xla)?-are your security settings in Excel set that way that macros and VB-code are allowed?-did you enter =getcustomers() (exactly like this including the = sign) into an empty cell?

If all of these questions are answered with yes, it should work, please let me know if that helped...

Hi HerbD, This was a very useful project to come across and I actually implemented someof the things you wrote about in my project. Many thanks to you !I had a query. Since you end up with array formulas in the resulting range, is it possiblein your case to modify the formulas from any cell. Also what happens in the case of recalculation ?

Nice to hear that you could use the idea in your project and that you were successful in implementing them!

Regarding your question:yes, it is possible to modify the function in any cell as long as you use the <SHIFT><ALT><ENTER> key combination to finalize your change (This combination is the standard way to change values within arrays in Excel). A second way to change the formula is to use the function argument editor by clicking on the ƒ-symbol on the toolbar. You do not need the <SHIFT><ALT><ENTER> key combination then.

I assume that your question about recalculation means what happens when the change of the function parameter result in a different size of the result table. In this case the size of the array will be automatically adjusted to the required size. To demonstrate this within the excelmvf example project:-open Excel and activate the excelmvf addin.-type =getorderitems() in an empty cell and press <ENTER> You will get a table of values with 6 rows and 9 columns.-Now click on one of the cells of the result array and open the function argument editor. In the FieldList argument enter 1,2,3,4 and press <ENTER> again. Now the array will automatically be adjusted to only show columns 1,2,3 and 4.

It should work the same with your own functions.

Just because I'm curious: could you let me know, for what purpose you use the functions and how much time you spent to implement them? Maybe you can also tell me where you had difficulties with the explanations in the article...

Hi HerbD had emailed you. I mainly had to populate a range of cells from a function called from a single cell in my project and that is the part I picked up from your project.Your article is pretty explanatory but more comments to the code will be helpful to all those who are not very proficient and familiar with C, like me.

Actually I wanted each cell of the resulting range to contain theformula without treating it as an Array formula as I would like tomodify the formula from any cell eventually. Is there any API , interface anything that can help me in doing this.

I don't know a way to exactly do what you want (fill a range with a formula and keep track to all the cells of the range without using an array formula) but actually the array formula does allow modifications in an arbitrary cell of the range, you only have to press <CTRL><ALT><ENTER> simoultanously in contrast to only <ENTER> when changing a normal function. By using the function editor dialog even this slight disadvantage can be eliminated.In our company we use some functions of that kind and our experience is that users are absolutely capable of handling this different behaviour without problems. I use the functions myself rather often and I got used to the <CTRL><ALT><ENTER>-thing very fast.

However, if you find a way to eliminate the use of the array formula, please let me know!

Good luck!Herbert

P.S. the late answer is because i've been on vacation...(and didn't touch a computer )

I made a very minor modification to the C++ code, so that it works from a subroutine. When I run and rerun the TestMemoryLeak sub below, memory usage steadily increases over time. Is Excel not freeing the memory for the returned VARIANT? How could I track down the cause? I am familiar with C++, but not yet familiar with COM.

//fetch data now precedes TComHelper codeTOrderItemDataset* dataset = requestController.getOrderItems(pstartdate,penddate,std::string(psortcriterias ? psortcriterias :""));if (pcell.vt==VT_ERROR) { //application.caller returns an error since the function is called from a subroutine //the function was either called by automatic calculation or after rewriting the formula in the Calculate-Event //which triggers a further function call. Return the results in an Array TRequestController::funcParams.setCalculating(false); if (dataset==NULL || dataset->isempty() || (dataset->isvalid() == false)){

without reproducing your test case I would assume the following as the reason for the increasing memory usage:As you may have noticed a local time controlled caching mechanism is implemented to avoid repeated database access within certain intervals.The timestamp for this caching mechanism is immediately set after fetching data from the DB (in the retrieve method).I now assume that by your call method the caching mechanism is invalidated and the container that holds the cached data grows. This container would automatically be freed when Excel is shut down.The Variant in Excel should automatically be freed without having to use COM operations.

Sorry, there's no C# implementation and I do not plan to port it to C# due to lack of time (and C# knowledge). I think the main mechanisms in the program (event handling etc.) should work similarly so there shouldn't be too many obstacles, but I'm afraid there will be many details to stumble upon.If you decide to try a port feel free to contact me for any questions!

Hi ! I downloaded the demo project and copied the .xla , .dll and .mdb filesinto c:/excelmvf/ folder. Inspite of that I am getting an error msg sayingthe "C:/excelmvf/excelmvf.dll cannot be found".I have excel version 2003.What is wrong ? Thanks a million in advance !

Hello rashul,it is quoted in the !Readme!.txt file, that there are some Borland runtime files needed that are not included in the package at CodeProject, since I don't think that I'm allowed to distribute those files although I have a licenced version of C++ Builder. The files that are needed are:vclx60.bplvcl60.bplrtl60.bpldbrtl60.bpladortl60.bplcc3260mt.dllI googled them, they should be easy to get...As an alternative you can also use VC++ project that is build with the VC++ Express Edition, which is freely available. You also need to download the platform SDK, but this is free too.Hope that helps...

Hi Herb, Thanks for your reply.I tried your suggestion and d/l the dll and bpl files.But I am still getting the error "C://excelmvf/excelmvf.dll" not being found.Am i supossed to use the C++ editor.I am opening the xla file directly.Another query which I had was can I do the entire thing in VBA with the tablesdefined on the worksheets itself and not in a separate database.If possible,can you advise me on how to go about it !

I made a list of all library files that Excel loads when I start it with the excelmvf-addIn installed. This list was made with the free sysinternals utility Process explorer that you can find here http://technet.microsoft.com/de-at/sysinternals/default(en-us).aspx[^]I would recommend that you look for missing files on your system and add them.

For the second part of your question:It would of course be possible to use Excel tables as data source as well although I do not really understand why since there are numerous built-in functions to link to other Excel sheets. It could possibly done with VBA too, but for that purpose I would recommend Roland Kapls article.http://www.codeproject.com/KB/office/DBFuncs.aspx[^]

Maybe I could help you more if you describe in detail what you are planning to do.

List with dll and bpl files loaded by Excel when excelmvf is installed

I was desperately looking for a function like this. So, thanks, great job! Unfortunately ! face some problems getting the whole thing to work. Whatever i download, project or source, from here or from sourceforce, i always get the same error: "file not found" for the excelmvf.dll

In the VBA Declarations theres this path: "D:\develop\Experimente\Artikel Exel Multivalue Formula\dll\excelmvf.dll", which does obviously not work. I changed the path to my projectfolder, but im still not able to open the .dll file.

It seems that I took the wrong .xla file in the sources download.The path should be C:\excelmvf\excelmvf.dll Download the demo project that does not only include the .xla file with the correct path (which is C:\excelmvf) but also contains all the neccessary dll-Files to run the example project. Missing dll files are the main reason for the very unspecific "File not found" VBA-error message.

I will correct the wrong file in the sources as soon as possible and will also add a version for Microsoft Visual Studio Express Edition, which is freely available here...http://msdn.microsoft.com/vstudio/express/visualc/[^]You will also need the SDK. Follow the guidelines on the start page of Visual C++ 2005 to download and install it.

Thanks for your hint! Please contact me again if further problems occur.

It's best to correct it yourself, because the path must point to your project directory (which I don't know).To achieve that simply open Excelmvf.xla and then press <ALT><F11> in Excel to enter the Visual Basic environment. Open the Excelmvf project in the project pane and change the path in the function declarations in Modul1. Press Save to store your changes.

The hardcoded path in the function declarations may seem a little bit strange, but the alternative would have been to store the .xla File in your add-in directory or in the system path (Windows or windows\system32 directory). Both alternatives are not very handy especially during development. Excel add-ins do not look for dll-files in their home directories for unknown reasons...

I have some data I want to get from a database. I'm currently using a querytable that refreshes every minute, but I'd prefer it to refresh on calculate. If I just use the calculate event to refresh the querytable then the refresh is likely to occur after all the formulas that depend on the querytable have been calculated (possibly I could get around this by having a named range that included the querytable and a volatile function (e.g. now()), and then have other cells reference this range instead of the querytable directly).