And once again: X and Y Screen Coordinates of a Range

[marsou]

Hi all,

The reason I am posting a new topic is because the thread where I'd
like to ask this question is about 2 month old, hence I cannot do it
there.
I only hope that those who were posting to the thread
http://groups.google.com/group/micro...ing/browse_fr=
m/thread/37e779765319f889/295d146a1df45c75?lnk=3Dst&q=3Dexcel+PointsToScree=
nPixels&rnum=3D4#295d146a1df45c75

will look here too.
However maybe somebody else could help me with this.
In that thread the question was how to get the screen coordinates of a
Range. And the solution was in using PointsToScreenPix=ADelsX and
PointsToScreenPix=ADelsY functions.
Now my problem here is that when I use this functions they are not
returning what I expect them to. For example if I get the screen
coordinates of Cell A1 and put there a user form, I was expecting that
the userform top left point will be at the top left of A1 cell. But it
is positioned a little lower and to the right. If I change the position
of the whole excel window and run the same macros, I expected that this
behaviour will be repeated, but now the form is displayed lower and
more to the right than the previous one - it looks like some
coefficient is multiplied.
I don't understand this, especially when I see from the posts I refered
to above that they didn't have this problem
So did anybody see this kind of behaviour from those functions?
Is it possible that it is because of 2003 excel which I am currently
using?
Or what am I missing here?
I really need help..

Regards,=20
M

[Stephen Bullen]

Hi Marsou,

> Now my problem here is that when I use this functions they are not
> returning what I expect them to. For example if I get the screen
> coordinates of Cell A1 and put there a user form, I was expecting that
> the userform top left point will be at the top left of A1 cell. But it
> is positioned a little lower and to the right. If I change the position
> of the whole excel window and run the same macros, I expected that this
> behaviour will be repeated, but now the form is displayed lower and
> more to the right than the previous one - it looks like some
> coefficient is multiplied.

We explain how to do this on page 325-326 of our book. Basically, you
create a embedded chart object at the range, then immediately delete it.
Excel has a window with a class name of EXCELE, which will be where the
chart object was, so we can use API calls to read the position of that
window (using FindWindowEx to get the handle and GetWindowRect to read
its position), then show the form there.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

[David Welch]

marsou wrote:
> Hi all,
>
> The reason I am posting a new topic is because the thread where I'd
> like to ask this question is about 2 month old, hence I cannot do it
> there.
> I only hope that those who were posting to the thread
> http://groups.google.com/group/micro...5d146a1df45c75
>
> will look here too.
> However maybe somebody else could help me with this.
> In that thread the question was how to get the screen coordinates of a
> Range. And the solution was in using PointsToScreenPix*elsX and
> PointsToScreenPix*elsY functions.
> Now my problem here is that when I use this functions they are not
> returning what I expect them to. For example if I get the screen
> coordinates of Cell A1 and put there a user form, I was expecting that
> the userform top left point will be at the top left of A1 cell. But it
> is positioned a little lower and to the right. If I change the position
> of the whole excel window and run the same macros, I expected that this
> behaviour will be repeated, but now the form is displayed lower and
> more to the right than the previous one - it looks like some
> coefficient is multiplied.
> I don't understand this, especially when I see from the posts I refered
> to above that they didn't have this problem
> So did anybody see this kind of behaviour from those functions?
> Is it possible that it is because of 2003 excel which I am currently
> using?
> Or what am I missing here?
> I really need help..
>
> Regards,
> M
>

You could do this, which I found worked for sitations where the window
is maximised or minimised, and where the worksheet scroll is not in its
topmost position and when you have different sized toolbars.

There are still some magic numbers though, don't know where they came
from. The form name is UserForm1.

Option Explicit

Public Const SPI_GETNONCLIENTMETRICS = 41
Public Const SPI_SETNONCLIENTMETRICS = 42
Public Const LOGPIXELSX = 88
Public Const LOGPIXELSY = 90

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
Public Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Public Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Public Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

private const MAGICX as long = 30
private const MAGICY as long = 48

