Why does the units of Range.ColumnWidth not match either points or Centimeters (default unit used)

Question

I am producing a VBA subroutine in an Access database which generates an Excel File however when I modify the column widths using:

Range("A:A").ColumnWidth = ...

Produces mixed results depending on the unit of measurement. Excel's default measurement set in options is Centimeters. I have tried entering the value in points and in cm but the result is either too wide or too small (see below):

Range("A:A").ColumnWidth = 3.07 'In Centimeters - Too small
Range("A:A").ColumnWidth = 87.02 'In Points - Too big

According to the documentation Range.ColumnWidth is set using the measurement in the points unit of measurement (width of 0). Regardless of the value I enter the result isn't the same.

Answer 1

The .ColumnWidth property refers to the number of zeros you can type in a cell in the default font without exceeding the cell's width.

In a new workbook where presumably your default column width is 8.43, type '000000000 in a cell and you'll see that the 9th zero overflows the right cell border by about half a zero.

To set the cell width in points, set .ColumnWidth to points / cell.Width * cell.ColumnWidth. Here's the catch, you have to set it three times to get it close, but it will almost never be exact. So put the .ColumnWidth assignment in a For Next loop and set it three times.

Note that the .Width property is in points and you can't set it. The .ColumnWidth property is in crazy-zero-width-measurement and you can set it. Why it takes three times, I don't know.

See http://dailydoseofexcel.com/archives/2004/06/01/column-widths-in-points/ for some test results on the 'three times' thing.