Column width in pixels how?

Discussion in 'Microsoft Excel Programming' started by robbie de sutter, Aug 19, 2003.

  1. 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
     
    robbie de sutter, Aug 19, 2003
    #1
    1. Advertisements

  2. robbie de sutter

    jaf Guest

    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" <> wrote in message
    news:...
    > 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, Aug 19, 2003
    #2
    1. Advertisements

  3. 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" <> wrote in message
    news:%...
    > 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" <> wrote in message
    > news:...
    > > 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

    >
    >
     
    Robbie De Sutter, Aug 20, 2003
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    Excel page width in pixels?

    John, Jan 28, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    924
    Guest
    Jan 28, 2006
  2. Replies:
    0
    Views:
    553
  3. Replies:
    0
    Views:
    505
  4. And1
    Replies:
    0
    Views:
    256
  5. Ag
    Replies:
    4
    Views:
    487
    Guest
    Sep 29, 2007
Loading...

Share This Page