"Get last used column/row" in a spreadsheet (Macro)

[Johnny Rosenberg]

At page 142 in the macro manual by A. Pitonyak, there is a section
about how to do it, here's a function that returns the last column of
the used area:

Function getLastUsedColumn(oSheet as Object) as Integer
  Dim oCell As Object
  Dim oCursor As Object
  Dim aAddress As Variant
  oCell = oSheet.GetCellbyPosition( 0, 0 )
  oCursor = oSheet.createCursorByRange(oCell)
  oCursor.GotoEndOfUsedArea(True)
  aAddress = oCursor.RangeAddress
  GetLastUsedColumn = aAddress.EndColumn
End Function

I am wondering about the oCell thing. Why is that necessary? I tried
the following and it seems to work:

Function getLastUsedColumn(oSheet as Object) as Integer
  Dim oCursor As Object
  Dim aAddress As Variant
  oCursor = oSheet.createCursor
  oCursor.GotoEndOfUsedArea(True)
  aAddress = oCursor.RangeAddress
  GetLastUsedColumn = aAddress.EndColumn
End Function

Or even shorter, and still works and just as easy to follow (in my opinion):

Function getLastUsedColumn(oSheet as Object) as Integer
  Dim oCursor As Object
  oCursor = oSheet.createCursor
  oCursor.GotoEndOfUsedArea(True)
  GetLastUsedColumn = oCursor.RangeAddress.EndColumn
End Function

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[pitonyak]

Nice tip... I will change this in my document...

I have no idea why this is written as it is, but many of the snippets
were created years ago with OOo 1.x, so, the issue may be that the
author of that particular macro (probably me) was not properly
efficient, or, that OOo did not support the methodology. In this case, I
suspect the first.

On 08/30/2009 08:54 AM, Johnny Rosenberg wrote:

> At page 142 in the macro manual by A. Pitonyak, there is a section
> about how to do it, here's a function that returns the last column of
> the used area:
>
> Function getLastUsedColumn(oSheet as Object) as Integer
>    Dim oCell As Object
>    Dim oCursor As Object
>    Dim aAddress As Variant
>    oCell = oSheet.GetCellbyPosition( 0, 0 )
>    oCursor = oSheet.createCursorByRange(oCell)
>    oCursor.GotoEndOfUsedArea(True)
>    aAddress = oCursor.RangeAddress
>    GetLastUsedColumn = aAddress.EndColumn
> End Function
>
> I am wondering about the oCell thing. Why is that necessary? I tried
> the following and it seems to work:
>
> Function getLastUsedColumn(oSheet as Object) as Integer
>    Dim oCursor As Object
>    Dim aAddress As Variant
>    oCursor = oSheet.createCursor
>    oCursor.GotoEndOfUsedArea(True)
>    aAddress = oCursor.RangeAddress
>    GetLastUsedColumn = aAddress.EndColumn
> End Function
>
> Or even shorter, and still works and just as easy to follow (in my opinion):
>
> Function getLastUsedColumn(oSheet as Object) as Integer
>    Dim oCursor As Object
>    oCursor = oSheet.createCursor
>    oCursor.GotoEndOfUsedArea(True)
>    GetLastUsedColumn = oCursor.RangeAddress.EndColumn
> End Function
>
> Johnny Rosenberg
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>
>    

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info:  http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Steffen Grund]

I was just wondering: createCursor() creates a cursor containing the
whole sheet, which could be slow when you have a big sheet.
Maybe this is a way to create the cursor with better performance.

Just an idea, Steffen

Andrew Douglas Pitonyak wrote:

