缩放几列以适合VBA页面 [英] Zoom couple of columns to fit page with VBA

查看：121
本文介绍了缩放几列以适合VBA页面的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我无法在工作表上的Excel中调整列. 我有一个工作表，其中的列从A到CK(每个项目可能有所不同). 我不需要打印A列，但B列必须在所有页面上，B列旁边必须是3列.这样一来，第一页将成为列"B，C:E"，下一页将成为"B，F:H"，依此类推... B列被设置为标题，因此将在每页上打印. >

我的问题是设置刻度.我在做什么:

  • 获取页面大小并转换为磅，从左边距取下，右边距取下=我的可打印区域
  • 获取范围的宽度("B:E")=我的范围以适合页面
  • 将我的可打印区域除以适合的范围，将其乘以100％，然后提取1％以确保其适合

在我的情况下，结果为83，但要适合该页面，结果必须为77.我必须找到我认为的其他数字，但我不知道如何以及该....

我的代码:

If ActiveSheet.Name = "Meterkastlijst" Then
    Dim lngZoom As Long
    Dim lngKolB As Long
    Dim lngPagB As Long
    lngKolB = ActiveSheet.Range("B:E").Width
    If ActiveSheet.PageSetup.PaperSize = xlPaperA4 Then
        lngPagB = CLng(Application.CentimetersToPoints(21)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))
    ElseIf ActiveSheet.PageSetup.PaperSize = xlPaperA3 Then
        lngPagB = CLng(Application.CentimetersToPoints(29.7)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))
    End If
    If lngPagB <> 0 And lngKolB <> 0 Then
        lngZoom = ((lngPagB / lngKolB) * 100) - 1
        With ActiveSheet.PageSetup
            .Zoom = lngZoom
        End With
    End If
End If

不同的宽度:

  • B列:45(319像素)->在Excel中，使用VBA设置
  • 列C:15(109像素)->在Excel中，使用VBA设置
  • D列:30(214像素)->在Excel中，使用VBA设置
  • E栏:20(144像素)->在Excel中，使用VBA设置
  • B-E栏:589点->使用VBA
  • 页面:21厘米(595点)
  • 边距(左右):1.8厘米(50.4分)
  • 打印区域:595-101(100.8)= 494点

上面的数字可以计算出83％，但随后却不适合，当我手动将其设置为77％时，它确实适合，但是如何使用VBA获得该数字呢?我不了解列宽，在Excel中看到的以及在VBA中如何设置(45 + 15 + 30 + 20)与VBA告诉我应该是(589)...

解决方案

列宽单位

列宽以字符，点，厘米/英寸，像素，...为单位

  • 以字符为单位的列宽
    如果通过手动值输入或通过鼠标来设置列宽，则会看到标准字体编号字符的数量".请参考 Microsoft支持了解详情.
    可以在VBA中读取和写入此值:.Range.ColumnWidth = 10.78.
    最大值为255.

  • 以点为单位的列宽
    这是在手动调整列大小时未在GUI中显示的内部值.
    相当于每英寸72点.
    在VBA中，只能读取:.Range.Width

  • 列宽(以像素为单位)
    Excel在普通视图中手动调整列宽的大小时显示列宽(以像素为单位)(在括号中).无法在VBA中直接读取或写入此值.

  • 以厘米或英寸为单位的列宽
    在页面布局视图中进行手动调整大小时，Excel将显示以厘米(或英寸)为单位的列宽，而不是以像素为单位.
    仅此值取决于打印缩放级别！
    可以在VBA中读取测量单位本身:
    Application.MeasurementUnit ' 0 = xlInches, 1 = xlCentimeters, 2 = xlMillimeters

转换公式

这样，您可以检查或验证环境中的所有值:

Dim ScreenResolution As Double
Dim ColumnWidthChars As Double
Dim ColumnWidthPoints As Double
Dim ColumnWidthPixels As Double
Dim ColumnWidthInches As Double
Dim ColumnWidthCentimeters As Double

