How to debug UDFs


Since only Angels get code running right the first time around, it is useful to actually be able to debug UDFs.


The way to debug UDFs is to attach to the w3wp.exe process that is running Excel Services and place a breakpoint in your UDF. However, if you bring up the process list in Visual Studio (Ctrl-Alt-P), you will see that there are multiple w3wp.exe processes running. Show how to pick?


You could attach to all of them – that would certainly work, but it would make debugging more sluggish and potentially more noisy.


To figure out the process id that is the correct one, you can use the IISAPP script. Just execute the following command in the console, and you will see the correct process id:


iisapp /a SharedServices1


The output will look like this:


Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.


The following W3WP.exe processes are serving AppPool: SharedServices1
W3WP.exe PID: 1980


That means that the process ID to attach to is 1980.

Comments (11)

  1. ciruli says:

    Ah!  Very useful!  When I was working on a UDF recently, I eventually just changed my UDF to return a string, then filled that string with error messages, so my error message just appeared in my cell in Excel.

    However, it seems that in order for your solution to work, I’d have to have Excel Services (Sharepoint) and my debugger running on the same machine, right?  Or can I attach to that w3wp.exe remotely?

  2. Shahar says:

    You can remote-debug to W3WP just like you can to any other process.

    Read up on MSDN about how to do it. If you have any problems, feel free to drop me a line.

  3. ciruli says:

    I’m seeing some very strange behavior in Excel Services.

    I’m launching a UDF that takes many arguments (including a two-dimensional argument) and returns a large, two-dimensional array.

    So far, so good.

    However, there seems to be a dependency problem.  If I make any cells in my workbook dependent on the results, I get in an infinite loop of recalculation:  my UDFMethod gets invoked over and over again.

    Note that there is not actually any circular dependency:  My UDF takes as argument cell A, fills cell B, and cell C is dependent on cell B.  Can I not make Cell C dependent on Cell B?

  4. Instead of re-hashing information I’ve found elsewhere I figured a pre-reqs post would be good.

    One…

  5. ajeesh_pullin_out_my_hair says:

    i’m passing a parameter from Excel2007 to a udf…. i’ve added a parameter when i published the sheet…

    i’ve set a default value on the excel sheet. so the 1st time the pg loads, the default value is passed to the UDF & it returns the correct string( in the following sample code, the count of items)

    but when i change the parameter at runtime, it returns an "Access is Denied" error….

    i tried debugging n found that on the 1st pg load, when everythin works fine,

    SPWeb l_web = l_site.OpenWeb();

    l_web has "SiteName", when i ‘watch’ed it…

    but when i pass parameter,

    l_web=SPWeb…

    n then on the next step

    SPList list = l_web.Lists[ListName];

    it throws an exception as below ….. a sample code is also attached below

    System.UnauthorizedAccessException was caught

    Message="Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"

    it seems like there is some authorization problem when a parameter value is passed from excel services…..

    plz help me out…….

    /* CODE */

    [UdfMethod(IsVolatile = true)]

    public string testFn(string l_strSiteUrl, string ListName, string test)

    {

    try

    {

    SPSite l_site = new SPSite(l_strSiteUrl);

    SPWeb l_web = l_site.OpenWeb();

    SPList list = l_web.Lists[ListName];

    SPListItemCollection l_listItemCollection;

    l_listItemCollection = list.Items;

    return l_listItemCollection.Count.ToString();

    }

    catch (Exception ex)

    {

    return "Error!!! " + ex.InnerException.Message;

    }

    }

  6. 7545454 says:

    Echtedr, edr lijdkt eden afhandkelijkheid probleem. Als ik alle cellen in mijn werkmap afhankelijk van de resuldtaten, krijg ik in een oneindige lus van herberekening: mijn UDFMethod krijgt ingeroepen telkens weer.

  7. lonag says:

    In plaats van opnieuw hashing informatie die ik heb gevonden elders Ik denk een pre – reqs bericht zou goed

  8. ganiiiiiii says:

    Merk op dat er niet echt een circulaire afhankelijkheid: Mijn UDF neemt als argument van cel A, vult cel B,

  9. jpmarrapu says:

    I wrote code snippet that gets the report data from the share point list and displays the graph on the worksheet.

    This snippet is working perfect on excel client, it is throwing code exception after publish and configure on excel web access web part.

    I tried to debug the code attaching all w3wp.exe processes, but the debugging is not happing.

    Please suggest me the debugging approach.

  10. Shahar says:

    Do you have "Enable Just My Code" disabled in options?