What is the unit of Excel column width?

Have You already noticed, that row height unit is not the same as column’s width? If yes and You’re curious what is the unit of Excel column width read more to find out!

Some background

Recently I was working on the tool, which main goal is to make previews of the image files. To make it nice and visible I wanted that cells, or to be more precise – columns, to adjust its width to the image width.

Sounds simple, right?

So following that I took the .Width of the image object and wanted to set its value to the column .Width. Unfortunately I received run-time error 1004.

It turned out that .Width property is Read-Only.

Two types of width

After short research I realized, that there is another width property .ColumnWidth. Unfortunately again, this property doesn’t have pixel unit.

One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

So default width of column in Excel, which is 8.43, is the measure of 8 zeros and one apostrophe. Really funny Microsoft, really…

Is this really a joke?

At first I was helpless and did not know what to do. When my brain came back, after that life turning information, I realized that I can solve it with simple mathematical proportion.

.ColumnWidth = i / .Width * .ColumnWidth

Where i is the chosen width value in pixels.

Satisfactory solution

Obtained width was close to set value, but not the same. It wasn’t good enough. I did small research again and found Dick Kusleika article about this case. He read, that doing this equation twice gets You closer to the set value, than one time. Also he proved, with some tests, that in most cases it is enough, but to be sure You get as close as it could ever be, You need 3rd iteration.

Sub f_width(rng As Range, i As Long)

Dim j As Long

With rng
    For j = 1 To 3
        .ColumnWidth = i / .Width * .ColumnWidth
    Next j
End With

End Sub

I was a little shocked, surprised and impressed at the same time.

Results

I did try this theory and also confirm, that 3rd iteration can get You as close as it is possible to the set value. Just try it and You’ll see that magic.

I think this solved the mystery width issue and now You know what is the unit of Excel column width. I hope You will be able to handle with this by yourself.