Excelmvf Excel Multivalue User defined functions




		What is Excelmvf and what can it do for me? System requirements How can I install the sample Add-In Usage of the sample Add-In What skills do I need to implement my own functions? Which IDE is recommended? How to implement own functions How to contribute What is Excelmvf and what can it do for me? Excelmvf stands for Excel Multivalue Functions. Functions of that kind can return an arbitrary number of values without knowing the size of the returned data table in advance thus overcoming an limitation of “usual” Excel functions. This can be useful e.g. For database functions For functions that return matrices The Excelmvf-AddIn presented here is an Add-In that includes some sample functions and can serve as base for implementation of own functions. System requirements Microsoft Excel 2002 or higher. Microsoft Jet 4.0 OLE DB Provider. This provider is automatically installed with Microsoft Office, but can be downloaded otherwise at http://msdn.microsoft.com/data/downloads/updates/default.aspx#jet How to install the sample Add-In Simply copy the whole excelmvf directory into the root directory of your local drive C:. It is important to strictly stick to that path since the absolute path is used in the declarations of the dll-functions within excelmvf.xla (the alternative would have been to copy the files into your system directories, which makes cleaning up more difficult). You therefore should have a C:\excelmvf directory after installation containing the files excelmvf.xla, excelmvf.dll and excelmvf.mdb. 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 Excel’s function input dialog within the section “User defined functions”, the functions are ready to use. Usage of the sample Add-In The sample includes 2 functions that return the content of a table and a view respectively. Syntax: getCustomers(fieldList as String, sortingCriteria as String, header as boolean) fieldList: String with comma or ; delimited list of columns to be displayed sortingCriteria: String with comma or ; delimited list of sorting criteria optionally including sorting direction asc or desc header: true (default) columns headers are displayed, false: no column headers. getOrderItems(startdate as date,enddate as date, fieldlist as String,sortingCriteria as String,header as boolean) fieldList, sortingCriteria and header as above. Startdate and enddate: Date values to confine results to Orders processed within the specified range. All function parameters are optional, so if you enter =getcustomers() or =getOrderItems() the functions return all records found in the table (view) What skills do I need to implement my own functions? Medium skills in C++, beginner skills in Visual basic for Applications Which IDE is recommended? The Add-In was implemented with Borland C++ Builder 6. There are some Borland VCL-classes used, so the Add-In will not compile e.g. in VC6 without some modifications. The used VCL-classes are: TEventDispatcher TADOQuery TADOConnection If you port the project, please contribute… How to implement own functions Implementing own function is not mere nothing, but I tried to make it as simple as possible. The necessary steps are: Adopt the Datasource: In the example the ADO data Provider for Access is used, but there are a bunch of other data providers too. Most probably you only have to change the ConnectionString in the main module of the dll. Derive a new class from class TRecord Derive a new classe from class TDataset using previously derived TRecord class as Template parameter. Adopt class TFuncParams if required. Add a method to class TreqestController similar to the getCustomers or getOrderItems- method of the example add-in. Adopt TExcelEventDispatchers Calculate method. Insert a new function to the dllmain module. Add a function pair to the Excel Add-In (.vba) file. I have collected all the necessary steps in detail here How to contribute If you find the idea interesting and implement functions of your own you will most probably stumble over implementation details that could be simplified or improved. Or maybe you prefer Visual C++ or C# and therefore did some porting… Maybe you even succeeded in creating a wizard, that enables the definition of new functions without programming at all… In any case please let us know too…