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: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
--
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: [email protected]
For additional commands, e-mail: [email protected]
