function not getting exported

Jun 27, 2009 at 9:23 PM

I wrote multiple UDFs and they are working fine except the following, I have tried variations of it without success, the function compiles but doesn't get exported so excel doesn't recognise it although the other functions in the same assembly are getting recognized. Any help will be much appreciated.

using System;
using System.Collections;
using System.Text;
using System.Globalization;
using Xl = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;
 

namespace udf1
{
    class class1
    {

        [ExcelFunction(Description = "Pivot table1", Category = "test1 Functions")]
        public static void funcPvt(int nRows)
        {
            Xl.Application xlApp;
            xlApp = (Xl.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
            xlApp.ScreenUpdating = false;
            Xl.Workbook wb = xlApp.ActiveWorkbook as Xl.Workbook;
            Xl.Worksheet ws = xlApp.ActiveWorkbook.Worksheets["Sheet2"] as Xl.Worksheet;
            Xl.PivotCaches pch = wb.PivotCaches();
            pch.Add(Xl.XlPivotTableSourceType.xlDatabase, "Sheet1!R5C1:R" + nRows + "C92").CreatePivotTable(ws.Cells[6, 2], "PivTab1", Type.Missing, Type.Missing);
            Xl.PivotTable pvt = ws.PivotTables("PivTab1") as Xl.PivotTable;

            Xl.PivotField fld = ((Xl.PivotField)pvt.PivotFields(25));
            fld.Orientation = Xl.XlPivotFieldOrientation.xlPageField;

            fld = ((Xl.PivotField)pvt.PivotFields(65));
            fld.Orientation = Xl.XlPivotFieldOrientation.xlColumnField;

            fld = ((Xl.PivotField)pvt.PivotFields(20));
            fld.Orientation = Xl.XlPivotFieldOrientation.xlColumnField;

            fld = ((Xl.PivotField)pvt.PivotFields(79));
            fld.Orientation = Xl.XlPivotFieldOrientation.xlDataField;

            fld = ((Xl.PivotField)pvt.PivotFields(81));
            fld.Orientation = Xl.XlPivotFieldOrientation.xlDataField;


            pvt.RowGrand = false;
            //pvt.ColumnGrand = false;
            wb.ShowPivotTableFieldList = false;
            ws.get_Range("C8", "Z20").NumberFormat = "#,##0.00_);[Red](#,##0.00)";

            xlApp.ScreenUpdating = true;
            ws = null;
            wb = null;
            xlApp = null;

            return;
        }
}
}

Coordinator
Jun 28, 2009 at 2:53 PM

Hi Rookie,

Your method 'returns' void, so is not considered a UDF by ExcelDna,
but as a macro. I think ExcelDna does not currently support macros
with parameters. Strictly speaking I should export these, but the only
way you could call it would be from VBA. I couldn't call these from
the Tools->Run menu, so I have not fixed it. You could enter an issue
into CodePlex, as a reminder that I should fix it in future.

How would you call this macro from Excel? If from VBA, I guess my
advice would be just to put the macro in the VBA. If you know of
another way to call it, say from a button, I'd like to know....

Regards,
Govert