none
UDF's as part of an Excel Add-in (VS 2008 , Excel 2007) RRS feed

  • Question

  • I have created an excel add-in, which so far has a GUI that allows the user to select tables/fields from a datasource (usually SQL Database).

    Now I want to create a UDF, so that I can display information using the tables/fields selected by the GUI.

    My question is, can the UDF's be part of the VS2008 Add-in solution I have? Or should it be in its own solution, and it is registered/installed before the Add-in solution is run?

    Friday, January 13, 2012 2:53 AM

Answers

  • Hello,

    Please check my answer in How do I create an excel add-in function which can be called as a UDF within Excel. If you have further questions, please let me know.

    I'm not sure if you really need the UDF. What do you mean by "display information using the tables/fields selected by the GUI"? Do you want to use data from these tables/fields in Excel formulas?


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, January 13, 2012 7:43 AM
  • See my comment below

    1) A UDF should only return one value back to the cel with the function.  All parameters required by the UDF should be passed through the paramter list.  If you don't pass a parameter through the parameter list the function wil not get called when the worksheet is updated.

    2) Don't mix up UDF functions with general purpose subroutine macros (top level) which can be call through the worksheet menu or using control buttons on the worksheet.  A subroutine macro (top level) can write to any cell(s) in your workbook and the cannot have any returned values or passed parameters.  A top levbel macro can call other subroutin/function with parameters but cannot be called from a worksheet using the menu or a control button.

    3) An addin in excel becomes part of the worksheet class and can be a UDF, a Subroutine, or a Function.  There is no difference between a UDF and a function except a UDF cannot read or write data directly to a worksheet cell.  If a UDF tries to write to a worksheet cell an error will occur, and if you try to read a worksheet cell the function will not get called if the cell get changed on the worksheet.


    jdweng
    Monday, January 16, 2012 12:55 AM

All replies

  • Hello,

    Please check my answer in How do I create an excel add-in function which can be called as a UDF within Excel. If you have further questions, please let me know.

    I'm not sure if you really need the UDF. What do you mean by "display information using the tables/fields selected by the GUI"? Do you want to use data from these tables/fields in Excel formulas?


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, January 13, 2012 7:43 AM
  • Thanks Andrei,

    What I'm trying to do is re-write an excel add in which was previously written in delphi. This add in, as far as I can see, has two parts.

    The first part is a UDF. This is a function that takes in a number of parameters (connection name, table name, field name (s), filter critiera (SQL statement), and destination cell. It uses all this information to query a database (the connection information is found in the registry, based on the connection name), and displays information formatted in a particular way.

    The second part is a UI to assist with populating the parameters of this function correctly (it really is quite involved).

    I'm reading your other post now. I may need to read it a few times before I completely follow how this all works.

    Thanks again,

    Shanon.

    Sunday, January 15, 2012 9:50 PM
  • Maybe I should  be clearer here in what I'm trying to achieve.

    I have an Excel Add-in, which uses a GUI to get information about what the user wants. It queries databases, gets table names, field names, constructs criteria (SQL statement).

    Now I need to make a UDF, which will take certain inputs, and put an output into another field.

    That is, the formula/function will be placed in cell A1, and the output will go in other cells. The output may be multiple rows/columns, so the output may start at cell C3, but eventually cover the range C3:F30.

    I have started creating a second project for the UDF. This will probably work, as the Add-in/GUI doesnt need access to the UDF itself - it just needs it to be there so when it populates the cell, the UDF can kick in.

    The sample I'm following is from http://www.quantnet.com/forum/threads/how-to-create-udf-in-excel-using-c-and-visual-studio-2008.1260/ , but I'm having my doubts this is the right one to use. It seems they are just basic functions, with no awareness of the excel spreadsheet they are being used in, so I have limited ability to populate other cells.

    Sunday, January 15, 2012 10:56 PM
  • See my comment below

    1) A UDF should only return one value back to the cel with the function.  All parameters required by the UDF should be passed through the paramter list.  If you don't pass a parameter through the parameter list the function wil not get called when the worksheet is updated.

    2) Don't mix up UDF functions with general purpose subroutine macros (top level) which can be call through the worksheet menu or using control buttons on the worksheet.  A subroutine macro (top level) can write to any cell(s) in your workbook and the cannot have any returned values or passed parameters.  A top levbel macro can call other subroutin/function with parameters but cannot be called from a worksheet using the menu or a control button.

    3) An addin in excel becomes part of the worksheet class and can be a UDF, a Subroutine, or a Function.  There is no difference between a UDF and a function except a UDF cannot read or write data directly to a worksheet cell.  If a UDF tries to write to a worksheet cell an error will occur, and if you try to read a worksheet cell the function will not get called if the cell get changed on the worksheet.


    jdweng
    Monday, January 16, 2012 12:55 AM
  • Writing to an arbitrary cell is definitely what Excel doesn't expect from an UDF. In an XLL add-in, you can try to set the cells by running a command-equivalent Excel 4 macro via the ExecuteExcel4Macro() method of the Excel.Application interface. Note that this approach doesn't work if the UDF is invoked programmatically e.g. via ExcelApp.Evaluate().

    The sample you mentioned is an Excel Automation add-in. The restrictions on writing into other cells in an UDF apply to it as well.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Monday, January 16, 2012 11:40 AM