Get X Y screen coordinates of Excel cell/Range

Add-in Express™ Support Service
That's what is more important than anything else

Get X Y screen coordinates of Excel cell/Range
 
nwein




Posts: 577
 Joined: 2011-03-28
I was wondering if there's an easy way to retrieve back the Point (x,y) of the top left cell of a selected range.
I'm talking about the absolute screen coordinates, not the column/row, i.e. it should return a Point object (or two ints specifying the x and y coordinates), not a range or anything like that.
I've found some funky VBA code that can do it somehow, but it's not pretty.
It most likely requires some pinvokes (user32 and gdi32) which I'm fine with, though I thought I'd ask here first before going to the dark side :)
Posted 20 Jul, 2012 17:22:30 Top
nwein




Posts: 577
 Joined: 2011-03-28
Well, I figured I might as well share with you my current solution (which works pretty darn well).
As before, if there's an easier way to do it I'd be happy to hear about it.

(this code all goes into the ADXAddinModule class, i.e. COM Addin): 

[DllImport("gdi32.dll")]
static extern int GetDeviceCaps(IntPtr hdc, int nIndex);
[DllImport("user32.dll")]
static extern IntPtr GetDC(IntPtr hWnd);
[DllImport("user32.dll")]
static extern bool ReleaseDC(IntPtr hWnd, IntPtr hDC);
private const int LOGPIXELSX = 88;
private const int LOGPIXELSY = 90;

private System.Drawing.Point GetCellPosition(Range range)
{
	Worksheet ws = range.Worksheet;
	IntPtr hdc = GetDC((IntPtr)0);
	long px = GetDeviceCaps(hdc, LOGPIXELSX);
	long py = GetDeviceCaps(hdc, LOGPIXELSY);
	ReleaseDC((IntPtr) 0, hdc);
	double zoom = ExcelApp.ActiveWindow    .Zoom;

	var pointsPerInch = ExcelApp.Application.InchesToPoints(1); // usually 72
	var zoomRatio = zoom/100;
	var x = ExcelApp.ActiveWindow    .PointsToScreenPixelsX(0);

	// Coordinates of current column
	x = Convert.ToInt32(x + range.Left * zoomRatio * px / pointsPerInch);

	// Coordinates of next column
	//x = Convert.ToInt32(x + (((Range)(ws.Columns)[range.Column]).Width + range.Left) * zoomRatio * px / pointsPerInch);
	var y = ExcelApp.ActiveWindow    .PointsToScreenPixelsY(0);
	y = Convert.ToInt32(y + range.Top * zoomRatio * py / pointsPerInch);

	Marshal.ReleaseComObject(ws);
	Marshal.ReleaseComObject(range);

	return new System.Drawing.Point(x, y);
}
Posted 20 Jul, 2012 18:46:53 Top
Eugene Astafiev


Guest


Hi Nir,

Thank you for sharing your solution (the code) to other forum readers!

As you may see there is no trivial way for implementing the required functionality. I.e. the Excel Object Model doesn't provide any property or method for this. That is why you chose the right way and use some WinAPI functions.
Posted 22 Jul, 2012 12:31:44 Top