> Nice tip... I will change this in my document...
>
> I have no idea why this is written as it is, but many of the snippets
> were created years ago with OOo 1.x, so, the issue may be that the
> author of that particular macro (probably me) was not properly
> efficient, or, that OOo did not support the methodology. In this case, I
> suspect the first.
>
> On 08/30/2009 08:54 AM, Johnny Rosenberg wrote:
>> At page 142 in the macro manual by A. Pitonyak, there is a section
>> about how to do it, here's a function that returns the last column of
>> the used area:
>>
>> Function getLastUsedColumn(oSheet as Object) as Integer
>>    Dim oCell As Object
>>    Dim oCursor As Object
>>    Dim aAddress As Variant
>>    oCell = oSheet.GetCellbyPosition( 0, 0 )
>>    oCursor = oSheet.createCursorByRange(oCell)
>>    oCursor.GotoEndOfUsedArea(True)
>>    aAddress = oCursor.RangeAddress
>>    GetLastUsedColumn = aAddress.EndColumn
>> End Function
>>
>> I am wondering about the oCell thing. Why is that necessary? I tried
>> the following and it seems to work:
>>
>> Function getLastUsedColumn(oSheet as Object) as Integer
>>    Dim oCursor As Object
>>    Dim aAddress As Variant
>>    oCursor = oSheet.createCursor
>>    oCursor.GotoEndOfUsedArea(True)
>>    aAddress = oCursor.RangeAddress
>>    GetLastUsedColumn = aAddress.EndColumn
>> End Function
>>
>> Or even shorter, and still works and just as easy to follow (in my
>> opinion):
>>
>> Function getLastUsedColumn(oSheet as Object) as Integer
>>    Dim oCursor As Object
>>    oCursor = oSheet.createCursor
>>    oCursor.GotoEndOfUsedArea(True)
>>    GetLastUsedColumn = oCursor.RangeAddress.EndColumn
>> End Function
>>
>> Johnny Rosenberg
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: dev-unsubscribe@...
>> For additional commands, e-mail: dev-help@...
>>
>>    
>

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[pitonyak]

I wrote some test code to see which was faster. I found the following:

Shorter version: 0.1337 system ticks per iteration
Longer version:  0.2431 system ticks per iteration

My understanding is that the Basic interpreter is not nearly as
efficient as the API, so I guessed that I could make the longer version
more efficient by reducing the number of statements and not assigning
the address to a temporary variable. This brought the longer longer
version down in time to 0.2228 system ticks per iteration. This is
better, but still not as good as the first version.

Sub TestGetSpeed
   Dim nItCount As Integer
   Dim nMaxIt As Integer
   Dim lTick1 As Long
   Dim lTick2 As Long
   Dim oSheet
   Dim nRow As Integer
   Dim s As String


   Dim oCell
   Dim oCursor
   Dim aAddress

   nMaxIt = 10000
   oSheet = ThisComponent.getSheets().getByIndex(0)
   lTick1 = GetSystemTicks()
   For nItCount = 1 To nMaxIt
     oCursor = oSheet.createCursor
     oCursor.GotoEndOfUsedArea(False)
     nRow = oCursor.RangeAddress.EndRow
   Next
   lTick2 = GetSystemTicks()

   s = s & "Small version used " & (lTick2 - lTick1) & " ticks for " & _
           nMaxIt & " iterations " & CHR$(10) & _
           CStr((lTick2 - lTick1) / nMaxIt) & _
           " ticks per iteration" & CHR$(10)

   lTick1 = GetSystemTicks()
   For nItCount = 1 To nMaxIt
     oCell = oSheet.GetCellbyPosition( 0, 0 )
     oCursor = oSheet.createCursorByRange(oCell)
     oCursor.GotoEndOfUsedArea(False)
     nRow = oCursor.RangeAddress.EndColumn
   Next
   lTick2 = GetSystemTicks()



   s = s & "Large version used " & (lTick2 - lTick1) & " ticks for " & _
           nMaxIt & " iterations " & CHR$(10) & _
           CStr((lTick2 - lTick1) / nMaxIt) & _
           " ticks per iteration" & CHR$(10)


   MsgBox s, 0, "Run Time"
End Sub


On 09/07/2009 06:40 AM, Steffen Grund wrote:

> I was just wondering: createCursor() creates a cursor containing the
> whole sheet, which could be slow when you have a big sheet.
> Maybe this is a way to create the cursor with better performance.
>
> Just an idea, Steffen
>
> Andrew Douglas Pitonyak wrote:
>> Nice tip... I will change this in my document...
>>
>> I have no idea why this is written as it is, but many of the snippets
>> were created years ago with OOo 1.x, so, the issue may be that the
>> author of that particular macro (probably me) was not properly
>> efficient, or, that OOo did not support the methodology. In this
>> case, I suspect the first.
>>
>> On 08/30/2009 08:54 AM, Johnny Rosenberg wrote:
>>> At page 142 in the macro manual by A. Pitonyak, there is a section
>>> about how to do it, here's a function that returns the last column of
>>> the used area:
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>    Dim oCell As Object
>>>    Dim oCursor As Object
>>>    Dim aAddress As Variant
>>>    oCell = oSheet.GetCellbyPosition( 0, 0 )
>>>    oCursor = oSheet.createCursorByRange(oCell)
>>>    oCursor.GotoEndOfUsedArea(True)
>>>    aAddress = oCursor.RangeAddress
>>>    GetLastUsedColumn = aAddress.EndColumn
>>> End Function
>>>
>>> I am wondering about the oCell thing. Why is that necessary? I tried
>>> the following and it seems to work:
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>    Dim oCursor As Object
>>>    Dim aAddress As Variant
>>>    oCursor = oSheet.createCursor
>>>    oCursor.GotoEndOfUsedArea(True)
>>>    aAddress = oCursor.RangeAddress
>>>    GetLastUsedColumn = aAddress.EndColumn
>>> End Function
>>>
>>> Or even shorter, and still works and just as easy to follow (in my
>>> opinion):
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>    Dim oCursor As Object
>>>    oCursor = oSheet.createCursor
>>>    oCursor.GotoEndOfUsedArea(True)
>>>    GetLastUsedColumn = oCursor.RangeAddress.EndColumn
>>> End Function
>>>
>>> Johnny Rosenberg
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: dev-unsubscribe@...
>>> For additional commands, e-mail: dev-help@...
>>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info:  http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Johnny Rosenberg]