ScreenResolution = 120  ' normal (96 dpi) or large (120 dpi)
ColumnWidthChars = ActiveSheet.Columns(1).ColumnWidth
ColumnWidthPoints = ActiveSheet.Columns(1).Width
ColumnWidthPixels = (ColumnWidthPoints / 72) * ScreenResolution
ColumnWidthInches = ColumnWidthPoints / 72 * ActiveSheet.PageSetup.Zoom / 100
ColumnWidthCentimeters = ColumnWidthInches * 2.54

Debug.Print ColumnWidthChars, ColumnWidthPoints, ColumnWidthInches, _
    ColumnWidthCentimeters, ColumnWidthPixels

ScreenResolution可以使用API​​函数GetDeviceCaps(hDC, 88)

检索

环绕效果

Excel将工作簿文件中每个相关列的基于字符的.Range.ColumnWidth用小数存储.如果将其设置为100，则将其存储为e. g.
<cols><col min="1" max="1" width="100.77734375" customWidth="1"/></cols> 重新打开此文件后，报告的.ColumnWidth为100，不带小数.

如果您设置了较大的列宽并在普通视图和页面布局视图之间切换，则可能会发现小节之间的差异约为2％(.Range.Width和像素突然改变)-但所有值仍彼此对应根据上述公式.

显示比例依赖性

所有不同的列宽值均独立于Excel的视图缩放级别和/或Windows 10显示比例.

打印缩放依赖性

如果更改打印缩放级别，则仅英寸和厘米值会更改.

但是您会获得更多或更少的列i. e.纸上的点数.
Excel以磅为单位测量.PageSetup.Leftmargin(刻度为每英寸72点).这对应于.Range.Width，它也以点为单位.

示例:如果我将两个纸页边距都设置为5.5厘米，则生成的10厘米A4纸宽度将保持e. G.两列，总.Width约为appr. 283点，对应于72点/英寸. 如果将打印缩放比例设置为83％，则为.Width.最高为340点，而打印缩放比例为30％时，则接近943点.

打印比例缩放

打印缩放系数的计算方式是
WorkSheet.PageSetup.Zoom = (PageWidthInPoints / AllColumnsWidthInPoints) * 100

您的计算似乎是正确的，但我至少会减去2％(请参见上面的舍入效果).

I'm having trouble fitting my columns in Excel on a sheet. I have a sheet with columns from A to CK (can be different per project). I don't need to print column A, but column B has to be on all pages and next to column B has to be 3 columns. So that will make column "B,C:E" on first page, next page "B,F:H", and so on... Column B is set as title, so it will be printed on every page.

My problem is to set the scale. What I'm doing:

  • Take pagesize and translate to points, take off margin left and margin right = my printable area
  • Get the width of range("B:E") = my range to fit the page
  • Divide my printable area by my range to fit, multiply that with 100%, and extract 1% to make sure it will fit

The outcome in my situation is 83, but is has to be 77 to fit the page. I'll have to find other numbers I think, but I don't know how and which...

My code:

If ActiveSheet.Name = "Meterkastlijst" Then
    Dim lngZoom As Long
    Dim lngKolB As Long
    Dim lngPagB As Long
    lngKolB = ActiveSheet.Range("B:E").Width
    If ActiveSheet.PageSetup.PaperSize = xlPaperA4 Then
        lngPagB = CLng(Application.CentimetersToPoints(21)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))
    ElseIf ActiveSheet.PageSetup.PaperSize = xlPaperA3 Then
        lngPagB = CLng(Application.CentimetersToPoints(29.7)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))
    End If
    If lngPagB <> 0 And lngKolB <> 0 Then
        lngZoom = ((lngPagB / lngKolB) * 100) - 1
        With ActiveSheet.PageSetup
            .Zoom = lngZoom
        End With
    End If
End If

Different widths:

  • Column B: 45 (319 pixels) -> in Excel, set with VBA
  • Column C: 15 (109 pixels) -> in Excel, set with VBA
  • Column D: 30 (214 pixels) -> in Excel, set with VBA
  • Column E: 20 (144 pixels) -> in Excel, set with VBA
  • Column B-E: 589 points -> with VBA
  • Page: 21 centimeters (595 points)
  • Margins (left & right): 1.8 centimeters (50.4 points)
  • Print area: 595 - 101 (100.8) = 494 points

