XLL UDF: number of rows and columns in active sheet?

Add-in Express™ Support Service
That's what is more important than anything else

XLL UDF: number of rows and columns in active sheet?
 
Heiko Sunev




Posts: 13
 Joined: 2008-11-30
Hi.
Is there a way to determine the (maximum) number of rows and columns of the worksheet in which the XLL UDF is located?
For instance In Excel2003, 256 columns. In Excel2007/10, 256 or 16384 columns, depending on whether or not the compatibility mode is used for the workbook.

I could not get the worksheet and its Rows.Count and Columns.Count properties. Therefore, I tried to use the built-in Excel functions =ROWS(A:A) and =COLUMNS(1:1) within an XLL function.

Dim XLc(0) as Object
XLc(0)="1:1"
XDim Cmax As Object = XLLModule1.CurrentInstance.CallWorksheetFunction( _
AddinExpress.MSO.ADXExcelWorksheetFunction.Columns, XLc)

I also tried, in vain, to pass the argument 1:1 in this way, by using INDIRECT:
Dim XLc(0) As Object
XLc(0) = XLLModule1.CurrentInstance.CallWorksheetFunction( _
AddinExpress.MSO.ADXExcelWorksheetFunction.Indirect, {"1:1", True})
Dim Cmax As Object = XLLModule1.CurrentInstance.CallWorksheetFunction( _
AddinExpress.MSO.ADXExcelWorksheetFunction.Columns, XLc)

However, ADXExcelWorkSheetFunction.Columns returns a wrong result, 15, not 256 or 16384.
How would I have to formulate the argument XLc to have ADXExcelWorksheetFunction.Columns return the same result as =COLUMNS(1:1) ?

Or is there an alternative way to get these worksheet properties from within a XLL UDF?

thank you
Posted 16 Jan, 2011 13:39:59 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Heiko.

Usually, to get the available/used range you need to call the UsedRange property of Excel worksheet.
I would advise you to access the Excel object model from an XLL macro function.
To call the macro you can use the ExecuteExcel4Macro method of Excel.
Posted 17 Jan, 2011 08:32:54 Top