Public Sub ActiveCellForm()
Dim Target As Range
Dim RangeLeft As Long
Dim RangeTop As Long
Dim SheetLeft As Long
Dim SheetTop As Long
Dim hdc As Long
Dim PixelsX As Long
Dim PixelsY As Long
Dim xlwindow As Long
Dim wndrect As RECT
Dim newForm As UserForm1
Dim nRet As Long

hdc = GetDC(0)
PixelsX = GetDeviceCaps(hdc, LOGPIXELSX)
PixelsY = GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc

Set Target = ActiveCell

xlwindow = FindWindowEx(0, 0, "XLMAIN", Application.Caption)
xlwindow = FindWindowEx(xlwindow, 0, "XLDESK", vbNullString)
xlwindow = FindWindowEx(xlwindow, 0, "EXCEL7", ActiveWindow.Caption)

nRet = GetWindowRect(xlwindow, wndrect)
If nRet <> 0 Then
SheetLeft = wndrect.Left
SheetTop = wndrect.Top

RangeLeft = MAGICX + SheetLeft + PixelsX * (Target.Left -
ActiveWindow.VisibleRange.Left) / 72
RangeTop = MAGICY + SheetTop + PixelsY * (Target.Top -
ActiveWindow.VisibleRange.Top) / 72

Set newForm = New UserForm1
newForm.Show 0
newForm.Left = 72 * RangeLeft / PixelsX
newForm.Top = 72 * RangeTop / PixelsX
End If
End Sub

[marsou]

Guys, thank you very much for your answers and tips!!
I'll surely try those first thing at work

But I'd really like to know what is wrong with the API in my case..
Is it OK that it doesn't work??

Regards,
M

David Welch wrote:
> marsou wrote:
> > Hi all,
> >
> > The reason I am posting a new topic is because the thread where I'd
> > like to ask this question is about 2 month old, hence I cannot do it
> > there.
> > I only hope that those who were posting to the thread
> > http://groups.google.com/group/micro...ramming/brows=
e_frm/thread/37e779765319f889/295d146a1df45c75?lnk=3Dst&q=3Dexcel+PointsToS=
creenPixels&rnum=3D4#295d146a1df45c75
> >
> > will look here too.
> > However maybe somebody else could help me with this.
> > In that thread the question was how to get the screen coordinates of a
> > Range. And the solution was in using PointsToScreenPix=ADelsX and
> > PointsToScreenPix=ADelsY functions.
> > Now my problem here is that when I use this functions they are not
> > returning what I expect them to. For example if I get the screen
> > coordinates of Cell A1 and put there a user form, I was expecting that
> > the userform top left point will be at the top left of A1 cell. But it
> > is positioned a little lower and to the right. If I change the position
> > of the whole excel window and run the same macros, I expected that this
> > behaviour will be repeated, but now the form is displayed lower and
> > more to the right than the previous one - it looks like some
> > coefficient is multiplied.
> > I don't understand this, especially when I see from the posts I refered
> > to above that they didn't have this problem
> > So did anybody see this kind of behaviour from those functions?
> > Is it possible that it is because of 2003 excel which I am currently
> > using?
> > Or what am I missing here?
> > I really need help..
> >
> > Regards,
> > M
> >
>
> You could do this, which I found worked for sitations where the window
> is maximised or minimised, and where the worksheet scroll is not in its
> topmost position and when you have different sized toolbars.
>
> There are still some magic numbers though, don't know where they came
> from. The form name is UserForm1.
>
> Option Explicit
>
> Public Const SPI_GETNONCLIENTMETRICS =3D 41
> Public Const SPI_SETNONCLIENTMETRICS =3D 42
> Public Const LOGPIXELSX =3D 88
> Public Const LOGPIXELSY =3D 90
>
> 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
> Public Declare Function GetDeviceCaps Lib "gdi32" _
> (ByVal hdc As Long, ByVal nIndex As Long) As Long
> Public Declare Function GetWindowRect Lib "user32" _
> (ByVal hwnd As Long, lpRect As RECT) As Long
> Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
> (ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
> ByVal lpszClass As String, ByVal lpszWindow As String) As Long
>
> Public Type RECT
> Left As Long
> Top As Long
> Right As Long
> Bottom As Long
> End Type
>
> private const MAGICX as long =3D 30
> private const MAGICY as long =3D 48
>
> Public Sub ActiveCellForm()
> Dim Target As Range
> Dim RangeLeft As Long
> Dim RangeTop As Long
> Dim SheetLeft As Long
> Dim SheetTop As Long
> Dim hdc As Long
> Dim PixelsX As Long
> Dim PixelsY As Long
> Dim xlwindow As Long
> Dim wndrect As RECT
> Dim newForm As UserForm1
> Dim nRet As Long
>
> hdc =3D GetDC(0)
> PixelsX =3D GetDeviceCaps(hdc, LOGPIXELSX)
> PixelsY =3D GetDeviceCaps(hdc, LOGPIXELSY)
> ReleaseDC 0, hdc
>
> Set Target =3D ActiveCell
>
> xlwindow =3D FindWindowEx(0, 0, "XLMAIN", Application.Caption)
> xlwindow =3D FindWindowEx(xlwindow, 0, "XLDESK", vbNullString)
> xlwindow =3D FindWindowEx(xlwindow, 0, "EXCEL7", ActiveWindow.Captio=
n)
>
> nRet =3D GetWindowRect(xlwindow, wndrect)
> If nRet <> 0 Then
> SheetLeft =3D wndrect.Left
> SheetTop =3D wndrect.Top
>
> RangeLeft =3D MAGICX + SheetLeft + PixelsX * (Target.Left -
> ActiveWindow.VisibleRange.Left) / 72
> RangeTop =3D MAGICY + SheetTop + PixelsY * (Target.Top -
> ActiveWindow.VisibleRange.Top) / 72
>
> Set newForm =3D New UserForm1
> newForm.Show 0
> newForm.Left =3D 72 * RangeLeft / PixelsX
> newForm.Top =3D 72 * RangeTop / PixelsX
> End If
> End Sub

