Rob van Gelder – Page 8 – Daily Dose of Excel

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

Hi Everyone!
Dick has kindly invited me to post for Daily Dose of Excel.

Being my first post I want to share something useful, but not too technical.

One bit of VBA I like is the Immediate If function – IIF.
IIF is basically If-Then-Else logic bundled into a single function.

Here is an example.

Let’s say you have 5 fruit in cells A1 to A5.
Apple Orange Pear Banana Mango

We could test for the existence of Mango as follows:

Sub test_bool_big()
Dim bln As Boolean, rng As Range

bln = False
For Each rng In Range(“A1:A5”)
If rng.Value = “Mango” Then bln = True
Next

If bln = True Then
MsgBox “Mango was found”
Else
MsgBox “Mango was not found”
End If
End Sub

But by using IIF, we can reduce the If-Then-Else block into a single line.

Sub test_bool_small()
Dim bln As Boolean, rng As Range

bln = False
For Each rng In Range(“A1:A5”)
If rng.Value = “Mango” Then bln = True
Next