I am new to writing add-ins in C# (to C# as well). I h开发者_StackOverflow中文版ave seen that ExcelDNA works very well for simple functions, but I got stuck with a simple task: manipulating ranges (especially addressed by their name). You see that I tried to use the Interop for this task, which looks the easy way to me. What am I doing wrong in this very simple piece of code? I already went to the ExcelDNA google groups and I only found very awkward answers to the
Thanks very much in advance
using ExcelDna.Integration;
namespace MyAddIn
{
public class MyClass {
[ExcelFunction(Category = "MyFunctions", IsMacroType = true)]
public static string MyMacro(int a, int b)
{
var app = (Microsoft.Office.Interop.Excel.Application) ExcelDnaUtil.Application;
var range = app.get_Range("MyTag"); // this line does not fail, but I don't know if it
// selects the right range -- although MyTag exists
range.set_Value("hello") // it fails here
return (string) range.Value2; // or it would fail here as well
}
}
}
It throws this error
A first chance exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
If you comment out the set_range line, and ensure that the MyTag cell has a string in, your function would work fine.
The main issue is with the set_Range attempt. Excel does not allow you to set some other part of the sheet from a worksheet function. You need to instead change it to a macro (return 'void') that is triggered by a menu or ribbon button. (Setting IsMacroType=true does not turn your function into a macro, it just registers your function as a special type.)
You could try:
[ExcelCommand(MenuName="My Macros", MenuText="Set MyTag value")]
public static void MySetterMacro()
{
var app = (Microsoft.Office.Interop.Excel.Application) ExcelDnaUtil.Application;
var range = app.Range["MyTag"];
range.Value = "Hello there!";
}
I'm assuming you are using C# 4, so we don't need the get_Value, set_Value stuff. If you are using C# 3.5, I think you need to say
range.set_Value(Type.Missing, "Old-Style Hello");
because Value is a parameterized property, and the C# / COM interop before C# 4 was retarded.
精彩评论