[okaizawa]

Hi,

how about the example in my post in the following thread:

http://groups.google.com/group/micro...a3b66508433a37

--
HTH,

okaizawa


marsou wrote:
> Hi all,
>
> The reason I am posting a new topic is because the thread where I'd
> like to ask this question is about 2 month old, hence I cannot do it
> there.
> I only hope that those who were posting to the thread
>
http://groups.google.com/group/micro...5d146a1df45c75
>
> will look here too.
> However maybe somebody else could help me with this.
> In that thread the question was how to get the screen coordinates of a
> Range. And the solution was in using PointsToScreenPixelsX and
> PointsToScreenPixelsY functions.
> Now my problem here is that when I use this functions they are not
> returning what I expect them to. For example if I get the screen
> coordinates of Cell A1 and put there a user form, I was expecting that
> the userform top left point will be at the top left of A1 cell. But it
> is positioned a little lower and to the right. If I change the position
> of the whole excel window and run the same macros, I expected that this
> behaviour will be repeated, but now the form is displayed lower and
> more to the right than the previous one - it looks like some
> coefficient is multiplied.
> I don't understand this, especially when I see from the posts I refered
> to above that they didn't have this problem
> So did anybody see this kind of behaviour from those functions?
> Is it possible that it is because of 2003 excel which I am currently
> using?
> Or what am I missing here?
> I really need help..
>
> Regards,
> M
>

[marsou]

Hi okaizawa,

Thank you very much for your response!!!!
As a matter of fact your solution solved my problem.
But I have a couple of questions for you and I would highly appreciate
if you could answer me.

The main part of your solution is:
> x =3D ActiveWindow.PointsToScreenPix=ADelsX(0)
> For i =3D 1 To r.Column - 1
> x =3D x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)
> Next
>
>
> y =3D ActiveWindow.PointsToScreenPix=ADelsY(0)
> For i =3D 1 To r.Row - 1
> y =3D y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001)
> Next
>
>
> Me.Left =3D x * 72 / px
> Me.Top =3D y * 72 / py


In numbers.
for cell "A1", Zoom =3D 100 , LOGPIXELSX =3D 96 and LOGPIXELSY =3D 96 I get:
x =3D 28; y =3D 157 from PointsToScreen-s
and after the conversion suggested: x=3D21; y=3D117.75

Here are my questions
what do these numbers mean: 7200, 0.5000001, 72?

