passing exceptions to excel

[tinoswe]

Hi guys,

could you please clarify the proper way to handle exceptions between F# and excel? 

What I'd like to do is to show a pop up window in excel when an exception in F# is raised.

I am going through the googlegroup messages but I still have not found what I need...

Thanks for the great work.

Martin

 

[Govert van Drimmelen]

Hi Martin,

By default, Excel-DNA will return any unhandled exception as #VALUE to Excel.

You can insert your own handler for unhandled exceptions by calling ExcelIntegration.RegisterUnhandledExceptionHandler.

If you want to show a message in that context, it's a bit tricky, since the error handler runs on Excel's calculation thread. The Excel-DNA LogDisplay is safe to call from here though.

You could show an alert after the calculations are complete, but a modal message for every error is a bad idea for the long run.

I show how to do all of these things in the snippet below.

Regards,

Govert

module MyFunctions

open System

open ExcelDna.Integration

open ExcelDna.Logging

// The unhandled exception handler should have the type  obj->obj

let private myErrorHandler (exn : obj) : obj  =

    

    // ex is typically an Exception

    let ex = exn :?> Exception

    // We can get hold of the calling cell, if we want to add that to the error logging

    // (But directly converting the ExcelReference to an address can't (easily) be done in this context)

    let caller = XlCall.Excel(XlCall.xlfCaller) :?> ExcelReference

    // The LogDisplay is safe to call from a calculation context

    LogDisplay.WriteLine("Error at " + caller.ToString() + " : " + ex.ToString())

    

    // This will run as a macro after the calculation is complete, so now we can get the address of the ExcelReference:

    // But one might get quite irritated if many cells had errors, and each error showed an alert...

    ExcelAsyncUtil.QueueAsMacro(

        fun () -> 

            let callerAddress = XlCall.Excel(XlCall.xlfReftext, caller, true) :?> string

            XlCall.Excel(XlCall.xlcAlert, "There was an error in cell " + callerAddress + " - " + ex.Message) 

            |> ignore

        )

    // Whatever this handler function returns will be returned to the cell, 

    // instead of the default #VALUE error result

    "ERROR!!! " + ex.Message :> obj

// A public class implementing IExcelAddIn can be used to run code when the add-in is loaded

type public MyAddIn() = 

  interface IExcelAddIn with

    member x.AutoOpen() = 

        // Why can't F# do the myErrorHandler without the delegate constructore here?

        ExcelIntegration.RegisterUnhandledExceptionHandler(UnhandledExceptionHandler(myErrorHandler))

        // This is no longer needed if you are using Excel-DNA 0.32

        ExcelAsyncUtil.Initialize()

    member x.AutoClose() = ()

// Here are the regular function to register with Excel

[<ExcelFunction(Description="An F# function to say Hello")>]

let dnaHelloFs name = 

    "Hello from F# " + name

// This function will throw an exception, which will then be processed by the UnhandledExceptionHandler

[<ExcelFunction(Description="An F# function to test the exception handler")>]

let dnaTestFsException () : obj = 

    raise (InvalidOperationException "You shouldn't do this...")

[tinoswe]

Thanks, Govert!

Illuminating answer.

 

[tinoswe]

Hi again.

One more question on this:

I am using the R type provider for a simple multiple linear fit. 

It can be that some of the coefficients (coefficients.[1], in the example below) are NaN and I would like to raise an exception when this happens (using your previous hint).

However the exception is never raised... I attach the function I am using. I tested your solution and works perfectly fine with finite numbers. 

I am not sure about how to handle the case of a NaN in the array...

Thanks for your help

    [<ExcelFunction(Name="r_lm_new", Description="A Non Linear Model from R", Category="RFunctions")>]

    let rlm_new ((y:float[]),

                         (x1:float[]),

                         (x2:float[]),

                         (x3:float[])) : obj = 

        let dataset = namedParams ["Y", box y;

                                   "X1", box x1;

                                   "X2", box x2; 

                                   "X3", box x3;]

                                   |> R.data_frame

        let result = R.lm(formula = "Y ~ X1 + X2 + X3", data = dataset)

        let coefficients = result.AsList().["coefficients"].AsNumeric().GetValue<float[]>()

        if (Double.IsNaN(coefficients.[1])) then raise (InvalidOperationException "1-is a NaN")

        else        

            [| coefficients.[0]; coefficients.[1]|; coefficients.[2]|; coefficients.[3]| |] |> box

[Govert van Drimmelen]

Hi Martin,

This sounds weird, and not related to Excel-DNA.

Maybe that number is not NaN, but Infinity, or something like that, causing the IsNaN test to fail.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of tinoswe [olivom...@gmail.com]
Sent: 02 May 2014 05:46 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: passing exceptions to excel

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[tinoswe]

Hi again,

there is something I don't understand... 

If I execute the code using the F# interactive shell it throws the exception when asked to check for a NaN in the output array (R lm coefficients). Same function and same data in Excel: it does not throw the exception...

In case anybody can help with the debug I attach all necessary files:

- "test_data.csv": contains data (6 columns).

- "function.txt": contains all necessary functions to raise exceptions in Excel as indicated by Govert (see previous posts, it works perfectly fine when used in simple testing examples.) The function returns either an exception or an array object of floats (length=4)

- "test_R_lm.txt": a few lines of code to load the "test_data.csv" file in F# interactive shell. Two quick modifications are needed: 1) check the path of the file 2) copy and paste the "rlm" function here from "functions.txt"

Thanks for your time.

To post to this group, send email to exc...@googlegroups.com.

[Terry Aney]

I'm not great at 'locks/ing' so maybe better way to do it.  But thought I would show how I did one alert prompt per calculation.  It is in C#.  Hope someone finds it helpful.

private static object alertCalculationErrorLock = new object();
private bool alertCalculationErrorQueued;
 
// This only is triggered for errors from BTR.Excel.Signatures functions in Excel cells. 
// (not called in VBA calls to VBAHelpers or errors from Ribbon)
private object unhandledExceptionHandler( object exception )
{
	// https://groups.google.com/d/msg/exceldna/cHD8Tx56Msg/MdPa2PR13hkJ
	// Explains why needs caller here and QueueAsMacro for other XlCall methods.
	var caller = (ExcelReference)XlCall.Excel( XlCall.xlfCaller );
 
	// If I call LogDisplay.WriteLine *outside* QueueAsMacro, it shows, but if I call
	// it within QueueAsMacro it doesn't show for some reason.
	// Posted to: https://groups.google.com/forum/#!topic/exceldna/97aS22hYR68
	// No response yet.
	ExcelDna.Logging.LogDisplay.Show();
 
	ExcelAsyncUtil.QueueAsMacro( () =>
	{
		var address = caller.GetAddress(); // just extension helper that uses XlfRefText.
 
		// Would like to configure whether or not I log and display dialog based on app.config
		// setting or something, posted to: https://groups.google.com/d/msg/exceldna/0Q7jeF6-E7Q/d9JQ-ef8EgAJ
		// No response yet.
		ExcelDna.Logging.LogDisplay.WriteLine( address + " Error: " + exception.ToString() );
 
		lock ( alertCalculationErrorLock )
		{
			if ( !alertCalculationErrorQueued )
			{
				alertCalculationErrorQueued = true;
				ExcelAsyncUtil.QueueAsMacro( () =>
				{
					XlCall.Excel( XlCall.xlcAlert, "There was one or more errors during calculation.  Please review the Diagnostic Display." );
					lock ( alertCalculationErrorLock )
					{
						alertCalculationErrorQueued = false;
					}
				} );
			}
		}
	} );
 
	return ExcelError.ExcelErrorValue;
}