how to determine whether a cell contains a formula

[Rui]

hi everyone,

I started using ExcelDna this weekend, and I can't find any
information in the archives related to my question.

When creating a UDF that takes a single parameter, which is a
reference to another cell, is there a way to determine whether
reference cell contains a formula?

For example,
A1 contains 2
A2 contains 4
A3 contains 7
A4 contains "=sum(A1:A3)"
A5 contains "=MyUDF(A4)"

I want my function, MyUDF(), to tell me whether the cell, A4, contains
a formula, which it does in this case, "=sum(A1:A3)".

thank you for your help.

Regards

[Govert van Drimmelen]

Hi Rui,

You need three bits in your function for this to work:
1. The function must be marked as IsMacroType=true,
2. mark the input argument as AllowReference=true, and
3. call xlfGetFormula to get the formula that the reference points to.

In VB, the function could look like this:

<ExcelFunction(IsMacroType:=True)> _
Public Function GetFormula( <ExcelArgument(AllowReference:=True)>
input as Object) As String
Dim formula As String
If TypeOf input Is ExcelReference Then
formula = XlCall.Excel(XlCall.xlfGetFormula, input)
Return "Formula: " & formula
Else
Return "<Not a reference>"
End If
End Function

Regards,
Govert

[Rui]

hi Govert,

You're a genius! Thanks for the quick response. Really appreciate it. your framework is really great. I was able to start coding my UDFs without having to worry about the intricacies of the Excel interface, which still seems to be a mystery for me. Thank you for your contribution!

Quick follow up question: given that your architecture requires us to write code (I use C#) in a dna file (or alternatively a DLL callable from the dna file), what's the best way to debug this dna file as a project in Visual Studio 2010?

Right now, I'm writing C# code in my editor, VIM, but I don't get the benefit of intellisense, code completion, and compilation to check for bugs. How are you and others developing code for ExcelDna?

thanks again!
rui

--
You received this message because you are subscribed to the Google Groups "Excel-Dna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Rui,

On the Google group you can find a page that discusses how to get
debugging working:
http://groups.google.com/group/exceldna/web/how-to-setup-vs-2005-to-be-able-to-use-breakpoints-in-your-addin

For Visual Studio 2010 when targeting the .NET 2.0 runtime, you should
take some care starting Excel under the debugger - the debugger is
only ready to debug mixed code with the version 4 runtime. There are
some workarounds - the whole issue is discussed here:
http://groups.google.com/group/exceldna/browse_thread/thread/6767ac80f2bb3f11/55f5b400b1cba673?lnk=gst&q=debug+connext#55f5b400b1cba673
and the Microsoft connect issue is here:
https://connect.microsoft.com/VisualStudio/feedback/details/554067/cannot-debug-net-2-0-3-0-3-5-code-using-f5-in-mixed-mode-from-native-c-projects-or-c-project-with-start-external-program-set-to-native-exe&usg=AFQjCNF0SYjEIcgdiVjAVy_jSXfBCsibDA

Regards,
Govert

> > exceldna+u...@googlegroups.com<exceldna%2Bunsubscribe@googlegroups.c om>
> > .

[Rui]

Thanks Govert. That worked. However, using the example I originally sent instead of returned the absolute position of the range, e.g. "=sum(A1:A3)", I get the relative position relative to A4, e.g. "=sum(R[-3]C:R[-1]C)".

Question: is there a way to get the absolute position of the range, e.g. A1:A3, that was specified in the cell with the SUM function?

I searched through the ExcelDNA google group and all the samples and source code, but couldn't find the answer.

here's the code I'm currently using. the xlfCaller isn't the right call since it gives me the position of the cell where I call my UDF, nDescribe(). Thanks for your help again.

             [ExcelFunction(Description="Describes the value passed to the function.", IsMacroType=true)]
             public static string nDescribe([ExcelArgument(AllowReference=true)]object arg)
             {
                 if (arg is ExcelReference)
                 {
                     ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
                     return "Formula: " + XlCall.Excel(XlCall.xlfGetFormula, arg) + " | " + cell.RowFirst + " | " + cell.ColumnFirst;
                 }
                 else
                     return "<not a formula>";
             }

On Tue, Oct 5, 2010 at 12:10 AM, Govert van Drimmelen <gov...@icon.co.za> wrote:

--
You received this message because you are subscribed to the Google Groups "Excel-Dna" group.
To post to this group, send email to exce...@googlegroups.com.

To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.

[Govert van Drimmelen]

Hi Rui,

I'm sorry, I really don't understand your question.
Could you try to re-phrase it, and state it differently with a new
example, maybe with some other formulas, stating what you would like,
and what you have tried so far?

Govert

> > exceldna+u...@googlegroups.com<exceldna%2Bunsubscribe@googlegroups.c om>
> > .

[Rui]

no worries. I was afraid that I might have communicated my question clearly. Let me try again.

I'm trying to determine the range passed to a function contained in a cell.  After I determine whether a cell contains a function, I need to know the parameters to that function. For example, if a cell, say cell A3, contains a function, "=ABS(B6)", I want to know the parameters passed to the ABS() function, in this case, the parameter is "B6". Unfortunately, the call: "XlCall.Excel(XlCall.xlfGetFormula, arg)" where "arg" is the cell object referenced, returns the parameters to the function, "=ABS(B6)", as "=ABS(R[-4]C[2])". The input parameter to the ABS function is returned as a relative position to the cell, A3, instead of the absolute position which is what I'm looking for.

Is there a way to get the absolute position or alternatively is there a way to get the position of the cell containing the function, in this case A3? I tried calling "(ExcelReference)XlCall.Excel(XlCall.xlfCaller);" but that gives me the position of the cell that is checking whether the cell A3 contains a function.

Does this make it a little clearer?  thanks.

To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.

[Govert van Drimmelen]

Hi Rui,

I see the problem with xlfGetFormula. I found xlfGetCell (GET.CELL)
(with parameter 6) which will give you the actual formula in whatever
reference style is current. Also useful might be xlfFormulaConvert
(FORMULA.CONVERT) which can convert the references in a formula to be
relative or absolute references.
You can download the official help file (XLMACR8.HLP) for the Excel
Macro language from here:
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=94be9dfa-8a84-4155-b75f-f29b15d5629f
The translation from the Macros to XlCall.Excel(XlCall.xlc....) is
normally easy.

This doesn't answer your complete question. The formula in a cell
might have many functions, and they might be nested. To build the
reference tree you will have to parse the formulas yourself.

Getting the actual formula that is in a cell can be done with
xlfGetCell like this:
<ExcelFunction(IsMacroType:=True)>
Function GetCellInfo(<ExcelArgument(AllowReference:=True)>
InputReference As Object)
Dim formula As String

If TypeOf InputReference Is ExcelReference Then
formula = CStr(XlCall.Excel(XlCall.xlfGetCell, 6,
InputReference))
Return formula

Else
Return "Not a reference"

End if
End Function

Hope this helps.

--Govert--

> > <exceldna%2Bunsubscr...@googlegroups.c om>

[Rui]

Thanks Govert. You answered my question. I didn't get around to trying it out until this morning, and the xlfGetCell function did what I needed. Something called "work" took priority over my time this week. :-)

To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.