Exception from HRESULT: 0x800A03EC when writing UDF output to other cell
965 views
Skip to first unread message
Norman Ordas
Mar 10, 2011, 11:37:13 AM3/10/11
to exce...@googlegroups.com, Govert van Drimmelen
On Fri, Mar 11, 2011 at 12:34 AM, Norman Ordas <norma...@gmail.com> wrote:
Hi All,
Anyone kind enough to enlight me on this. I have the function code below wrap in my MyFunction class.
[ExcelFunction()]
public void WriteFuncOutToCell(string name)
{
Microsoft.Office.Interop.Excel.Application app =
(Microsoft.Office.Interop.Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
if (wks != null)
{
XL.Range rng = wks.get_Range("H1");
rng.Value2 = name;
}
}
Now If I call this function in Excel =WriteFuncOutToCell("Test") it throws "Exception from HRESULT: 0x800A03EC" simply because the cell still has the focus (not allowed). The way I'm doing this in COM and VBA combination is via exposing an event in COM object and let VBA subscribe to it, then VBA will call a method in COM object that does nothing (should return immediately to lose the focus on cell) but invoke an event. COM object in VBA will then be notified of the event and will write the output of the function to the target cell. I don't know how this can be done by using ExcelDNA. Thanks.
Regards,
Norman
Govert van Drimmelen
Mar 10, 2011, 12:01:41 PM3/10/11
to Excel-DNA
Hi Norman,
Setting other cells from a function breaks Excel's calculation model,
so Excel tries really hard to prevent this.
Scarily, yours is a common request. I'd advise against it, but you can
do this in Excel-DNA as well as any other tool.
From Excel-DNA you could hook the same event you did in VB, or you can
start a separate thread to do the update.
A more robust design might be something like this:
1. Keep a list of pending work items in a static variable.
2. Create a macro "MyWorkerMacro" that processes all the pending work
items. This will run in the macro context, so will be able to change
cells etc.
3. In your function, append to the work item list, and call
AsyncRunMacro("MyWorkerMacro"), where AsyncRunMacro can look something
like the code I paste below.
The code comes from the Resize example I posted here:
http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.
Please report back any problems you run into.
Regards,
Govert
// Most of this from the newsgroup thread:
// http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
private static readonly TimeSpan BackoffTime
= TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread( delegate ()
{
while(true)
{
try
{
RunMacro(macroName);
break;
}
catch(COMException cex)
{
if(IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
catch(Exception ex)
{
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
try
{
object xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run",
BindingFlags.InvokeMethod, null,
xlApp, new object[] {macroName});
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch(errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
On Mar 10, 6:37 pm, Norman Ordas <normanor...@gmail.com> wrote:
Setting other cells from a function breaks Excel's calculation model,
so Excel tries really hard to prevent this.
Scarily, yours is a common request. I'd advise against it, but you can
do this in Excel-DNA as well as any other tool.
From Excel-DNA you could hook the same event you did in VB, or you can
start a separate thread to do the update.
A more robust design might be something like this:
1. Keep a list of pending work items in a static variable.
2. Create a macro "MyWorkerMacro" that processes all the pending work
items. This will run in the macro context, so will be able to change
cells etc.
3. In your function, append to the work item list, and call
AsyncRunMacro("MyWorkerMacro"), where AsyncRunMacro can look something
like the code I paste below.
The code comes from the Resize example I posted here:
http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.
Please report back any problems you run into.
Regards,
Govert
// Most of this from the newsgroup thread:
// http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
private static readonly TimeSpan BackoffTime
= TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread( delegate ()
{
while(true)
{
try
{
RunMacro(macroName);
break;
}
catch(COMException cex)
{
if(IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
catch(Exception ex)
{
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
try
{
object xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run",
BindingFlags.InvokeMethod, null,
xlApp, new object[] {macroName});
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch(errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
On Mar 10, 6:37 pm, Norman Ordas <normanor...@gmail.com> wrote:
Gudguy
Mar 11, 2011, 8:03:49 AM3/11/11
to Excel-DNA
Hi Govert,
Thanks for giving me some insight. However I'm still struggling to
make it work and the error is same. My code is below:
public static class MyFunction
{
public static object ProcessAndWrite(string data)
{
Debug.WriteLine(Thread.CurrentThread.GetApartmentState());
return XlCall.Excel(XlCall.xlUDF, "MyWorkerMacro", data);
}
}
public class ToolkitTest
{
public static string _data;
public static object MyWorkerMacro(string data)
{
_data = data;
AsyncRunMacro("ProcessData");
return _data;
}
public static void ProcessData()
{
XlCall.Excel(XlCall.xlcEcho, false);
Debug.WriteLine(Thread.CurrentThread.GetApartmentState());
XL.Application app =
(XL.Application)ExcelDnaUtil.Application;
Debug.WriteLine(app.Version);
try
{
_data);
}
}
catch (Exception exception)
{
Debug.WriteLine(exception.StackTrace);
}
finally
{
XlCall.Excel(XlCall.xlcEcho, true);
(XL.Application)ExcelDnaUtil.Application;
try
{
//xlApp.GetType().InvokeMember("Run",
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
is causing the same exception now. Also can you pls explain why we are
calling the code below:
Marshal.ReleaseComObject(xlApp);
The .xll is hosted in the Excel Application so I a bit lost as to why
we need to release/destroy the host application.
Thanks so much,
Norman
On Mar 11, 1:01 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Norman,
>
> Setting other cells from a function breaks Excel's calculation model,
> so Excel tries really hard to prevent this.
> Scarily, yours is a common request. I'd advise against it, but you can
> do this in Excel-DNA as well as any other tool.
>
> From Excel-DNA you could hook the same event you did in VB, or you can
> start a separate thread to do the update.
> A more robust design might be something like this:
> 1. Keep a list of pending work items in a static variable.
> 2. Create a macro "MyWorkerMacro" that processes all the pending work
> items. This will run in the macro context, so will be able to change
> cells etc.
> 3. In your function, append to the work item list, and call
> AsyncRunMacro("MyWorkerMacro"), where AsyncRunMacro can look something
> like the code I paste below.
>
> The code comes from the Resize example I posted here:http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.
>
> Please report back any problems you run into.
>
> Regards,
> Govert
>
> // Most of this from the newsgroup thread:
> //http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f...
> > > Norman- Hide quoted text -
>
> - Show quoted text -
Thanks for giving me some insight. However I'm still struggling to
make it work and the error is same. My code is below:
public static class MyFunction
{
public static object ProcessAndWrite(string data)
{
Debug.WriteLine(Thread.CurrentThread.GetApartmentState());
return XlCall.Excel(XlCall.xlUDF, "MyWorkerMacro", data);
}
}
public class ToolkitTest
{
public static string _data;
public static object MyWorkerMacro(string data)
{
_data = data;
AsyncRunMacro("ProcessData");
return _data;
}
public static void ProcessData()
{
XlCall.Excel(XlCall.xlcEcho, false);
Debug.WriteLine(Thread.CurrentThread.GetApartmentState());
XL.Application app =
(XL.Application)ExcelDnaUtil.Application;
Debug.WriteLine(app.Version);
try
{
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
if (wks != null)
{
XL.Range rng = wks.get_Range("H1");
rng.Value2 = string.Format("It worked {0}.",
if (wks != null)
{
XL.Range rng = wks.get_Range("H1");
_data);
}
}
catch (Exception exception)
{
Debug.WriteLine(exception.StackTrace);
}
finally
{
XlCall.Excel(XlCall.xlcEcho, true);
}
}
private static readonly TimeSpan BackoffTime =
TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread(delegate()
{
while (true)
{
try
{
RunMacro(macroName);
break;
}
catch (COMException cex)
{
if (IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
catch (Exception ex)
}
private static readonly TimeSpan BackoffTime =
TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread(delegate()
{
while (true)
{
try
{
RunMacro(macroName);
break;
}
catch (COMException cex)
{
if (IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
{
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
XL.Application xlApp =
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
(XL.Application)ExcelDnaUtil.Application;
try
{
//xlApp.GetType().InvokeMember("Run",
BindingFlags.InvokeMethod, null, xlApp, new object[] { macroName });
xlApp.Run(macroName);
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch (errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
}
The call to
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch (errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
}
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
is causing the same exception now. Also can you pls explain why we are
calling the code below:
Marshal.ReleaseComObject(xlApp);
The .xll is hosted in the Excel Application so I a bit lost as to why
we need to release/destroy the host application.
Thanks so much,
Norman
On Mar 11, 1:01 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Norman,
>
> Setting other cells from a function breaks Excel's calculation model,
> so Excel tries really hard to prevent this.
> Scarily, yours is a common request. I'd advise against it, but you can
> do this in Excel-DNA as well as any other tool.
>
> From Excel-DNA you could hook the same event you did in VB, or you can
> start a separate thread to do the update.
> A more robust design might be something like this:
> 1. Keep a list of pending work items in a static variable.
> 2. Create a macro "MyWorkerMacro" that processes all the pending work
> items. This will run in the macro context, so will be able to change
> cells etc.
> 3. In your function, append to the work item list, and call
> AsyncRunMacro("MyWorkerMacro"), where AsyncRunMacro can look something
> like the code I paste below.
>
> The code comes from the Resize example I posted here:http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.
>
> Please report back any problems you run into.
>
> Regards,
> Govert
>
> // Most of this from the newsgroup thread:
>
> - Show quoted text -
Govert van Drimmelen
Mar 12, 2011, 3:13:53 PM3/12/11
to Excel-DNA
Hi Norman,
Your code should nearly runs. The line:
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
should probably be
XL.Worksheet wks = (XL.Worksheet)app.ActiveWorkbook.ActiveSheet;
"ThisWorkbook" would refer to the workbook where a macro itself lives,
but in our case the macro is in an .xll, so it doesn't work.
After making this change to ActiveWorkbook, the writing work fine for
me.
If you plan to call such functions from many cells, you might need to
think how to throttle the creation of new threads - or change the work
to be posted to the ThreadPool somehow.
The current scheme seemed to crash Excel with around 1000 calls at a
time, in effect a full recalculation would create a new thread for
every call, which doesn't really work well.
Maybe you need a check to see whether outstanding work is pending, and
not create a new thread while there is work (and a thread to trigger
the work) pending.
The Marshal.ReleaseObject() call is discussed in this thread:
http://groups.google.com/group/exceldna/browse_thread/thread/fe820ec9ca9fd407.
Regards,
Govert
Your code should nearly runs. The line:
XL.Worksheet wks = app.ThisWorkbook.ActiveSheet;
should probably be
XL.Worksheet wks = (XL.Worksheet)app.ActiveWorkbook.ActiveSheet;
"ThisWorkbook" would refer to the workbook where a macro itself lives,
but in our case the macro is in an .xll, so it doesn't work.
After making this change to ActiveWorkbook, the writing work fine for
me.
If you plan to call such functions from many cells, you might need to
think how to throttle the creation of new threads - or change the work
to be posted to the ThreadPool somehow.
The current scheme seemed to crash Excel with around 1000 calls at a
time, in effect a full recalculation would create a new thread for
every call, which doesn't really work well.
Maybe you need a check to see whether outstanding work is pending, and
not create a new thread while there is work (and a thread to trigger
the work) pending.
The Marshal.ReleaseObject() call is discussed in this thread:
http://groups.google.com/group/exceldna/browse_thread/thread/fe820ec9ca9fd407.
Regards,
Govert
Norman Ordas
Mar 14, 2011, 9:18:52 PM3/14/11
to exce...@googlegroups.com
Wow that works like a charm to me :)
Thanks so much Govert! What a wonderful piece of tool you have.
Sent from my iPhone4
> --
> You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.
>
Hristo Hristov
Feb 4, 2015, 4:31:43 AM2/4/15
to exce...@googlegroups.com
How do you pass a range parameter to the macro? I'm using a macro with the excel function attribute in C#.
Reply all
Reply to author
Forward
0 new messages