Partager via

How to debug UDFs

  • Article

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

  • Anonymous
    July 14, 2006
    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?

  • Anonymous
    July 14, 2006
    The comment has been removed

  • Anonymous
    July 18, 2006
    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?

  • Anonymous
    September 28, 2006
    Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
    One...

  • Anonymous
    November 06, 2007
    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; } }

  • Anonymous
    December 08, 2007
    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.

  • Anonymous
    December 08, 2007
    In plaats van opnieuw hashing informatie die ik heb gevonden elders Ik denk een pre - reqs bericht zou goed

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

  • Anonymous
    March 04, 2008
    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.

  • Anonymous
    March 04, 2008
    Do you have "Enable Just My Code" disabled in options?

  • Anonymous
    March 11, 2010
    Here's a quick post on how to debug SharePoint 2010 UDFs': http://www.chaholl.com/archive/2010/03/11/debugging-excel-services-udfs-using-visual-studio-2010.aspx