vba 如何以绝对单位设置列宽

How to set column width in absolute units

提问by nateAtwork

I figured it out and posted the problem statement and solution below:

我想通了，并在下面发布了问题陈述和解决方案：

I had been trying to make it so the margins and column widths in a spreadsheet I have created are the same regardless of the user settings so that it prints the same for all the users. The only method I could come up with involved two macros. One macro would measure the rows, columns, and margins and store it in cells on the excel document on a PC where everything is looking and printing like it should. The second macro take those values and set the margins etc to the correct values before the user printed.

我一直试图让我创建的电子表格中的边距和列宽是相同的，无论用户设置如何，以便为所有用户打印相同的内容。我能想到的唯一方法涉及两个宏。一个宏将测量行、列和边距，并将其存储在 PC 上的 excel 文档的单元格中，在那里一切都看起来和打印的一样。第二个宏采用这些值，并在用户打印之前将边距等设置为正确的值。

My problem was that the ColumnWidth function measures rows in units tied to the default font, which can be changed in the user settings. To get around this, I tried to use .width (which measures in pts), but I just learned that this function can measure width but can't set it. But I realized that all I had to do was pick a column to measure in the second macro, and use that measurement and the origional measurement to derive a conversion factor.

我的问题是 ColumnWidth 函数以与默认字体相关的单位测量行，可以在用户设置中更改。为了解决这个问题，我尝试使用 .width （以 pts 为单位），但我刚刚了解到这个函数可以测量宽度但不能设置它。但我意识到我所要做的就是在第二个宏中选择一列进行测量，然后使用该测量值和原始测量值来推导出转换因子。

Here's the column measuring portion of the first Macro (I had a loop originally, but I realized I only need to measure one column):

这是第一个宏的列测量部分（我最初有一个循环，但我意识到我只需要测量一列）：

 'Collumn Width
  .Cells(249, 84).Value = .Cells(1, 1).Width
  For i = 1 To 65
     .Cells(250 + i, 81).Value = .Cells(1, i).ColumnWidth
  Next i

Here is the corrected column width setting portion of the second macro with the appropriate conversion factor:

这是具有适当转换因子的第二个宏的更正列宽设置部分：

'Set Column Widths
'[column width in current font]
'*[correct width of any collumn in pts]/[current width of same column in pts]
For j = 1 To 65
With ActiveWorkbook.ActiveSheet
    .Columns(j).ColumnWidth = .Cells(250 + j, 81).Value * _
        .Cells(249, 84).Value / .Cells(1, 1).Width
End With
Next j

回答by Ron Rosenfeld

I believe the RowHeight property sets the height in terms of points. It is the columnWidth property that sets the width in terms of the default font size.

我相信 RowHeight 属性以点为单位设置高度。columnWidth 属性根据默认字体大小设置宽度。

However, the Width property of the column reads the width in points. You may not be able to set the column width exactly, but here is one way that may get you close, setting height and width to 72 points.

但是，列的 Width 属性以磅为单位读取宽度。您可能无法准确设置列宽，但这里有一种方法可以让您接近，将高度和宽度设置为 72 磅。

Option Explicit
Sub SetWidthHeightPoints()
    Const dWidth As Double = 72
    Const dHeight As Double = 72
    Dim RW As Range, COL As Range
Set RW = ActiveSheet.Rows(1)
Set COL = ActiveSheet.Columns(1)

RW.RowHeight = dHeight
COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth
COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth

MsgBox "Row Height: " & RW.Height & vbLf & "Column Width: " & COL.Width

End Sub

EDIT

编辑

@AlexPeters makes a good suggestion, which can get result in a closer match to the desired result, by putting the columnwidth adjustment into a loop. We'll loop until there's no change, and also put a limit of 10 on the number of loops:

@AlexPeters 提出了一个很好的建议，通过将列宽调整放入循环中，可以使结果更接近所需的结果。我们将循环直到没有变化，并且还将循环次数限制为 10：

Option Explicit
Sub SetWidthHeightPoints()
    Const dWidth As Double = 72
    Const dHeight As Double = 72
    Dim RW As Range, COL As Range
Set RW = ActiveSheet.Rows(1)
Set COL = ActiveSheet.Columns(1)

Dim CNT As Long, lastWidth As Double

RW.RowHeight = dHeight

CNT = 0
Do Until CNT = 10 Or COL.Width = lastWidth
    lastWidth = COL.Width
    COL.ColumnWidth = dWidth / COL.Width * COL.ColumnWidth
    CNT = CNT + 1
Loop

MsgBox "Row Height: " & RW.Height & vbLf & "Column Width: " & COL.Width _
        & vbLf & "Required: " & CNT & " iterations"

End Sub

回答by barryleajo

Use .RowHeight.

使用 .RowHeight。

More info hereand here.

更多信息在这里和这里。