ExcelReference From Other Worksheet?

[Andrew DeBear]

I am put together a UDF (C_Formula) that looks up and puts cell values of a formula into a string.

Everything was working as expected for cell references on the same sheet. I'm running into a problem when it references a cell on another worksheet. 

          entered formula     = C_Formula(A3)

          should lookup         = C_Formula("=Sheet2!A1")

          should return           = 50

Try

{

strCell = strSheet + strCell;

ExcelReference refCell = (ExcelReference)Excel(xlfIndirect, strCell, true);

object objCell = refCell.GetValue();

strCell = ToolsArgs.GetStr(objCell);

}

When the code gets to the line:

ExcelReference refCell = (ExcelReference)Excel(xlfIndirect, strCell, true); (Line 173 in attached PDF),

Excel freezes for a minute and then crashes with no exception being thrown. The Visual Studio output log just lists:

The program '[12104] EXCEL.EXE' has exited with code 3762504530 (0xe0434352).

Any chance someone can point out what I'm doing wrong? Thanks!

[Andrew DeBear]

Like I said, attached PDF...

[Andrew DeBear]

Still putzing with this, I just tried turning all of the exceptions, Started the debugger, reset to the Visual Studio to "restore the list to default settings", and started it again.

After I reset, the output log showed this, but not sure how to interpret it, yet...

15:06:00:755 'EXCEL.EXE' (CLR v4.0.30319: FullTrustSandbox(Excel-DNA: C:\Users\%username%\...\Add-Ins\UDFakhC\bin\Debug\UDFakhC-AddIn64.xll)): Loaded 'Anonymously Hosted DynamicMethods Assembly'.
15:06:00:755 Exception thrown: 'System.AccessViolationException' in ExcelDna.Loader
15:06:04:947 The program '[23992] EXCEL.EXE' has exited with code 3762504530 (0xe0434352).

[Govert van Drimmelen]

If would be helpful to know what string you are passing to the xlfIndirect function when it causes Excel to crash.

-Govert

[Andrew DeBear]

Hi, Govert. If the formula in cell D5 is entered as "=C_Formula(A5)", the string being passed refCell is "Sheet2!A1".

Thanks!

[Andrew DeBear]

Just getting the chance to look back into this, but getting nowhere.

Any ideas why this line is causing Excel to crash when debugging?

          (ExcelReference)Excel(xlfIndirect, "Sheet2!A1", true);

On another test workbook, I tried Excel's native Indirect function to another worksheet and it worked as expected.

[Andrew DeBear]

Getting a chance to get back to this...I am working on an Excel add-in of UDFs written in C# / ExcelDNA. And am trying to clean up the code for a UDF called "C_Formula".

The UDF is called in a cell by "= C_Formula(cell ref)". It looks at the formula in cell ref, tests to see if there are any other cell references in between any mathematical operators, and looks up the numerical values of those cell references.

It's working fine - so far - as long as all cell references are on the same worksheet, but it's crashing excel or just returning errors when other worksheets are referenced.

This is a snippet from my  CS file that I think I need to to work on...

//if there is a cell reference to another worksheet
if (strI == "!")
{
int sheetEnd = i - 1;
strSheet = strFormText.Substring(p1, sheetEnd - p1 + 1);
if (strSheet.Contains(" "))
strSheet = "'" + strSheet + "'";
strSheet += "!";
p1 = i + 1;
i++;
goto STARTLOOP;
}
...
...
...
// check if string is an actual cell reference
try

{
strCell = strSheet + strCell;
ExcelReference refCell = (ExcelReference)Excel(xlfIndirect, strCell, true);

object val = refCell.GetValue();
string format = (string)Excel(xlfGetCell, 7, refCell);  // Get cell number format
strCell = (string)Excel(xlfText, val, format);  // Format value as displayed in Excel
}
// ...if strCell is a number (OR) if cell is empty
catch (InvalidCastException)
{
if (objCell is ExcelEmpty) strCell = "0";
else if (ToolsArgs.ChkStringDouble(strCell)) { }
else if (strList.Contains(strH)) strCell = "";
}
catch { strFormula = "CatchAll 3"; }

For an example...
cell D68 on the active worksheet ("Sheet1") contains the formula "=Sheet2!D27+Sheet2!D28+Sheet2!D29+Sheet2!D30"

If a cell calls "=C_Formula(D68)" while debugging in Visual Studio, in the first loop, if I break & hover over strCell, VS says the current value is "Sheet2!D27". WHICH IS CORRECT!

But the code isn't currently able to process and lookup that value on the other worksheet.

Any ideas how I might try to fix that? Thanks!

[Andrew DeBear]

I tried making a few changes to the code and added in a few debugging triggers. I think I at least narrowed down where the issue is.

/ wrap the string in double-quotes, so it will work with the Indirect function

object refCellObj = Excel(xlfIndirect, $"\"{strCell}\"");

 strDebug = $"\"{strCell}\""; strDebug = ""; 

; // DIAGNOSTIC LOGGING

strDebug = $"[DEBUG] INDIRECT({strCell}) → {refCellObj?.GetType().Name}";

For the case where strCell = D27 (same sheet), everything works fine.

For the case where strCell = Sheet2!D27, the value of refCellObj = ExcelError, becuase xlfIndirect appears to be choking on strCell.

Any idea how I should format the string (strCell) that gets fed into the xlfIndirect function?

[Govert van Drimmelen]

You can use single quotes around the sheet name.

-Govert

[Andrew DeBear]

Ended up getting this to work last week...forget EXACTLY what I did (busy jumping between tasks)...

The big things were taking out the inline INDIRECT calls and moving those operations into a helper method with better error handling and removing a couple of GOTO calls to make sure the looping worked smoother.

        public static string ResolveCellValue(string strCellFull, int intExtSht)
        {
            object objCell = strCellFull;

            try
            {
                object refCellObj;

                if (intExtSht == 0)
                {
                    refCellObj = Excel(xlfIndirect, strCellFull);
                }
                else if (intExtSht == 1)
                {
                    refCellObj = Excel(xlfIndirect, $"\"\"{strCellFull}\"\"");
                }
                else
                {
                    refCellObj = null;
                }

                // DEBUGGING VARIABLES
                string strDebug = "";
                strDebug = $"\"{strCellFull}\"";
                strDebug = "";
                strDebug = $"[DEBUG] INDIRECT({strCellFull}) → {refCellObj?.GetType().Name}";


                // if refCellObj is just a string, not a valid cell reference, throw it to catch below
                if (!(refCellObj is ExcelReference refCell))
                {
                    throw new InvalidCastException();
                }
                // else if it is a valid cell reference, return the formatted contents
                //else if (refCellObj is refCell)
                //{

                    object val = refCell.GetValue();
                    string format = (string)Excel(xlfGetCell, 7, refCell);

                    return (string)Excel(xlfText, val, format);
                //}
                //else if (refCellObj is ExcelError)
                //{
                //    return "Invalid reference";
                //}
                //else
                //{
                //    return "Indirect did not process.";
                //}
            }
            catch (InvalidCastException)
            {
                if (objCell is ExcelEmpty) return "0";
                else if (ToolsArgs.ChkStringDouble(strCellFull)) return strCellFull;
                else return strCellFull;
                //else return "InvalidCast";
            }
            catch
            {
                return "Indirect exception";
            }
        }