Justifying column width in spreadsheet

[RussR]

I see something called ‘VertJustify’ and ‘VertJustifyMethod’ in ‘com.sun.star.sheet.XSpreadsheet’. I assume this is akin to ‘Columns(“A:A”).EntireColumn.AutoFit’ in VBA for Excel which justifies column A.
I can’t find how to use either of these to justify a column. The recorder produces nothing for this operation. Any help would be appreciated.

[RussR]

Sorry I was using the shortcut way of auto adjusting column widths. Now that I have used ‘Format’>‘Column’>‘Optimal Width’>'OK" the recorder gives me something I can work with. I will post an answer in a little bit once I work it out.

[RussR]

The recorder gave me this:

sub optimalcol
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "aExtraWidth"
args1(0).Value = 254
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args1())
end sub

With no column identified this obviously does not work.
Poking around I got this to work. It does an optimal width on a cell which of course affects the whole column. I pass the cell column and row to the subroutine

sub autoadjustcol(cellcol as string, cellrow as integer)
	dim document   as object
	dim dispatcher as object
	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dim args1(0) as new com.sun.star.beans.PropertyValue
	dim args2(0) as new com.sun.star.beans.PropertyValue	
	'the next two lines moves the focus to a cell
	args1(0).Name = "ToPoint" 
	args1(0).Value = "$” & shcol & “$” & Shrow ‘this could be passed to the subroutine
	'the next two lines set the variables that are in the dialog for Optimal Width
	args2(0).Name = "aExtraWidth"
	args2(0).Value = 254
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())
	
end sub

So far I have not determined how to change “ToPoint” to something pointing to a column. I can find no list of things related to com.sun.star.beans.PropertyValue that even includes “ToPoint”
I did a work around where I find the cell with the max string length in column A and the cell for column B as I dump the array into the spreadsheet. When I set the longest cell to Optimal I have in affect set the column. It works so I am not going to spend any more effort on this. Maybe somebody can point us in the right direction

I was pointed to this solution by ‘newOOo3’.

Sub UnoOptimalCol()
    Dim oFrame as Object
    Dim oDispatcher as Object
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oFrame = ThisComponent.Frame
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "A:A" 
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		oProp(0).Name = "aExtraWidth"
		oProp(0).Value = 254
		oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalColumnWidth", "", 0, oProp())
End Sub

This works correctly.