ExcelDNA throwing exception accessing Range.Value2

Question

I am porting an excel addin (used shimloader) to exceldna, and yeah, I have seen the other SO (and off SO) questions but nothing resolves my question, and I'm hoping there are newer solutions.

The code is simple.

[ExcelFunction(Name="DoSomething")]
string DoSomething()
{
     var xl = ExcelDna.Application;    
     var callerCell = xl.Caller;
     var row = getRow(excelReference.RowFirst+1, callerCell.WorkSheet) ;
}

In GetRow():

var row = (Range)worksheet.Rows[row];
var cell = (Range)bracketRow.Columns[4];

When I check debugger, I can see the retrieved cell is 100% correct because cell.FormulaLocal matches the excel row and column formula.

The value in FormulaLocal is "OtherSheet!A12".

But for some reason, whenever I try cell.Value2, it throws a COMException and nothing else. This is not a multithreaded application and I can't understand why this is happening.

Any ideas?

EDIT:

When I modify the formula to the value it should have gotten had the sheet reference been successful, it doesn't throw.

EDIT 2: I got around this by adding IsMacroType=true attribute to the excel function. But now xl.Caller returns null, argh

Answer 1

Two issues needed solving: range.Value2 threw a COMException if the cell has an invalid value e.g. #VALUE in excel. range.Value2 threw a COMException if the cell referenced another worksheet in the same workbook e.g. "OtherSheet!A2"

To solve this, I set the IsMacroType attribute to true:

[ExcelFunction(Name="DoSomething",IsMacroType=true)]
string DoSomething()
{
     var xl = ExcelDna.Application;    
     var callerCell = xl.Caller;
     var row = getRow(excelReference.RowFirst+1, callerCell.WorkSheet) ;
}

The problem now though is, IsMacroType causes xl.Caller will now return null.

I got around this by:

ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

            string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,reference);

            int index = sheetName.IndexOf(']', 0) + 1;
            int endIndex = sheetName.Length - index;
            sheetName = sheetName.Substring(index, endIndex);
            var worksheet = (Worksheet)xl.ActiveWorkbook.Sheets[sheetName];

This is my first rodeo to Excel world, is there any side effect to enabling IsMacroType? 'Cause I saw @Govert expressing some concerns of undefined behavior...