WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 482 reviews for our classroom and online training
Referring to a range using square brackets
This short blog shows that there is a third way to refer to a range, other than using the RANGE or CELLS keywords.

Posted by Andy Brown on 17 December 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Another way to refer to a range in VBA

This website only mentions two ways to refer to a range in VBA, shown here by example:

'select cell A2 by its reference


'select it again as row 1, column 2

Cells(1, 2).Select

However, there is a third way!  You can enclose the cell reference in square brackets:

'the third way


OK, it probably won't change your life, but I confess that I didn't know it was possible until recently. 

Advantages of each method

The Range("...") method has the advantage that it supports autocompletion (intellisense):

Intellisense with Range

This is not to be sniffed at!

The Cells(row, column) method allows you to use a variable for the row and/or the column number, as this example demonstrates:

Sub CreateChessboard()

Dim r As Integer

Dim c As Integer

'create a chessboard effect

For r = 1 To 8

For c = 1 To 8

'colour every other cell

If (r + c) Mod 2 = 1 Then

Cells(r, c).Interior.Color = RGB(200, 200, 200)

End If

Next c

Next r

End Sub

This would produce this effect when run:

A chessboard from VBA

The start of a good game of chess.

The main (only?) advantage of my new-found square bracket notation is brevity.  This code would be hard to read with any other notation:

Sub DrawHouse()

'colour some cells

Union([H1], [C2], [D2], [E2], [F2], [G2], [H2], [I2], [B3], _

[C3], [I3], [J3], [B4], [C4], [D4], [E4], [F4], [G4], _

[H4], [I4], [J4], [B5], [C5], [D5], [E5], [F5], [G5], _

[H5], [I5], [J5]).Interior.Color = RGB(200, 200, 200)

Union([B6], [D6], [E6], [F6], [G6], [H6], [J6], [B7], [C7], _

[D7], [E7], [F7], [G7], [H7], [I7], [J7], [B8], [C8], [D8], _

[E8], [F8], [G8], [H8], [I8], [J8], [B9], [D9], [E9], _

[G9], [H9]).Interior.Color = RGB(200, 200, 200)

Union([J9], [B10], [C10], [D10], [E10], [G10], [H10], [I10], _

[J10]).Interior.Color = RGB(200, 200, 200)

End Sub

To assuage your curiosity, here's what this would colour:

Drawing a house

A house, drawn the long way!

There - now my conscience is clear! 

This blog has 0 threads Add post