Can the default value for OptimalWidth of a column be changed?

[als50]

Using Libre Office 6.0.6.2(x64) on Windows 7.

If you think the answer to this is easy, you are probably not understanding the question.

How do you permanently change the default value for the optimal width of a column so that when a basic macro uses the instruction setPropertyValue(“OptimalWidth”, TRUE), 0.1 inches are not added to the column?

I have tried using a template where the default is not selected (in Format->Columns->Optimal Width…). When I then manually select the columns and set them to OptimalWidth, the 0.1 inch is not added. But when a basic macro is run and the columns set to OptimalWidth the 0.1 inch is added.

I have the below work around where I set the width to optimal, get that width, subtract ~ 0.1 inch (it works) but would like to know if there is a way to disable or remove the builtin default of adding 0.1 inches when the Basic instruction setPropertyValue(“OptimalWidth”, TRUE) is used?

oColumn = oSheet.getColumns.getByIndex(i)
oColumn.setPropertyValue("OptimalWidth", TRUE) 
oColumn.setPropertyValue("Width",(oColumn.getPropertyValue("Width")-180)) 

The answer by Mike fully answers my question. There is no way to change the default optimal width from within a Basic macro run from My Macros & Dialogs. I will use the workaround.

WRT 200 being the correct calculation: also correct; however, this results in the column being too narrow. 188 should give an exact fit (if I did the math right) but I used 180 just to be sure.

[mikekaganski]

188 should give an exact fit (if I did the math right)

113 twips = 113 * 1/1440 in = 0.07847(2) * 25.4 mm = 1.993 mm (which gives 199 hundredths of mm when represented as integers). However, both 199 and 200 mm/100ths will give 113 integer twips when rounded back.

[als50]

Mike: Your math is correct, as I stated. However, for my workaround, I am subtracting from optimal width (whose zero width is too narrow). Therefore, I have to subtract less than 200 or the column is too narrow.

[mikekaganski]

Well - this question is actually simple to answer… and I feel like I should think that I am not understanding the question

Setting the “OptimalWidth” property uses the ScTableColumnObj::SetOnePropertyValue implementation, with a call to ScDocFunc::SetWidthOrHeight with nSizeTwips set to STD_EXTRA_WIDTH (equal to 113 twips = 2 mm). This is just an invariant to this method.

On the other hand, when you use UI, you are calling an UNO command .uno:SetOptimalColumnWidth, which is handled in ScCellShell::Execute. It has two modes: when the expected extra width option is passed in the UNO call (then it’s non-interactive mode, used in scripts), and when it’s not passed (interactive mode - what you see in UI). Passing the argument is possible using this code:

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args(0) as new com.sun.star.beans.PropertyValue
args(0).Name = "aExtraWidth"
args(0).Value = 0

dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args())

Well - this works, but only for the selection

Looking through the code, I see that there is only one code path that can be used in macros to define optimized ranges, without the “default extra width” - that is using VBA Autofit method:

Option VBASupport 1
sub tst
  ActiveWorkbook.Worksheets(1).Columns("A:D").Autofit
end sub

This uses 0 for the extra size, and allows to define ranges (columns, or specific cells)… but as it requires option VBASupport 1, it can only be used in document modules (iiuc, option VBASupport has no effect in non-document modules, like those in “My Macros & Dialogs”).

Otherwise, the workaround subtracting the pre-set extra 113 twips = 200 (instead of 180) hundredths of mm (the units used in API) is the proper hack.

EDIT: and I found out that using the zero extra-width is somewhat broken - it gives “column too narrow” (###) display for me in case of e.g. cell value 11… filed tdf#123513.