With numbers above it calculates 83%, but then it doesn't fit, when I set it manually to 77% it does fit, but how can I get this number with VBA? I don't understand the column widths, what I see in Excel and how I set it in VBA (45+15+30+20) is different from what VBA tells me it should be (589)...

解决方案

Column Width Units

Column width is measured in Characters, Points, Centimeters / Inches, Pixels, ...

  • Column width in Characters
    If you set a column width by manual value input or by mouse, you see the "amount of standard font number characters". Please refer to Microsoft support for details.
    This value can be read and written in VBA: .Range.ColumnWidth = 10.78.
    The maximum value is 255.

  • Column width in Points
    This is an internal value not shown in GUI during manual resize of a column.
    It corresponds to 72 points per inch.
    In VBA it can only be read: .Range.Width

  • Column width in Pixels
    Excel shows the column width in pixels (in parentheses) during manual resize of a column width in normal view. This value can not be read or written directly in VBA.

  • Column width in Centimeters or Inches
    During manual resize within the page layout view Excel shows column width in centimeters (or inches) instead of pixels.
    Only this value depends on print zoom level!
    The measurement unit itself can be read in VBA:
    Application.MeasurementUnit ' 0 = xlInches, 1 = xlCentimeters, 2 = xlMillimeters

Conversion Formulas

By this you may check or verify all values in your environment:

Dim ScreenResolution As Double
Dim ColumnWidthChars As Double
Dim ColumnWidthPoints As Double
Dim ColumnWidthPixels As Double
Dim ColumnWidthInches As Double
Dim ColumnWidthCentimeters As Double

ScreenResolution = 120  ' normal (96 dpi) or large (120 dpi)
ColumnWidthChars = ActiveSheet.Columns(1).ColumnWidth
ColumnWidthPoints = ActiveSheet.Columns(1).Width
ColumnWidthPixels = (ColumnWidthPoints / 72) * ScreenResolution
ColumnWidthInches = ColumnWidthPoints / 72 * ActiveSheet.PageSetup.Zoom / 100
ColumnWidthCentimeters = ColumnWidthInches * 2.54

Debug.Print ColumnWidthChars, ColumnWidthPoints, ColumnWidthInches, _
    ColumnWidthCentimeters, ColumnWidthPixels

ScreenResolution may be retrieved with API function GetDeviceCaps(hDC, 88)

Rounding Effects

Excel stores the character-based .Range.ColumnWidth with decimals for each relevant column in the workbook file. If you set it to 100, it is stored as e. g.
<cols><col min="1" max="1" width="100.77734375" customWidth="1"/></cols> After reopening this file, the reported .ColumnWidth is 100 without decimals.

If you set a large column width and switch between normal view and page layout view, then you may register difference of about 2% between the measures (.Range.Width and pixels suddenly change) - but all values still correspond to each other according to above formulas.

Display Scaling Dependency

All different column width values are independent of Excel's view zoom level and/or Windows 10 display scaling.

Print Zoom Dependency

Only the inch- and centimeter values change, if you change the print zoom level.

But you get more or less columns i. e. amount of points on your paper.
Excel measures .PageSetup.Leftmargin in points (with a scale of 72 points per inch). This corresponds to .Range.Width which is also measured in points.

Example: If I set both paper margins to 5.5 cm, then the resulting A4 paper width of 10 cm holds e. g. two columns with a total .Width of appr. 283 points which corresponds to 72 points/inch. If I set the print zoom to 83 percent a .Width of appr. 340 points is maximum, and at a print zoom of 30 % it's almost 943 points.

Print Scaling

The calculation of a print zoom factor is
WorkSheet.PageSetup.Zoom = (PageWidthInPoints / AllColumnsWidthInPoints) * 100

Your calculation seems to be correct, but I would subtract at least 2 % (see rounding effects above).

这篇关于缩放几列以适合VBA页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