C#: get last used column in row

[Simon]

Hi there,

I'm new to Excel-Dna and want to create a UDF using the C API that returns the number of the last used column in the row selected by the user.
In VBA I have the following which works:

Public Function LastUsedColumnInRow(rRow As Range) As Long
    LastUsedColumnInRow = Range("XFD" & rRow.row).End(xlToLeft).Column
End Function

The following C# function works as intended but is painfully slow if e.g. 10 of them are added to a sheet.

[ExcelFunction(IsMacroType = true)]

public static object LastUsedColumnInRow([ExcelArgument(AllowReference = true)] object objRange)

{

    if (objRange.GetType().ToString() == "ExcelDna.Integration.ExcelReference")

    {

        ExcelReference xlr = (ExcelReference)objRange;

        int row = xlr.RowFirst;

        int newCol = ExcelDnaUtil.ExcelLimits.MaxColumns - 1;

        ExcelReference xlref = new ExcelReference(row, row, newCol, newCol, xlr.SheetId);

        while (xlref.GetValue() == ExcelEmpty.Value && newCol > -1)

        {

            newCol--;

            xlref = new ExcelReference(row, row, newCol, newCol);

        }

        return xlref.ColumnLast + 1;

    }

    else { return false; }

}

The following C# function seems to be fast, but is not working correctly.

The function returns the column number of the currently selected cell in a sheet instead of the column number of the input cell.

[ExcelFunction(IsMacroType = true)]

public static object LastUsedColumnInRow2([ExcelArgument(AllowReference = true)] object objRange)

{

    if (objRange.GetType().ToString() == "ExcelDna.Integration.ExcelReference")

    {

        ExcelReference xlr = (ExcelReference)objRange;

        int row = xlr.RowFirst;

        int firstCol = xlr.ColumnFirst;

        ExcelReference xlref = new ExcelReference(row, row, firstCol, firstCol, xlr.SheetId);

        ExcelReference xlEnd = xlref.End(DirectionType.ToRight);

        return xlEnd.ColumnLast + 1;

    }

    else { return false; }

}

// Direction

public enum DirectionType

{

    ToLeft = 1,

    ToRight = 2,

    Up = 3,

    Down = 4

}

// Get last used cell in the specified direction

public static ExcelReference End(this ExcelReference reference, DirectionType direction)

{

    ExcelReference end = null;

    using (new ExcelSelectionHelper(reference))

    {

        // myReference is selected now... ???

        XlCall.Excel(XlCall.xlcSelectEnd, (int)direction);

        ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

        int row = selection.RowFirst;

        int col = selection.ColumnFirst;

        end = new ExcelReference(row, row, col, col, selection.SheetId);

    }

    return end;

}

I guess the problem is in the last part with the End function, where I'm also using the ExcelSelectionHelper by Govert.

Hope any of you can help in the right direction.

Thanks

[Govert van Drimmelen]

You don't have to interfere with the selection (which is based on the ActiveSheet and not the current sheet where the calculation caller is, which might be different).

To get the used range with the C API you can use GET.DOCUMENT (xlfGetDocument) with the type_num parameters 10 to 12:

9   - Number of the first used row. If the sheet is empty, returns 0.

10 -  Number of the last used row. If the sheet is empty, returns 0.

11 -  Number of the first used column. If the sheet is empty, returns 0.

12 -  Number of the last used column. If the sheet is empty, returns 0.

You'd call these with the sheet name:

    double lastRow = (double)XlCall.Excel(XlCall.xlfGetDocument, 10, "Sheet1");

You can get an ExcelReference for the caller with xlfCaller, and get the sheet name with xlSheetNm.

If you call without the sheet name:

    double firstRow = (double)XlCall.Excel(XlCall.xlfGetDocument, 9);

    double lastRow = (double)XlCall.Excel(XlCall.xlfGetDocument, 10);

    double firstCol = (double)XlCall.Excel(XlCall.xlfGetDocument, 11);

    double lastCol = (double)XlCall.Excel(XlCall.xlfGetDocument, 12);

I'm not sure which sheet is used. If I get a chance I'll experiment a bit.

-Govert

[Govert van Drimmelen]

OK - here's a nice example that gets the sum of a range: https://gist.github.com/govert/e66c5462901405dc96aab8e77abef24c

using ExcelDna.Integration;

using static System.Math;

using static ExcelDna.Integration.XlCall;

public static class TestFunctions

{

    [ExcelFunction(Description = "Returns the sum of cells in the target (using the used range for performance)", IsMacroType = true)]

    public static object GetUsedSum([ExcelArgument(AllowReference = true)] object target)

    {

        // NOTE: This function will be volatile by default,

        // since it is both IsMacroType=true and has an AllowReference=true.

        // But I don't think it needs to be (not quite sure), since we only read cells that are part of the input arguments.

        // So we can reset the volatile flag for every call.

        Excel(xlfVolatile, false);

        if (!(target is ExcelReference))

            return ExcelError.ExcelErrorValue;

        object targetSheetName = Excel(xlSheetNm, target);

        // The used range rows and columns here are 1-based (returning 0 for an empty sheet)

        // We adjust to be 0-based, to match the ExcelReference convention

        // For an empty sheet (where we now get RowLast = ColumnLast = -1) the ExcelReference will convert the -1 values back to 0, 

