X and Y Screen Coordinates of a Range !!!

R

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

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


RAFAAJ2000 wrote :
 
K

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

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

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

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>


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


RAFAAJ2000 wrote :
 
R

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find x-y coordinates of cell? 2
Mouse X-Y Coordinates 1
Table range coordinates? 0
Range passed to a function - trying to just move through the range 4
Use mouse location to place autoshape via VBA 0
Writing a logic for shape coordinates 6
Setting a code range = coords of the cell containing the function 1
Converting Excel Cell Location into X and Y Coordinates 3

Top