And another one: I am returning to my "bete noir"
PointsToScreenPix=ADelsX and PointsToScreenPix=ADelsY functions.
Aren't they supposed to return the coordinates of exact excel
point(even if conversion needed).
I mean why start with 0,0 point and then add all the widths(heigths) up
to selected range?


If you could answer my questions(especially the first one, because I'd
like to know what exactly did solve my problem )...

However thank you VEEEEEEERY much for your help.

Waiting for your answer,
Regards,
M


okaizawa wrote:
> Hi,
>
> how about the example in my post in the following thread:
>
> http://groups.google.com/group/micro...mming/browse_=
frm/thread/5e9e531116f3ed66/d7a3b66508433a37
>
> --
> HTH,
>
> okaizawa
>
>
> marsou wrote:
> > Hi all,
> >
> > The reason I am posting a new topic is because the thread where I'd
> > like to ask this question is about 2 month old, hence I cannot do it
> > there.
> > I only hope that those who were posting to the thread
> >
> http://groups.google.com/group/micro...mming/browse_=
frm/thread/37e779765319f889/295d146a1df45c75?lnk=3Dst&q=3Dexcel+PointsToScr=
eenPixels&rnum=3D4#295d146a1df45c75
> >
> > will look here too.
> > However maybe somebody else could help me with this.
> > In that thread the question was how to get the screen coordinates of a
> > Range. And the solution was in using PointsToScreenPixelsX and
> > PointsToScreenPixelsY functions.
> > Now my problem here is that when I use this functions they are not
> > returning what I expect them to. For example if I get the screen
> > coordinates of Cell A1 and put there a user form, I was expecting that
> > the userform top left point will be at the top left of A1 cell. But it
> > is positioned a little lower and to the right. If I change the position
> > of the whole excel window and run the same macros, I expected that this
> > behaviour will be repeated, but now the form is displayed lower and
> > more to the right than the previous one - it looks like some
> > coefficient is multiplied.
> > I don't understand this, especially when I see from the posts I refered
> > to above that they didn't have this problem
> > So did anybody see this kind of behaviour from those functions?
> > Is it possible that it is because of 2003 excel which I am currently
> > using?
> > Or what am I missing here?
> > I really need help..
> >
> > Regards,
> > M
> >

[okaizawa]

Hi,

"Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)" is equivalent to
Application.Round(ws.Columns(i).Width * (z / 100) * (px / 72), 0)

"* (z / 100)" means zooming.
"* (px / 72)" converts from points to pixels.
"Application.Round( ,0)" rounds to an integer.
this code converts each row and column, not a whole range.

it is just one of hypotheses about the design of PointsToScreenPixelsX
and PointsToScreenPixelsY. I don't know the reason actually.
the exact coordinates have been calculated so far on my pc in this way.

--
HTH,

okaizawa


marsou wrote:
> Hi okaizawa,
>
> Thank you very much for your response!!!!
> As a matter of fact your solution solved my problem.
> But I have a couple of questions for you and I would highly appreciate
> if you could answer me.
>
> The main part of your solution is:
>
>> x = ActiveWindow.PointsToScreenPixÂ*elsX(0)
>> For i = 1 To r.Column - 1
>> x = x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)
>> Next
>>
>>
>> y = ActiveWindow.PointsToScreenPixÂ*elsY(0)
>> For i = 1 To r.Row - 1
>> y = y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001)
>> Next
>>
>>
>> Me.Left = x * 72 / px
>> Me.Top = y * 72 / py
>
>
>
> In numbers.
> for cell "A1", Zoom = 100 , LOGPIXELSX = 96 and LOGPIXELSY = 96 I get:
> x = 28; y = 157 from PointsToScreen-s
> and after the conversion suggested: x=21; y=117.75
>
> Here are my questions
> what do these numbers mean: 7200, 0.5000001, 72?
>
> And another one: I am returning to my "bete noir"
> PointsToScreenPixÂ*elsX and PointsToScreenPixÂ*elsY functions.
> Aren't they supposed to return the coordinates of exact excel
> point(even if conversion needed).
> I mean why start with 0,0 point and then add all the widths(heigths) up
> to selected range?
>
>
> If you could answer my questions(especially the first one, because I'd
> like to know what exactly did solve my problem )...
>
> However thank you VEEEEEEERY much for your help.
>
> Waiting for your answer,
> Regards,
> M
>

