Revision history [back]

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:

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 distracting the pre-set 113 twips = 200 (instead of 180) hundredths of mm (the units used in API) is the proper hack.

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:

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 distracting the pre-set 113 twips = 200 (instead of 180) hundredths of mm (the units used in API) is the proper hack.

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:

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 distracting the pre-set extra 113 twips = 200 (instead of 180) hundredths of mm (the units used in API) is the proper hack.

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 VBAVBA 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 distracting 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...

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 distracting 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...

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.