get the text string of a cell formula?

[Andrew DeBear]

I haven't been able to figure out yet. Is there a way to get a text string of a formula that is in a cell...?

For example, if I want to show what the formula in cell "A3" is...

In Excel, you just use the function =FormulaText(A3) and it returns "A2 + A1".

In VBA, you could use String = range("A3").formula and it should return "A2 + A1".

But my limited C# and ExcelDNA experience hasn't been able to get a formula string.

This is what my fumbling has gotten, so far. It compiles, but Excel returns a #VALUE error at the string  string strFormula  line.

using ExcelDna.Integration;

using ExcelDna.Registration;

using System.Globalization; //used for another function

namespace Functions

// ==================== START of Class ====================

public class FuncString : XlCall

{

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

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

    public static string C_Formula

        ([ExcelArgument(

          AllowReference = false,

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

          object objFormula        

        )

    {

        string strFormula = (string)XlCall.Excel(XlCall.xlfFormulatext, objFormula);

        strFormula = objFormula.ToString();

        return strFormula;

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

[Govert van Drimmelen]

Hi Andrew,

 

You have it nearly right.

In this case you want to get the cell reference (if the function is called with a cell reference) to pass to the xlfFormulaText C API method.

So you would set AllowReference=true, and then a cell reference will not be passed by value, but as an ExcelReference object.

 

Then you must also remove the line that says

        strFormula = objFormula.ToString();

since this overwrites the formula you got in the previous line.

 

You don’t have to have your class inherit from XlCall – that was an old trick that can be better done these days by adding

    using static ExcelDna.Integration.XlCall;

 

Then your function can be simplified by removing the XlCall qualifiers:

 

        [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

            )

        {

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

            // strFormula = objFormula.ToString();

            return strFormula;

        }

 

 

Remember that the function might still be called with non-reference arguments, e.g. as

     =C_Formula("Hello")

And you might want to deal with this case, where the objFormula argument will not be of type ExcelReference.

 

-Govert

--
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/794adad0-2636-42d4-9f61-56559ed5e30fn%40googlegroups.com.

[Andrew DeBear]

Yup. That did it. Thanks!

using ExcelDna.Integration;

using static ExcelDna.Integration.XlCall;

using ExcelDna.Registration;

using System.Globalization; // used for another function

 ==================== START of NAMESPACE ====================

namespace Functions

{ // ==================== START of Class ====================

public class FuncString : XlCall

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

[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 )

{

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

return strFormula;

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