2009/9/7 Steffen Grund <Steffen.Grund@...>:
> I was just wondering: createCursor() creates a cursor containing the whole
> sheet, which could be slow when you have a big sheet.

I guess it depends on how it is doing that. Maybe it is only setting a
bunch of pointers rather than reading and copying information from the
spreadsheet, but seriously I don't have a clue. I actually downloaded
the source code for OpenOffice.org once, and I was going to find out
how parts of it is done, but it was too heavy so I gave up after a
short while…

J.R.

> Maybe this is a way to create the cursor with better performance.
>
> Just an idea, Steffen
>
> Andrew Douglas Pitonyak wrote:
>>
>> Nice tip... I will change this in my document...
>>
>> I have no idea why this is written as it is, but many of the snippets were
>> created years ago with OOo 1.x, so, the issue may be that the author of that
>> particular macro (probably me) was not properly efficient, or, that OOo did
>> not support the methodology. In this case, I suspect the first.
>>
>> On 08/30/2009 08:54 AM, Johnny Rosenberg wrote:
>>>
>>> At page 142 in the macro manual by A. Pitonyak, there is a section
>>> about how to do it, here's a function that returns the last column of
>>> the used area:
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>   Dim oCell As Object
>>>   Dim oCursor As Object
>>>   Dim aAddress As Variant
>>>   oCell = oSheet.GetCellbyPosition( 0, 0 )
>>>   oCursor = oSheet.createCursorByRange(oCell)
>>>   oCursor.GotoEndOfUsedArea(True)
>>>   aAddress = oCursor.RangeAddress
>>>   GetLastUsedColumn = aAddress.EndColumn
>>> End Function
>>>
>>> I am wondering about the oCell thing. Why is that necessary? I tried
>>> the following and it seems to work:
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>   Dim oCursor As Object
>>>   Dim aAddress As Variant
>>>   oCursor = oSheet.createCursor
>>>   oCursor.GotoEndOfUsedArea(True)
>>>   aAddress = oCursor.RangeAddress
>>>   GetLastUsedColumn = aAddress.EndColumn
>>> End Function
>>>
>>> Or even shorter, and still works and just as easy to follow (in my
>>> opinion):
>>>
>>> Function getLastUsedColumn(oSheet as Object) as Integer
>>>   Dim oCursor As Object
>>>   oCursor = oSheet.createCursor
>>>   oCursor.GotoEndOfUsedArea(True)
>>>   GetLastUsedColumn = oCursor.RangeAddress.EndColumn
>>> End Function
>>>
>>> Johnny Rosenberg
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: dev-unsubscribe@...
>>> For additional commands, e-mail: dev-help@...
>>>
>>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Niklas Nebel]

On 09/07/09 18:59, Johnny Rosenberg wrote:

> 2009/9/7 Steffen Grund <Steffen.Grund@...>:
>> I was just wondering: createCursor() creates a cursor containing the whole
>> sheet, which could be slow when you have a big sheet.
>
> I guess it depends on how it is doing that. Maybe it is only setting a
> bunch of pointers rather than reading and copying information from the
> spreadsheet, but seriously I don't have a clue. I actually downloaded
> the source code for OpenOffice.org once, and I was going to find out
> how parts of it is done, but it was too heavy so I gave up after a
> short while…

As long as no contents or cell attributes are read from the object,
having a cursor for the whole sheet isn't a problem.

Not directly related, but perhaps worth mentioning: If you use
gotoEndOfUsedArea together with lockControllers (XModel interface), you
might run across issue 105279. The work-around then is to unlock
controllers temporarily.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...