Excel的加载项：单元格的绝对位置 [英] Excel addin: Cell absolute position

问题描述

我怎么能找到绝对坐标在某小区的像素？

How can I find the absolute coordinates in pixels of a certain cell?

我在开发Office 2010的插件（Ribbon UI的），我添加一个新的按钮在功能区一个新的菜单，并按下按钮时，我想获得该单元的屏幕位置。问题是，

I am developing an Office 2010 addon (the Ribbon UI) and I add a new button to a new menu in the Ribbon and when the button is pressed, I want to get the screen position of that cell. The problem is that

Globals.ThisWorkbook.Application.ActiveCell . Top / Left

只给相对于 A1 <位置/ code>电子表格的角落里，而我想相对于屏幕的0,0位置

only give the position relative to the A1 corner of the spreadsheet, while I want the position relative to 0,0 of the screen.

我发现这一点：的How~~V得到的Excel 2003的屏幕X和Y在C＃中细胞，但它的Office 2003，我不完全理解的答案。

I found this: How to get screen X and Y of an Excel 2003 cell in C# but it's for Office 2003 and I don't fully understand the answer.

我使用C＃进行DEVEL，但VB也会做

I'm using C# for devel, but VB will also do.

谢谢！

推荐答案

我发现的这个帖子，其中包含下面使用的API调用。我还想起，你可以得到与Application.Commandbars色带（丝带）高度的高度。所以，在VBA你会怎么做：

I found this post, which contains the API calls used below. I also was reminded that you can get the height of the ribbon with Application.Commandbars("Ribbon").Height. So, in VBA you'd do:

编辑：在回答公式栏和标题高度的问题，我补充说，他们隐藏功能，获取ActiveWindow.Height，然后显示他们和获得新ActiveWindow.Height和数字的区别。该功能现在被调用下面该行转换之前增加了高峰在一起。我觉得它的工作原理，但我没有做大量的测试。

In response the Formula Bar and Headings height issue I added a function that hides them, gets the ActiveWindow.Height, then shows them and gets the new ActiveWindow.Height and figures the difference. That function now gets called in the line below that adds together the heights before converting. I think it works but I didn't do a lot of testing.

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 Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Const LOGPIXELSX As Long = 88
Private Const LOGPIXELSY As Long = 90

Sub CellTopLeftPixels(rng As Excel.Range)
Dim RibbonHeight As Long
Dim TotalTop As Long
Dim TotalLeft As Long

RibbonHeight = Application.CommandBars("Ribbon").Height
TotalTop = (RibbonHeight + GetFormulaBarAndHeadingsHeight + rng.Top) * PixelsPerPointY
TotalLeft = rng.Left * PixelsPerPointX
Debug.Print "Top: "; TotalTop; " Left: "; TotalLeft
End Sub

Function GetFormulaBarAndHeadingsHeight()
Dim ActiveWindowHeightWhenHidden As Long
Dim ActiveWindowHeightWhenShown As Long

Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindowHeightWhenHidden = ActiveWindow.Height
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
ActiveWindowHeightWhenShown = ActiveWindow.Height
GetFormulaBarAndHeadingsHeight = ActiveWindowHeightWhenHidden - ActiveWindowHeightWhenShown
End Function

Function PixelsPerPointX() As Double
Dim hdc As Long
Dim PixPerInchX As Long

hdc = GetDC(0)
PixPerInchX = GetDeviceCaps(hdc, LOGPIXELSX)
PixelsPerPointX = PixPerInchX / 72
ReleaseDC 0, hdc
End Function

Function PixelsPerPointY() As Double
Dim hdc As Long
Dim PixPerInchY As Long

hdc = GetDC(0)
PixPerInchY = GetDeviceCaps(hdc, LOGPIXELSY)
PixelsPerPointY = PixPerInchY / 72
ReleaseDC 0, hdc
End Function

72以上的每英寸点数

The 72 above is the points per inch.

调用它：

Sub test()
CellTopLeftPixels ActiveCell
End Sub

这篇关于Excel的加载项：单元格的绝对位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！