Ask Your Question

Macro: Listing multiple cell ranges

asked 2019-05-31 01:33:28 +0200

rammy gravatar image

updated 2019-06-10 13:24:55 +0200

Hi all,

I am using Calc, LibreOffice version 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", I would like to use something like the code below:

wb_ = ThisComponent

sh_ = wb_.sheets(0)                                              'get sheet by index number

rng_ = sh_.getCellRangeByName("c2:e7","g2:l7","t2:l7")   'set rangeaddress, only works for first

for i = 0 to rng_.**GetCellranges().Count** - 1

res = res + rng_.**GetCellranges(i).AddressRange**              'show i-th range address

next i

msgbox res                                                                                               'show res

Could somebody give direction for the ,** (bolded), lines, to solve the problem.

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

edit retag flag offensive close merge delete


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

Ratslinger gravatar imageRatslinger ( 2019-06-02 03:36:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-06-10 13:12:05 +0200

rammy gravatar image

updated 2019-06-10 18:07:47 +0200

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_)
elseif IsObject(area_) then

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
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 ...
edit flag offensive delete link more



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.

Ratslinger gravatar imageRatslinger ( 2019-06-10 18:24:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-31 01:33:28 +0200

Seen: 494 times

Last updated: Jun 10 '19