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

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.


--

John

johnf202 at hotmail dot com


"robbie de sutter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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
---

Sincerly yours,
Robbie De Sutter

"jaf" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>
> --
>
> John
>
> johnf202 at hotmail dot com
>
>
> "robbie de sutter" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>