CODEWITHSUNDEEP
excelvba
Joete
Joete

Reputation: 39

Zoom couple of columns to fit page with VBA

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:

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:

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)...

Upvotes: 3

Views: 1601

Answers (1)

Asger
Asger

Reputation: 3877

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).

Upvotes: 2

Related Questions