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
- Calculate the intersection points of two Circles
- Trackball Rotation using Quaternions
- Create a circle out of three points
- People near you with MySQL
Sorry, comments are closed for this article. Contact me if you want to leave a note.