[Solved] Get cell width

[Arthas]

How can I get a cell witdh? This is my (broken) code:

Code: Select all

REM  *****  BASIC  *****

Sub Main

Dim Doc As Object
Dim DestSheet As Object
Dim w As Long
 
Doc = StarDesktop.CurrentComponent

DestSheet = Doc.Sheets(1)

w = DestSheet.getCellRangeByName("A1").Width

End Sub

Basic runtime error, Property or method not found: Width

Thanks in advance

[FJCC]

The width is a property of the column, not of a cell. Here is code with two routes to get to a column.

Code: Select all

 oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  
  'Method 1
  oColumns = oObj1.getColumns()
  oObj2 = oColumns.getByName("A")
  nWidth = oObj2.Width
  
  'Method 2
  oCellRangeByName = oObj1.getCellRangeByName("A1")
    oColumns2 = oCellRangeByName.getColumns()
  oObj3 = oColumns2.getByName("A")
  nWidth = oObj3.Width
  

[Arthas]

Can I select two adjacent column, and his total width? There is certainly a better method than this

Code: Select all

 oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  
  'Method 1
  oColumns = oObj1.getColumns()
  oObj2 = oColumns.getByName("A")
  nWidth = oObj2.Width
  
  'Method 2
  oCellRangeByName = oObj1.getCellRangeByName("A1")
    oColumns2 = oCellRangeByName.getColumns()
  oObj3 = oColumns2.getByName("A")
  nWidth = oObj3.Width
  
w = 0
For I = 1 To 2
  w = w + oObj1.Columns(I).Width
Next I
REM now w is colum B width + column C width
  

[FJCC]

I do not know of a better method than iterating over the columns of the cell range. I do that in the code below. Notice that the column index still starts at 0 though the cell range is B1:C1 because I am iterating over the columns of the range not the columns of the Sheet.

Code: Select all

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oRng = oSheet.getCellrangeByName("B1:C1")
oCols = oRng.getColumns()
ColCount = oCols.Count
w = 0
for I = 0 to ColCount - 1
  Column = oCols.getByIndex(I)
  w = w + Column.Width

next I
print w

[Lupp]

There are many kinds of objects which have a .Size property. Some have additional similar properties using different units.
A com.sun.star.Sheet.SheetCellRange has a .Size of type com.sun.star.awt.Size . The used unit is "by concept" 10 µm (0.01 mm), but the relation to the actual size on screen or in print may depend.

Code: Select all

Sub showSizeOf(Optional pObj)
REM Only a primitive demo which can't treat selctions returned as containers.
If IsMissing(pObj) Then pObj = ThisComponent.CurrentSelection
sz = tryGetSize(ThisComponent.CurrentSelection)
If NOT IsNull(sz) Then
 With sz
  MsgBox("(Width;Height)=(" & .Width & ";" & .Height & ")")
 End With
Else
 MsgBox("Selected object has no 'Size'.")
EndIf
End Sub

Function tryGetSize(pObj) As Object
On Local Error Goto fail
tryGetSize = pObj.Size
fail:
End Function

===Editing, @FJCC===
Sorry. I missed to emphasize that

Code: Select all

com.sun.star.sheet.SheetCellRange

is one of the services knowing the .Size property in the above mentioned sense.