PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Column width in pixels how?

 
 
robbie de sutter
Guest
Posts: n/a
 
      19th Aug 2003
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
 
Reply With Quote
 
 
 
 
jaf
Guest
Posts: n/a
 
      19th Aug 2003
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



 
Reply With Quote
 
 
 
 
Robbie De Sutter
Guest
Posts: n/a
 
      20th Aug 2003
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

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
column pixels vs column width Marc C Microsoft Excel Misc 2 21st Mar 2008 12:21 AM
How do i resize a photo of 320/240 pixels to 240/320 pixels? =?Utf-8?B?QmluZ2Fz?= Microsoft Outlook Discussion 1 28th Nov 2005 05:07 PM
Re: 800*600 pixels and 1024*768 pixels =?Utf-8?B?TWFuc29vcg==?= Microsoft Access Reports 0 8th Sep 2005 11:03 AM
some columns 30 characters 155 pixels others 310 pixels why =?Utf-8?B?dnVyZGVu?= Microsoft Excel Misc 2 26th Apr 2005 03:30 AM
NV40 getting over 7000M pixels/sec (over 7G pixels!) NV55 ATI Video Cards 30 25th Mar 2004 05:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:47 AM.