When I get a SheetChange event that contains a cell using my custom function
I cannot modify the value of any cells in the target range.
range is the target range passed into the sheet change event
gid_col is the column I wish to validate


for(int a = 1; a <= range.Areas.Count; a++)
{
Excel.Range area = range.Areas[a];
for(int r = 0; r < area.Rows.Count; r++)
{
//Excel.Range cell = (Excel.Range)area.Cells[r,1];
int row = area.Row + r;
if((row_changes[row].Equals(3))
&&(area.Column <= gid_col)
&&(area.Column+area.Columns.Countgid_col))
{
Excel.Range cell =
(Excel.Range)area.Cells[r+1,gid_col+1-area.Column];
String f = (String)cell.Formula;
try {
cell.set_Value(Excel.XlRangeValueDataType.xlRangeV alueDefault, "wtf"); }
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Sheet change
error\n" + ex.ToString(),"Localization Addin");
}
}
}
area = null;
}


the code works fine if no cell in the target range uses my custom function,
but blows up with this error otherwise...

System.Runtime.InteropServices.COMException(0x800A 03EC): Exception from
HRESULT 0x800A03EC.
at System.RuntimeTYpe.ForwardCallToInvokeMember(Strin g memberName, ...)
at Microsoft.Office.Interop.Excel.Range.set_Value(Obj ect
RangeValueDataType, Object)


also if I try to change other cell properties, like Font.Italic it has no
effect if my custom function is used by any cell in the target

This is the code for my custom function
using System;
using System.Windows;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace LocalizationFunctions
{
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

/// <summary
/// Summary description for Class1.
/// </summary
//[GuidAttribute("EE98CECC-2DEF-4d0b-B707-1985711163F5"),
ProgId("LocalizationFunctions.Functions")]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
//
// TODO: Add constructor logic here
//
}
public String TAG(Excel.Range range)
{
String result = "";
try
{
for(int a = 1; a <= range.Areas.Count; a++)
{
Excel.Range area = range.Areas[a];
for(int r = 1; r <= area.Rows.Count; r++)
{
for(int c = 1; c <= area.Columns.Count; c++)
{
Excel.Range _cell = (Excel.Range)area.Cells[r,c];

if(_cell.Value2 != null)
{
String val = _cell.Value2.ToString();
if(val.Length0)
{
if(result.Length0)
{
result += "_";
}
result += val;
}
}
//_cell = null;
}
}
//area = null;
}
result = result.Replace(' ','_');
result = result.Replace('\n','_');
result = result.Replace('\t','_');
//result = (String)range.Value2;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ex.ToString(),"Localization Function");

result = "ERROR!";
}

return result;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(ty pe));
Registry.ClassesRoot.CreateSubKey("CLSID\\{" +
type.GUID.ToString().ToUpper() + "}\\Programmable");
RegistryKey key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + type.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("",
System.Environment.GetFolderPath(Environment.Speci alFolder.System)
");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(ty pe),false);
}

private static string GetSubKeyName(Type type)
{
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() +
@"}\Programmable";
return s;
}

}
}

Any ideas?

Hi,

Currently I am researching the issue and we will reply here with more
information as soon as possible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi

Based on my research, there seems to be infinite recursion - if in the
change event you modify the sheet, which fires another change event, and so
on and so forth.
So we need to prevent reentrancy in your event handler with a bool flag.
bool bChanged = false;
void exApp_SheetChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{
if( bChanged )
return;
try
{
bChanged = true;
Target.Value2 = "Test2";

Target.set_Value(Excel.XlRangeValueDataType.xlRang eValueDefault, "Test");
Target.Font.Italic = true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
bChanged = false;
}
}

You may have a try.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

No luck with that
I already had

m_excel_app.EnableEvents=false;
m_excel_app.ScreenUpdating = false;
....
m_excel_app.EnableEvents=true;
m_excel_app.ScreenUpdating = true;

in my SheetChange function, but I tried your suggestion anyway. It didn't
have any effect.




""Peter Huang" [MSFT]" wrote:

Hi

Based on my research, there seems to be infinite recursion - if in the
change event you modify the sheet, which fires another change event, and so
on and so forth.
So we need to prevent reentrancy in your event handler with a bool flag.

Just tried a few tests and it appears that if I don't use the range passed
into my custom function everything is fine.
So it can be reduced to this test case if that helps...

public String TAG(Excel.Range range)
{
String result = "";
try
{
result = "banana apple";
//comment the following line out to make it work
result += range.Value2.ToString();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ex.ToString(),"Localization Function");

result = "ERROR!";
}
return result;
}

Hi

Do you mean call the TAG function in the SheetChange event?
Based on my test, I can not reproduce the problem.
Here is my test code.
public String TAG(Excel.Range range)
{
String result = "";
try
{
result = "banana apple";
//comment the following line out to make it work
result += range.Value2.ToString();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ ex.ToString(), "Localization Function");

result = "ERROR!";
}
return result;
}

bool bChanged = false;
void exApp_SheetChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{
if( bChanged )
return;
try
{
bChanged = true;
TAG(Target);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
bChanged = false;
}
}

You may have a try.
If you still have any concern, can you build a simple reproduce sample and
send the whole solution to me via removing "online" from my email address.
So that I can reproduce the problem at my side.
Thanks for your efforts.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

No TAG is a custom function being used in one of the cells, if I change the
custom function (which must be getting called automatically to evaluate the
cell) as I described I can make the problem appear or disappear.

The code for the custom function was in the first email, along with the
problematic part of the SheetChange callback function.
I'll try and cut down my app to something that I can send you.

""Peter Huang" [MSFT]" wrote:

Hi

Do you mean call the TAG function in the SheetChange event?
Based on my test, I can not reproduce the problem.
Here is my test code.
public String TAG(Excel.Range range)
{
String result = "";
try
{
result = "banana apple";
//comment the following line out to make it work
result += range.Value2.ToString();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ ex.ToString(), "Localization Function");

result = "ERROR!";
}
return result;
}

bool bChanged = false;
void exApp_SheetChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{
if( bChanged )
return;
try
{
bChanged = true;
TAG(Target);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
bChanged = false;
}
}

You may have a try.
If you still have any concern, can you build a simple reproduce sample and
send the whole solution to me via removing "online" from my email address.
So that I can reproduce the problem at my side.
Thanks for your efforts.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.