Excel Function not re-processing String variable?

[Andrew DeBear]

...I'm trying to recreate an existing UDF (ADDRTOVAL), written in VBA, in C# ("C_Formula"). I WANT it to:

- turn the referenced cell's formula into a text string
- break up the formula text string into strings of cell names
- use INDIRECT to get those cell values
- combine the values into a single string

Right now, "strCell" has a value of "A1", which is correct.

But "objCell", which should apply the INDRECT function, is empty an not even in the locals window.

I tried creating a test function (C_Indirect) that just calls the INDIRECT function to process a cell value directly. And that seems  to be working.

Can anyone see what I'm doing wrong and point out why objCell doesn't have a value?

using ExcelDna.Integration;

//using static ExcelDna.Integration.XlCall;

using System.Linq;

public class FuncString : XlCall

{

[ExcelFunction(IsMacroType = true, Description = "Retrieves the numeric formula of a referenced cell.")]

public static string C_Formula (

[ExcelArgument( AllowReference = true,

Name="Cell",Description ="Cell containing formula.")]

object objFormula )

// ==================== START of Function ====================

// Creates a string of the referenced cell's formula, including referenced values

// ===========================================================

{

string strFormText = (string)Excel(xlfFormulatext, objFormula);

string strFormula = "";

string strCell = "";

string strI = "";

char chI = 'i';

strFormText = strFormText.Replace("$", "");

if (!(strFormText.Contains("=")))

{

return strFormula = "No formula found."; }

// remove the "=" from the formula

// strFormText = strFormText.Replace("=", "");

List<string> strList = new List<string>

{ "(", ")", ",", "+", "-", "*", "/", ":", "&", "^", "<=", ">=", "<", ">" };

int i = 0; int j = 0; int k = 0;

int p1 = 1; // p1 is initially first character/index in formula after "="

int p2 = 0; // p2 will be the ???

for (i = 0; i < strFormText.Length - 1; i++)

{

chI = strFormText[i];

strI = chI.ToString();

// when character is math or worksheet operator…

if (strList.Contains(strI))

{

p2 = i - 1;

strCell = strFormText.Substring(p1, p2 - p1 + 1);

Try

{

object objCell = Excel(xlfIndirect, strCell, true);

strFormula = strFormula + (string)objCell;

}

Catch

{

object objError1 = ExcelError.ExcelErrorValue;

// object objError1 = ToolsErrors.GetReferenceError();

// object objError2 = ToolsErrors.GetValueError();

strFormula = "It's A Trap!!!";

}

}

} //end of for loop

return strFormula;

} // -------------------- End of Function --------------------

} // ==================== END of Class ====================

[Govert van Drimmelen]

Hi Andrew,

 

When catching exceptions, it’s helpful to also return the exception message from a “catch (Exception ex)” clause – that can sometimes give a clue.

 

Anyway, I think xlfIndirect will return an object of type ExcelReference.

So you can’t just cast it to a string, but need to call .GetValue() to get the value, which again might be of the various types as we’ve discussed before.

 

Let me know if that’s not the problem.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Andrew DeBear
Sent: Thursday, June 22, 2023 9:27 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Excel Function not re-processing String variable?

 

...I'm trying to recreate an existing UDF (ADDRTOVAL), written in VBA, in C# ("C_Formula"). I WANT it to:

- turn the referenced cell's formula into a text string
- break up the formula text string into strings of cell names
- use INDIRECT to get those cell values
- combine the values into a single string

Right now, "strCell" has a value of "A1", which is correct.

But "objCell", which should apply the INDRECT function, is empty an not even in the locals window.

I tried creating a test function (C_Indirect) that just calls the INDIRECT function to process a cell value directly. And that seems  to be working.

 

Can anyone see what I'm doing wrong and point out why objCell doesn't have a value?

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/b8e98687-e58f-4dcb-993f-8900eb671104n%40googlegroups.com.

[Andrew DeBear]

I must admit that I am equally unfamiliar with ExcelDNA as I am with C#.

From what I could tell, xlfindirect is in fact returning an ExcelReference object, but it doesn't seem to accept the .GetValue() property directly.

I blindly threw together the current "GetValue" line. And it's throwing a System.NullReferenceException.

Any chance you could point out my mistake? Thanks!

Try

{

object objCell = Excel(xlfIndirect, strCell, true);

objCell = objCell.GetType().GetProperty("value").GetValue(objCell, null);

strCell = ToolsArgs.GetStr(objCell);

strFormula = strFormula + strCell;

}

catch (Exception ex)

{

object objError1 = ExcelError.ExcelErrorValue;

strFormula = "It's A Trap!!!";

}

System.NullReferenceException
  HResult=0x80004003
  Message=Object reference not set to an instance of an object.
  Source=UDFakhC
  StackTrace:
   at UDFakhC.FuncString.C_Formula(Object objFormula) in C:\...\FuncString.cs:line 172

[Govert van Drimmelen]

Maybe something like this:

 

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

objCell = refCell.GetValue();

strCell = ToolsArgs.GetStr(objCell);

strFormula = strFormula + strCell;

 

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/e4eaa7fc-3ed9-41fc-a07e-c83a669732a3n%40googlegroups.com.

[Andrew DeBear]

This is what worked and got what I was expecting. Thanks so much!

Try

{

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

object objCell = refCell.GetValue();