Macro: Listing multiple cell ranges

Hi all,

I am using Calc, LibreOffice version 6.0.7.3.
I have a problem with finding the correct code to list the address of a multiple range selection.

When I want to use a multiple rangeaddress, “c2:e7”, “g2:l7”, “t2:l7”.

You could use the code below:

dim lR as long, lC as long

dim sRng as string, sR as string

dim oRng as object, oRngTmp as object

sRng = “c2:e7”, “g2:l7”, “t2:l7”

for each sR in Split(sRng, “,”)

oRng = ThisComponent.getSheets().getByName(“Sheet2”).getCellRangeByName(sR)

for lR = oRng.RangeAddress.startRow to oRng.RangeAddress.startRow

for lC = oRng.RangeAddress.startColumn to oRng.RangeAddress.endColumn

oRngTmp = ThisComponent.getSheets().getByName(“Sheet2”).getCellRangeByPosition(lC, lR)

print oRngTmp.absoluteName

next

next

p.s.:
where can I find documentation that lists the methods and property´s used by objects.

Hello, Retracted answer because it was incomplete. However, for the best reference concerning macros, see Open Office Macros by Andrew Pitonyak → OOME

.

Hello Ratslinger,

Thanks for your response. You responded with:

Your line:

rng_ = sh_.getCellRangeByName(“c2:e7”,“g2:l7”,“t2:l7”) is not correct. See → getCellRangeByName. It is for a single range. The statement will work but will only use the first range. The code below gives a small example of how you might want to break this down but I left any looping to you.

I know of no actual property or method to obtain the count directly so it must be calculated based upon the start/end of rows/columns:

I have noticed that the code line only works for the first rangeaddress, but I used it only for an looklike code, so you get a better understandig for what I am asking.

Sub CountRanges
oSheet=ThisComponent.CurrentController.ActiveSheet
sCellRanges = “c2:e7,g2:l7,t2:l7”
Dim sRangeSplit(3) As String
sRangeSplit = Split(sCellRanges,",")
Rem Get range using first set - use as you may need
oCellRangeByName = oSheet.getCellRangeByName(sRangeSplit(0))
oRangeAddr = oCellRangeByName.getRangeAddress()
iCount = (oRangeAddr.EndRow - oRangeAddr.StartRow +1) * (oRangeAddr.EndColumn - oRangeAddr.StartColumn +1)
Print iCount
End Sub

Although it may not be the easiest to find, the OOME document I directed you to has all this information.

I´m still studying that OOME document but what I really need is a simple listing from methods and its properties that are available for objects.

You use for icount the code: (oRangeAddr.EndRow - oRangeAddr.StartRow +1) * (oRangeAddr.EndColumn - oRangeAddr.StartColumn +1)
But you could also use the code: (oCellRangeByName.rows.count * oCellRangeByName.Columns.count), or am I wrong?

To solve the problem with the string variant I made last week a few subs/functions that perform the nessesary actions, see below:


sub AreaTest()
AreaList(“B1:C2,D1:E2,F1:G2”)
MsgBox AreaCount(“B1:C2,D1:E2,F1:G2”)
MsgBox AreaGet(“B1:C2,D1:E2,F1:G2”, 3)
end sub

'return the n-th range in the area_ string
Function AreaGet(area_ as variant, optional n_ as integer) as string
dim arr_() as string : arr_() = Split( area_, “,”)

if IsMissing(n_) or n_ <= 0 then n_ = 1

AreaGet() = ""
if IsString(area_) then
	if not IsEmpty(arr_) then
		n_ = n_ - 1
		if IsArray(arr_) and ( n_ >= LBound(arr_()) and n_ <= UBound(arr_()) ) then AreaGet() = arr_(n_)
	endif
elseif IsObject(area_) then
else
endif

end function

'return the number of ranges in the area_ string
Function AreaCount(area_ as variant) as integer
dim arr_() as string

AreaCount() = 0
if IsString(area_) then
	arr_() = Split( area_, ",")
	if not IsEmpty(arr_) and IsArray(arr_) then AreaCount() = ( UBound(arr_()) - LBound(arr_()) + 1)
elseif IsObject(area_) then
else
end if

end function

'List all the range address found in the area_ string
sub AreaList(area_ as variant)
dim arr_() as string : arr_() = Split( area_, “,”)
dim i as long
dim s as string : s = “”

if IsString(area_) then
	if not IsEmpty(arr_) then
		if IsArray(arr_) and ( AreaCount(area_) >= 1 ) then
			s = "Areas : " + AreaCount(area_) + chr$(13)
			for i = LBound(arr_()) to UBound(arr_())
				s = s + "Area(" + i + ") : "  + arr_(i) + chr$(13)
			next i
			MsgBox s
			exit sub
		end if
	end if
elseif IsObject(area_) then
else
end if

end sub


function IsString(s_ as variant) as boolean
dim sVar_ as string

IsString() = False
if VarType(s_) = VarType(sVar_) then IsString() = True

end function

@rammy,

I did delete my answer(s) as after re-reading your question it was not clear as to what you actually were attempting. As I have only briefly glanced at this answer I am still not certain but will look again later.

In this answer you state:

…but what I really need is a simple listing from methods and its properties that are available for objects.

and from past experience can say there is nothing which may come close to simple as you note. One method most used is an Object Inspector. Two I have used are MRI & Xray with my preference being MRI. You can find more info on these on this Wiki → Debugging Within LibreOffice.