Get size of excel cell in pixels

Question

I am trying to programatically (C++ but VBA explanations are OK) get the size of an excel cell in pixels. The excel application gui shows the size of the cell as:
Width: 8.28 (160 pixels) Height: 24.6 (41 pixels), Font is Arial 20 pt.

Using an excel range I can get the:
ColumnWidth: 8.3, RowHeight: 24.6
Range Width: 96, Range Height 24.6

I tried using PointsToScreenPixelsX and PointsToScreenPixelsY for all of the above values but they returned values which didn't match up with what the excel gui said (396 for row/cell height, 136 for column width and 224 for column width).

Any ideas?

Solution 1

The conversion from points to pixels depends on your DPI setting. There are 72 points to an inch, so if you have 96 points that's 4/3 of an inch. If your DPI (in Display Properties) is 120 then that works out to 160 pixels.

In other words, pixels = points * DPI / 72.

However, this doesn't take zoom into account. ActiveWindow.Zoom in Excel is a percentage, so for instance 200 is twice normal size. Note that the UI still shows unzoomed pixels.

Solution 2

The OP stated:

The excel application gui shows the size of the cell as:

Width: 8.28 (160 pixels) Height: 24.6 (41 pixels), Font is Arial 20 pt.

First let me clarify: the application gui shows column widths and height in a decimal measurement and a pixel measurement, regardless of font size, screen size, zoom, etc. For any of these factors, if the Excel column width is 8.43, it will always be defined as 64 pixels. Second, I am a little confused, because my version of Excel (2010 currently) and every prior version I can remember had the standard column width of 8.43 equal 64 pixels; likewise, the standard row height of 15 equals 20 pixels, which does not seem to match the OP's example.

Having established that, one poster asked "Why?" One reason: if you're adjusting column widths or row heights, Excel allows that in discrete units that, unfortunately, they decided to name pixels. Maybe they related to pixels in some early version, but they seem just as random as the units used - 8.43 is what, inches, picas, ??? Not twips, that's for sure! Here, I'll call it a decimal unit.

Anyway, for all column widths over 1.00, that discrete pixel unit is 1/7th of a decimal unit. Bizarrely, column widths under 1.00 are divided into 12 units. Therefore, the discrete widths up to the 2.00 decimal unit are as follows:

0.08, 0.17, 0.25, 0.33, 0.42, 0.5, 0.58, 0.67, 0.75, 0.83, 0.92, 1.00, 
1.14, 1.29, 1.43, 1.57, 1.71, 1.86, 2.00

with 2.00 equaling 19 pixels. Yes, I'll pause while you shake your head in disbelief, but that's how they made it.

Fortunately, row heights appear to be more uniform, with 1 pixel equaling 0.75 decimal units; 10 pixels equaling 7.50; standard row height of 20 pixels equaling 15.00; and so on. Just in case you should ever need to convert between these randomly discrete units, here are a couple of VBA functions to do so:

Function ColumnWidthToPixels(ByVal ColWidth As Single) As Integer
    Select Case Round(ColWidth, 4)      ' Adjust for floating point errors
    Case Is < 0:
        ColumnWidthToPixels = ColumnWidthToPixels(ActiveSheet.StandardWidth)
    Case Is < 1:
        ColumnWidthToPixels = Round(ColWidth * 12, 0)
    Case Is <= 255:
        ColumnWidthToPixels = Round(12 + ((Int(ColWidth) - 1) * 7) _
            + Round((ColWidth - Int(ColWidth)) * 7, 0), 0)
    Case Else:
        ColumnWidthToPixels = ColumnWidthToPixels(ActiveSheet.StandardWidth)
    End Select
End Function

Function PixelsToColumnWidth(ByVal Pixels As Integer) As Single
    Select Case Pixels
    Case Is < 0:
        PixelsToColumnWidth = ActiveSheet.StandardWidth
    Case Is < 12:
        PixelsToColumnWidth = Round(Pixels / 12, 2)
    Case Is <= 1790:
        PixelsToColumnWidth = Round(1 + ((Pixels - 12) / 7), 2)
    Case Else:
        PixelsToColumnWidth = ActiveSheet.StandardWidth
    End Select
End Function