        // so will get an ExcelEmpty from GetValue() and eventually (correctly) return 0 from the function.

        int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;

        int lastRow  = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;

        int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;

        int lastCol  = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;

        

        ExcelReference targetRef = (ExcelReference)target;

        ExcelReference usedTarget = new ExcelReference(

                rowFirst: Max(firstRow, targetRef.RowFirst),

                rowLast: Min(lastRow, targetRef.RowLast),

                columnFirst: Max(firstCol, targetRef.ColumnFirst),

                columnLast: Min(lastCol, targetRef.ColumnLast),

                sheetId: targetRef.SheetId);

        

        object values = usedTarget.GetValue();

        double sum = 0;

        if (values is object[,])

        {

            object[,] valuesArr = (object[,])values;

            for (int i = 0; i < valuesArr.GetLength(0); i++)

            {

                for (int j = 0; j < valuesArr.GetLength(1); j++)

                {

                    var value = valuesArr[i,j];

                    if (value is double)

                    {

                        sum += (double)value;

                    }

                }

            }

        }

        else if (values is double)

        {

            // Single cell

            sum = (double)values;

        }

        return sum;

    }

}

You can call it with 

    =GetUsedSum(Sheet2!A:XFD)

and it won't fail (unless you make the used range huge)!

-Govert

[Simon]

Hi Govert, thank you for your comments.

The used range approach works fine, but it doesn't really achieve what I want. 

I want to get the "used range" of a target row, i.e. the last column number (letter) of that target row.

Perhaps it's not possible to do it similar to the VBA approach, in which case, I will just have to stick with the used range approach that you suggested.

Regards,

Simon

[Govert van Drimmelen]

Ah - I understand!

I got carried away a bit.

There might some some constraints on using the C API to change the selection in a UDF.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Simon [bonde...@gmail.com]
Sent: 14 April 2016 10:21 PM
To: Excel-DNA
Subject: [ExcelDna] Re: C#: get last used column in row

--
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Terry Aney]

This code should help (Might have a few helpers in there that are unnecessary but code should get you going in right direction).  Let me know if you have any questions.:

public enum DirectionType

{

Up = 3,

ToRight = 2,

Down = 4,

ToLeft = 1

}

public static ExcelReference End( this ExcelReference reference, DirectionType direction )

{

ExcelReference end = null;

reference.RestoreSelection( () =>

{

if (

( direction == DirectionType.Down && reference.Offset( 1, 0 ).GetValue() == ExcelEmpty.Value ) ||

( direction == DirectionType.ToRight && reference.Offset( 0, 1 ).GetValue() == ExcelEmpty.Value ) ||

( direction == DirectionType.ToLeft && reference.Offset( 0, -1 ).GetValue() == ExcelEmpty.Value ) ||

( direction == DirectionType.Up && reference.Offset( -1, 0 ).GetValue() == ExcelEmpty.Value )

)

{

end = reference;

}

else

{

// Govert talks about 'messiness' at http://stackoverflow.com/a/10920622/166231, but pretty straight forward

XlCall.Excel( XlCall.xlcSelectEnd, (int)direction );

var selection = XlCall.Excel( XlCall.xlfSelection ) as ExcelReference;

var row = selection.RowFirst;

var col = selection.ColumnFirst;

end = new ExcelReference( row, row, col, col, selection.SheetId );

}

} );

return end;

}

Then in a static class:

public static void RestoreSelection( this ExcelReference reference, Action action )

{

ApplicationEx.RestoreSelection( action, reference );

}

public static void RestoreSelection( Action action, ExcelReference range = null )

{

// https://groups.google.com/d/msg/exceldna/h3SqSA8DkPc/X0uxH4pUBgAJ - read comment about his SelectionHelper

bool updating = ScreenUpdating;

try

{

if ( updating ) ScreenUpdating = false;

//remember the current active cell

var current = new

{

Selection = XlCall.Excel( XlCall.xlfSelection ),

Cell = XlCall.Excel( XlCall.xlfActiveCell )

};

if ( range != null )

{

//select caller worksheet containing range caller desires to be active

// (need to do this before reading selection/cell on this sheet)

var rangeSheet = (string)XlCall.Excel( XlCall.xlSheetNm, range );

XlCall.Excel( XlCall.xlcWorkbookSelect, new object[] { rangeSheet } );

}

// record selection and active cell on the sheet we want to select

var sheetCurrent = range != null

? new

{

Selection = XlCall.Excel( XlCall.xlfSelection ),

Cell = XlCall.Excel( XlCall.xlfActiveCell ),

OriginalSheet = (string)XlCall.Excel( XlCall.xlSheetNm, current.Selection )

}

: null;

if ( range != null )

{

// Select the range caller desires to be active...

XlCall.Excel( XlCall.xlcSelect, range );

}

action();

// Now restore everything...

if ( range != null )

{

// Reset the selection on the target sheet

XlCall.Excel( XlCall.xlcSelect, sheetCurrent.Selection, sheetCurrent.Cell );

// Reset the sheet originally selected

XlCall.Excel( XlCall.xlcWorkbookSelect, new object[] { sheetCurrent.OriginalSheet } );

}

// Reset the selection in the active sheet (some bugs make this change sometimes too)

XlCall.Excel( XlCall.xlcSelect, current.Selection, current.Cell );

}

finally

{

if ( updating ) ScreenUpdating = true;