BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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):
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:
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)
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:
'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)
To assuage your curiosity, here's what this would colour:
A house, drawn the long way!
There - now my conscience is clear!