How to refer to the Calling Cell?

[Faraz Ahmed Qureshi]

Dear Govert,

While I am using C# with Excel DNA latest 1.6 Preview version, sure am interested in any sample for a UDF, without any argument, to be considering the cell containing such  a function.

For Example, one like  ThisCellHeight() or ThisCellWidth() to be returning a Double representing the Height or Width of the said Cell's Row or Column.

Thanks in advance.

Your Fan,

Faraz

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

With a small monthly contribution you encourage further development and support.

--------------------------------------------------

 

Hi Faraz,

 

If you want information about the calling cell for a function, you can get an ExcelReference object that refers to the calling call with code like this:

    ExcelReference refCaller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

(It might sometimes be null, e.g. if the function is called via Application.Run from VBA.)

 

To get information about the cell referred to by an ExcelReference, you can use the C API information function xlfGetCell.

You can find a some sample code here: Samples/GetInfoAddIn.cs at master · Excel-DNA/Samples (github.com)

GetInfoAddIn exposes the various GET.XXXX information functions from the C API as user-defined worksheet functions.

These functions let you get information about a cell, sheet, workbook etc.

Every function gets passed a type_num  argument which specifies what piece of information to fetch.

For your case you’d call GetCell with the numbers 16 and 17.

 

There is also a matching workbook called GetInfoSample.xls which shows the various functions and information types.

The full documentation for the GET.XXX functions is in the macro help file – you can find more information about the C API and links to the help file here: Excel C API · Excel-DNA/ExcelDna Wiki (github.com)

 

-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/bf6423f8-c950-42f5-a929-ea5a5b303cccn%40googlegroups.com.

[Faraz Ahmed Qureshi]

No doubt, 16 and 17 may be used in an example of xlfGetCell, as  GetCell(16, $A$1) for cell A1, but how to use the same without any address parameter i.e. without referring the $A$1 in the arguments itself?

16 or 17 too used in the body of the code instead of being passed as a parameter.

Kindly see if you can present an example of xlfCaller in this regard.

[Govert van Drimmelen]

Hi Faraz,

 

You have all the pieces you need to write those functions now:

 

using ExcelDna.Integration;

using static ExcelDna.Integration.XlCall;

 

public static class Functions

{

            [ExcelFunction(IsMacroType=true)]

            public static object GetCallerCellWidth() => Excel(xlfGetCell, 16, Excel(xlfCaller));

 

            [ExcelFunction(IsMacroType=true)]

            public static object GetCallerCellHeight() => Excel(xlfGetCell, 17, Excel(xlfCaller));

}

 

I don’t know how you would get them to recalculate automatically when the height or width change.

For now you need to re-enter with F2, or recalculate with Alt+Ctrl+F9.

 

There was an earlier discussion asking if there is some Excel event that fires when a column or row is resized, but I don’t see an answer there, and don’t have any better suggestion. See here Intercept column width changed (google.com)

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d06f5925-6477-406c-9a2d-551b57dce0fan%40googlegroups.com.

[Faraz Ahmed Qureshi]

Thanks Govert!

No doubt excellent work of yours.

However, any reason of the GetCallerCellWidth returning result in an array of two cells, while the Height function reflects in one?

In a modified version as follows, how to implement a dimensional array for a conditional parameter like TheCell and the xlfCaller.

[ExcelFunction(IsMacroType = true, IsVolatile = true)]
        public static object GetCallerCellWidth([ExcelArgument(AllowReference = true)] object TheCell)
        {
            if (TheCell is ExcelMissing)
            {
                return XlCall.Excel(XlCall.xlfGetCell, 16, XlCall.Excel(XlCall.xlfCaller));
            }
            else
            {
                return XlCall.Excel(XlCall.xlfGetCell, 16, TheCell);
            }

        } 

Thanx again, buddy.

[Govert van Drimmelen]

Hi Faraz,

 

Yes, according to the documentation in the MacroHelp file, item 16 returns:

“A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell's width is set to change as the standard width changes (TRUE) or is a custom width (FALSE).”

 

Your function can just return the  first item in the array:

            [ExcelFunction(IsMacroType=true)]

            public static object GetCallerCellWidth() => ((object[,])Excel(xlfGetCell, 16, Excel(xlfCaller)))[0,0];

 

I don’t understand what you mean by “a dimensional array for a conditional parameter”

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/91079084-c0b8-4c02-b251-6f80dc883769n%40googlegroups.com.

[Faraz Ahmed Qureshi]

Parameter TheCell is Conditional as to be referring to the default value in case of being ExcelMissing my dear!