Calc sum column-widths

English
#9

After finding out how many zeros fit into a page width and if this unit turns out to be precisely enough, zeroes should be just as good or bad as any other unit. I like that approach. This unit seems to be based on zeroes of cell style “Default”. If I change the font size of that stlye, CELL(“width”;A1) adjusts accordingly. This is one of these things that requires hard recalculation [Ctrl+Shift+F9] or a content change in the referenced cell. Changing the column width does not trigger any recalculation.
If your page setup requires A:F being within one page width and your page width takes 100 zeroes, you get a “warning cell” like this
=CELL(“width”;A1)+CELL(“width”;B1)+CELL(“width”;C1)+CELL(“width”;D1)+CELL(“width”;E1)+CELL(“width”;F1)+STYLE(IF(CURRENT()>100;“Warn”))

#4

This macro will place the width of the current cell or of continuous selected cells in the cell of your choice (your “dead” cell). You can put it on your toolbar with Tools-Customize-Toolbars(Tab)-Macros(Category)-RangeWidth once you have placed the macro in your document’s macros.

If you aren’t familiar with adding macros you should seek a fuller description, but in short it is Tools-Macros-Organize Macros-BASIC-Organizer…(Button) then highlight your document, click New… just use Module1 so OK. Then exit out of all of that and use Tools-Macros-Edit Macros… then double click on your document’s name, double click on Standard, double click on Module1, then paste the macro in at the right. (This is not the shortest, but the clearest route for me.)

Sub RangeWidth()
	Dim Doc As Object
	Dim ActiveCells As Object
	Dim Selection As Object
	Dim Width As Long
	Dim Value As String
	Const UnitsPerInch As Integer = 2540
	Const OutputAddress As String = "$A$1"
	
	Selection = ThisComponent.currentSelection
	If Selection.supportsService("com.sun.star.sheet.SheetCell") Or _
		Selection.supportsService("com.sun.star.sheet.SheetCellRange") Then
		Width = Selection.Size.Width
		Value = "Width of current cell or selection is: " & Width & " (" & Round(Width / UnitsPerInch, 2) & "'')"
		'MsgBox Value
		ThisComponent.getCurrentController.ActiveSheet.getCellRangeByName(OutputAddress).String = Value
	End If

End Sub 'RangeWidth

Personally, I think the MsgBox option makes more sense, so just uncomment it and comment out the cell setting line if that makes more sense for you, too. (Use the ’ to comment out, etc.)

#5

This is confusing, the size is not measured in points but 1/100 mm, see also LibreOffice: SheetCellRange Service Reference. (the conversion to inch would be 2540).

#6

If resorting to user code I would prefer a function.
You may check the raw code contained in the attached example.
(Always check alien code for the absence of malign parts in advance of permitting executpon.)
disask71715.ods (12.5 KB)

#8

I knew there would be a little confusion using “points” since that is a typographic measurement, but it didn’t sound like the OP would be concerned; I just wanted to be clear it was not pixels. I’ll dream up something other than TensOfMicrometers and change it.

As an aside, @erAck, if you quote me and correct a detail, perhaps you could include in the quote that I had a comment saying the detail was not official. I mean, we can’t all know the metric system…it’s only used in 200 countries or so.

#10

:joy: yeah right, metric system is unfortunate.

#11

The zero is the essence of the metric system, isn’t it?

#12

But there are zero deniers! Even AD!
And they need to be clever guys. How would they understand otherwise that AM is before, but AD is after, that 12:30 AM is one hour earlier than 1:30 AM, ….
I always look at them standing in awe.
.

1 Like
#25

AM is ANTEmeridian or before the meridian
AD is ANNODomini or after the Death
-and -
12:30 AM (just after midday) is Thirteen hours before 1:30 AM (just after midnight)
For the record, points are the vertical size of the Capitals of the typographic example with 72 points being equal to 1 inch. The width is something totally different - usually referred to as “tracking” and can only be fixed if the font example is non-proportional.

#26

It’s 30 minutes after midnight.

#27

Sadly mistaken that’s 00:30

#17

Many thanks for quite a spectrum of suggestions!
erAck’s mention of WIDTH does indeed reflect my using this CELL function, resulting in a number of unknown units. Why, oh why can this not be refined by the excellent LO programmers to a cell’s property WIDTH in cm/mm? This is currently NOT the case: this function on a cell of 2.25 cm width brings a result=11! What a waste of a potentially useful function. Imagine a further development whereby n columns of equal width have to fit a page!
May I point out that, on my test page, I have a table that fits the page. Below this, I have written in a row of ‘dead’ cells the width of each particular column, the SUM at the end, of course.
LeroyG’s visually adjustment of the column-widths would demand a time-consuming procedure, resulting in finely varied widths.
You are correct, eeigor, in assuming I do not want to scale the sheets, just tables OF VARYING columns to FILL the sheets.

#18

Why, oh why did Excel introduce it that way.

#20

@ALOmgb41238, what’s stopping you from manually adjusting the width of the columns so that the total width of the ranges is the same on different sheets? Wrap text automatically is on.

#21

BTW: Summing column widths and looking for SheetCellRange.Size.Width won’t necessarily yield the same result. Column width isn’t changed when a column gets hidden. In the same way hidden and filtered rows keep their .Height value.

#22

“ask” some cell near the “border” for its …Position.X and …Position.Y -property

#23

Well, it’s just Basic - and it even uses VBAsupport.
However, it should work.

Option Explicit
Option VBAsupport 1

Function cellRangeMeasurement(pRangeRef As Object, _
                              pOrient As String, _
                     Optional pIncludeHidden As Boolean)
cellRangeMeasurement = ":err:"
Dim rg As Object, width As Double, height As Double, _
    uC As Long, uR As Long, c As Long, r As Long
If IsMissing(pIncludeHidden) Then pIncludeHidden = False
On Local Error Goto fail
rg = pRangeRef.cellRange
If pIncludeHidden Then
  width  = 0
  height = 0
  uC = rg.Columns.Count - 1
  uR = rg.Rows.Count    - 1
  For c = 0 To uC
    width = width + rg.Columns(c).Width
  Next c
  For r = 0 To uR
    height = height + rg.Rows(r).Height
  Next r
Else
  width  = rg.Size.Width
  height = rg.Size.Height
EndIf
REM Unit now is cm.
Select Case Ucase(Left(pOrient, 1))
  Case "W"
    cellRangeMeasurement = width/1000
  Case "H"
    cellRangeMeasurement = height/1000
  Case "B"
    cellRangeMeasurement = ""  & (width*0.001) & _
                           "+" & (height*0.001) & "i"
End Select
fail:
End Function

Example document containing the code:
disask71715cellRangeAreas.ods (19.0 KB)

#24

This is one of the “spreadsheet traditionals” which exists for backwards compatibility with other programs. It has to be compatible or dropped.

#28

Perhaps something simple like this
Column Widths.ods (13.6 KB)

#29

Here’s one where the column can automatically structure the desired character width and all that’s necessary is to select the appropriate columns and set them to “Optimal Width” - try it on the sample.
Carefully placing and hiding rows or indeed just concealing the text by colouring it white removes the dross from the bottom of the pages.
Even if the “fake space string” is shorter than the actual maximum cell contents it will make no difference to “Optimal”.
It won’t be millimetre precise with proportional fonts but I suspect an easy “close enough” result would suffice
Column Widths2.ods (16.2 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.