[marsou]

Thank you very much, okaizawa

I guess now I need to go and dig into the theory.
72, 96, dpi etc.
I don't know much about that. :")

Thnx again for your help.

Regards,
M

okaizawa wrote:
> Hi,
>
> "Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)" is equivalent to
> Application.Round(ws.Columns(i).Width * (z / 100) * (px / 72), 0)
>
> "* (z / 100)" means zooming.
> "* (px / 72)" converts from points to pixels.
> "Application.Round( ,0)" rounds to an integer.
> this code converts each row and column, not a whole range.
>
> it is just one of hypotheses about the design of PointsToScreenPixelsX
> and PointsToScreenPixelsY. I don't know the reason actually.
> the exact coordinates have been calculated so far on my pc in this way.
>
> --
> HTH,
>
> okaizawa
>
>
> marsou wrote:
> > Hi okaizawa,
> >
> > Thank you very much for your response!!!!
> > As a matter of fact your solution solved my problem.
> > But I have a couple of questions for you and I would highly appreciate
> > if you could answer me.
> >
> > The main part of your solution is:
> >
> >> x =3D ActiveWindow.PointsToScreenPix=ADelsX(0)
> >> For i =3D 1 To r.Column - 1
> >> x =3D x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)
> >> Next
> >>
> >>
> >> y =3D ActiveWindow.PointsToScreenPix=ADelsY(0)
> >> For i =3D 1 To r.Row - 1
> >> y =3D y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001)
> >> Next
> >>
> >>
> >> Me.Left =3D x * 72 / px
> >> Me.Top =3D y * 72 / py
> >
> >
> >
> > In numbers.
> > for cell "A1", Zoom =3D 100 , LOGPIXELSX =3D 96 and LOGPIXELSY =3D 96 I=
get:
> > x =3D 28; y =3D 157 from PointsToScreen-s
> > and after the conversion suggested: x=3D21; y=3D117.75
> >
> > Here are my questions
> > what do these numbers mean: 7200, 0.5000001, 72?
> >
> > And another one: I am returning to my "bete noir"
> > PointsToScreenPix=ADelsX and PointsToScreenPix=ADelsY functions.
> > Aren't they supposed to return the coordinates of exact excel
> > point(even if conversion needed).
> > I mean why start with 0,0 point and then add all the widths(heigths) up
> > to selected range?
> >
> >
> > If you could answer my questions(especially the first one, because I'd
> > like to know what exactly did solve my problem )...
> >
> > However thank you VEEEEEEERY much for your help.
> >=20
> > Waiting for your answer,
> > Regards,
> > M
> >

[marsou]

Ok, I've done my study

And with a little bit of mathematics - here is okaizawa's code update
that allows to put the form at whichever excel coordinate that I want -
without regard to any range:

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 Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90

Sub GetPositionInScreeenPoints(ByVal Left As Double, ByVal Top As
Double, _
ByRef X As Double,
ByRef Y As Double)
Dim hdc As Long
Dim PixelsPerInchX As Long
Dim PixelsPerInchY As Long
Dim PointsPerInch As Long
Dim CurrentZoomRatio As Long

hdc = GetDC(0)
PixelsPerInchX = GetDeviceCaps(hdc, LOGPIXELSX)
PixelsPerInchY = GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc

PointsPerInch = Application.InchesToPoints(1) ' most of the time =
72

CurrentZoomRatio = ActiveWindow.Zoom / 100

X = ActiveWindow.PointsToScreenPixelsX(0)
X = X + Left * CurrentZoomRatio * PixelsPerInchX / PointsPerInch
X = Round(X, 0)
X = X * PointsPerInch / PixelsPerInchX

Y = ActiveWindow.PointsToScreenPixelsY(0)
Y = Y + Top * CurrentZoomRatio * PixelsPerInchY / PointsPerInch
Y = Round(Y, 0)
Y = Y * PointsPerInch / PixelsPerInchY
End Sub

Thank you guys for your help!

Regards,
M