Column width in pixels how?

[robbie de sutter]

Hello,

How can I determine the column width in pixels in VBA (excel 2002 sp2)?

In a new workbook I try to determine the width of column A.
Clicking between 2 columns displays the following information (provided
by excel - the pixel value is in this case correct):
"Width: 8.43 (64 pixels)"

So I need the value 64 in my VBA code, but HOW?

Allready tried to following:

range("A1").width

returns 48 (?!?!?!) range("A1").Columnwidth
returns 8.43 ActiveWindow.PointsToScreenPixelsX(range("A1").width)
returns 146 ActiveWindow.PointsToScreenPixelsX(range("A1").width)
returns 106

Also I have exactly the same problem when trying to determine the row
height...

Can anybody help me?
thanks in advance,
Robbie De Sutter

 

[jaf]

Hi Robbie,
COLUMNWIDTH: One unit of column width is equal to the width of one character
in the Normal style.
range("A1").Columnwidth

returns 8.43 characters

WIDTH: Returns or sets an object's width, in points.
range("A1").width

returns 48 points. 72 point/inch=.6666"=64 pixels @ 96 pixels/inch

So...

Sub testwidth()
Sheets("sheet3").Activate

screenres = 96 '96/inch

mypoints = Sheets("sheet3").Range("A1").Width
'> returns 48 points

mychars = Sheets("sheet3").Range("A1").ColumnWidth
'> returns 8.43 chars

mypixels = (mypoints / 72) * screenres 'pixel width of column

Debug.Print mypoints, mychars, mypixels
'> returns 48 8.43 64

End Sub

The column width is 48 points or 8.43 characters or 64 pixels.

I couldn't get ActiveWindow.PointsToScreenPixelsX to return anything but
zero.

 

[Robbie De Sutter]

Hello John, all

Thank you very much for your reply. It works like a charm

The ActiveWindow.PointsToScreenPixelsX returns zero when the cell is not
visible (for example when the vba editor is in front of the worksheet). Any
sollution with the ActiveWindow.PointsToScreenPixelsX would have required
that the workbook and worksheet is visible and on top of all other windows,
but your solution is much better.

Meanwhile I've found on the internet the following subroutine to determine
the current DPI setting of the device (in VBA):

---
'define API entries
Private Declare Function CreateICA Lib "gdi32" (ByVal sDriver As String, _
ByVal sDevice As String, ByVal sOut As String, ByVal pDVM As Long) As Long
Private Declare Function DeleteDC Lib "gdi32" (ByVal hDC As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, _
ByVal nIndex As Long) As Long

'retrieve X and Y DPI setting
Sub GetScreenDPI()
Dim hDC As Long
Dim x As Long
Dim y As Long
hDC = CreateICA("DISPLAY", vbNullString, vbNullString, 0)
If (hDC <> 0) Then
x = GetDeviceCaps(hDC, 88) 'screen res x
y = GetDeviceCaps(hDC, 90) 'screen res y
DeleteDC (hDC)
End If
End Sub