[Solved] Selecting multiple cell ranges in OOo Basic

[theMoose]

Hi all,

I know how to select a cell range in OOo Basic, e.g.:

Code: Select all

oRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("A3:F3")

Is it also possible to select multiple cell ranges, like when holing the CTRL key pressed when selecting? e.g. A1:F1 and A3:F3 together.
If so, how to do this.

All help is welcome. Thanks in advance.

[Villeroy]

Many API functions take collections of type c.s.s.sheet.SheetCellRanges as arguments.
http://www.openoffice.org/api/docs/comm ... anges.html
http://www.openoffice.org/api/docs/comm ... Query.html

Code: Select all

a() =  Array("A1:B5", "B2:C6", "C3:D7")
oBasket = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
oView = ThisComponent.getCurrentController()
oSheet = oView.getActiveSheet()
for each n in a()
  rg = oSheet.getCellRangeByName(n)
  addr = rg.getRangeAddress()
  oBasket.addRangeAddress(addr, False)
next
oView.select(oBasket)

Easier to get but not that useful is an array of ranges:

Code: Select all

a() =  Array("A1:B5", "B2:C6", "C3:D7")
REM convert to semicolon separated list
s = join(a(), ";")
aRanges() = ThisComponent.Sheets.getCellRangesByName(s)

However, this can be converted into a SheetCellRanges collection:

Code: Select all

oBasket = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
for each rg in aRanges()
  oBasket.addRangeAddress(rg.getRangeAddress(), False)
next

[theMoose]

Thanks! This helped a lot. Works fine now.

[theMoose]

Okay.... while trying out some new things in my macro, I came up with an issue I can't explain.
If I declare the arrays a(), b() and c() as below, a() and b() will work but c() gives me an error if I use them like described in the first example by Villeroy.

Code: Select all

a() =  Array("A1:B5", "B2:C6", "C3:D7")
b() =  Array("A1", "B2", "C3")
c(0) = "A1"
c(1) = "B2"
c(2) = "C3"

If I print the TypeName and VarType for b(1) and c(1) (like below) I see that both are 'String' and '8'.

Code: Select all

print b(1), typename(b(1)), vartype(b(1))
print c(1), typename(c(1)), vartype(c(1))

The error I get is:
BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .

So what am I missing here? Why does c() give me an error?

[Villeroy]

c is a new, undefined variable. c(0) makes no sense because something new (typename = "Empty") can not contain any elements.

Code: Select all

Dim c as Integer
c(0) = x

raises the same error because something that has been declared as integer number can not hold any elements.
Basic is a dead language with poor array support. May be you find some Basic book on the flea market.
The developers guide on Basic is not too bad if you have some programming experience: https://wiki.openoffice.org/wiki/Docume ... .org_Basic
IMHO, Basic is not the right language for beginners because it is a dead end and because it is strictly limited to certain environments (MS Office, this one and some Windows-only environments). No Asian traveller learns Latin for a Europe trip.

[JeJe]

IMHO, Basic is not the right language for beginners because it is a dead end and because it is strictly limited to certain environments (MS Office, this one and some Windows-only environments).

BASIC was created for beginners. There are a lot of interpreters around... including cross platform ones. Plus VBA and Openoffice - the world's two main office suites.

The problem is Openoffice isn't very accessible for a beginner because the IDE is a bit clunky and lacks some of the most helpful features. I'd agree Openoffice basic probably isn't a place to start. But basic somewhere else wouldn't be a bad place. Macros on Microsoft Word are where I got into programming.

[musikai]

Code: Select all

dim c as integer
c(0) = 1

or

Code: Select all

dim c as string
c(0) = "A1"

[Villeroy]

Code: Select all

dim c as integer
c(0) = 1
c(1) = 2
c(3) = 3
print typename(c), c(999)

OK, no error, however neither an array. c is one integer simply ignoring the (x).
I think it should raise an error.

[musikai]

Yes, the correct way to declare the array is:

Code: Select all

dim c(1) as string
c(0) = "A1"
c(1) = "A2"
msgbox c(0)
msgbox c(2) 'Error

or as variant:

Code: Select all

dim c(1) as variant
X = 1
Y = "A2"
c(0) = X
c(1) = Y

print typename(c), c(0) , c(1)

[theMoose]

When I tried it with defining an empty array, say a() as Variant, that gave an error.
When I defined an array, say a(1) as Variant, it works without an error.
So, if I understand correctly, I need to define an array with a certain length to get it working. Afterwards I can ReDim that array anyway to adapt it to the length needed.

Thanks for all your effort with helping me with this issue.

[RoryOF]

The OO BASIC manual says "The array name is followed by parentheses which contain the specification for the number of elements."
In OO BASIC arrays start numbering at 0. There is further elaboration in the OO BASIC manual.

[Villeroy]

True programming languages provide many functions to expand, shrink, filter, sort, analyse arrays.
One of many functions desparately missing in StarBasic:

Code: Select all

'very simple routine appending some element to an array which can be undimensioned (LBound > UBound)
Sub bas_PushArray(xArray(),vNextElement)
Dim iUB%,iLB%
	iLB = lBound(xArray())
	iUB = uBound(xArray())
	If iLB > iUB then
		iUB = iLB
		redim xArray(iLB To iUB)
	else
		iUB = iUB +1
		redim preserve xArray(iLB To iUB)
	endif
	xArray(iUB) = vNextElement
End Sub

Usage:

Code: Select all

a = Array()
' print lBound(a), uBound(a) ' -1 to 0
bas_PushArray(a, "abc")
bas_PushArray(a, 123)
bas_PushArray(a, ThisComponent)

[JeJe]

OOBasic is very similar to VB6 so there are a lot of array functions available. This is one of the best sources:

http://www.planet-source-code.com/vb/sc ... 6&lngWId=1

Probably windows only as it uses CopyMemory - don't know if it could run under Linux with Wine. And some minor adaptations will be needed - eg with OOBasic not allowing a value to be specified for optional declares for the sub/function.