How to set column width in absolute units?

Question

I want to make it so the margins and column widths in a spreadsheet are the same regardless of the user settings so that it prints the same for all users.

The method I came up with involves two macros.
One macro to measure the rows, columns, and margins and store it in cells on the Excel document on a PC where everything prints like it should.
The second macro to take those values and set the margins etc. to the correct values before the user prints.

My problem was that the ColumnWidth function measures rows in units tied to the default font, which can be changed in the user settings. To get around this, I tried .width (which measures in pts), but I learned that this function can measure width but can't set it.

I realized that all I had to do was pick a column to measure in the second macro, and use that measurement and the original measurement to derive a conversion factor.

The column measuring portion of the first macro:

 'Collumn Width
  .Cells(249, 84).Value = .Cells(1, 1).Width
  For i = 1 To 65
     .Cells(250 + i, 81).Value = .Cells(1, i).ColumnWidth
  Next i

The column width setting portion of the second macro with the appropriate conversion factor:

'Set Column Widths
'[column width in current font]
'*[correct width of any collumn in pts]/[current width of same column in pts]
For j = 1 To 65
With ActiveWorkbook.ActiveSheet
    .Columns(j).ColumnWidth = .Cells(250 + j, 81).Value * _
        .Cells(249, 84).Value / .Cells(1, 1).Width
End With
Next j

Answer 1

' to save too many loops

' use like ' large movement then small movement

' WidthColumn WidInPts, CI, 5

' WidthColumn WidInPts, CI, 0.5

' '

Sub WidthColumn(ColWid!, ColNum&, ChangeBy!)

' too big

While Columns(ColNum + 1).Left - Columns(ColNum).Left > ColWid

  Columns(ColNum).ColumnWidth = Columns(ColNum).ColumnWidth - ChangeBy

Wend

' too small

While Columns(ColNum + 1).Left - Columns(ColNum).Left < ColWid

  Columns(ColNum).ColumnWidth = Columns(ColNum).ColumnWidth + ChangeBy

Wend

' close to it ' to see result ' Cells(2, ColNum) = Columns(ColNum).ColumnWidth 'Cells(3, ColNum) = ColWid

End Sub

Answer 2

I believe the RowHeight property sets the height in terms of points. It is the columnWidth property that sets the width in terms of the default font size.

However, the Width property of the column reads the width in points. You may not be able to set the column width exactly, but here is one way that may get you close, setting height and width to 72 points.

Option Explicit
Sub SetWidthHeightPoints()
    Const dWidth As Double = 72
    Const dHeight As Double = 72
    Dim RW As Range, COL As Range
Set RW = ActiveSheet.Rows(1)
Set COL = ActiveSheet.Columns(1)

RW.RowHeight = dHeight
COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth
COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth

MsgBox "Row Height: " & RW.Height & vbLf & "Column Width: " & COL.Width

End Sub

EDIT

@AlexPeters makes a good suggestion, which can get result in a closer match to the desired result, by putting the columnwidth adjustment into a loop. We'll loop until there's no change, and also put a limit of 10 on the number of loops:

Option Explicit
Sub SetWidthHeightPoints()
    Const dWidth As Double = 72
    Const dHeight As Double = 72
    Dim RW As Range, COL As Range
Set RW = ActiveSheet.Rows(1)
Set COL = ActiveSheet.Columns(1)

Dim CNT As Long, lastWidth As Double

RW.RowHeight = dHeight

CNT = 0
Do Until CNT = 10 Or COL.Width = lastWidth
    lastWidth = COL.Width
    COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth
    CNT = CNT + 1
Loop

MsgBox "Row Height: " & RW.Height & vbLf & "Column Width: " & COL.Width _
        & vbLf & "Required: " & CNT & " iterations"

End Sub

Answer 3

Use .RowHeight.

More info here and here.