Tiling Windows

If you’ve ever worked with more than one workbook, or even multiple windows per a workbook, you’ll probably have used Window arrangements.
You can arrange the windows by selecting Arrange from the Window menu.

With VBA you can arrange your windows using:

Windows.Arrange ArrangeStyle:=xlArrangeStyleTiled

Arrangements come in a few styles: xlArrangeStyleCascade, xlArrangeStyleTiled, xlArrangeStyleHorizontal, xlArrangeStyleVertical

For more control over your tiling, you could use the following code:

‘Example best with 3 or more windows
Sub test_windows()
    ActiveWindow.WindowState = xlNormal ‘maximised windows cannot be resized

    ’tile the usable area with 2 columns
   Tiler Windows, 0, 0, Application.UsableWidth, Application.UsableHeight, , 2
End Sub
 
Sub Tiler(ObjColl As Object, OffsetX As Double, OffsetY As Double, _
          UsableWidth As Double, UsableHeight As Double, _
          Optional Rows As Long = 0, Optional Cols As Long = 0)
    Dim i As Long, blnByCols As Boolean
    Dim lngPri As Long, lngSec As Long, lngPriRemainder As Long
    Dim dblPriMax As Double, dblSecMax As Double
    Dim dblPriStart As Double, dblSecStart As Double
    Dim dblPriLen As Double, dblSecLen As Double
 
    If Cols = 0 And Rows = 0 Then Exit Sub
 
    blnByCols = Not Cols = 0
 
    lngPri = IIf(blnByCols, Cols, Rows)
    dblPriMax = IIf(blnByCols, UsableWidth, UsableHeight)
    dblSecMax = IIf(blnByCols, UsableHeight, UsableWidth)
    lngPriRemainder = ObjColl.Count Mod lngPri
    lngSec = -Int(-ObjColl.Count / lngPri)
    dblSecLen = dblSecMax / lngSec
 
    For i = 0 To ObjColl.Count – 1
        If i >= ObjColl.Count – lngPriRemainder Then
            dblPriStart = dblPriMax / lngPriRemainder * ((i Mod lngPri) Mod lngPriRemainder)
            dblPriLen = dblPriMax / lngPriRemainder
        Else
            dblPriStart = dblPriMax / lngPri * (i Mod lngPri)
            dblPriLen = dblPriMax / lngPri
        End If
        dblSecStart = (dblSecMax / lngSec) * Int(i / lngPri)
 
        ObjColl(i + 1).Left = IIf(blnByCols, dblPriStart, dblSecStart) + OffsetX
        ObjColl(i + 1).Top = IIf(blnByCols, dblSecStart, dblPriStart) + OffsetY
        ObjColl(i + 1).Width = IIf(blnByCols, dblPriLen, dblSecLen)
        ObjColl(i + 1).Height = IIf(blnByCols, dblSecLen, dblPriLen)
    Next
End Sub

I’ve made the Tiler procedure generic enough to take any collection of rectangular objects.
The collection must have the Count property and each item must have Left, Top, Width and Height properties.

For example, it can be used against a collection of Charts.

Sub test_charts()
    Tiler ActiveSheet.ChartObjects, 100, 100, 500, 500, 2
End Sub
Posted in Uncategorized

2 thoughts on “Tiling Windows


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *