vba 以像素为单位获取excel单元格的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5917182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get size of excel cell in pixels
提问by anderl.heckmaier
I am trying to programatically (C++ but VBA explanations are OK) get the size of an excel cell in pixels. The excel application gui shows the size of the cell as:
Width: 8.28 (160 pixels)Height: 24.6 (41 pixels), Font is Arial 20 pt.
我正在尝试以编程方式（C++ 但 VBA 解释可以）获得 excel 单元格的大小（以像素为单位）。excel 应用程序 gui 将单元格的大小显示为：
宽度：8.28（160 像素）高度：24.6（41 像素），字体为 Arial 20 pt。
Using an excel range I can get the:
ColumnWidth: 8.3, RowHeight: 24.6
Range Width: 96, Range Height 24.6
使用 excel 范围我可以得到：
ColumnWidth: 8.3, RowHeight: 24.6
Range Width: 96, Range Height 24.6
I tried using PointsToScreenPixelsX and PointsToScreenPixelsY for all of the above values but they returned values which didn't match up with what the excel gui said (396for row/cell height, 136for column width and 224for column width).
我尝试将 PointsToScreenPixelsX 和 PointsToScreenPixelsY 用于上述所有值，但它们返回的值与 excel gui 所说的不匹配（行/单元格高度为396，列宽为136，列宽为224）。
Any ideas?
有任何想法吗？
回答by Neil
The conversion from points to pixels depends on your DPI setting. There are 72 points to an inch, so if you have 96 points that's 4/3 of an inch. If your DPI (in Display Properties) is 120 then that works out to 160 pixels.
从点到像素的转换取决于您的 DPI 设置。一英寸有 72 个点，所以如果你有 96 个点，那就是 4/3 英寸。如果您的 DPI（在显示属性中）为 120，则计算结果为 160 像素。
In other words, pixels = points * DPI / 72.
换句话说，pixels = points * DPI / 72。
However, this doesn't take zoom into account. ActiveWindow.Zoomin Excel is a percentage, so for instance 200 is twice normal size. Note that the UI still shows unzoomed pixels.
但是，这并没有考虑缩放。ActiveWindow.Zoom在 Excel 中是一个百分比，例如 200 是正常大小的两倍。请注意，UI 仍显示未缩放的像素。
回答by GlennFromIowa
The OP stated:
OP 表示：
The excel application gui shows the size of the cell as:
Width: 8.28 (160 pixels)Height: 24.6 (41 pixels), Font is Arial 20 pt.
excel 应用程序 gui 将单元格的大小显示为：
宽度：8.28（160 像素）高度：24.6（41 像素），字体为 Arial 20 pt。
First let me clarify: the application guishows column widths and height in a decimal measurement and a pixel measurement, regardless of font size, screen size, zoom, etc. For any of these factors, if the Excel column width is 8.43, it will alwaysbe defined as 64 pixels. Second, I am a little confused, because my version of Excel (2010 currently) and every prior version I can remember had the standard column width of 8.43 equal 64 pixels; likewise, the standard row height of 15 equals 20 pixels, which does not seem to match the OP's example.
首先让我澄清一下：应用程序 gui以十进制度量和像素度量显示列宽和高度，而不管字体大小、屏幕大小、缩放等。对于这些因素中的任何一个，如果 Excel 列宽为 8.43，它将始终定义为 64像素。其次，我有点困惑，因为我的 Excel 版本（目前为 2010）和我记得的每个以前的版本都有 8.43 等于 64像素的标准列宽；同样，标准行高 15 等于 20 pixel，这似乎与 OP 的示例不匹配。
Having established that, one poster asked "Why?" One reason: if you're adjusting column widths or row heights, Excel allows that in discrete unitsthat, unfortunately, they decided to name pixels. Maybe they related to pixels in some early version, but they seem just as random as the units used - 8.43 is what, inches, picas, ??? Not twips, that's for sure! Here, I'll call it a decimal unit.
确定这一点后，一位海报问“为什么？” 原因之一：如果您要调整列宽或行高，Excel 允许以离散单位进行调整，不幸的是，他们决定将其命名为pixel。也许它们与某些早期版本中的像素有关，但它们似乎与使用的单位一样随机 - 8.43 是什么，英寸，派卡，？？？不是缇，这是肯定的！在这里，我将其称为十进制单位。
Anyway, for all column widths over 1.00, that discrete pixelunit is 1/7th of a decimal unit. Bizarrely, column widths under 1.00 are divided into 12 units. Therefore, the discrete widths up to the 2.00 decimal unit are as follows:
无论如何，对于超过 1.00 的所有列宽，该离散像素单位是十进制单位的 1/7。奇怪的是，1.00 以下的列宽被分成 12 个单位。因此，直到 2.00 十进制单位的离散宽度如下：
0.08, 0.17, 0.25, 0.33, 0.42, 0.5, 0.58, 0.67, 0.75, 0.83, 0.92, 1.00,
1.14, 1.29, 1.43, 1.57, 1.71, 1.86, 2.00
with 2.00 equaling 19 pixels. Yes, I'll pause while you shake your head in disbelief, but that's how they made it.
2.00 等于 19像素。是的，当你难以置信地摇头时，我会停下来，但他们就是这样做到的。
Fortunately, row heights appear to be more uniform, with 1 pixelequaling 0.75 decimal units; 10 pixelsequaling 7.50; standard row height of 20 pixelsequaling 15.00; and so on. Just in case you should ever need to convert between these randomly discrete units, here are a couple of VBA functions to do so:
幸运的是，行高看起来更均匀，1 个像素等于 0.75 个十进制单位；10像素等于 7.50；20像素的标准行高等于 15.00；等等。以防万一您需要在这些随机离散单位之间进行转换，这里有几个 VBA 函数可以这样做：
Function ColumnWidthToPixels(ByVal ColWidth As Single) As Integer
Select Case Round(ColWidth, 4) ' Adjust for floating point errors
Case Is < 0:
ColumnWidthToPixels = ColumnWidthToPixels(ActiveSheet.StandardWidth)
Case Is < 1:
ColumnWidthToPixels = Round(ColWidth * 12, 0)
Case Is <= 255:
ColumnWidthToPixels = Round(12 + ((Int(ColWidth) - 1) * 7) _
+ Round((ColWidth - Int(ColWidth)) * 7, 0), 0)
Case Else:
ColumnWidthToPixels = ColumnWidthToPixels(ActiveSheet.StandardWidth)
End Select
End Function
Function PixelsToColumnWidth(ByVal Pixels As Integer) As Single
Select Case Pixels
Case Is < 0:
PixelsToColumnWidth = ActiveSheet.StandardWidth
Case Is < 12:
PixelsToColumnWidth = Round(Pixels / 12, 2)
Case Is <= 1790:
PixelsToColumnWidth = Round(1 + ((Pixels - 12) / 7), 2)
Case Else:
PixelsToColumnWidth = ActiveSheet.StandardWidth
End Select
End Function
回答by Neil
Example
例子
This example determines the height and width (in pixels) of the selected cells in the active window and returns the values in the lWinWidth and lWinHeight variables.
本示例确定活动窗口中选定单元格的高度和宽度（以像素为单位），并返回 lWinWidth 和 lWinHeight 变量中的值。
With ActiveWindow
lWinWidth = PointsToScreenPixelsX(.Selection.Width)
lWinHeight = PointsToScreenPixelsY(.Selection.Height)
End With
