How can I get the reference of the cell (and not the value) as a parameter?

[Ana Clara Rocha]

        public static string CodCor(object fCell)
        {
            try
            {
                ExcelReference cellRef = (ExcelReference)fCell;
                double colorValue = (double)XlCall.Excel(XlCall.xlfGetCell, 63, cellRef);
                string xColor = ((int)colorValue).ToString("X").PadLeft(6, '0');

                return $"{xColor.Substring(4, 2)}{xColor.Substring(2, 2)}{xColor.Substring(0, 2)}";
            }
            catch (Exception ex)
            {
                return $"Erro: {ex.Message}";
            }
        }

In my code, Excel-DNA does not accept the ExcelReference type as a parameter. However, when I use an object, it returns the value in the cell. I want to retrieve the background color. How can I achieve this in this function?  

[Govert van Drimmelen]

Hi Ana,

 

I give a fixed up example below.

 

You need to:

1. Mark the parameter as “AllowReference=true” to get an ExcelReference object when appropriate, and
2. Mark the function as “IsMacroType=true” to allow you to use the xlfGetXXX info functions inside a UDF.

 

Note that a function with both of these settings will be considered ‘volatile’ by Excel, and hence calculate on every sheet change.

This holds even if you mark the function as “IsVolatile=false”.

I added a time to the result so you can see the function recalculating as you enter other values on the sheet.

 

-Govert

 

 

        [ExcelFunction(IsMacroType = true)]

        public static string CodCor([ExcelArgument(AllowReference=true)] object fCell)

        {

            try

            {

                ExcelReference cellRef = fCell as ExcelReference;

                if (cellRef != null)

                {

                    double colorValue = (double)XlCall.Excel(XlCall.xlfGetCell, 63, cellRef);

                    string xColor = ((int)colorValue).ToString("X").PadLeft(6, '0');

 

                    return $"{xColor.Substring(4, 2)}{xColor.Substring(2, 2)}{xColor.Substring(0, 2)} @ {DateTime.Now:HH:mm:ss.fff}";

                }

                else

                {

                    return "Erro: Referência inválida";

--
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/80e71969-8506-4a99-a2b2-0ff5586e2383n%40googlegroups.com.

[Sergey Zhilyakov]

Hello all,

just in case, the function may become non-volatile after adding the following code at the beginning of the function.

XlCall.Excel(XlCall.xlfVolatile, false)

Best regards,

Sergey

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/037701db0ab8%2465f86740%2431e935c0%24%40gmail.com.