ColumnWidth property vs Width is not consistent

Thanks Tasos for responding. I have actually written a sub that that will set the column width in points to the nearest value possible within the limitations of the ColumnWidth property. This still will NOT set it exactly but it will find the nearest width to the target.

I've included the code below for anyone else that would like to use it. The most iterations that I've seen it take before the nearest value is detected (identified when the AdjustmentFactor = 1) is 5 loops.

The reason for submitting the ticket was either:

1. Have someone identify an error in my logic or
2. Have it confirmed to be a bug and have one of the MVP's submit to Microsoft to get fixed and improve the quality of the software

While workarounds are great and necessary in the short-term, I don't agree they should be the "last word" - we should always be striving to get the product improved.

Public Sub Set_ColumnWidth_In_Points(rng As Range, sizeInPoints, Optional maxIterations As Integer = 10)

Dim i As Integer

Dim NewColWidthPointsRatio As Double, NewColumnWidth As Double, NewWidth As Double

Dim LastColWidthPointsRatio As Double, lastColumnWidth As Double, lastWidth As Double

Dim errorFactor As Double, CWPRFactor As Double, AdjustmentFactor As Double

Const cDebugOn = True

On Error GoTo ErrorHandler

lastColumnWidth = rng.Cells(1, 1).ColumnWidth

lastWidth = rng.Cells(1, 1).Width

If lastWidth = sizeInPoints Then GoTo Cleanup

If lastWidth = 0 Then

    LastColWidthPointsRatio = 0.15

Else

    LastColWidthPointsRatio = rng.Cells(1, 1).ColumnWidth / rng.Cells(1, 1).Width

End If

AdjustmentFactor = 1

If sizeInPoints > 1341.75 Then sizeInPoints = 1341.75   'maximum column width in points

If cDebugOn Then Debug.Print "Current Width = " & lastWidth & ", Target Width = " & sizeInPoints

For i = 1 To maxIterations

    If cDebugOn Then Debug.Print "LastColWidthPointsRatio = " & LastColWidthPointsRatio

    NewColumnWidth = sizeInPoints * LastColWidthPointsRatio

    rng.ColumnWidth = NewColumnWidth

    NewWidth = rng.Width

    If cDebugOn Then Debug.Print i, "Width = " & NewWidth

    If NewWidth = 0 Then GoTo Cleanup

    errorFactor = sizeInPoints / NewWidth

    NewColWidthPointsRatio = rng.Cells(1, 1).ColumnWidth / rng.Cells(1, 1).Width

    CWPRFactor = LastColWidthPointsRatio / NewColWidthPointsRatio

    AdjustmentFactor = 1 / CWPRFactor ' errorFactor

    If cDebugOn Then Debug.Print "Width = " & NewWidth & ", Error factor = " & errorFactor & ", AdjFactor = " & AdjustmentFactor

    LastColWidthPointsRatio = LastColWidthPointsRatio * AdjustmentFactor

    If AdjustmentFactor = 1 Then Exit For

Next

Cleanup:

Exit Sub

ErrorHandler:

MsgBox Err.Number & ": " & Err.Description, vbCritical

Resume Cleanup

Resume

End Sub

Example on how to run the sub Set_ColumnWidth_In_Points worksheets("sheet1").columns(1), 100

Current Width = 44.25, Target Width = 100

LastColWidthPointsRatio = 0.174237288135593

 1            Width = 95.25

Width = 95.25, Error factor = 1.0498687664042, AdjFactor = 1.05024663745826

LastColWidthPointsRatio = 0.182992125984252

 2            Width = 99.75

Width = 99.75, Error factor = 1.00250626566416, AdjFactor = 1.00200156156312

LastColWidthPointsRatio = 0.183358395989975

 3            Width = 99.75

Width = 99.75, Error factor = 1.00250626566416, AdjFactor = 1

Hi,

cell A1, Column Width in points

vba

[Edit..]

Sub Cell_A1_Width_in_Points()

' ### 16/08/2022 ###

Dim r As Range

Dim nPoints As String

Dim N, xx, yy, diaf

Set r = Range("A1")

r.Select

N = r.Column

diaf = 0.1

r.Interior.Color = vbYellow

Do

*nPoints = InputBox("Select a Number [10.5 - 1342.5]", "ColumnWidth in Points", "") '/// 1342.5 points = 255 characters \*

If StrPtr(nPoints) = 0 Or nPoints = vbNullString Then Exit Sub

Loop Until nPoints >= 10.5 And nPoints <= 1342.5

xx = Val(nPoints)

Application.ScreenUpdating = False

While Columns(N + 1).Left - Columns(N).Left - diaf >= xx

Columns(N).ColumnWidth = Columns(N).ColumnWidth - diaf

Wend

While Columns(N + 1).Left - Columns(N).Left + diaf <= xx

Columns(N).ColumnWidth = Columns(N).ColumnWidth + diaf

Wend

Application.ScreenUpdating = True

yy = Replace(r.Address, "$", "")

MsgBox "cell " & yy & ", info" & vbCr & "Column Width nearest to " & nPoints & " points" & vbCr & vbCr & "Cell.width (read only property in points)=" & r.Width _

& vbCr & "Cell.ColumnWidth (in characters)=" & r.ColumnWidth _

& vbCr & "cell width in Pixels=" & r.Width / 0.75

End Sub

This has nothing to do with how it looks on the screen.

Re: using column width in Excel

The column width does not increment by 1/100, but more by ~1/8.

Your spreadsheet on a different computer, with different screen resolution,

graphics board, font size or font will not always look/fit the same.

My advice: Allow extra room and get used to it.

'---
Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)