Cannot easily access excel range by name in excel add-in in C#

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.