PointsToScreenPixels Documentation Incorrect

[Lazzaroni]

I ran this little piece of code to test the PointsToScreenPixelsX and
PointsToScreenPixelsY methods. It's better to run the code using a command
button placed on a spreadsheet, otherwise the active window may change to a
window other than the one with the selected cells in, which causes the
methods to return 0.

Dim oCell As Range
For Each oCell In Selection
oCell.Value = "W = " & ActiveWindow.PointsToScreenPixelsX(oCell.Width) & _
", H = " & ActiveWindow.PointsToScreenPixelsY(oCell.Height)
Next

According to the VBA documentation the methods "convert a vertical
measurement from points (document coordinates) to screen pixels (screen
coordinates). Returns the converted measurement as a Long value."

That doesn't appear to be the case. I can see what the pixel size of any
column or row is by adjusting the width using the column and row markings on
the header. Whatever numbers PointsToScreenPixels is returning, they are not
pixels.

In fact, there is an inverse relationship between the number returned and
the actual width or height of the column or row. In other words, if the width
or height is larger, the value returned by PointsToScreenPixels is smaller.

If anyone can help me sort out this mystery and retrieve the REAL width and
height of any given column or row I would greatly appreciate it.

Thanks.

 

[Peter T]

I think you are missing the important "(document coordinates)" though I
agree help is misleading.

Put a button on your sheet and assigned to -

Sub test()
With ActiveWindow
MsgBox .PointsToScreenPixelsX(0) & " " _
& .PointsToScreenPixelsY(0)
End With
End Sub

Copy the button and paste into say row 20000 (wider row headers) and run
again
Also 'Restore' the active window, move it down & to the right and reapeat
the macro. I imagine what the actually functions do will become clear.

For what I think you are looking for, (for most users not using large fonts)
the conversion is 72 points to 96 pixels (ie 3:4), though there's an API to
confirm.

Regards,
Peter T

 

[Gary''s Student]

Consider this small test:

Sub Macro1()

Range("I6").Select
Columns("I:I").ColumnWidth = 10
Rows("6:6").RowHeight = 20


ActiveWindow.Zoom = 100
With ActiveWindow
lWinWidth = .PointsToScreenPixelsX(.Selection.Width)
lWinHeight = .PointsToScreenPixelsY(.Selection.Height)
End With
MsgBox (lWinWidth & " x " & lWinHeight)

ActiveWindow.Zoom = 50
With ActiveWindow
lWinWidth = .PointsToScreenPixelsX(.Selection.Width)
lWinHeight = .PointsToScreenPixelsY(.Selection.Height)
End With
MsgBox (lWinWidth & " x " & lWinHeight)

End Sub


The first msgbox gives:
84 x 151

the second ( with a much smaller cell) gives:
79x145

 

[Lazzaroni]

Peter:

Thanks for your help.

You're right. PointsToScreenPixel is definitely not what I am looking for.

I download images to a spreadsheet as thumbnails. I am trying to streamline
the process as much as possible by determining the maximum width and height
of the image in pixels based on the target cell's dimensions before
downloading the image. I send the size to a client-side thumbnail application
using a .php URL. That way the image is pre-sized for display in the sheet,
and I am not downloading any more than I have to.

For example:
sThumbLink = sURL & Cells(1, 1).Value & "&w=" & lCellWidth & "&h=" &
lCellHeight
ActiveSheet.Shapes.AddPicture(sThumbLink, msoTrue, msoFalse, lCellLeft,
lCellTop, lCellWidth, lCellHeight)

Were you referring to the PixelsPerInch property when you mentioned an "API
to confirm?"

Now if I could just figure out how to change the source path of the
LinkedPicture to that .php URL after the image has been loaded into the
spreadsheet so that if the spreadsheet is closed and reopened, Excel knows to
look on the Internet for my linked image instead of on my hard drive. But
that's a different thread:

http://www.microsoft.com/communitie...ming&tid=6babdee1-2392-439c-8f15-e9f98b066d4e

Thanks again for your help.

 

[Lazzaroni]

Thank you for the suggestion.

When I try your example verbatim, I get "0 x 0" before and after the zoom.

 

[Peter T]

I'm not sure how to get dimensions of an image on a web page. No doubt
there's a way, otherwise maybe download the image to file. Thereafter a
couple of completely different approaches to get its size here -

http://tinyurl.com/2gzgck

(sizes of some other image types besides gif can also be read from file)

Were you referring to the PixelsPerInch property when you mentioned an "API
to confirm?"

I forgot about that, but had in mind something like this

Option Explicit

Public Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long
Public Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long

Private Const LOGPIXELSX As Long = 88&
Private Const LOGPIXELSY As Long = 90&

Function Points2Pixels(ByVal ptX As Single, ByVal ptY As Single, _
pixX As Long, pixY As Long) As Long
Dim hdc As Long
Static bGotDPI As Boolean
Static nX, nY As Long
Const PPI As Long = 72&

' typically nX & nY will return 96

If Not bGotDPI Then
hdc = GetDC(0)
nX = GetDeviceCaps(hdc, LOGPIXELSX)
nY = GetDeviceCaps(hdc, LOGPIXELSY)
hdc = ReleaseDC(0, hdc)
bGotDPI = True
End If

pixX = ptX * nX / PPI
pixY = ptY * nY / PPI

End Function

Sub test()
Dim px As Long, py As Long
Dim wdPnt As Single, htPnt As Single
Dim zm As Long
Dim wdPixels As Long, htPixels As Long

zm = ActiveWindow.Zoom
'may or may not want to cater for zoom depending needs
With Selection
wdPnt = .Width * zm / 100
htPnt = .Height * zm / 100
End With

Points2Pixels wdPnt, htPnt, px, py

MsgBox "points / pixels" & vbCr & _
"Width: " & wdPnt & " / " & px & vbCr & _
"Height: " & htPnt & " / " & py

End Sub

Regards,
Peter T

 

[Peter T]

PointsToScreenPixelsX (&Y) returns the distance in "points" from top left
corner of the window of the function' argument value.

Regards,
Peter T

 

[Gary''s Student]

Thank you
--
Gary''s Student - gsnu200713

PointsToScreenPixelsX (&Y) returns the distance in "points" from top left
corner of the window of the function' argument value.

Regards,
Peter T