Issues with using C# library in Excel 2007 VBA

Wheels012

Good morning. I have created a library of functions in C#, and I am testing them to see if they work in both VBA and on a spreadsheet (I am ok if they only work in VBA). I have C# code to display the LastColumn and the LastRow in a spreadsheet as follows:

///
/// Finds the last column on a worksheet.
///
///
///
public string FindLastColumn(Worksheet xlWorkSheet)
{
int nInLastCol = 0;

// Find the last real column
nInLastCol = xlWorkSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, XlSearchOrder.xlByColumns,XlSearchDirection.xlPrevious,
false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

return GetExcelColumnName(nInLastCol);
}

///
/// Converts the column # to the corresponding letter.
///
///
///
private string GetExcelColumnName(int columnNumber)
{
int dividend = columnNumber;
string columnName = String.Empty;
int modulo;

while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}

return columnName;
}

///
/// Finds the last row on a worksheet.
///
///
///
public long FindLastRow(Worksheet xlWorkSheet)
{
long nInLastRow = 0;

// Find the last real row
nInLastRow = xlWorkSheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
XlSearchOrder.xlByRows,XlSearchDirection.xlPrevious,
false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;
return nInLastRow;
}

The FindLastColumn code works in VBA, but the FindLastRow does not, and neither works on a sheet (not sure how to correctly pass in the current sheet). The error is: Function or interface marked as retricted, or the function uses an Automation type not supported in Visual Basic. VBA:

Dim TSExcelLib As New TechSolutionsXLLibrary.ExcelFunctions

Private Sub CommandButton1_Click()

MsgBox TSExcelLib.FindLastColumn(Sheets("Sheet1")) 'Works
MsgBox TSExcelLib.FindLastColumn(Sheet1) 'Works

MsgBox TSExcelLib.FindLastColumn(Sheets("Sheet1")) 'Doesn't work
MsgBox TSExcelLib.FindLastColumn(Sheet1) 'Doesn't work

Dave Kreskowiak

I'm not an Office dev, but is there any particular reason you wouldn't use _worksheet_.UsedRange() and just return the appropriate row or column value? Or...Worksheets("_name_").Cells.SpecialCells(xlCellTypeLastCell).Row?

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Wheels012

It has been a while since I created the code, and I don't recall why I took that approach, but I can give the UsedRange 'method' a try. Thank you for your reply. WHEELS

TnTinMn

Quote:

The FindLastColumn code works in VBA, but the FindLastRow does not, and neither works on a sheet (not sure how to correctly pass in the current sheet). The error is: Function or interface marked as retricted, or the function uses an Automation type not supported in Visual Basic

Just a stab in the dark, but you have defined FindLastRow as "long". The VBA "Long" integer is 32 bit versus the c# "long" which is a 64 bit integer.

Chris Quinn

The UsedRange in Excel is notoriously unreliable - if the cursor has been moved to a blank cell outside the range of your data, Excel often sets the UsedRange to that cell, meaniing that you can potentially end up by reading large numbers of blank rows when you don't need to

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================