Set Excel columnWidth Property in Points with VBA

A recent project required a VBA-Macro in Excel to set the column-width to a certain value in points (1/72 of an inch). While the .RowHeight attribute is measured in points, .ColumnWidth is measured in the number of zeros that fit in a column in normal style, which is really the strangest unit I ever came across.

The definition of the normal style can be changed by clicking Home → Cell Styles → Right Click on Normal → Modify:

Now to set the width of a column in points, we can use the read-write attribute .ColumnWidth to set the width and the read-only attribute .width to measure the actual column width in points. In theory, the following code should set the width w of column A in points correctly:

w = 123 ' points
With Columns("A")
    .ColumnWidth = w / .Width * .ColumnWidth
End With

but it does not! There is always a little error. Solutions on the net proposed an incremental solution with a small fixed step-width, which however locked Excel for several seconds if the necessary change was too large. After trying a binary-search I realized the error converges pretty quickly to zero, if the code is executed 2-3 times:

w = 123 ' points
With Columns("A")
    .ColumnWidth = w / .Width * .ColumnWidth
    .ColumnWidth = w / .Width * .ColumnWidth
    .ColumnWidth = w / .Width * .ColumnWidth
End With

Scaling the width for different units

Having the width initially in inches, millimeter or centimeter requires a conversion to points. This can be done with built-in functions like so:

' Width in Centimeter
w = Application.CentimetersToPoints(widthInCentimeter)
' Width in Millimeter
w = Application.CentimetersToPoints(widthInMillimeter / 10)
' Width in Inches
w = Application.InchesToPoints(widthInInches)

You might also be interested in the following

 

Sorry, comments are closed for this article. Contact me if you want to leave a note.