X and Y Screen Coordinates of a Range !!!

[RAFAAJ2000]

Hi all,

Is there any way I can determine the x and y screen coordinates of a given
Worksheet Range ?

Something similar to the 'RangeFromPoint' property of the Window Object
but the other way around .I would call it ' PointFromRange ' !!

I can't seem to get an answer to this anywhere.

Any suggestions would be much appreciated .

Regards.

[keepITcool]

You could hardcode for 96 dpi settings,
better to read it form the system:

Option Explicit

Private Type POINTAPI
x As Long
y As Long
End Type

Private Declare Function SetCursorPos Lib "user32.dll" ( _
ByVal x As Long, ByVal y As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Function DPIfactors()
Static sdArr(1 To 2) As Double
Dim hDC&
If sdArr(1) = 0 Then
hDC = GetDC(0)
sdArr(1) = GetDeviceCaps(hDC, 88) / 72 'Horz
sdArr(2) = GetDeviceCaps(hDC, 90) / 72 'Vert
ReleaseDC 0, hDC
End If
DPIfactors = sdArr
End Function

Sub Test()
With TopLeftPoint(Selection)
SetCursorPos .x, .y
End With
End Sub

Function TopLeftPoint(rng As Range) As POINTAPI
With TopLeftPoint
.x = ActiveWindow.PointsToScreenPixelsX(rng.Left * DPIfactors(1))
.y = ActiveWindow.PointsToScreenPixelsY(rng.Top * DPIfactors(2))
End With
End Function

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

[keepITcool]

note that you need to check that the range intersects with the
activewindow.visiblerange else you should set the scrollrow and
scrollcolumn first.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

keepITcool wrote :

[RAFAAJ2000]

Really impressed !!

Although I don't really understand the use of the 'GetDeviceCaps' or where
the '88' & '90' constantes came from but it works !

I looked up this API function but found it somewhat confusing :(

I am impressed with the level of knowledge on this Forum with regards to Win
API and how it can be succesfully applied to the Excel environment\VBA.

All the API learning material that I have come accross ( Including the most
acclaimed Dan Appleman's textbook ) seem to me rather shallow, abstract or
far too advanced.

Deos anyone know of more step by step VB API learnig material(s) with
plenty of real world working examples for beginners ?

Any suggestions would much appreciated.

Jaafar.
Thanks very much.

[keepITcool]

get yourself 2 tools:

ApiViewer 2004 (syntax w/copy paste)
make sure you install/load the VBE addin)
http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html

ApiGuide (reference and examples)
http://www.mentalis.org/agnet/apiguide.shtml

happy learning!

[RAFAAJ2000]

Hi again,

The code doesn't work as expected when I change the Sheet Zooming !

In other words, the code works only if the Zoom is set to 100 otherwise it
gives erroneous results !

Any Idea how this can be fixed so it always works regardeless of the
current Sheet Zoom ?

Regards.

[keepITcool]

yep.
i agree didnt think about that...

wouldn't it be nice if:

you'd do some research and fiddling yourself
you'd find a solution
you'd give me the refined code.

.. would fit well with "happy learning"
<g>

[RAFAAJ2000]

Ok, after some research and trial & error I got it... I just needed to
multipy the Pixels Per Points by the Zoom percentage as follows :

Sub Test()
With TopLeftPoint(Selection)
MsgBox " ActiveCell Is : " & ActiveWindow.RangeFromPoint(.X,
.Y).Address
End With
End Sub

Function TopLeftPoint(rng As Range) As POINTAPI

hndDC = GetDC(0)
With TopLeftPoint

.X = ActiveWindow.PointsToScreenPixelsX(rng.Left * _
(GetDeviceCaps(hndDC, 88) / 72 * (ActiveWindow.Zoom / 100)))

.Y = ActiveWindow.PointsToScreenPixelsY(rng.Top * _
(GetDeviceCaps(hndDC, 90) / 72 * (ActiveWindow.Zoom / 100)))

End With
ReleaseDC 0, hndDC
End Function

I have a problem though :
The 'PointsToScreenPixels' Argument is supposed to be defined in Points.
However the expression : ' rng.Left *(GetDeviceCaps(hndDC, 88) / 72 '
actually returns Pixels !.......I am still not fully clear about how this
works :(

Again, thanks for your help KeepItCool.

Jaafar.
Regards.

"keepITcool" wrote:

> yep.
> i agree didnt think about that...
>
> wouldn't it be nice if:
>
> you'd do some research and fiddling yourself
> you'd find a solution
> you'd give me the refined code.
>

> ... would fit well with "happy learning"

[keepITcool]

yep
s/b named PointsToScreenPixelsAt72Dpi :)