0 votes
in VBA by Expert (911 points)

Application.Left returns the distance in points from the left edge of the screen to the left edge of the main Excel window. Range.Left returns the distance in points from the left edge of column A to the left edge of the range. But between these two is the row header, assuming ActiveWindow.DisplayHeadings is True. The row header's width varies with row number and ActiveWindow.Zoom. How do you determine the width of the row header?

1 Answer

0 votes
by Expert (911 points)
 
Best answer

Try this:

Public Sub Headings_Size(ByRef Height_Points As Single, ByRef Width_Points As Single)
'
' Return parameters Height_Points (column header height in points) and Width_Points (row header width in points)
' The results depend upon current ActiveWindow.Zoom
' WARNING: Do not change ByRef in parameter list above
'
' Sep 2020 by J. Woolley
'
    Dim rC As Range, bSU As Boolean
    Dim xA As Long, xB As Long, yA As Long, yB As Long
    
    Const PixPerPt As Single = 96 / 72
    
    bSU = Application.ScreenUpdating
    If bSU Then Application.ScreenUpdating = False
    With ActiveWindow
        Set rC = .VisibleRange.Cells(1)
        yB = .PointsToScreenPixelsY(rC.Top * PixPerPt)
        xB = .PointsToScreenPixelsX(rC.Left * PixPerPt)
        .DisplayHeadings = Not .DisplayHeadings
        yA = .PointsToScreenPixelsY(rC.Top * PixPerPt)
        xA = .PointsToScreenPixelsX(rC.Left * PixPerPt)
        .DisplayHeadings = Not .DisplayHeadings
    End With
    Height_Points = Abs(yA - yB) / PixPerPt
    Width_Points = Abs(xA - xB) / PixPerPt
    Application.ScreenUpdating = bSU
    
End Sub

This procedure is located in module M_Miscellaneous of My Excel Toolbox.

There are 72 points/inch. Windows normally assumes a logical value of 96 dots/inch for desktop and laptop computer displays, but tablet computers like Surface Pro might have a different value. The module M_DisplayScreen function DisplayScreen("dpiWin") gives the actual value for any Windows computer. See Display Screen Size and Other Metrics for more on this subject.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...