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
