X and Y Screen Coordinates of a Range !!!
 

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.

X and Y Screen Coordinates of a Range !!!
 

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 :

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.

X and Y Screen Coordinates of a Range !!!
 

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 :



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

X and Y Screen Coordinates of a Range !!!
 

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" wrote:


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 :

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.

X and Y Screen Coordinates of a Range !!!
 

get yourself 2 tools:

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

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


happy learning!

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


RAFAAJ2000 wrote :

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" wrote:


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 :

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.

X and Y Screen Coordinates of a Range !!!
 

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" wrote:

get yourself 2 tools:

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

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


happy learning!

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


RAFAAJ2000 wrote :

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" wrote:


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 :

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.

X and Y Screen Coordinates of a Range !!!
 

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 :

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" wrote:

get yourself 2 tools:

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

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


happy learning!

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


RAFAAJ2000 wrote :

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" wrote:


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 :

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.

X and Y Screen Coordinates of a Range !!!
 

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"
<g


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


RAFAAJ2000 wrote :

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" wrote:

get yourself 2 tools:

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

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


happy learning!

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


RAFAAJ2000 wrote :

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" wrote:


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 :

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.

X and Y Screen Coordinates of a Range !!!
 

yep
s/b named PointsToScreenPixelsAt72Dpi :)

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


RAFAAJ2000 wrote :

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