Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
This website only mentions two ways to refer to a range in VBA, shown here by example:
'select cell A2 by its reference
Range("A2").Select
'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
[a2].Select
OK, it probably won't change your life, but I confess that I didn't know it was possible until recently.
The Range("...") method has the advantage that it supports autocompletion (intellisense):
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:
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:
A house, drawn the long way!
There - now my conscience is clear!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.